9 Things to Do When You Inherit a Database

  • Sylvia Moestl Vasilik

    Old Hand

    Points: 384

    Comments posted to this topic are about the item 9 Things to Do When You Inherit a Database

  • P Jones

    SSChampion

    Points: 12323

    Run the performance dashboard reports and have a look at long running queries and missing indexes.

  • John Gregory-458324

    SSC Rookie

    Points: 25

    This is a good check-list - thanks very much.

    It sounded like you suggested deliberately causing a problem to get buy-in from your users (...sending out hundreds of thousands of emails). Perhaps I misunderstood this bit. Abusing trust is never a good long-term strategy.

    Thanks

    J

  • SuperDBA-207096

    SSCrazy Eights

    Points: 8176

    Most of this looks pretty good but this could be a recipe for disaster:

    "Drop obsolete objects"

    It's sometimes not straightforward how and when objects are used. before dropping something you need to be sure it's really not used.

    It's quite possible you could drop a sproc or table thats only used periodically (once a month or once a quarter). This could create major problems for you a few months out!

    Just my 02c... 😉

    Mark

  • Michael Garstin-341427

    SSC Rookie

    Points: 44

    I am stunned that the list does not include "Run CHECKDB" (or at least find out when it last ran).

    The free but unsupported RML Utilities released by the Microsoft SQL Server PSS engineers can be used to analyze a Profiler trace. That can tell you what is running and how often.

    This would be a good spot to plug Red-Gate's SQL Doc tool (no, I'm not a RG employee!). This might not be free but it is a good way to acquaint yourself with a new database.

  • TheSQLGuru

    SSC Guru

    Points: 134017

    1) I agree with Mark about Drop obsolete objects. It is virtually impossible to find and properly scan all code that might run against any given database that might access a particular object. Just don't go there.

    2) I fire up ApexSQL Doc first thing and get it going creating a full blow set of documentation about the database.

    3) I check quite a few configuration options such as statistics settings, auto-close/auto-shrink, recovery mode, growth increments, etc.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • stevecurtis3

    Old Hand

    Points: 337

    We never delete anything in one go!!! What we do is change its name by prefixing XXX to its name, and then later delete all the XXX's. As we run a weekly system, we can delete the XXX's for most of our system after a fortnight. For the monthly and quarterly bits, we just wait a bit longer!!!

    Rgds,

    Steve

  • TheSQLGuru

    SSC Guru

    Points: 134017

    stevecurtis3 (6/22/2009)


    We never delete anything in one go!!! What we do is change its name by prefixing XXX to its name, and then later delete all the XXX's. As we run a weekly system, we can delete the XXX's for most of our system after a fortnight. For the monthly and quarterly bits, we just wait a bit longer!!!

    Rgds,

    Steve

    But how can you know that object A is a daily/weekly/monthly/quarterly/yearly/etc object? If this is a database you have no prior knowledge of (heck, even if it is) you run a significant risk IMNSHO.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • rperry

    Valued Member

    Points: 71

    I like this article a lot...just the right number of topics to get someone started on a newly inherited database (or even someone new to DBA work themselves).

    In line with other posters who remark about what's missing from the list, I would suggest they post their own "new DB to-do" lists so everyone can get a diverse view of this topic. Also, I'm putting together my own list called "Things to Do When You Inherit Replication"....which brings with it some very specific needs.

  • mzak

    Mr or Mrs. 500

    Points: 562

    Great list. I would throw in "review security privileges"

  • cy-dba

    SSCarpal Tunnel

    Points: 4149

    Good article. I would also add having an updated data dictionary helps too.

  • stevecurtis3

    Old Hand

    Points: 337

    But doing the delet in two stages does reduce the risk a bit doesn't it??

  • Thomas-282729

    Say Hey Kid

    Points: 663

    I must say that from a maintenance point of view, one of the hardest skills to teach developers is the skill of removing obsolete code. Many developers simply comment old code even if it is in source control or leave it because doing an analysis of whether it is still in use takes more effort. However, from a maintenance standpoint, removing obsolete code is crucial to having a well maintained system.

    In SQL Server, unfortunately, all stored procedures are effectively scoped public which makes determining obsolescence difficult. It would be nice if there were a simple way of scoping a procedure such that it could only be executed from another stored procedure and not from the outside.

  • Andy DBA

    SSCommitted

    Points: 1776

    John Gregory (6/22/2009)


    ...

    It sounded like you suggested deliberately causing a problem to get buy-in from your users (...sending out hundreds of thousands of emails). Perhaps I misunderstood this bit. Abusing trust is never a good long-term strategy.

    ...

    I didn't get that impression at all. I got the impression that it might help in cost justification or work policy discussions to mention horror stories from past experiences either with the newly inherited system or other systems where development occurred against production. If it's been going on for any length of time at all, it's very likely a blunder was made at some point that upper level management wishes they could forget.

    This point ties in well with mzak's point about reviewing security policies. If developers have no rights on the production system, they won't be tempted to run that poorly tested "quick fix" when nobody's looking.

    Good overview article and follow-up discussion.

  • Jon Monahan

    SSCarpal Tunnel

    Points: 4954

    Sylvia - nice article to get started with. I also like to look at performance monitor to get an idea of peak load on the CPU, Memory, Disk utilization, etc. And I'm a big fan of the idea of renaming objects - and keeping them for a year (if you just inherited the database and/or server). Too many objects get used for one tiny, but critical, app that gets run once per year... No point in taking any chances. After the year is up - feel free to delete all of those renamed objects.

Viewing 15 posts - 1 through 15 (of 84 total)

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