January 4, 2011 at 10:35 am
Yes, that worked. There are only about 8 jobs in there, so not much to find, but they all came up.
January 4, 2011 at 10:55 am
kmaida (1/4/2011)
Yes, that worked. There are only about 8 jobs in there, so not much to find, but they all came up.
Do you have any external suites that do maintenance on the system (like backup exec). I've never heard of it breaking the app like that but that's one option to investigate.
I'm also using MS Dynamics Nav and in there there's an option to set single user. So maybe your apps also have that option.
January 4, 2011 at 11:56 am
Ok, got into the trace file. I found a few lines in there pertaining to the database in question. These lines appeared at 5:00 this morning and only for this one db. It was right after this that there was line after line of "audit login failed" messages.
Can anyone decipher them?
January 4, 2011 at 1:27 pm
Ok, finding the time that it was occurring may have helped us figure it out. We'll see tomorrow morning, but it appears that it was an old scheduled task syncing the db to an external db. That would have switched it to single-user. We've removed the task and hopefully the issue is fixed.
Thanks so much for all your help! I couldn't have done it without you!!
January 4, 2011 at 2:49 pm
to import and process the default trace files have a look at
http://sqlblog.com/blogs/tibor_karaszi/archive/2008/06/19/did-we-have-recent-autogrow.aspx
It is a good starting point to figure out when things happen !
Then start a regular trace right before that time and figure out who is doing it.
... and why.
Someone probably forgot to put it back into multi user mode after they needed it to be in single user.( never really needed to do that unless for big schema modifications and upgrade processes)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 4, 2011 at 2:54 pm
also check out the restore process in the trace.
If you restore a backup of a db in single user mode, it will be in single user mode when the restore completes !
So, You'll have to figure out:
- why the restore occurs
- modify it to alter to multi user after the restore and post restore maintenance (security,..)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 4, 2011 at 2:54 pm
ALZDBA (1/4/2011)
to import and process the default trace files have a look athttp://sqlblog.com/blogs/tibor_karaszi/archive/2008/06/19/did-we-have-recent-autogrow.aspx
It is a good starting point to figure out when things happen !
Then start a regular trace right before that time and figure out who is doing it.
... and why.
Someone probably forgot to put it back into multi user mode after they needed it to be in single user.( never really needed to do that unless for big schema modifications and upgrade processes)
Did you ever write that article about how to make your own default trace? I know it's easy enough to schedule a trace, but how can we get the same trace definition as the default trace??
January 5, 2011 at 1:37 am
No I didn't write an article on it.
Adam Hanes published a nice artilce at SSC: "Default trace - a beginner's guide" http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/ with a nice follow up discussion.
Another, more easy way to (re)start the default trace has been published at SSC by Jeffrey Yao http://www.sqlservercentral.com/blogs/jeffrey_yao/archive/2010/8/13/restart-sql-server-default-trace.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 5, 2011 at 2:16 am
Thanx. But what I need is to be able to control how much data I keep in the trace (above and beyond 5 files @ 20 mb).
January 5, 2011 at 9:35 am
I must admit I haven't heart of such a setting to controle the file size for the default trace, nor did I search for it in sqlserver.
The easiest way IMO is just to copy these files away at run time of the instance.
Maybe even look for a sqlserver event to capture trace file switch and set up a SSB app to copy the files away to safe zone. (might be Event number 150 :trace file close)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 5, 2011 at 9:54 am
ALZDBA (1/5/2011)
I must admit I haven't heart of such a setting to controle the file size for the default trace, nor did I search for it in sqlserver.The easiest way IMO is just to copy these files away at run time of the instance.
Maybe even look for a sqlserver event to capture trace file switch and set up a SSB app to copy the files away to safe zone. (might be Event number 150 :trace file close)
Close, but no cigar. The actual event should be trace file roll over and that does not exists.
I do have all 5 files in there, yet there's nothing about trace end. There's an alter trace event too but that one seems like a deadend too.
The best I can come up with is tu use
SELECT * FROM fn_trace_geteventinfo (1)
and then reverse engineer the create script from there.
January 5, 2011 at 10:28 am
For that matters, you can just open the trace file with profiler and use the save trace as script feature.
I thought you were looking for a solution to actually modify the systems default trace.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 5, 2011 at 11:32 am
Hmm, why the heck didn't I think of that???
Yup, just open the trace file, script it out, disable the default trace and run that one of startup and we're done.
January 5, 2011 at 11:41 am
Ninja's_RGR'us (1/5/2011)
Hmm, why the heck didn't I think of that???Yup, just open the trace file, script it out, disable the default trace and run that one of startup and we're done.
Am I doing something wrong? When I save the template of the default trace and reopen it again none of the events are there?!?!?
January 5, 2011 at 12:03 pm
Cannot test on SQL2005 profiler right now, but on SQL2008R2 the saved template contains the expected events and columns
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply