0

I did the backup of database which is small 2MB. I have transaction log file (LDF file) of 15GB! My database is in Full recovery mode. First I tried to do the backup of transaction log but it failed because of the space on the server. Then I succeeded to do the backup of transaction log and now backup+backup of transaction log have 9MB. statement SELECT name,log_reuse_wait_desc FROM sys.databases; returns now NOTHING instead of LOG_BACKUP which is good.But transaction log is still 15 GB. What should I do to release it?

Veljko
  • 1,708
  • 12
  • 40
  • 80

2 Answers2

1

If you aren't regularly backing up the transaction log, the log fie will continue to grow. If you are willing to risk losing changes since your last backup, change the database recovery model to Simple. My experience has been that SQL Server will shrink the log file automatically in a short while after making this change. If it does not, you may need to issue a manual shrink.

You can switch back to Full recovery afterwards-- but verify you are performing the regularly scheduled log backups required for Full recovery.

There are many resources online about recovery models. Here is an article on MSDN about Backup Under the Full Recovery Model.

Paul Williams
  • 16,585
  • 5
  • 47
  • 82
  • Hi Paul, thank you for your time I did some change in my question. I succeeded to do the backup of transaction log (it is 9MB). I changed the recovery model to Simple. but Log is still 15GB although database is only 2MB. Log does not become smaller although it is Simple model. Do I have to do some restart? – Veljko Jan 29 '15 at 14:33
  • Can you give me instruction how to do the Shrink? Do I have to do the shrink of the database or of the files? – Veljko Jan 29 '15 at 14:36
  • Hi Paul, now in database is the value NOTHING for ClickMobile which is good. But file is still 15GB? Any idea please? – Veljko Jan 29 '15 at 14:40
  • Paul can you please assist me with this what to do? LDF file is stil 15GB:( – Veljko Jan 29 '15 at 15:02
  • You may need to do a 1DBCC SHRINKFILE1 to get it to shrink afterwards. – Paul Williams Jan 29 '15 at 23:17
  • @PaulWilliams I have similar issue but the shrink doesn't work. I have Tlog backups scheduled for every 30 mins 6 AM - 7 PM.even in that window the log reuse column states LOG_BACKUP. I do not want to change the recovery model to fix the issue. WOuld like to know if you can suggest any other options. – RMu Dec 30 '16 at 17:49
1

The log file size on disk doesn't get smaller after a log backup. The reason for this is that the engine assumes that it's likely that it needs to be that size for future use and growing the log file is an expensive (in terms of time) operation. What the log backup does is mark the internal structures (called "virtual log files" or "VLFs") in the log file as available for reuse. You can check the status of the VLFs in a couple of different ways:

  • DBCC SQLPERF('LOGSPACE') will show you how much of the log is internally used as a percentage
  • DBCC LOGINFO will show you the information regarding each VLF in the log file.

All that said, if you know for a fact that it's unlikely that you'll need a 15 Gb log file going forward, you can shrink the file using DBCC SHRINKFILE. There's a lot of good information out there on VLFs in general. This blog post is a good start.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68