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 |
|
lluisdelta
Starting Member
9 Posts |
 Posted - 2008-05-07 : 20:23:45
|
| Hi,I was recently assigned the administration of a SQL 2000 server with 1 instance and ~10 databases and I would like to run the maintenance/DR plan by you.- MDFs are stored in D: drive- LDFs are stored in a dedicated, mirrored, G: drive.- 5:00am-10:30 pm - Transaction Log Backup job runs every 15 minutes, backing up LDFs from G: to a backup (E:) drive and deleting older ones (>24 hrs) from E:- 1:30 am - System Optimization checks (master, model, msdb)- 3:45 am - Full Daily System DB Backup, copies system DBs to E: (overwriting previous backups)- 4:00 am - Full Daily Database Backup, copies all MDFs to E: (overwriting previous MDFs)- 4:30 am - System Integrity checks (master, model, msdb)- 5:00 am – All backup files in E: are copied over to another server and backed up to tape. - And then, EVERY 15 MINUTES during the NIGHT (10:30pm-3:30 am) – Truncate Transaction Log job runs the following script: BACKUP Log <database> with Truncate_only DBCC SHRINKFILE (<database_Log> , 5, TRUNCATEONLY)I’m not sure about this last step. Does it makes sense to run it every 15 minutes during the night? Your comments greatly appreciated.Thanks in advance, |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-07 : 20:28:45
|
| You do not need to backup the LDF files as they are probably unusable anyway unless the SQL Server service was stopped or the databases were detached at the time of the copy. Same goes for the MDF files. You should be backing up your BAK and TRN files to tape or another server but not the MDF and LDF files.Why aren't you running integrity checks on all of the databases?You should not be truncating the transaction log!!! You are breaking the transaction log chain and can not restore to a point in time. There is no point in backing up the transacton logs if you are going to be truncating them each day.Also don't shrink the files as it's causing you performance issues.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
lluisdelta
Starting Member
9 Posts |
Posted - 2008-05-07 : 20:40:54
|
| Oops, my bad. I am actually running _backups_ (thus creating BAKs and TRNs) which is what I am backing up.Re: Integrity checks on all of the databases, point taken, thanks!Same goes with not truncating the transaction log.Should I truncate the database just once per night?Thanks |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-07 : 21:06:28
|
| Don't do that if server has enough free disk space to work with, it'll grow tomorrow anyway. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-08 : 12:40:47
|
| Do not truncate the transaction log on a scheduled basis. Only do it in an emergency situation when you are out of disk space and the LDF is full.Each time you truncate it, you are breaking the transaction log chain and therefore your ability to restore to a point in time. Let me ask this, why do you want to truncate the tlog on a scheduled basis such as daily?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
lluisdelta
Starting Member
9 Posts |
Posted - 2008-05-09 : 15:37:47
|
| Thank you all for your comments. Tara: I dont really know why that daily truncation (every 15 minutes every night actually!!...) was in place. Maybe one of the reasons my predecesor was let go. I am trying to make some sense of all this, but at the same time I don't want to change things that are ok. so far, it looks like that log truncation is the first thing I should stop. rmiao: You mean don't truncate EVER? Maybe only as per need basis? How would I know when?Thanks again guys, your help is being great!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-09 : 15:45:31
|
| Truncate the log manually when you or the application is getting an error that the tlog is full and you are out of disk space. And then immediately run a full backup to start the transaction log chain again.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
|
|
|
|
|
Backup strategy