Now that we know what version you have it makes thing a bit easier.
SQL 2000 uses different recovery models SIMPLE, FULL, BULK-LOGGED. The last 2 support restores off the transaction logs and SIMPLE doesn't. By default SQL 2000 assigns the FULL model to newly created databases.
If you are using FULL(which is the best) the only way to keep it small is to backup the log file regularly. eg daily, hourly half-hourly depending on how transaction intensive your database is. The FULL model makes all transaction entries into the log.
If you use SIMPLE then SQL 2000 makes the bare minimum of entries into the log and therefore the log doesn't grow all that much but you can't restore off the log, only the last full backup.
If you are using SIMPLE then execute the following statement against the database
shrinkfile(2,0)
go
If you are using FULL then first backup the log file. Change the recovery model to SIMPLE by right clicking the database, properties, options. Then execute the above statment. Change the recovery model back to FULL. Create a maintenance plan to backup the transaction log more frequently.
Here is a scenario to show you the benefits of backing up your logs:
You do a full database backup nightly at 23:00, you use the SIMPLE recovery model. Your database crashes at 16:30 the following day. You have to restore, you only have the previous night's backup and therefore you lose 17.5 hours of data.
You do a full database backup nightly at 23:00, you use the FULL model, you backup your logs every 2 hours 01:00, 03:00, 05:00, 07:00, 09:00, 11:00, 13:00, 15:00, 17:00 etc.
The database crashes at 16:30. You have to restore. So you first restore the full backup and then every log backup in the order that they were created meaning that 15:00 was the last one. In this scenario you only lose 1.5 hours of data which is far less then 17.5 hours.
In addition to the FAQ about shrinking the log file I recommend reading faq183-1784 - "How Do I Develop a Backup and Restore Strategy?" Terry L. Broadbent - DBA
SQL Server Page:
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.