SQL Server 2008 log will not truncate

sqlsql-serversql-server-2008

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).

Best Solution

In my situation, I had a 650 MB database with a 370 GB log file in SQL Server 2008. No matter what I tried, I could not get it to shrink down. I tried everything listed as answers here but still, nothing worked.

Finally, I found a very short comment somewhere else that did work. It is to run this:

BACKUP LOG DatabaseName TO DISK = N'D:\Backup\DatabaseName_log.bak'
GO
DBCC SHRINKFILE('MyDatabase_Log', 1)
GO

This caused the log file to shrink from 37 GB down to 1 MB. Whew!