Single-user mode issue 1

[unclerico]

I have a job that requires the one of our Databases to be run in Single-user mode. Unfortunately, this job will sometimes fail and will keep the DB locked in Single-user mode until I manually go into EM and uncheck the Restrict Access check box. I am wondering if there is a way to a) when the job in question fails, e-mail me using CDOSYS (we do not have Outlook installed on the server to use SQL Mail) to notfiy me of the occurrence and b) set the DB back to multi-user mode automatically. Any thoughts?? Thanks.

 

[unclerico]

Here is what I belive to be the offender in this case:

alter database dynamics set single_user DBCC CHECKDB ('dynamics', repair_rebuild) WITH NO_INFOMSGS alter database dynamics set multi_user

 

[mrdenny]

a) Not using the Job notification. You would need to add a job step to catch this. If you don't want to install Outlook on the server I recommend installing xp_smtp_sendmail. Google for it, and you can download and install the DLL. It lets you send SMTP email without outlook, without using CDOSYS. Using the job notification requires the use of Outlook.

b) You would need a job to handle this.

I would recommend moving the set multi_user command to a second job step. Then have the first step continue to the second step on success or failure.

If you wanted to get really fancy you could setup to steps with the set multi_user. One would get called when the dbcc works, and the other when it doesn't. Then set the one that gets called when it doesn't to report failure when it is complete. This way you know that the job failed.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[unclerico]

Eye see what you're saying MrD, sounds like a really good plan. Thanks a lot for the advice. Star pour vous.

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief