DB Reverts to Single-User Mode

Question

DB Reverts to Single-User Mode

kmaida

SSC Enthusiast

Points: 152
More actions
January 3, 2011 at 11:15 am

#149633

SQL Server 2005
I am having an issue with a database reverting to single-user mode almost on a daily basis. Each time I have to go in and manually change it back to multi-user, causing a lot of frustration for the users. This db was restored originally from a backup of a template I created and no other db's restored from that template have this issue; just the one.
Does anyone have any idea what could be causing this? I'm assuming something must be triggering it, but I have no idea where to begin looking. As far as I can tell, all the settings are the same as my other databases.
Any suggestions would be greatly appreciated!!
Thank you!

Viewing 15 posts - 1 through 15 (of 30 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

I'd start with checking the jobs in SQL Agent on that server. It could be being done by another scheduler, but my guess is it'd be local if anywhere. Look for a statement starting with ALTER DATABASE.

Next step would be to start a serverside trace and figure out the machine/login that the command is coming from. Look for ALTER DATABASE commands coming in.

You might also look in the log files to make sure you don't have an automated restore occurring every morning.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

kmaida SSC Enthusiast Points: 152 More actions · Answer 2

Sorry, I feel like a complete novice, but how do I check to see if there's an ALTER DATABASE statement being run on the db thru a job?

Thanks!

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 3

kmaida (1/3/2011)
Sorry, I feel like a complete novice, but how do I check to see if there's an ALTER DATABASE statement being run on the db thru a job?
Thanks!

You can query directly against the tables in MSDB. That'd be the quicker way to do it. Something like this:

SELECT sj.name,

sjs.step_name,

sjs.command

FROM dbo.sysjobs AS sj

JOIN dbo.sysjobsteps AS sjs

ON sj.job_id = sjs.job_id

WHERE sjs.command LIKE '%ALTER DATABASE%'

"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

kmaida SSC Enthusiast Points: 152 More actions · Answer 4

When I run this I get the following error:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'dbo.sysjobs'.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

It works for me and I'm on 2005 too. Make sure you are in the MSDB databse.

kmaida SSC Enthusiast Points: 152 More actions · Answer 6

Ah, sorry, I was in the problem db. Ran fine in msdb. It found nothing. Anything else I might try?

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 7

I'm not sure, but it has to be somewhere in the system or attaching to the system. There's no reason for it to just randomly set itself to single user.

"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

Marco V SSCrazy Points: 2939 More actions · Answer 8

check the default trace for the alter statement it is located in youre log directory by default.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

do you have anything in the sql and windows logs pointing to a possible reason?

Is your server or msdb case sensitive? Maybe you could rerun the query with a lower on both sides of the equation.

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 10

Marco V (1/4/2011)
check the default trace for the alter statement it is located in youre log directory by default.

Excellent idea. That's very likely in the default trace.

"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

kmaida SSC Enthusiast Points: 152 More actions · Answer 11

I tried opening the most recent trace file, but it gives me a "failed to open - access is denied" notice and won't open it. I tried opening it both from Explorer and the Profiler. Is there a special way to open it?

Thanks for the help you all are giving, btw! I really appreciate it, and am learning a lot in the process...

kmaida SSC Enthusiast Points: 152 More actions · Answer 12

I tried running it with the "alter database" in upper/lower, as well as all lower and still nothing.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

I've never had that error. Make sure you are connected as system admin on the server.

Also I've tried using the default trace to target single_user changes and it seems that this is not logged... I hope I'm wrong tho.

http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/

Here's an exemple of a path to look for to make sure that the trace file actuall exists on your server :

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_554.trc

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

kmaida (1/4/2011)
I tried running it with the "alter database" in upper/lower, as well as all lower and still nothing.

Try with something really silly to make sure it actually works on your server. Like a search for '%a%'