Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
baseballerkra
Starting Member
3 Posts |
 Posted - 2007-10-30 : 13:21:23
|
| I must introduce myself... Kris Allain. I am a VERY new DBA. Basically I as sort of pushed into the position even though I have no previous DBA experience. I come more from a Net Admin background although I've only graduated with a degree in MIS 2.5 years ago.With that said, I ran into a problem last week in which took down a mission critical application for 4 hours. We have an app that his highly transactional and I'm using the Full Recovery method for this DB with weekly full backups, daily differential backups, and hourly log backups. Someone (could have been me) disabled the log backup job and a couple of days later the log grew until there was no more disk space (35+ gb). Well, it took me 4 hours to back up and shrink the log and get the system up and running again.I told my boss what had happened and now he wants to know who did it. I told him it could've possibly been me, but I highly doubt it because it appears to have been disabled between 7-8pm on my "work from home" day, which I specifically remeber packing it up and playing xbox at 4:30pm.Could someone help me figure out what happened and if there it is possible to track these changes in the future? |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-10-30 : 14:28:22
|
How do you not know if you did it? In any case, the first question is, who has access to do this in your organization and do they need it? Secondly, you should have monitoring and notifications in place for when drive space gets within 10% of capacity. Third, if being a DBA is your full time job then you need to start paying closer attention to the state of your database, for example you can setup SQL mail to send notifications on job success.
Future guru in the making. |
 |
|
|
baseballerkra
Starting Member
3 Posts |
Posted - 2007-10-30 : 15:26:34
|
Thanks for the contructive crticism. You bring up great points that I will definitely look into. Domain Admins, developers, myself, etc have access to the box. I don't care for this at all, but I am the first DBA in my organization and have no experience in this field. I know that if I start cutting access and removing "unnecessary" users, then things will break. I need to take it slow and start to map out how applications utilize the databases. I've only been in this position for a few months and I am not just a dedicated DBA. I am a DBA/Net Admin/Jack of all trades. I've been on the road for the last month with the exception of the few days in which the problem occurred.As far as how do I know that I didn't do it? Well as stated previously, I don't. I seriously doubt I would go and disable my log backup job and not remember it, especially being that job history events stopped between the hours of 7 - 8pm. I specifically remember packing up my laptop on that day at 4:30pm so I seriously doubt that I did it.Again, thanks for the constructive criticism as I will be looking for that exact thing in the future because I have no one at my organization to ask database questions to. Back to the question at hand, is there a way for me to find out who disabled the job? My boss wants EVERYTHING logged in the future.quote:
Originally posted by Zoroaster
How do you not know if you did it? In any case, the first question is, who has access to do this in your organization and do they need it? Secondly, you should have monitoring and notifications in place for when drive space gets within 10% of capacity. Third, if being a DBA is your full time job then you need to start paying closer attention to the state of your database, for example you can setup SQL mail to send notifications on job success.
Future guru in the making.
|
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-10-30 : 16:04:06
|
quote:
Back to the question at hand, is there a way for me to find out who disabled the job? My boss wants EVERYTHING logged in the future.
Probably the best you can do there is to turn on login auditing in the SQL Server properties. See: http://technet.microsoft.com/en-us/library/ms175850.aspxIf it was already configured, you can look in the logs to see who all was logged on at the time the change was made. The act of disabling a job is not logged anywhere (to my knowledge).
Future guru in the making. |
|
|
|
baseballerkra
Starting Member
3 Posts |
Posted - 2007-10-30 : 16:33:36
|
Ah, OK. That is basically the conclusion I was coming to being that I haven't found anything on it after a couple days of googling. I know there are ways to track inserts, deletes, updates, etc, but wasn't sure about enabling, disabling jobs, etc.quote:
Originally posted by Zoroaster
quote:
Back to the question at hand, is there a way for me to find out who disabled the job? My boss wants EVERYTHING logged in the future.
Probably the best you can do there is to turn on login auditing in the SQL Server properties. See: http://technet.microsoft.com/en-us/library/ms175850.aspxIf it was already configured, you can look in the logs to see who all was logged on at the time the change was made. The act of disabling a job is not logged anywhere (to my knowledge).
Future guru in the making.
|
|
|
|
|
|
|
|
|
Who disabled my job?!