DB Randomly sets in Single User Mode

Question

DB Randomly sets in Single User Mode

riggins13

SSC Veteran

Points: 273
More actions
May 31, 2013 at 5:37 am

#273347

I have a database that on at least 2 occasions randomly went into single user mode.
It was not immediately after a backup - I don't see any alter database statements and I find no comments in syscomments
Is there any idea why it does/did this and where else I can look?
Please advise

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

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

andrecesarr SSC Veteran Points: 207 More actions · Answer 1

Are these databases being restored from some bkp file? If its not, you can try to use the blocker script to monitor the commands running against your SQL Server with more accuracy.

See more about it here:

http://support.microsoft.com/kb/271509

Regards,

André CR

Lowell SSC Guru Points: 323518 More actions · Answer 2

Stating the obvious, a database doesn't just get set to single user by itself, some process or person actually issued the command.

that ALTER DATABASE command counts as a DDL operation, and it thus logged in the Default Trace.

you can see some quick whoodunnit info from the Schema Changes history Report, or query the default trace directly:

--SELECT * from sys.traces

declare @TraceIDToReview int

declare @path varchar(255)

SET @TraceIDToReview = 1 --this is the trace you want to review!

SELECT @path = path from sys.traces WHERE id = @TraceIDToReview

SELECT

TE.name As EventClassDescrip,

v.subclass_name As EventSubClassDescrip,

T.*

FROM ::fn_trace_gettable(@path, default) T

LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id

LEFT OUTER JOIN sys.trace_subclass_values V

ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value

WHERE DatabaseName ='Blank'

AND IndexID = 15 --Single User

OR IndexID = 16 --Multi User

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

riggins13 SSC Veteran Points: 273 More actions · Answer 3

No, they are not being restored... just randomly during the course of a normal day the log states, 'Setting database optin SINGLE_USER to ON for database [databasename]

No other message prior to that... not at start up. This instance set it 28 minutes after a tlog backup. Tlog backups take less than a minute to run

riggins13 SSC Veteran Points: 273 More actions · Answer 4

I ran the query, (changed the blank db to my db name) and I get nothing 🙁

boggled

Lowell SSC Guru Points: 323518 More actions · Answer 5

riggins13 (5/31/2013)
I ran the query, (changed the blank db to my db name) and I get nothing 🙁
boggled

well, the default trace only keeps the last 100 meg of DDL changes, so if it has been a while, on a server with a lot of DDL changes, the change could be pushed out of the history.

it sounds automated to me, i'd search all the jobs and all the procedures on the server for SINGLE_USER to track down the script that is being run:

select * from msdb.dbo.sysjobsteps where command like '%SINGLE_USER%'

select OBJECT_NAME(OBJECT_ID),* from msdb.sys.sql_modules where definition like '%SINGLE_USER%'

select OBJECT_NAME(OBJECT_ID),* from master.sys.sql_modules where definition like '%SINGLE_USER%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Try this slight modification (IndexID is null for alter database events)

declare @TraceIDToReview int

declare @path varchar(255)

DECLARE @DatabaseName = 'Test' -- make this the database you're interested in.

SET @TraceIDToReview = 1 --this is the trace you want to review!

SELECT @path = path from sys.traces WHERE id = @TraceIDToReview

SELECT

TE.name As EventClassDescrip,

v.subclass_name As EventSubClassDescrip,

T.*

FROM sys.fn_trace_gettable(@path, default) T

LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id

LEFT OUTER JOIN sys.trace_subclass_values V

ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value

WHERE DatabaseName = @DatabaseName

It'll get you all the database alteration events, but you can correlate the time with the database going single user.

Or, set up a manual trace, trace for the Object:Altered event and filter on the database you're interested in, run the trace to disk for a couple weeks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

equipe9 Old Hand Points: 361 More actions · Answer 7

This is very helpful for a similar issue I am facing with a database randomly going into single user mode but doesn't seem to work for SQL Server 2000.

Would you have the same code that you can share that would work for SQL Server 2000?

Thanks in advance.