Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I just started a new job, and I'm reviewing the database maintenance plan. I've got quite a bit of experience writing SQL, but not much experience with DB administration. My last job was at a large company, and they didn't let regular people touch that sort of stuff.

We are locked into SQL Server 2000 (it's embedded in some quite old software and we can't upgrade yet). The current maintenance plan (Full Recovery model) does the following:

Every hour from 6am - 11pm:

  1. backup log Accounting to Accounting_Logs with noinit

Every night at 1am, this happens:

  1. backup Log Accounting WITH TRUNCATE_ONLY
  2. DBCC SHRINKDATABASE (Accounting, TRUNCATEONLY)
  3. backup database Accounting_ReadOnly to Accounting with init

Then at 3am:

  1. all the indexes are rebuilt

Is this a decent plan? Will this give us good backups that are easy to recover? I know I'm asking for a lot, but any thought/comments/suggestions would be appreciated.

Please let me know if you need more information. Thanks!

share|improve this question
1  
Is this a decent plan? -- No. –  Jon Seigel Jan 3 '13 at 22:24
1  
Do you know why the DBs are being shrunk down every night? You might want to look to see if there was some odd valid reason, but most likely they just did it without realizing why. I remember when I was a Jr. DBA I would shrink DBs and log files without knowing why heh. –  Ali Razeghi Jan 3 '13 at 22:47
    
Ali - I think the previous person set this up in 2007, possibly at the recommendation of the software vendor that has MSSQL 2000 embedded. We have three databases that are around 8GB and a few more around 2 GB, so we're not talking about much data. However, we do have problems with log files and tempdb growing big enough to fill the entire 250GB drive. –  Daniel Jan 4 '13 at 13:12

2 Answers 2

up vote 3 down vote accepted

While this is functional, there's some holes in here that will make it difficult for to recover in the event of a problem:

  1. I'm not sure what the device is your backing up to for your logs, but based on what I'm seeing here, you're appending all your log backups to the same file/device for all your log backup actions. This can be cumbersome to manage and can cause you storage issues.

  2. The BACKUP LOG ... WITH TRUNCATE_ONLY is redundant and can possibly cause you data loss. Your log truncation occurs after every log backup, so that should be sufficient to manage your log, though you should consider scheduling your log backups for every hour 24/7 instead of just 6 AM-11 PM.

  3. Your full backup with it's current syntax will overwrite your previous backup every run, so unless your saving that backup off to tape between the full backup runs, you will not be able to restore previous to your last full backup.

  4. Shrinking databases regularly is VERY bad practice, for a variety of reasons. Paul Randal talks more about it here.

In general, it looks like your backup/maintenance plan is as follows:

  • Execute a full backup nightly (1 AM)
  • Execute transaction log backups every hour
  • Rebuild fragmented indexes nightly

This is a solid enough plan, it looks like you just need to fill in some of the gaps. Unfortunately, backup/restore is far to large a topic to cover here. I recommend you read about this at SQLServerPedia(now ToadWorld). Since you're committed to SQL 2000, this limits you to making use of some of the currently existing tools out there. However, plenty of code examples exist to fine tune and polish your currently existing code.

As for index fragmentation, without seeing your code, I can't comment to any possible issues with it. If it's been working fine up until now, there's probably no issues there, but you can read more about managing this up on MSDN.

share|improve this answer

Do not shrink your databases. This kills performance by fragmenting your indexes to 99%. in your situation it seems to be a pointless task, so just remove that step. You also do not need the 1am log backup if you are doing a full backup right after.

Other than that it looks pretty decent.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.