February 6, 2006 at 9:05 am
This pertains to SQL 7.0...
I am trying to shrink a DB log that currently stands at 1151.24 mb, only has 2.79% used 32.14 MB, and hasn't been used in days.
The DB is set to truncate transaction on checkpoint. This log file used to be at 2400 mb. I used the DBCC statement DBCC SHRINKFILE (2, 200) and the log file shrunk to its current size and provided the following output:
CurrentSize=147360, MinimumSize=128, UsedPages=147360, EstimatedPages=128.
Ok, dealing in 8k pages I should be able to shrink this thing as small as 1mb. BOL indicates that I either have portions of my TLOG in a Virtual Log File up around the 1151.24 mb range of my VLF chain, or my VLOG is really that large, or my smallest VLF is 1151.24.
Well, none of these seem the should be the case. My MinimumSize (initial size) is 128X8K, or 1mb, so I should have at least 2 VLF's. DBCC SQLPERF(LOGSPACE) shows 2.79 percent so I'm not truly storing 1154.24 mb of TLOG, and I have backed up the log with the truncate_only option, backup with the database as a full, and I know there have been no entries into this DB for days so I can't imagine I'm using a VLF at the tail end of my VLF chain.
Yet, my UsedPages=147360(*8k)=1151.25mb indicating I am using the last VLF in my chain.
Does anyone have any idea why this is the case and how I can shrink this log file? This DB is about to become a historical read-only version so I'd like to make the log as small as possible.
February 6, 2006 at 10:38 am
I have always used dbcc shrinkfile along with backup (truncate log) to get the most out of it. But if the DB is going to become a read only, then you won't need to log transactions, since there won't be any. So just do a full back up the database and restore it as read-only, without logging. Also, get yourself a copy of SQL 2000 -- you're not even supported in any way, and your pool of resources to get help is vanishing!
February 6, 2006 at 10:54 am
Thanks Bill. We have SQL 2000 in the environment - this server remains at 7.0 for the time being for a legacy app. Also, while I can get around this error with the option you've mentioned, I am trying to figure out why the log's ability to shrink is not as I expect.
Since I don't have to have this fixed right away - I'm taking time to glean from the solution what I can.
Thanks. RH
February 6, 2006 at 3:01 pm
Hello,
I too had this problem when we were running on SQL Server 7 with one of my databases. I did not touch production, but when I needed to get copies of the database for development I restored the database on the test server, detached the database file and used sp_attach_single_file_db to create a nice small log and then had "Truncate log on checkpoint" clicked
Regards,Yelena Varsha
February 7, 2006 at 6:16 am
Yelena - thanks much.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply