General discussion


MS SQL Maintenance Plan Help!

By cd2041 ·
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"'

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Help is on the way

by MirrorMirror In reply to MS SQL Maintenance Plan H ...

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?

Collapse -

SQL Help

by cd2041 In reply to Help is on the way

It seems when ever I have ?CkDBRepair? in the command line the job fails.

Collapse -


by MirrorMirror In reply to SQL Help

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.

Collapse -

In other words...

by sswords In reply to CkDBRepair

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.

Related Discussions

Related Forums