Being kicked into 'Single User' mode after Maintenance Plan runs... 1

[ElectricEel]

I have a database that is randomly reset back to single user mode after my maintenance plan execs. I've checked all the logs and they come up clean. Any Ideas?

Is there command line utility to reset SQL server in the AM?
e.g. exec sp_dboption 'Database_name', 'single user', false

Or do i need to go through a full restart of SQL server each morning? Which may not guarantee the database is reset since I'm not sure where the server is being tripped into single user.

Thanks!

 

[BeckahC]

Do you know why it it becoming set to single user? When my maintenance plans run they don't cause this. Did you create the plans using EM or Scripts? BeckahC

 

[ElectricEel]

BeckahC,

I am using EM, specifically the DB Maintenance Plan Wizard to create the maintenance plan. Since it happens randomly and there is no mention of it being reset to single, I don't have a clue as to when it is being tripped.

Ideally I would like to exec sp_dboption at 7am just to be sure. I know that is not a fix, but just an insurance policy so my users aren't locked out of the dev server.

Aloha
Neil

 

[BeckahC]

Would you be able to set-up a job that would run the code every morning? Or maybe add it as a step in the Maintenance plan job? BeckahC

 

[ElectricEel]

I was originally thinking of running it as a scheduled task from NT, however i'm unaware of any utilities to exec sp's from the command line.

Does EM or DTS have a scheduler?

Neil

 

[BeckahC]

In EM you can schedule jobs to run at specific times, dates, frequencies, etc.

Under Management - SQL Server Agent - Jobs

and I believe you can run scripts from them too. BeckahC

 

[ElectricEel]

Well I created a job to run every morning at 7AM, and to page me if it fails! Hopefully that'll do the trick!

Thanks for pointing me in the right direction!

Aloha
Neil

 

[BeckahC]

You're welcome! I hope everything works out ok! BeckahC