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

Another easy way to reduce the size of the log file, is to:

• Backup logs
• Full backup of the database
• Shrink the logs file
• Backup logs again
• Shrink the logs file again

In this way you don't have to modify any database parameters and your logs file is 1 MB sized.

Answer 2

SQL Server 2012: I had an issue where no log file (and all were already in SIMPLE recovery) would shrink.

This worked for me... I restarted the SQL Server instance (because I could) and every one of those bad boys shrunk.

Whatever was holding it up from shrinking was released with the restart. This is only good for emergencies (or when it's your server), not a regular long-term solution.

Answer 3

Please run:

SELECT name, log_reuse_wait, log_reuse_wait_desc FROM sys.databases

and see what is the log_reuse_wait for your problem db if it is replication that this is error you need this value 0, 2 or 4