February 1, 2002 at 9:19 am
I have a database that is in Full recovery mode and it appears that the Log file is not being truncated after the backup ( have tried this both through a maintenance plan and using Veritas SQL agent). I only discovered the problem when the Log File had grown to 9GB and ran out of room on the server. Also, I am not "reorganinzing indexes and pages" as I understand that this is a know bug. Any other suggestions?
February 2, 2002 at 6:12 pm
You're using SQL2K? If you only have one log file for the db the easiest way to get cleaned up is to detach the db, delete the log file, then reattach - either via EM or using the sp_attach_single_file_proc. How big the log gets depends on how often you do a transaction log backup. Once it gets big its a bear to shrink. I've had no problems with rebuilding indexes.
Andy
February 3, 2002 at 6:32 pm
Hi
Can you post the backup command? the log (from memory) will not magically shrink backup once its extended. You need to manage this yourself. Check batch data loads etc for a reason why its growing so large.
Look at using:
DBCC SHRINKDATABASE
DBCC SHRINKFILE
Remember this from the BOL: "Log truncation does not reduce the size of a physical log file, it reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log. A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size."
You may also have active transactions at the end of the physical log, therefore preventing the shrink.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
February 3, 2002 at 9:41 pm
Chris makes a good point in that you'll need to shrink the log file after the backup. Here's a link to the Microsoft Knowledge Base article on the subject:
http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q272318
Also, can you expand a bit on the "reorganizing indexes and pages" bug?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 4, 2002 at 9:46 am
Thanks. Have already done the detach to get rid of the 9GB log file. Here is the link to the reference to the reorganize bug http://www.sqlmag.com/Forums/Thread.cfm?CFApp=57&Thread_ID=75310#Message196991
Not sure if this is really related. When the Log File was out of control, it was throwing the DB into single user mode when the back up would fail.
Ended up creating a seperate maintenance plan for each DB. I think that the Log backup was failing for the system DB's and so it was never getting to the truncate.
Appears to be working now. Log File went from 177MB to 124MB today. I do have a batch data load, but have watched this and it only grows the log by 1 to 2 MB. Does the "autoshrink" property apply to logfile as well? What should the proper size be for the log?
Thanks again for the replies!
February 4, 2002 at 11:19 am
Perhaps this is the actual bug (SQL7):
http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q276234
And again for SQL2K:
http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q290622
I was aware of an issue with single user mode if the maintenance plan is set to try and fix minor errors. As far as reorganizing space, I don't believe there are any issues there.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
Edited by - bkelley on 02/04/2002 11:21:27 AM
K. Brian Kelley
@kbriankelley
February 4, 2002 at 1:40 pm
February 4, 2002 at 3:50 pm
The Q article says SQL Server SP 3 has the fix, but the other article doesn't say anything about an SP having the fix for SQL 2K.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 6, 2002 at 1:46 pm
I would be curious what happens if you run
BACKUP LOG [dbnamehere]
WITH TRUNCATE ONLY
and then try to shrink.
February 6, 2002 at 1:49 pm
In SQL Server 7, it depends where the active portion of the log is. If it's towards the end, there won't be much shrinkage. In SQL Server 2000, dummy transactions happen to move the log to the front and then truncates the inactive portion with the DBCC SHRINKFILE-BACKUP LOG-DBCC SHRINKFILE combination.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 7, 2002 at 4:52 am
Ah yes but if he looks in scripts for Forcibly Shrink the Transaction Log which someone graciously posted he will find a method that works quite well in shrinking the logfile. I use it after my last differential of the week the night before the first full backup and it has worked for me on SQL 7.
February 7, 2002 at 8:23 am
Correct. It is necessary on SQL 7. SQL 7 doesn't do the dummy records automatically.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
August 8, 2002 at 6:52 am
Another possible solution is to change the recovery mode to simple and do a backup and then truncate file.
Rob
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply