January 3, 2011 at 11:15 am
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!
January 3, 2011 at 11:21 am
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.
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
January 3, 2011 at 7:18 pm
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!
January 3, 2011 at 8:57 pm
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
January 4, 2011 at 7:37 am
When I run this I get the following error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.sysjobs'.
January 4, 2011 at 7:40 am
It works for me and I'm on 2005 too. Make sure you are in the MSDB databse.
January 4, 2011 at 7:54 am
Ah, sorry, I was in the problem db. Ran fine in msdb. It found nothing. Anything else I might try?
January 4, 2011 at 8:07 am
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
January 4, 2011 at 8:09 am
check the default trace for the alter statement it is located in youre log directory by default.
January 4, 2011 at 8:10 am
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.
January 4, 2011 at 8:11 am
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
January 4, 2011 at 8:27 am
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...
January 4, 2011 at 8:27 am
I tried running it with the "alter database" in upper/lower, as well as all lower and still nothing.
January 4, 2011 at 8:30 am
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
January 4, 2011 at 8:31 am
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%'
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply