December 5, 2003 at 9:35 am
I have inherited numerous databases that look like they were created to have a 1GB data file and a 500MB log file. Both are way too large for current usage. I have been able to get the datafile down to proper size (about 600MB) using DBCC SHRINKFILE. And I have whittled down the log files using the SQL Server 7 script found here. BUT...the smallest I seem to be able to get them is about 187MB with 124MB free (as reported by the sp_force_shrink_log script). And this seems to be pretty typical of my results on all of the databases. I have tried backing up the entire DB, backing up the transaction log, backup up the transaction log with truncate_only, issued a manual CHECKPOINT. Anybody have any idea why I am getting these results??? Oh yes, btw, it looks like the open transactions are at the beginning of the virtual log based on DBCC LOGINFO:
FileId FileSize StartOffset FSeqNo Status Parity CreateTime
2 65470464 8192 1016 2 64 2003-12-03 17:14:18.437
2 65470464 65478656 0 0 0 2001-02-04 02:06:55.670
2 65470464 130949120 0 0 0 2001-02-04 02:01:34.360
I know this has been covered ad-nauseum but I am missing something here 🙁
Thanks for any help you may be able to provide.
George
December 8, 2003 at 8:00 am
This was removed by the editor as SPAM
December 8, 2003 at 11:42 am
There is a minimum size to which you can shrink files, AFAIK, it's about the base level at which it was created. I'm sure you've run the shrink script a number of times, but I've found that if I really need to get as low as possible I run it a number of times, not more iterations, more times. For some reason (I'm not aware of) it works better this way.
If you truly need a smaller db/log, I'd actually BCP out the data, script the db and recreate everything and bring it back in. It's tedious, but not hard and actually adheres nicely to the KISS process.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 8, 2003 at 11:51 am
Steve, thanks so much for your reply. I am guessing that you are correct although the Books Online state:
The database cannot be made smaller than the size of the model database.
Use DBCC SHRINKFILE to reduce the size of a file to smaller than its originally created size. The minimum file size for the file is then reset to the newly specified size.
That said, I'll fiddle with your BCP idea. Unfortunately the site must be available 24/7. It isn't that big a deal now that I have the databases the correct size and have slimmed down the log files. It just bothers me when I can't explain why something is happening or not happening where my databases are concerned.
Thanks again for your help. If anyone has any ideas as to why this behavior might be, please let me know.
Thanks!
George
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply