Loading

Reduce ldf file size

Abdu Rafeeq

15y3.7k

I tried to reduce size of ldf file with the code

alter

@Dbname nvarchar(1000)

BEGIN

DECLARE

@qry nvarchar(4000)

SET

@qry='dbcc shrinkfile('+@Dbname+'_Log,TRUNCATEONLY) '

exec

(@qry)

END

but i got error

"

Cannot perform a shrinkfile operation inside a user transaction. Terminate the transaction and reissue the statement."

how possible?

As

procedure reduceDatabaseSize

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Guest User15y ago
Accepted answer
Try truncating the log before shrinking it:

backup log My_Database with truncate_only
dbcc shrinkfile(My_Database_log, 1)
Mahesh Chand15y ago
Also you can also set in your database settings, how many days worth of log you want to store. That will also reduce the file size.