Huge increase in Database size

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Huge increase in Database size

Author

Topic

venkath
Posting Yak Master

202 Posts

Posted - 2006-11-16 : 00:27:52

Hi all

My database of 50GB size has grown upto 233GB in few days of time.
database recovery model was set to FULL (No TRN backups have been taken)

when i check the space allocated by the databases using
Shrink database option at EM, i found that Free space is 169GB(72%)

Now, what are the steps i can take to reduce the log size..

Thanks

Kristen
Test

22859 Posts

Posted - 2006-11-16 : 01:06:36

You should not shrink a database as a routine - however this is an exception!

Shrink the database (preferably just the Log file)

Do this BEFORE you attempt to set up Transaction Log backups (unless you NEED the transaction log backed up for a potential Restore), because your TLog backup will be huge (probably 233G - 50GB)

If the recovery model is FULL you need to ALSO be making regular Transaction log backups (I suggest every 10 - 15 minutes) - then you will be able to recover to point-in-time

Alternatively change Recovery Model to SIMPLE - then you will only be able to recover to last Full (or Full + Differential) backup.

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Why%20is%20my%20LDF%20Log%20File%20so%20big,What%20type%20of%20backup%20should%20I%20use,Automating%20Backups,Backup,Database%20Shrinking,Shrink,Shrinking

(scroll down to see all the highlighted terms)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources