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.
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
SELECT log_reuse_wait, log_reuse_wait_desc FROM sys.databases WHERE NAME='bybox'
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
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).