Sql server 2008 log will not truncate. Driving me CRAZY!

Question

I consider myself a very experienced SQL person. But I'm and failing to do these two things:

• Reduce the size of the allocated log.

• Truncate the log.

  DBCC sqlperf(logspace)

returns:

Database Name   Log Size (MB) Log Space Used (%) Status
ByBox    1964.25  30.0657   0

The following does not work with SQL 2008

DUMP TRANSACTION ByBox WITH TRUNCATE_ONLY

Running the following does nothing either

DBCC SHRINKFILE ('ByBox_1_Log' , 1)
DBCC shrinkdatabase(N'bybox')

I've tried backups. I've also tried setting the properties of the database - 'Recover Model' to both 'FULL' and 'SIMPLE' and a combination of all of the above. Also tried setting the compatibility to SQL 2005 (I use this setting as I want to match our production server) and SQL 2008.

No matter what I try, the log remains at 1964.25Mb, with 30% used, which is still growing. I'd like the log to go back down near 0% and reduce the log file size to a say 100Mb which is plenty. My database must hate me, it just ignores everything I ask it to do regarding the log.

One further note, the production database has quite a few replicated tables, which I turn off when I perform a restore on my development box by using the following:

-- Clear out pending replication stuff
exec sp_removedbreplication
go
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL,
     @numtrans = 0, @time = 0, @reset = 1
go

Trying:

SELECT log_reuse_wait, log_reuse_wait_desc
FROM sys.databases
WHERE NAME='bybox'

Returns

log_reuse_wait  log_reuse_wait_desc
0   NOTHING

Any words of wisdom or things to try are all welcome!

---

Looking at this and setting the recovery model to FULL I have tried the following:

USE master
GO

EXEC sp_addumpdevice 'disk', 'ByBoxData', N'C:\<path here>\bybox.bak'

-- Create a logical backup device, ByBoxLog.
EXEC sp_addumpdevice 'disk', 'ByBoxLog', N'C:\<path here>\bybox_log.bak'

-- Back up the full bybox database.
BACKUP DATABASE bybox TO ByBoxData

-- Back up the bybox log.
BACKUP LOG bybox TO ByBoxLog

which returned:

Processed 151800 pages for database 'bybox', file 'ByBox_Data' on file 3.
Processed 12256 pages for database 'bybox', file 'ByBox_Secondary' on file 3.
Processed 1 pages for database 'bybox', file 'ByBox_1_Log' on file 3.
BACKUP DATABASE successfully processed 164057 pages in 35.456 seconds (36.148 MB/sec).

Processed 2 pages for database 'bybox', file 'ByBox_1_Log' on file 4.
BACKUP LOG successfully processed 2 pages in 0.056 seconds (0.252 MB/sec).

Perfect! But its not.

And DBCC SHRINKFILE ('ByBox_1_Log' , 1) now returns with

DbId    FileId CurrentSize MinimumSize UsedPages EstimatedPages
7   2 251425 251425 251424 251424

and DBCC SQLPERF(LOGSPACE) still reports 30% usage. I think I may have to resign myself to the fact there could well be a bug in SQL Server 2008, or that my log file has been corrupted in some manner. However, my database is in good working order, which leads me to think there is a bug (shudders at the thought).

Answer 1

I know what you mean - SQL server can be a bit maddening that way. Here is some sample code to try out. In essence, it truncates the log file and then tries to shrink the file. Let me know how it works. One other thing...you wouldn't have uncommitted transactions, would you?

Use YourDatabase
GO

DBCC sqlperf(logspace)  -- Get a "before" snapshot
GO  

BACKUP LOG BSDIV12Update WITH TRUNCATE_ONLY;  -- Truncate the log file, don't keep a backup
GO

DBCC SHRINKFILE(YourDataBaseFileName_log, 2);  -- Now re-shrink (use the LOG file name as found in Properties / Files.  Note that I didn't quote mine).
GO

DBCC sqlperf(logspace)  -- Get an "after" snapshot
GO

Update: Simon notes that he is getting an error on the BACKUP command. I didn't realize that "Truncate_only" has been discontinued in SQL Server 2008 when I answered earlier. After a bit of research, the recommended steps to shrink the log file is to (a) Change the Recovery Model to Simple and then (b) shrink the file using DBCC ShrinkFile as above. Unfortunately, you mention that you already tried setting the recovery model to Simple so I assume that you also ran the DBCC Shrinkfile afterward. Is this correct? Please let me know.

Answer 2

This can be a pain, and there are many things it could be. The first thing you should make sure of is that there is not a "stuck" transaction. If you have a transaction that never closes, you cannot ever shrink the log. Run "DBCC OPENTRAN" to find the longest running transaction.

Also, make sure you reorganize (I think that's the proper term) and move everything to the beginning of the file before shrinking.

Answer 3

Try running

DBCC OPENTRAN

to check if there are any open transactions.

Answer 4

I've always hated the way SQL Server handles the physical shrinking of log files. Please note that I've always done this via Enterprise Manager/SQL Server Management Studio, but it seems that when you shrink/truncate the log file, the physical size of the log file will not reduce until after doing a full backup on the database's data file, and then backing up the log file again. I could never nail down the exact pattern, but you could try and see what the exact sequence is. However, it has always involved doing a full backup of the data file.

Answer 5

I have finally come to the conclusion that there is a bug in SQLServer 2008.

I've tried everything, and every combination I can think of. I've backed up the database, dropped it, re-created it, restored it. Exact same problem.

I also ran:

DBCC CHECKDB
DBCC UPDATEUSAGE (bybox)

And all checks out ok.

Roll on the next service pack is all I can say.

Answer 6

Found the solution!

I added a load of data to the database, so the log was forced to expand. I then removed the uneeded data to get my database back to how it was.

Backup and voila, a perfect 0% log.

So the solution is to make the log expand.

Answer 7

Found here : http://technet.microsoft.com/en-us/library/ms189493.aspx

The following example shrinks the log file in the AdventureWorks database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.

USE AdventureWorks; GO -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE; GO -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (AdventureWorks_Log, 1); GO -- Reset the database recovery model. ALTER DATABASE AdventureWorks SET RECOVERY FULL; GO

Answer 8

Beware of the implications of changing recovery models!!

And now for one more sobering thought for all you production DBAs thinking about using the script:

BEFORE YOU CHANGE THE RECOVERY MODEL FROM FULL TO SIMPLE... there's no worries if you're in a development/QA environment. But if you're in a production environment where you're responsible to ensure full recovery of data in the event of an issue, you may want to take a closer look at what BOL says regarding doing this (see BOL under: "Managing Databases">"Transaction Log Management">"Recovery Models and Transaction Log Management"):

A database can be switched to another recovery model at any time. However, switching from the simple recovery model, is unusual. Be aware that if you switch to the full recovery model during a bulk operation, the logging of the bulk operation changes from minimal logging to full logging, and vice versa.

After Switching from the Simple Recovery Model If you switch from the full or bulk-logged recovery model to the simple recovery model, you break the backup log chain. Therefore, we strongly recommend that you back up the log immediately before switching, which allows you to recover the database up to that point. After switching, you need to take periodic data backups to protect your data and to truncate the inactive portion of the transaction log.

After Switching to the Simple Recovery Model If you switch from the full or bulk-logged recovery model to the simple recovery model, you break the backup log chain. Therefore, we strongly recommend that you back up the log immediately before switching, which allows you to recover the database up to that point. After switching, you need to take periodic data backups to protect your data and to truncate the inactive portion of the transaction log.

Really? "we strongly recommend that you back up the log immediately before switching, which allows you to recover the database up to that point." I cannot understand why this little tidbit of information is hidden in a section named "After Switching to the Simple Recovery Model" making most "normal people" think they can go ahead and switch it and then continue or come back and read this after changing it.

Rant

To Microsoft: So, please correct me if I'm wrong, if I fail to do the t-log backup BEFORE changing from FULL to SIMPLE and lo and behold my database gets corrupted somehow (ever heard of Murphy's Law?) right before I'm able to take a backup... then I'm screwed, right? If switching the recovery model of my *production* database from FULL to SIMPLE is something that can break the backup log chain such that if I fail to take a transaction log backup before doing it (like it suggests above) I'm potentially going to lose data, then WHY THE HECK AREN'T YOU HIGHLIGHTING THAT IN A BLINKING MARQUEE MAKING IT A BIGGER DEAL than you seem to be??! You should literally be grabbing me by the shirt and slaping me to get my attention (so to speak) and warning me of the importance of this UPFRONT!!