switching to single user mode for scheduled jobs? 2

[samuraiRocker]

Hi.

I've been getting an error message for one of my database maintenance plans. When it tries to check integrity/indexes as part of the maintenance, it fails saying that the database has to be in the single-user mode. It wouldn't be much of a problem if this was a one time deal, but since it's part of a scheduled job, I'm not sure how to go about this problem. Is there a way to make it switch to single user mode just for this job? But even then, I would have problem with other things while this job is running because of this restriction, I would assume? Error number I get is 7919, and it seems like it remedies itself on and off, but I would like to eliminate it permanently...Any help/suggestion/comments would be greatly appreciated.

Thanks in advance.

 

[tlbroadbent]

You can use sp_dboption to change a database to single user mode.

USE master
EXEC sp_dboption 'dbname', 'single user', 'TRUE'

Of course, you'll want to set the option to false upon completion of the DBCC.

More information from SQL BOL:
[ul]single user
When true, restricts database access to a single user connection. Any user who accesses the database when single user is set to true can continue to use the database. A new user can access this database only if all other users have disconnected or switched to another database. With this option set, the trunc. log on chkpt. database option is not supported. The log should be truncated after single-user operations are completed. When false, any number of currently connected users can access the database at the same time. By default, single user is false.
Set single user to true to ensure that no one is using the database when:

Renaming a database.
Restoring a database.

The status of this option can be determined by examining the IsSingleUser property of the DATABASEPROPERTY function.

SELECT DATABASEPROPERTY('dbname', 'IsSingleUser')[/ul] Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Bernadette]

Hi There

When the Integrity Check part of the Database Maintenance Plan is included then CHECKDB runs. If you choose to 'repair minor problems..' then CHECKDB runs with the REPAIR_FAST option which means that the DB in question is put into single-user mode for the duration of the check.

Uncheck the 'repair minor problems checkbox in your Database Maintenace Plan and it should fix this problem.

Hope This Helps
Bernadette :->

Bernadette

 

[samuraiRocker]

Thank you all for your replies.

tlbroadbent, thanks for the detailed explanation. But I'm not sure how I can incorporate that as part of a maintenace plan.....is there a way to do that??

Bernadette, wouldn't there be a shortcoming by unchecking that option? To be honest, I'm not sure how crucial it is to have this checked in the first place(I'm not an "authentic" DBA...s-)), but it seems like a good idea....on top of that, it actually works for the most of the time with this option checked.......it stops working only few days every 2 weeks or so, and I wanted to see what may be causing this "interruption"......

Thanks again.

 

[rooksmith]

The reason it works sometimes and not others is that if there actually is something to repair, the database must be in single user mode. Furthermore, you cannot just switch to single user mode - you need to kill all the processes on that database first. Since this is not polite, the other option is to not attempt repair in the first place unless there are no users present. So you can either put up with the daily error messages, or try something like this:

Write an intelligent isql script that counts the number of processes that are attached to that database first. Here are some lines of TSQL code (note: I'm doing this from memory so you need to test/debug etc. yourself).

-- add these lines to the maintenance script

declare @usercount int
select @usercount = count(*)
from master..sysprocesses where dbid = db_id('mydb')
if @usercount > 1
begin
-- some sort of 'normal' condidtion error handling such as a raiserror here ...
exit -- jump out of the script
end
else
begin
-- rest of script here ..
end



Hope this helps.
*****************************
* Doug Smith, Founder *
* DataNostics *
* Database Architects *
* doug@datanostics.com *
*****************************