Software

Ask something
Discussion Locked

MS SQL Maintenance Plan Help!

0 helpful
4 replies

by cd2041

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I?m trying hard to figure out what the write way of managing our SQL server is. We have sporadic success. Sometimes the jobs work sometimes they do not.

We would like to implement an effective MS SQL Maintenance Plan. Should we use the wizard or something else, not sure?

Sorry if this question was asked 50 million times. I?m new here.

The Transaction Log Backup job we created using the wizard doesn?t work anymore. Can some help.

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 4EC1FB53-8846-4604-B520-5157BE9213AF -WriteHistory -BkUpOnlyIfClean -CkDBRepair -BkUpMedia DISK -BkUpLog -UseDefDir -DelBkUps 2WEEKS -BkExt "TRN"'

4 replies

by mirrormirror

Check to make sure that the recovery plan is set to full and not simple.

What does the error message in the job history say the reason is for the failure?

by mirrormirror

That was the next thing to address. This is the switch to "Check database integrity, include indexes, attempt to repair any minor problems". When you use this option, the database must be in single user mode or basically have no connections to it to perform the repair. It is possible that a connection that has not been there before has been left open, causing the task to fail.

I only perform a DBCC CHECKDB when there is very little activity. I have the results logged and I check this each week to see if any problems are found.

by sswords

Uncheck the 'Attempt to repair any minor problems' item from the Wizard. If you leave the Integrity checks on, you'll then see the results of the DBCC CheckDB checks in the logs, but it won't attempt to repair them. You'll need to perform periodic reviews of the logs to make sure no problems exist. Hope this helps.

This discussion is closed to new replies.