SQL Server 2008 log will not truncate

Question

I consider myself a very experienced SQL person. But I'm 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. I also tried setting the compatibility to SQL Server 2005 (I use this setting as I want to match our production server) and SQL Server 2008.

No matter what I try, the log remains at 1964.25 MB, 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, say, 100 MB 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

How can I fix this problem?

---

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 it's 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

Reading the answers I hardly believe that they are written by DBAs. Basic golden rules:

1. In any database before you performing any maintenance, including shrinking the log, you should perform a full backup.

2. Carry out any database maintenance, including shrinking, when nothing else is happening on that particular database for the whole duration of the maintenance. If it is necessary, suspend non-essencial applications. Always keep it in mind that the healthy database is the soul of any applications interacting with it.

After all this, the following commands for shrinking the database's transaction log always worked fine with me on SQL Server 2005 and later SQL Server versions:

USE DatabaseName
GO
-- Truncate the Transaction log
ALTER DATABASE DatabaseName SET RECOVERY SIMPLE
CHECKPOINT
ALTER DATABASE DatabaseName SET RECOVERY FULL
GO
-- Shrink the Transaction Log as recommended my Microsoft.

DBCC SHRINKFILE ('database_txlogfilelogicalname', [n -size to shrink in MBytes])
GO
 -- Pass the freed pages back to OS control.
DBCC SHRINKDATABASE (DatabaseName, TRUNCATEONLY)
GO
-- Tidy up the pages after shrink
DBCC UPDATEUSAGE (0);
GO
-- IF Required but not essential
-- Force to update all tables statistics
exec sp_updatestats
GO

Answer 2

Try running

DBCC OPENTRAN

to check if there are any open transactions.

Answer 3

I finally found a solution for the logfile shrink problem. All of the previous options did not work for me and did not shrink the logfile to the required size. The solution I found is:

• Backup your database
• Set the recovery mode to simple
• Detach the database with SQL Server Management Studio
• Delete the logfile
• Attach the database without the logfile. In the lower half of the "add screen" you get a line which says that the logfile is missing
• Click remove for this line and OK for the attach
• Set the recovery mode back to full

Answer 4

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 5

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 6

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.