Backup failure using Maintenance Plan
-
We setup a maintenance plan to backup 3 dbs on a server. It worked for a couple of weeks and then it started to fail. We've changed the user from domain\admin to sa and have tried other things, but nothing we do gets it to work for the schedule. We have no problems backing it up manually, it just won't do it as part of the maintenance plan. Any suggestions???
Thanks,
Frank
-
Are all 3 of your databases still on the server. If any one is also no there, then it will fail since it wont find the database to backup it up.
Also, check with what account is the job running. It should proper priviliges to run the job.
Hope this helps.
--Kishore
-
Both the domain admin and the sa accounts have the proper privaleges and we've tried under both and have experienced failure for both. If there are people on the server at the time, which is unlikely, but possible would that be a reason for the jobs to fail?
-
What's the error message say in the history of the scheduled job?
-
It's not very descriptive at all. "The Job Failed". It says who it was executed by and that's about it.
-
Did you click the "Show step details" button and highlight the step that failed?
If you edit the job, edit the step that is failing, and switch to the Advanced tab, is the "Append output to step history" checkbox checked? If not check it, run the job, and look at the step's history again.
-
To get a more descriptive error message, right-click "Database Maintenance Plans" in Enterprise Manager and choose "Maintenance Plan History..."
Choose you plan and scroll down the list to find the errors. They'll be the ones with the big red crosses
--------------------
Colt 45 - the original point and click interface
-
Now we're getting somewhere. The Activity that failed was Check Data and Index Linkage - Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
I've seen this "single user mode" message before and this db is used 24 hrs a day typically 6 days a week. We have a small window to do the backups so how do we get it to single user mode without manual intervention? Is that something that can be done as part of the maintenance plan?
Now under the Activity for the Transaction Log Backup the message that comes back is "Backup can not be performed on this database. This sub task is ignored". Could this be related to the message above?
Thank you everyone for your input - it is greatly appreciated.
Frank
-
Okay, this is sort of a bug in the Maint Plan Wizard.
You most likely checked the box to check database integrity before backups. What happens is that if any userid -- like the Web Page ASP agent, the joker who just locks his computer and leaves for the night -- maintains a link to a database then the maint plans fail.
Side note: it also does not delete past successful backups or in other words you'll find days old expired backups in your backup directory that should have been deleted just sucking up disk space.
I learned the hard way. 
Now the way around this: dump the existing maint plans. Even if you take off that checkbox it doesn't re-write the jobs to take out the integrity checks.
When you set up the new plans -- do not check the box to do integrity before backups.
And just for grins -- hunt around here for sp_killuser. This is a custom procedure to kill all users in a database. Schedule it to run before the scheduled time for the integrity checks.
I've been there, done that and got the T-shirt.
Good luck. 
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Thanks Jim! So it's ok to do the integrity check, but not as part of the Maintenance Plan - am I reading that right? So would the process be to schedule the sp_killuser, then the integrity check sp before the maintenance plan executes?
Frank
-
You can do the integrity check as part of the maint plan - Just not before backups.
When you get to that screen it will have something like
[] Perform Integrity Checks O Repair Any minor problems [] Perform Integrity Checks before backupsDo not check that lower box.
And take note of your sheduled times. If you set it up daily at 1:00AM then run the sp_killuser at 12:55AM. Just go to the SQL Server Agent -> Jobs and create a new job.
The problem with the integrity checks is that the database has to changed to single-user mode. If anyone is in the database at the time then the SQL Server Agent can't change it and fails.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Jim - Thanks again.
I'll let you know how it turns out.
Frank
-
Has anyone seen the sp_killuser script I can not seem to find it on the site. Just several referances to it. If someone could post the script that would be great.
Jay
-
CREATE PROCEDURE sp_KillUsers @dbname varchar(50) as
SET NOCOUNT ON
DECLARE @strSQL varchar(255)
PRINT 'Killing Users'
PRINT '-----------------'
CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30))
INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname
DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN
PRINT 'Killing ' + @spid
SET @strSQL = 'KILL ' + @spid
EXEC (@strSQL)
END
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
PRINT 'Done'
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply