Database Maintenance Plan 1

[netnut1234]

Hi,

I have a couple of db maintenance plans set on my SQL 2000 database server. The first plan runs OK with out any errors. The other plan I am having issues with, the plan executes and runs OK, it backs up 3 other database, but fails on the master, northwind and pubs database. I have check the service account, and that is running a domain admin account, both the MSSQLSERVER and SQLSERVERAGENT are using this account. I have checked folder permissions and this account has full control on all folders. I just cant understand why it back ups some of the databases and not the other, below is a copy of the script:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 182B4960-3FC6-45B8-9F09-C0997CF675FC -To "Helpdesk" -Rpt "e:\Microsoft SQL Server\MSSQL\LOG\DB Maintenance Plan16.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia DISK -BkUpLog -UseDefDir -DelBkUps 2DAYS -BkExt "TRN"'

I have seen the thing on the Microsoft website about the "S" being used in the path, I took this out, still didn't make any difference.

Can anyone help us out with this error.
Thanks
Tim

 

[mutley1]

Hey NetNut,

It looks like this is the transaction log backup that is failing. Best guess is that you have those 3 databases in "simple" recovery mode. This means the transaction logs are truncated on checkpoint and are basically "not eligible" for backup and normally cause the job to fail (it will back up logs for databases that are in full recovery etc.). It just shows as a fail because it couldn't do all databases (or fail on at least 1).

Best suggestion (apart from write manual backups) is to create seperate maintenance plans. For those databases that are in full recovery mode, include the tran log backup. for those that aren't, don't!

It's usually easiest to have at least 2 maint plans. ! for the system databases (no tran log backups) and the other(s) for user databases, depending on the requirements.

Rgds,

M.

 

[netnut1234]

Hi Mutley1

Thanks for the heads up about the recovery mode. I have checked this on all of the failing databases and made sure that they are now set to "Full". Now when I run the backup via Query Analyzer it only now fails on the "Master" database. Any reasons why this would fail on this database when they are now all set to "Full" recovery mode?

Thanks
Tim

 

[mutley1]

I wouldn't change the recovery mode on those 3 - I'd leave them all as simple and ignore them in log backups. Firstly, set master back to simple then.....

Create 2 plans:

Plan 1 - when you create a new plan, the first screen has a few options. Select "all system databases" and set up as you require, but don't back up the tran logs.

Plan 2 - Select "these databases" and tick all the DB's you want (except Northwind and pubs). As long as they are in full recovery, then the tran log won't fail. Depends on your set up.


Basically, the tran log will keep filling up until it is backed up, then it will truncate (delete all that has been written to the DB). If you are in full recovery mode and don't back up the tran log, it will grow out of control.....well, basically keep growing!

If you are not using Northwind and pubs (the 2 sample DB's from MS) then I would recommend deleting them anyway.

Cheers,

M.

 

[netnut1234]

Hi Mutley,

I have now created 2 plans as you have indicated to do and this has resolved the issue of the failing backups on my SQL server.

Thanks for all your help.....

Tim

 

[mutley1]

You're welcome!