none
spid57,Unknown,Setting database option SINGLE_USER to ON for database ####

    Question

  • Hi All,

    can any body help me to find out any sql server database put in single user mode automatically without any anybody intervention

    spid57,Unknown,Setting database option SINGLE_USER to ON for database #### 

    after single user mode we getting below mention flush this shows database was in auto close ONmode

    05/06/2013 22:54:30,spid57,Unknown,SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
    05/06/2013 22:54:30,spid57,Unknown,SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
    05/06/2013 22:54:29,spid57,Unknown,SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

    Friday, May 10, 2013 1:54 PM

Answers

  • try your luck by checking in the default trace. I just tested by putting a database in single user mode and it is captured in the default trace with EventClass "Object:Altered". it shows databaseid and databasename.

    It is not showing the command executed but it will give the SPID and who executed. I think that is what you are after. Its going to be little difficult in a production system but as you have the time and spid of when it happened you should be able to find this information unless the default trace has been overwritten.

    http://blogs.technet.com/b/beatrice/archive/2008/04/29/sql-server-default-trace.aspx 

    https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/

    For the other error you have it need not necessary be Auto Close on for the database but a whole lot of other reasons. check this Kb article (information section)

    http://support.microsoft.com/kb/917828?wa=wsignin1.0

    HTH


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com


    Thursday, May 16, 2013 8:46 AM
  • You can go to eventviewer and check in Security and system logs who have logged into the server at that particular time,

    I hope you have different permission levels for diff logins.

    Also apart from what Satish and Ashwin suggested you can check which all users have alter database/db owner sysadmin  permission and can filter out some logins .May be this can narrow your search.Below query will give u table and access permission

    SELECT o.name, p.permission_name
    FROM     sys
    .objects o
    CROSS    APPLY sys
    .fn_my_permissions(o.name, 'OBJECT') p
    WHERE    p
    .subentity_name = ''

    Also there has to be request from some user or job to put DB in single user mode it cannot go by itself.

    reg"Unknown,SQL Server has encountered 2 occurrence(s) of cachestore flush' may be the login which had put ur DB in single user mode had also run few commands as below

    DBCC FREEPROCCACHE

    DBCC DROPCLEANBUFFERS


    Soldier..Sir we are surrounded from all sides by enemy.. Major: Good, we can attack in any direction Thats attitude.. Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, May 16, 2013 1:50 PM
    Moderator

All replies

  • See if you can get more info from the SQL logs. Have you tried doing a dbcc checkdb on it ?

    Jack of all trades, mastered by my wife.

    Wednesday, May 15, 2013 1:02 PM
  • Hi Pawan,

    you can create a Database Level trigger that fires off when an "ALTER_DATABASE" command is issued, which is a DDL statement with server level scope. Please follow this MSDN article on more details about how to do this - http://msdn.microsoft.com/en-US/library/ms189871(v=sql.90).aspx

    The content of this Database level trigger can contain code for sending you an email notification , so you will be automatically notified (without any manual intervention) when a database is set to SINGLE_USER mode.


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    Wednesday, May 15, 2013 2:57 PM
  • Hi Sunil,

    Please check the below mention error log details that captures during this event.

    05/06/2013 22:55:11,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.
    05/06/2013 22:55:08,Logon,Unknown,Login failed for user 'sa'. [CLIENT: ######]
    05/06/2013 22:55:08,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.
    05/06/2013 22:54:51,Logon,Unknown,Login failed for user 'sa'. [CLIENT: ######]
    05/06/2013 22:54:51,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.
    05/06/2013 22:54:30,spid57,Unknown,SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
    05/06/2013 22:54:30,spid57,Unknown,SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
    05/06/2013 22:54:29,spid57,Unknown,SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
    05/06/2013 22:54:29,spid57,Unknown,Setting database option SINGLE_USER to ON for database ####.
    05/06/2013 22:48:48,Backup,Unknown,Database backed up. Database: info<c/> creation date(time): 2013/04/01(14:36:41)<c/> pages dumped: 191<c/> first LSN: 58:314:37<c/> last LSN: 58:330:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'##k'}). This is an informational message only. No user action is required.
    05/06/2013 22:48:47,Backup,Unknown,Database backed up. Database: ihms_audit<c/> creation date(time): 2012/11/17(14:28:01)<c/> pages dumped: 1680<c/> first LSN: 353:442:221<c/> last LSN: 353:533:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'##.bak'}). This is an informational message only. No user action is required.
    05/06/2013 22:48:45,Backup,Unknown,Database backed up. Database: Payroll<c/> creation date(time): 2012/12/18(18:59:19)<c/> pages dumped: 922<c/> first LSN: 368:2580:37<c/> last LSN: 368:2596:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'##.bak'}). This is an informational message only. No user action is required.
    05/06/2013 22:48:42,Backup,Unknown,Database backed up. Database: LAB<c/> creation date(time): 2012/09/10(17:19:53)<c/> pages dumped: 12561<c/> first LSN: 1175:17:219<c/> last LSN: 1175:110:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'##.bak'}). This is an informational message only. No user action is required.

    Wednesday, May 15, 2013 3:57 PM
  • Hi All,

    can any body help me to find out any sql server database put in single user mode automatically without any anybody intervention

    spid57,Unknown,Setting database option SINGLE_USER to ON for database #### 

    after single user mode we getting below mention flush this shows database was in auto close ONmode

    05/06/2013 22:54:30,spid57,Unknown,SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
    05/06/2013 22:54:30,spid57,Unknown,SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
    05/06/2013 22:54:29,spid57,Unknown,SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

    Hi Pawan:

    NO database will go to single user mode until their is a request from SQL Side.

    Please check the following:

    1) Check if any SQL Job is putting the DB to single user mode

    2) Please check the triggers

    thanks,


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Thursday, May 16, 2013 2:58 AM
  • try your luck by checking in the default trace. I just tested by putting a database in single user mode and it is captured in the default trace with EventClass "Object:Altered". it shows databaseid and databasename.

    It is not showing the command executed but it will give the SPID and who executed. I think that is what you are after. Its going to be little difficult in a production system but as you have the time and spid of when it happened you should be able to find this information unless the default trace has been overwritten.

    http://blogs.technet.com/b/beatrice/archive/2008/04/29/sql-server-default-trace.aspx 

    https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/

    For the other error you have it need not necessary be Auto Close on for the database but a whole lot of other reasons. check this Kb article (information section)

    http://support.microsoft.com/kb/917828?wa=wsignin1.0

    HTH


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com


    Thursday, May 16, 2013 8:46 AM
  • Hi Satish and Ashwin,

    first of all i want to tell you my sql server Agent stopped never running. and for SPID we already know the spid 57 alter the database.thanks for sharing these useful link. i will let you know in case i get something .

    Thursday, May 16, 2013 9:54 AM
  • You can go to eventviewer and check in Security and system logs who have logged into the server at that particular time,

    I hope you have different permission levels for diff logins.

    Also apart from what Satish and Ashwin suggested you can check which all users have alter database/db owner sysadmin  permission and can filter out some logins .May be this can narrow your search.Below query will give u table and access permission

    SELECT o.name, p.permission_name
    FROM     sys
    .objects o
    CROSS    APPLY sys
    .fn_my_permissions(o.name, 'OBJECT') p
    WHERE    p
    .subentity_name = ''

    Also there has to be request from some user or job to put DB in single user mode it cannot go by itself.

    reg"Unknown,SQL Server has encountered 2 occurrence(s) of cachestore flush' may be the login which had put ur DB in single user mode had also run few commands as below

    DBCC FREEPROCCACHE

    DBCC DROPCLEANBUFFERS


    Soldier..Sir we are surrounded from all sides by enemy.. Major: Good, we can attack in any direction Thats attitude.. Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, May 16, 2013 1:50 PM
    Moderator