In order to truncate SQL transaction logs, open the SQL Server Management Studio (SSMS), select the desired database (with large transaction log), right click on it, and select Properties from the context menu. Go to Options and switch the database Recovery model to Simple.
The log can fill when the database is online, or in recovery. If the log fills while the database is online, the database remains online but can only be read, not updated. If the log fills during recovery, the Database Engine marks the database as RESOURCE PENDING.
Try one of the 4 suggestion below to fix this issue: Under SQL Server Management Studio set the Maximum File Size under options for Altiris database to Unrestricted File Growth. Increase the Restricted File Growth (MB) size to a larger value creating space for transaction logs to grow. Shrink the log files from task.
I consider myself a very experienced SQL person. But I'm failing to do these two things:
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With