Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
My Log File is larg I need to cleare it
- Thread starter baselbj
- Start date
- Thread starter
- #3
tlbroadbent
MIS
See faq183-1534 - "Shrinking Databases and Logs - SQL 7 and Higher."
Terry L. Broadbent - DBA
SQL Server Page:
If you want to get the best answer for your question read faq183-874.
Terry L. Broadbent - DBA
SQL Server Page:
If you want to get the best answer for your question read faq183-874.
- Thread starter
- #5
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.
Hope this helps
John
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.
Hope this helps
John
tlbroadbent
MIS
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:
If you want to get the best answer for your question read faq183-874.
SQL Server Page:
If you want to get the best answer for your question read faq183-874.
- Status
Similar threads
- Locked
- Question