• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

Looking for a simplified explanation and strategy for a nightly maintenance plan for SQL Server 2008

Hello,

I am running a program called ShipWorks that is connecting to a SQL Server 2008 instance(running on XP with decent hardware).  I am looking for suggestions and some newbie knowledge transfer on a nightly maintenance plan to keep the db(or all the databases) healthy and performing.

What I am currently doing:
Backing up all databases nightly with a maintenance plan I assembled via the wizard.

What I would like to do(and know more about):
Index and compact the db, plus whatever else is a best practice and just a good idea to keep things maintained.  I'd like to assemble this via the wizard as well, which I don't think I need much help with.  I would like to know that the actions I perform aren't going to hose the db.

Disclaimer:  I have almost NO db admin experience, but work with a guy who is handy with MySQL.

Thanks in advance,
Brian
0
clarkbg
Asked:
clarkbg
  • 5
  • 3
1 Solution
 
QPRCommented:
MPs are your friend if just starting out but when you become a little familiar you may want to create a script(s) that give you more control.
As an aside I would avoid the shrink database step unless disk space is a real issue for you.
Shrinking and subsequently re-growing a database is a cpu intensive task and can lead to performance issues if it occurs during working hours
0
 
clarkbgAuthor Commented:
I'm ok with not shrinking the db as we aren't hurting for storage space.

Based on your experiences, what are the most essential tasks to run nightly?

And should I be taking the shipping app offline to run any of these tasks, of can I leave it running without issue?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
QPRCommented:
the app can stay online without issue.
What is essential really depends on a few things.
Personally I would back up the DB nightly but reorganise/rebuild indexes weekly. But then if the DB is not in use during the scheduled maintenance time then it can't hurt.
Really depends on how busy your app is and how many data modifications are made.
What recovery model are you using, full? simple?

For the servers that still have MP (yet to get round to the scripting option on them).
I check integrity, reorganise/rebuild indexes, update stats, backup the log and db and then clean-up nightly.Mon-Fri overnight.

If you are using the FULL recovery mode and data loss is not an option then I would be backing up the transaction log periodically through the day too... this is over and above the m.plan

0
 
clarkbgAuthor Commented:
One thing I have noticed is that even though I am also backing up the transaction logs for all the databases nightly, the disk location where I am saving the backups never shows a .trn for the application database.  Is this something I should be worried about?

Thanks for the link and the good info. That has got me started out pretty well.
0
 
QPRCommented:
Depends If u have specified a trn extension for the log backups. If u are backing up the db at the same time then the log backup is redundant. Log backups mainly keep you safe between db backups although reg backups do help to keep the log file size down
0
 
clarkbgAuthor Commented:
Thanks for the good links and follow up.  I ran some maintenance tasks this AM and had fairly satisfactory results.  One task failed, but it was a backup cleanup task that I can address later.
0
 
QPRCommented:
"One task failed, but it was a backup cleanup task that I can address later."

make sure that the account that is running the "job" has delete permissions to the folder
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now