Whats the best method for keeping DB size under control

[simple99]

Hi,
I'm new to SQL and inherited an SQL 2000 server to manage. last week it had a transaction file 300GB large and ran out disk space. I've since reduced the transaction file and DB size by using a combination of DBCC and GUI methods but I want to prevent this from happening unexpectedly in the future.
After reading about it I'm a bit confused at the number of ways this should be done.
What is recommended method for prevneting this in the future. Do I:

1. Setup some maintenance plan for the Dbase ?
2. Do I have use the manual DBCC command or can this be scripted ?

Appreciate any info.
regards

 

[mutley1]

Hi,

It sounds like your batabase is probably in full recovery mode and the transaction log is not being backed up. Check that first and provide the info. Basically in full recovery mode, you back the transaction log up at regular intervals. This will clear out trnasactions that have neen applied since the last T log backup. This gives you the ability to recover to a specific point in time, otherwise you would have to use the last known good full backup, which could be up to a day ago.

If your database is in simple mode, then the trnasaction log is cleared out whenever the database checkpoints so it does not keep the logs and you are therefore forced to revert to the last good full backup if you need to restore. The models depend on your business requirements, afforability of losing data etc.

HTH.
M.

 

[thamms]

However, if you are in FULL recovery mode AND you are not doing regular log backups as part of your backup/recovery plan (which it sounds like you aren't), you can simply truncate the log IMMEDIATELY prior to doing the full backup.

Also, doing a log backup does not make the log file size any smaller.

This is what you can do:

-- truncate inactive transactions from log only without actually backing up the log
BACKUP LOG [db] WITH TRUNCATE_ONLY
-- release space to o/s
DBCC SHRINKFILE [logfilename, TRUNCATEONLY]

I recommend you familiarize yourself with these commands in BOL first, then put them in a job step immediately prior to your full backup.