question

srivivek avatar image
srivivek asked

how to shrink log file

for one of the Databases the data file 20GB and log file is 35 GB. I took a full backup and tried to shrink the database but i don't see any changes. how to shrink log file?
dbccshrinklogfile
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
Is the database set to FULL recovery? If so, have you backed up the transaction log part of the database recently? If not, then you won't be able to shrink the log file. See [`BACKUP LOG`][1]. Once you've taken a TLog dump, you may have to wait a bit before shrinking the log, as that last VLF might still be in use. Have a look at `DBCC LOGINFO` to see what's in use. [1]: http://msdn.microsoft.com/en-us/library/ms186865.aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

srivivek avatar image srivivek commented ·
When i took a full backup it came upto 11gb. and i just have 11Gb more free space,when i tries to take log backup it is exceeding 11GB so i canceled the log backup. For this situation can i change it to simple recovery and take full backup and change it again to full recovery,and shrink the log file if i do as above will there be any transactions/data lost
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
You shouldn't lose any data doing that. However, it's not really recommended... Given your circumstances, it's probably the easiest way to go, unless you can get your storage guys to give you some more.
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered
Right-click on the database is SSMS and select Tasks->Shrink->Files Change the File type to Log.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

kevchant avatar image
kevchant answered
Are you using Replication with it, if so might be the case that you haven't replicated data yet which is why the log has not cleared? Also, check to see if you have any transactions which are still running, as they may explain why you can't clear the log, failing that you may want to force a CHECKPOINT by issuing the CHECKPOINT command, but check what the above first...
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

srivivek avatar image srivivek commented ·
we are not using replication,if i run checkpoint will there be any data loss, i think it will just write the completed transactions to data file is it right ?
0 Likes 0 ·
KenJ avatar image KenJ commented ·
manually running a checkpoint will not cause data loss. The full backup will checkpoint automatically.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.