Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database which had mdf size of 350 MB and ldf size 4.9 GB
When i try to shrink the log file it's not shrinking. I know shrinking a database is not good and it should not be done. But still i am trying to do it for shrinking the log file. The recovery model is set to FULL.

Then i followed some steps:

When I run

dbcc SQLPerf(logspace)

i found that logsize is 4932 MB and Log space used is 98.76%
So large amount of (98%) of log is using now.
Then I tried this command

use <databasename> dbcc loginfo

Now almost all VLF has status 2 which means all are in use.
then I tried to take log backup. After log backup also shrinking didnt reduce the size. Then i changed recovery model to simple and then tried shrinking.But this also didn't help. Also I checked for open transaction

dbcc opentran (database)

and found that no transaction is open now. So what is making the database which does not allow me to shrink the log size.How can i solve this?

5 Answers
5

Read How to Shrink SQL Server log for an explanation how the circular nature of the log may prevent shrink after truncation. Is possible that you log's last LSN point into a VLF that is at the tail of the LDF. Counter intuitively you must advance the log, by generating log writes, to allow it to shrink.

I know how to do backup and truncate the log and reduce log file size. But for this database i am having problem.I just ran query select log_reuse_wait_desc from sys.databases where name = 'dbname' and found that replication is causing the problem .But i dont have replication set on it. So how to remove the repliaction from this db which is shown in log reuse wait_desc?
–
NavaneethApr 30 '13 at 12:44

Which SQL Server version are you using?
–
Toni KostelacApr 30 '13 at 12:58

Replication might be set as a Job, so open up the SQL Server Agent folder, and Expand the Jobs folder, check to see if there is a replication job set up and if so turn it off by right-clicking and selecting Stop job
–
Toni KostelacApr 30 '13 at 13:01

I have found that I have to perform 2 or 3 backups of both the database and the transaction log to get the transaction log to actually reduce in size. I have a database that was created with Full recovery model. Every night it performs backups of the database and the transaction log but inevitably the transaction log seems to continually grow over 2-3 weeks. When the remaining disk space gets to 1GB I will see that the transaction log is about 30GB. I followed the steps recommended by Microsoft and after the 4th or 5th iteration of backing up both the database and the transaction log the transaction log will finally release its extra space and shrink. Then I go back and delete the multiple backups I have created.

I think you are doing something wrong. If you take backup of log properly then unused log should be truncated. The commands given in my question may help you to solve the problem.
–
NavaneethJan 7 '14 at 6:40