Results 1 to 8 of 8

Thread: Information on a Dropped...Something

  1. #1

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    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

  2. #2
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    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?

  3. #3

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    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

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    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

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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"

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Information on a Dropped...Something

    Quote Originally Posted by techgnome View Post
    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

  8. #8

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Information on a Dropped...Something

    Quote Originally Posted by szlamany View Post
    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
  •  



Click Here to Expand Forum to Full Width