Maint Plan Fails When Taking Database(s) Offline

Question

Maint Plan Fails When Taking Database(s) Offline

JC-3113

SSCrazy Eights

Points: 8371
More actions
May 31, 2011 at 10:32 am

#240558

Hi Folks
can some tell me why my maint plan seems to fail everytime i take a database offline.
i check the maint plan and the database(s) are no longer listed but,
when the plan runs, it seems to think they are in the plan, so i am confused
here is the report:
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.4053
Report was generated on "NSAB-SS80-SQL-N".
Maintenance Plan: SS80-MP-UserDB-Backup-Diff
Duration: 00:13:35
Status: Warning: One or more tasks failed..
Details:
Notify Operator Task 1 (NSAB-SS80-SQL-N)
Notify Operator on Local Server Connection
Operator: SQL Server Administrator
Task start: 2011-05-31T09:13:38.
Task end: 2011-05-31T09:13:38.
Success
Check Database Integrity (NSAB-SS80-SQL-N)
Check Database integrity on Local Server Connection
Databases: QA_SharedServices1_DB,
QA_SharedServices1_Search_DB,
QA_SharePoint_Config,
QA_WSS_Content_Home,QA_WSS_Content_Mysite,
QA_WSS_Content_SSP,
QA_WSS_Search_NSAB-SS79-SPC-N,
QA_WSS_Search_NSAB-SS82-SPF-N,
ReportServer,ReportServerSS62,
ReportServerSS62TempDB
Include indexes
Task start: 2011-05-31T09:00:06.
Task end: 2011-05-31T09:11:38.
Failed:(-1073548784) Executing the query "USE [SS65_UpdateManager]
" failed with the following error: "Database 'SS65_UpdateManager' cannot be opened because it is offline.".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Thanks
Jim

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Maintenance plans do have a problem with that. That's one of the important reasons to move away from them as much as you can, and use more intelligent scripts than the ones that are automated there.

Offline, corrupt/suspect, even single-user mode, any of these can make the automatic maintenance plans bomb out. A script you build yourself, querying sys.databases, can be made to work much more intelligently.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 2

Thanks GSquared

i am beginning to think that these Maint Plans, though a good idea, have a lot to be desired

i hate to re-write everything i have done in SSMS and besides I am not that knowledgeable on writing SQL Server backup scripts right now

Thanks

Jim

GSquared SSC Guru Points: 260824 More actions · Answer 3

The maintenance plans that SQL Server Management Studio can build for you DO leave a lot to be desired. They're really useful to a new DBA who isn't sure what to do, which is the case for a lot of "accidental DBAs". (You know, the guy whose manager says, "hey, you're good with computers, you can be the DBA".)

They're definitely better than nothing. But improving on them is really easy.

The first thing I'll suggest you do is dig through the scripts section of this site (SQLServerCentral). There are several good ones for automating database maintenance. That and a little Google/Bing/whatever will get you started.

What I tend to do is write a select from sys.databases, and use that in a cursor to run the commands I need. I'll get the commands by, for example, right-clicking a database in Management Studio, selecting Back Up, setting the options I want, and then instead of clicking "Okay", I click "Script" (top of the window). Then I can copy that script into my cursor, modify it into the string I need, and run it there.

That way, I can set options like what to do if a database is offline, or suspect. Maybe I want the script to send me a report that lists databases that are offline, and how long they've been that way, for example.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

In 2008 there is a checkbox when you select the databases, either individually or as a group that says ignore them if offline, can't remember is 2005 has that checkbox, 2000 does NOT I know.

CEWII