DB Randomly sets in Single User Mode

  • riggins13

    SSC Veteran

    Points: 273

    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

  • andrecesarr

    SSC Veteran

    Points: 207

    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: 323460

    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

    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

    I ran the query, (changed the blank db to my db name) and I get nothing ๐Ÿ™

    boggled

  • Lowell

    SSC Guru

    Points: 323460

    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: 1004484

    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

    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.

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

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