single user mode

[jabroni]

hi there,

i'm running the database maintenance plan in sql 2000.
when the job tries to check data and index linkage", the log comes up with status failed with the following message:

"[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode."

is there a way to configure the databases to automatically switch to single user mode (say midnight) and then switch back a couple of hours later???

cheers

 

[MeanGreen]

Here are the TSQL commands to do what you want:

Alter Database Databasename
Set Single_USER

A few hours later -----

Alter Database Databasename
Set Multi_USER


Hope this helps.

 

[tlbroadbent]

You can only set a databse in Single User mode if all users are out of the database. You may have to create a proceure to disconnect users before you can alter the database. Check the the following links for some code you can download.

http://www.swynk.com/friends/knight/killusers.asp

http://www.sql-server-performance.com/q&a37.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.