9 Things to Do When You Inherit a Database

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

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

  • 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

  • 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

  • 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.

  • 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

  • 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

  • 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

  • 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.

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

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

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

  • 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.

  • 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.

  • 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