-
Jul 29th, 2021, 11:43 AM
#1
Information on a Dropped...Something
A colleague of mine found that a view that he needed had been dropped, or seriously altered...and then it came back. If I sound vague about what happened, it's because I am. I'll know a bit more tomorrow, but I have a more general question.
I went looking through some log files and found very little, aside from the fact that the view had been last updated sometime in the early morning. The view is just a restriction of data from a table found in a different database, so the issue may have been that the other database became inaccessible, or the source table was dropped, or the view was dropped, or the source table was altered such that the view was no longer meaningful.
There is no specific logging we've added that looks for who/when alterations to tables are made (aside from adding/updating records in the table). So, all I have to go on is whatever comes with SQL Server. However, there is a whole lot of information that gets logged, and I don't know it all that well. So, what I'm looking for is any suggestions as to how to try to figure out what happened. There are very few of us who should be able to alter either the source of the view. Some are away, some have no interest in the database in question, and so on. We've basically ruled everybody out, which means that somebody probably did something without realizing they had.
Any suggestions on how to try to track this down?
My usual boring signature: Nothing
-
Jul 29th, 2021, 12:34 PM
#2
Re: Information on a Dropped...Something
What version of SQL Server? The log files that you said you looked at, have you looked further back in the past to see if this type of thing has ever happened before?
-
Jul 30th, 2021, 10:16 AM
#3
Re: Information on a Dropped...Something
I didn't include the version because I wasn't sure. I use a much more recent version than my office does, and I couldn't remember which one they were using. I believe it's 2012, so not utterly ancient, but not up to date. I'm using 2017...or is it 2019, I'll have to look sometime, because I realize now that I've flat out forgotten.
The log files I was looking at appear to be truncated. There's some backup software, Redgate I believe, which runs pretty frequently, and it looks like it is trimming the logs. Some of the events only went back to the day before, and none went back more than a few months.
I may be looking at the wrong thing, or looking in the wrong place. I have not delved into the log files as a matter of course, and don't really know my way around very well. I would probably benefit from a straight up description from somebody as to how they would look at something like this. What tools? How? etc.
My usual boring signature: Nothing
-
Jul 30th, 2021, 10:56 AM
#4
Re: Information on a Dropped...Something
Do you have transaction logs for the database? If in full recovery you could use SQL tools to look though them. The default trace might have something if it was not done long ago
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Jul 30th, 2021, 12:00 PM
#5
Re: Information on a Dropped...Something
Do you source control your db objects, or at least the scripts for them? I'm curious as to whether there's a stored proc someone's written that''s supposed to drop/create a temp view or something, and they forgot a # or ## and it's dropping/creating the view - and the name happens to be a coincidence. OR... someone thinks they're being clever and wrote a stored proc to intentionally drop the view and recreate it to flush the cached execution plan -- yes, I've seen this done before.
-tg
-
Jul 30th, 2021, 12:11 PM
#6
Re: Information on a Dropped...Something
If you want to find out who connected and when, google for this:
"ms sql server trace auditing for login success and failure"
-
Aug 1st, 2021, 08:35 AM
#7
Re: Information on a Dropped...Something
Originally Posted by techgnome
Do you source control your db objects, or at least the scripts for them? I'm curious as to whether there's a stored proc someone's written that''s supposed to drop/create a temp view or something, and they forgot a # or ## and it's dropping/creating the view - and the name happens to be a coincidence. OR... someone thinks they're being clever and wrote a stored proc to intentionally drop the view and recreate it to flush the cached execution plan -- yes, I've seen this done before.
-tg
To the first question, no. We're far too informal. Too informal for that, and probably too informal in general.
We think somebody did something bad, and we think we know who, but they aren't fessing up.
My usual boring signature: Nothing
-
Aug 1st, 2021, 08:36 AM
#8
Re: Information on a Dropped...Something
Originally Posted by szlamany
If you want to find out who connected and when, google for this:
"ms sql server trace auditing for login success and failure"
Thanks for that, I'll take a look on Monday.
My usual boring signature: Nothing
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|