Post reply

Table Design

  • March 11, 2015 at 1:53 am

    #295859

    Hi All,

    Someone has changed table design for example changed the datatype of the production DB without any information. How can I track the user who did it.

    Please help me on this.

    Thanks,

    Arooj

  • March 11, 2015 at 3:13 am

    #1783164

    You could try using the undocumented fn_dblog. The article referenced by this link http://www.sqlskills.com/blogs/paul/finding-out-who-dropped-a-table-using-the-transaction-log/ concerns finding out who dropped a table, but you could adapt to look for ALTER statements.

    I would try this out on a Test/Dev database first!

    On a wider note, I suggest you review the security you have set up. 😉

    Regards

    Lempster

  • March 11, 2015 at 3:39 am

    #1783175

    Thanks Lempster for your suggesation, but this one small application, User will be using very rarely. I believe that has been happened almost 15-20 days back and log is also maintain for only 10 days so i could not found from the log also and what you have given link this will be showing for one days.

  • March 11, 2015 at 3:55 am

    #1783182

    Unless you have some auditing already set up, you probably can't.

    This may indeed be a good time to revisit the security setup on that DB.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 11, 2015 at 4:21 am

    #1783189

    Hi Gail,

    Thanks for the reply,

    Is there any way to maintain default trace file for a month or couple of months. Because when I seen each and every server is maintaing default trace with different timing like some of the server is maintaining one day trace file and some are 10 days . Can we fix this for atleast one month.

    please suggest.

    thanks

  • March 11, 2015 at 5:43 am

    #1783215

    The default trace is 5 files of 20MB each, that's the same on every SQL server.

    How far back that goes depends on how active the server is and how often it's restarted.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 11, 2015 at 5:53 am

    #1783222

    Yes Gail you are correct , each server is having 5 files , but the timing is differ. In one server where transaction load is less is maintaining 10 days default trace files but the other server where transaction is more is maintaing only 2 days trace log.

    I know we can not extend default trace. Is there any way to create trace log which will be seperate from default trace which I can maintain according to requirement.

    Please advise.

    Thanks in advance

  • March 11, 2015 at 6:00 am

    #1783227

    Sure. Go read up on sp_create_trace and create one that suits your requirement.

    The retention of the default trace isn't measured in time. It's in file size. 5 file of 20 MB. Yes, on some servers that'll go back further in time than others. More active servers will use up the 100MB faster than others.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 11, 2015 at 6:21 am

    #1783242

    Thanks Gail for your support 🙂

  • March 11, 2015 at 7:02 am

    #1783259

    If you are going to try to capture events on a 24/7 basis, you might want to use extended events instead of trace. They have less impact on the server. You will have to deal with the XML output though.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply