Scheduled SQL Server maintenance - any suggestions?

[Rx_]

Here is a set of weekly SQL Server maintenance task.
Does anyone have other suggestions to add to this list?

MyDBNAME is backed up every morning early AM. The backup is restored to a Test DB twice a week to insure the backups work.
It is a full DB backup.

The Recovery Model is "Simple" - idea to save data fast and not worry about Transaction files. My DB has no real batch updates.

Every Friday afternoon - manually run this script and review the results
USE MYDBName;
GO
EXEC sp_updatestats

go
dbcc checkdb

USE model;
dbcc checkdb

use master
dbcc checkdb

use msdb
dbcc checkdb

 

[SQL_Hell]

Hello,

I assume you backup master, model and msdb as well? if not you should be.

You have no re-indexing even though you are updating stats, you need a weekly re-index job also to stop the indexes from becoming fragmented.

I also have a monthly job where I clean up the backup history from msdb (if not it will grow forever). I also recycle the error log.

Do you have any deadlock tracing?

Do you have anythnig to tell you what indexes SQL server thinks it is missing.

Do you have anything to tell you indexes that are created but not used?

I also have a job to store IO statistics in a table in my Operations DB, so I can look for trends overtime and try to stop contention issues before they become a problem.

 

[Rx_]

Re back up master, model and msdb - oh gosh yes. Full backups, not incremental.
Little over a couple of years ago, a IT group ran both Exchange and SQL Server on the same huge dedicated server. The guy did an upgrade on Exchange. SQL stopped working and none of the backups would restore.
Well, right before the upgrade I did a full backup of each and every thing.
Did one of those expensive Microsoft Support calls for 10 hours. Long story short, Exchange had a known upgrade that invalidated all SQL backups on the server.
So, my backup saved to my desktop, ended up being the the only corporate copy of the company's entire DB that could be restored. And, it was the Master, model and msdb that made all the difference. To this day, I still make backups monthly of those three and save it on CD ROM.
For now, all nightly full backups are kept for 3 weeks then auto deleted on the server.

Will add recycling the error log. Am spoiled with huge disk space and get lazy about that.
Looked at the SQL Central Website ans saw a SP that creates a table for storing IO Stats and other information.
Will maybe try that later this month and post it if it works OK.