July 15, 2014 at 9:10 am
Hi,
I need to check when a database went into a single user mode and for what reason? is there any queries or any way to check that.
Thanks in Advance
Chelladurai
July 15, 2014 at 10:12 am
I'm pretty sure there's no way to check that. You might look at the system health extended events session. It has a rolling log of certain events, but I'm pretty sure setting the database to single user is not one of them. But, it's your best bet if you don't have any other kind of auditing set up already.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 15, 2014 at 10:39 am
There will be a message in the error log saying who did it (what login). And it probably is 'who'. SQL doesn't automatically switch databases to single user, either someone ran an alter database or there's a job/app which ran the alter database.
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
July 15, 2014 at 10:52 am
It's not in system_health. You can possibly get it from the Default Trace looking for the Object:Altered event and ObjectType of 16964 which is Database. It doesn't give the actual alteration but it does give the time the Object:Altered was run so you can get a pretty good idea of who did it, if you know about when. There shouldn't be that many instances of Object:Altered events for databases.
Gail's (GilaMonster) solution is really the best one.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply