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.
| Author |
Topic |
|
shantha
Starting Member
2 Posts |
 Posted - 2002-08-15 : 00:16:31
|
| We are using sql 6.5. Normally our transaction log remains less than 1%. But if something goes wrong with the tape backup it starts to grow until we truncate it manually and run DBCC CHECKDB. Even if I run the tape back up after I noticed the last backup failed it is not cleraring the log . I have scheduled a disk backup prior to the tape backup. But if the disk back up is successful and tape backup fails still I get the same problem. I appreciate if somebody can explain me how exsactly the check point and DUMP DATABASE works in SQL 6.5.ThanksShantha. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-15 : 08:08:31
|
| One thing to look out for is relying on the numbers returned by Enterprise Manager, it's very unreliable.That said, setting trunc. log on checkpoint will help keep the log from filling up, unless you run large transactions. Unfortunately you can't use log backups, but you don't seem to be doing that now.A checkpoint is basically a point in a transaction where SQL Server says, "OK, I'm done with that one" and performs the actual commit. Once the data is committed to the database itself then the log records are no longer needed and can be truncated.Another thing that might cause you problems is if your data and log are on the same SQL Server device. If so, you really need to move them to separate devices, you'll have tons of problems if you don't. You should also look into increasing the size of the log space.Lastly, you should really look more deeply into why the tape backup is failing. Does it fail with new tapes? Is the tape drive in the SQL Server itself or is it on a network? Are backups performed when people are using the SQL Server? Did CHECKDB reports any errors? Have you run DBCC DBREINDEX lately?If none of this helps, you'll need to provide more detail on the database, server, tape drive and your backup schedule. |
 |
|
|
|
|
|
|
|
transaction log