October 4, 2006 at 6:11 am
Hi,
I have a database with a 16GB transaction log (I believe because this database is in full recovery mode, but the transaction log has not been backed up for ages, if at all). I have been reading BOL to get a handle on shrinking the physical logfile, and have done a transaction log backup plus a DBCC SHRINKFILE on the transaction log, yet the physical file remains the same size.
I would like to know if there is any way to view the current structure of the transaction log to find out the values of things like MinLSN, number and size of virtual log files, etc, to hopefully "see" why the physical transaction log file is not shrinking. Are there any native command, documented or undocumented, or other utilities which can give me what I'm looking for?
Many thanks in advance,
Dave.
October 4, 2006 at 7:14 am
Hi Dave,
you can use dbcc loginfo to get some details relating to the logfile structure. A Status of 2 indicates the active part of the log.
The reason why the log file isn't shrinking is due to the physical structure of the log file and the position of the virtual files within in. SQL Server won't shrink the log beyond the active part of the log so if the active part of the log is near the end of the physical file structure this is why it isn't shrinking.
So what you want to do is make the active part of the log wrap around to the beginning of the file. You can do this by generating enough dummy transactions such that the active part of the log reaches the very end of the file and therefore wraps around.
Check out Transaction Log Architecture in BOL for a better description of the log architecture. But basically, what you want to do is make the end of the logical log be near the beginning of the physical file.
Hope that helps,
October 4, 2006 at 7:41 am
Hi Karl,
Thanks for your response. I've read the parts of BOL to which you refer several times in the last couple of days so I think I understand what's going (I'm very new to SQL).
I ran DBCC LOGINFO against this database and it returned 388 rows of information. The first 372 rows all have a status of 2. The next 11 rows have a status of 0. The final five rows have a status of 2. What does this mean in plain english? Is the information produced by DBCC LOGINFO documented anywhere?
This database is actually the database for our Navision system, which I do not use. I would be unsure how to generate dummy transactions against this database. Would it be a case of asking one of our Navision users to sit there and "use" the software, simply adding unwwanted information? How would I be able to ascertain that the active part of the virtual log had wrapped around?
Again, many thanks in advance.
Dave.
October 4, 2006 at 7:48 am
If you don't need to use transactions or transaction log backups for recovery then switch the database to simple mode. You should find reducing the size of the log device easy then. Issue a checkpoint command prior to the shrink.
you might also find dbcc shrinkfile(2) where 2 is the filenumber of the log works best. do sp_helpfile to get the file numbers.
failing that - backup database - change mode to simple - clear log - put back to full recovery - backup database. schedule regular tran log backups to overwrite ( if you don't really want them )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 4, 2006 at 7:55 am
Hi Dave,
it's easier to explain with a diagram but basically, rows with a status of 2 are active and cannot be shrunk. Rows with a status of 0 can be shrunk.
Try to imagine the data returned but sideways, so that the first row is on the left and the last row is on the right. dbcc shrinkfile will try to shrink from the right, but in this case you've got 5 active virtual logs on the far right so dbcc shrink file won't shrink anything.
dbcc loginfo isn't documented but there are various documents out there on the web if you do a google search. Try this one http://searchwincomputing.techtarget.com/tip/0,289483,sid68_gci1033401,00.html, which describes it in some more detail and its applicability with shrinking log files.
October 5, 2006 at 5:50 am
I'd just like to say thanks very much for the help on this forum. The physical transaction log is now 500MB in size (my choice), and I will be monitoring it for the next few days to see if I can reduce it any more.
Once again, great forum and thanks for the help.
Dave.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply