October 31, 2011 at 10:02 am
We have a SQL Server 2008 instance in which someone took a database offline couple of weeks ago. I have identified the SPID from the Log Viewer. Is there a way to identify the User?
Thanks
October 31, 2011 at 10:34 am
I'm afraid, without a third party tool to log the connections you wont be able to as far as I'm aware
October 31, 2011 at 1:54 pm
It's not impossible that this may be in the default trace (you could check with a quick offline, online on a test DB. That way you're sure that if the info is available, you,ll find it).
This of course assumes that over those few weeks, the trace didn't rollover and delete that info (only 25 mb logged IIRC).
October 31, 2011 at 1:55 pm
Was he trying to see if it was still in use?
If so you can just put it on autoclose and search for those events in the default trace. Real easy way to decide if you can decomission that DB.
October 31, 2011 at 2:01 pm
Ninja's_RGR'us (10/31/2011)
This of course assumes that over those few weeks, the trace didn't rollover and delete that info (only 25 mb logged IIRC).
5 files of 20MB each.
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
October 31, 2011 at 2:05 pm
Tx Gail, 100 MB is still a tiny trace on a busy server... but it's better than 25 mb :hehe:
October 31, 2011 at 2:11 pm
Ninja's_RGR'us (10/31/2011)
Tx Gail, 100 MB is still a tiny trace on a busy server... but it's better than 25 mb :hehe:
Bear in mind that not much is traced, DDL, security events, no DML of any form. Also, new file whenever SQL starts regardless of how full the old one is, so frequent restarts would mean even less is kept.
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
October 31, 2011 at 2:19 pm
GilaMonster (10/31/2011)
Ninja's_RGR'us (10/31/2011)
Tx Gail, 100 MB is still a tiny trace on a busy server... but it's better than 25 mb :hehe:Bear in mind that not much is traced, DDL, security events, no DML of any form. Also, new file whenever SQL starts regardless of how full the old one is, so frequent restarts would mean even less is kept.
I've just had a quick look at ours.
We a small ERP system with only 50-60 users, we get only 6 weeks of trace. With 1 reboot that possibly cost us 1-2 weeks extra.
Now with a similar system with 10 000 users? Maybe we'd be able to keep 1 day of data.
That being said someone's working on permissions right now and they're bloating the logs like crazy (MS dynamics Nav has lots and lots of permissions to grant).
October 31, 2011 at 3:04 pm
Thank you Gail and Ninja. I queried the trace file for the database in question and none of the events had the same timestamp as what I was seeing in the SQL Server Logs. It is not a big deal, it was just kind of odd.
October 31, 2011 at 3:12 pm
Don Bricker-331219 (10/31/2011)
Thank you Gail and Ninja. I queried the trace file for the database in question and none of the events had the same timestamp as what I was seeing in the SQL Server Logs. It is not a big deal, it was just kind of odd.
Same timestamp of just close timestamp?
Do you mean that there's nothing left in the trace on those dates?
October 31, 2011 at 3:18 pm
Ninja's_RGR'us (10/31/2011)
Don Bricker-331219 (10/31/2011)
Thank you Gail and Ninja. I queried the trace file for the database in question and none of the events had the same timestamp as what I was seeing in the SQL Server Logs. It is not a big deal, it was just kind of odd.Same timestamp of just close timestamp?
Do you mean that there's nothing left in the trace on those dates?
October 31, 2011 at 3:20 pm
Same timestamp of just close timestamp?
Do you mean that there's nothing left in the trace on those dates?
There are still records left from that date as well as the days before, but I see no entry in the trace file for the time I would expect based on what was in the log.
November 1, 2011 at 12:53 pm
Ninja's_RGR'us (10/31/2011)That being said someone's working on permissions right now and they're bloating the logs like crazy (MS dynamics Nav has lots and lots of permissions to grant).
I hate Dynamics! Isn't it interesting how Microsoft developers can't design an application that effectively uses their own database engine? I would even accept one that followed at least a couple of best practices 🙂 Sorry, this is not really the place, but I had to put in my 2 cents...
Jared
Jared
CE - Microsoft
November 1, 2011 at 1:31 pm
Just a way to import your default trace and query it... see attachment
( this one reports auto grow events ,should be peace of cake to have it filter detach or so)
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
November 1, 2011 at 1:41 pm
jared-709193 (11/1/2011)
. . . Isn't it interesting how Microsoft developers can't design an application that effectively uses their own database engine? I would even accept one that followed at least a couple of best practices . . .
May I humbly offer SharePoint and TFS as two examples of pretty big, sophisticated, SQL Server-based apps?
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply