Help Me Setup Transaction Log Maintenance!
-
Hello Everyone,
I'm new to the SQL scene and am having some trouble setting up transaction log maintenance. I have a SQL 2000 server with about 40 Databases that are used throughout the day. The databases are all set to FULL, and right now i have to manually truncate/shrink the transaction logs. Let me tell you what I would like to accomplish, and then you can tell me what I need to do!
I would like to be able to restore any of the databases to a point in time via the transaction logs + previous nights backup. I would also like to trucate/shrink the transaction logs everynight after the backup completes to restore the space back to the operating system. Since the backup runs everynight, there is no reason to save the transaction logs since the database has now been backed up, correct? Let me know what you think...if my approach is flawed/incorrect, please let me know. I'm all ears here...
Also, what are some good books you would recommend for SQL 2000+?
-
Hi Erik,
If you want to restore to a point in time, you do need the transaction logs! If you discard the transaction logs and you have a failure, you can only restore to the last full backup.
How often are you backing up transaction logs? Maybe you could reduce the time between log backups and this will keep the actual log sizes down.
Hope that helps!
Clive
-
Also, for the books...There are loads out there....Are you after any specific area's? But one I could not be without is Microsoft's Inside SQL Server 2000 by Kalen Delaney.
-
You can create "Database Maintenance Plans" through Enterprise Manager to back up your transaction logs. It's a simple GUI way to do it if you're not familiar with the code, and your data will be more secure after you spend just a few minutes setting them up. Then you can spend some time delving deeper into backups & restores knowing you've got the basics covered.
-
Clive,
Thanks for the reply. I perform a full backup of all the databases every night. Since i do this, i could delete the transaction logs everynight after the backup since I have a new, full backup of the databases, correct? That is my understanding, anyways...much like exchange when it flushes the transaction logs after a backup...if im wrong let me know!
-
I have a maintenance plan in place, but it does not backup the transaction logs. Take a look at my reply above and let me know if my thinking is in line...
-
Administration/implementation is all i really see myself doing...if i need to learn more I could always pick it up along the way...
Thanks, I'll check this book out!
-
What sort of help are you looking for? homebrew01's suggestion to create database maintenance plans is a good place to start. I would make the following recommendations regarding them:
1. Create separate plans for the dbs with different recovery models - simple, bulk logged, and full. In particular, do not mix simple dbs in with a full recovery plan - the trans log backups will fail for the simple dbs and the job will abort.
2. Do not mix system and user dbs in the same plan, for the same reason.
3. Have the plan create text report files so you can see error messages more detailed than simply "sqlmaint.exe failed".
4. Name the plans something more meaningful than the default DBMaitenancePlan1 (last step in the process).
5. Avoid mixing in Optimizations and Integrity Checks with backups - either perform these manually, or create separate plans.
6. If possible, have the backups created on separate disks from the data files themselves.
This should get you started. Later, you may want to implement a more sophisticated strategy, using hand-written jobs, better error logging, automated copies and restores to different servers, and perhaps using SMTP mail instead of SQL Mail...
-
" ......... i could delete the transaction logs everynight after the backup since I have a new, full backup of the databases, correct? ......."
Correct ... unless you think you might have a need to restore to an earlier point in time. For instance, on Wednesday, you realize some terrible data corruption happened Monday afternoon, and you want to restore to 1:34 pm Monday, If you only have Wednesday's logs, you can only go back to a full backup (Sunday night), not a point in time. You can set how many days of logs you want to keep. You have to consider how much space the logs take, and the likelihood that you'll want to go back more than a day. Everybody has different requirements. There's often no "right" answer. Just understand the options & risks and make the best choice for your environment.
-
One more caution: hardware failures can result in gradually worsening corruption, which may not be noticed right away. This can create a situation where seemingly successfully completed backups will actually not restore. The only way to be certain your backups are good is to restore them, and dbcc (consistency check) them after the restore. I learned this the hard way after a disk controller failure. Fortunately, the db in question was consistent enough that the system tables were intact, and the data was successfully re-imported from the original sources. After that experience, I implemented jobs to copy, restore, and dbcc the backups for all critical databases. If you are the caretaker for any such critical dbs, you may want to do something similar. Also, periodically scripting out the database structure is a good idea as well.
-
Hi Erik,
Do you mean delete the transaction logs that were taken before the last full backup?
If you run a full backup at say 10pm daily and you delete all of the log backups upto 9pm (assuming this is the last transaction log backup before the full backup), then yes, you can delete the log backups! If you deleted transaction logs from after your last full backup, then you could only restore your last full backup.
I do not delete transaction logs manually. I let SQL Server do the work for me. I saw that you use maintenance plans to manage your backups. How many days full backups do you keep on disk? I would suggest to keep the same number of days transaction logs as you do full backups.
The reason I say this is because you might be asked to restore to a point in time maybe 2 or 3 days ago to test something that happened.
Just one other thing with deleting transaction logs. If you have a full backup and then 6 transaction logs, but you have deleted 1 of them and not the others, you cannot do a full restore to a point in time as the logs will need to be restored in the same sequence as they were backed up.
I hope all of that makes sense!
Clive
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply