9 Things to Do When You Inherit a Database

Question

9 Things to Do When You Inherit a Database

Viewing 15 posts - 46 through 60 (of 83 total)

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

candiceolechna SSC Journeyman Points: 76 More actions · Answer 1

Hi Sylvia,

Thanks for the great list. Although I am a .net developer, I have inherited several Access databases, and I went through just about the same steps you listed here when I first started. So - I just wanted to say that this is a great list to print off and keep handy, whether you are supporting SQL or some other database. And if you are ever in the position of having an old, and shall we say "less than perfect" database thrown at you, it is a comfort to have a plan!Candice

wren.brynn SSC Rookie Points: 28 More actions · Answer 2

Good article. What's the best way to determine if an object is obsolete?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

wren.brynn (2/26/2010)
Good article. What's the best way to determine if an object is obsolete?

The only reliable way is to have ABSOLUTE control over all sourcecode that is EVER executed against a database and analyze all code for object names. The odds of that actually being reality are zero percent in every organization I have ever consulted at. :hehe:

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

Nadrek SSC-Insane Points: 20039 More actions · Answer 4

A few more, and some "I support the previous posters":

Look for Enabled but Untrusted constraints

+1 on check for index fragmentation

Check for external (OS level) fragmentation

Check for databases on OS compressed drives/folders (causes the above)

Check for excessive VLF's

Check autogrowth (too often it's in 1MB or 10% increments)

+1 on don't drop objects used for, say, end of year reporting!

Check the capacity planning

Check the physical spindle configuration (i.e. prod OLTP DB and prod reporting are on different LUNs, but share the same underlying physical drives... so reporting can and does impact OLTP IO performance)

Check the service pack/cumulative update/security patch levels and settings for SQL Server and the OS

Check the warranty/maintenance/licensing.

candiceolechna SSC Journeyman Points: 76 More actions · Answer 5

candiceolechna

SSC Journeyman

Points: 76

February 26, 2010 at 11:50 am

#1125761

Rename it, and then see who calls to complain. 😉

murph_32952 Valued Member Points: 73 More actions · Answer 6

Rename potentially obsolete objects by appending a "z" or "zzz" to the front of the object name. If the object is required, someone will get an error. Also, that moves all of those objects to the bottom of lists.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

murph_32952 (2/26/2010)
Rename potentially obsolete objects by appending a "z" or "zzz" to the front of the object name. If the object is required, someone will get an error. Also, that moves all of those objects to the bottom of lists.

I like this practice. I use it from time to time. Helps to eliminate unnecessary objects.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

irozenberg SSC-Addicted Points: 478 More actions · Answer 8

murph_32952 (2/26/2010)
Rename potentially obsolete objects by appending a "z" or "zzz" to the front of the object name. If the object is required, someone will get an error. Also, that moves all of those objects to the bottom of lists.

Not possible in some IT shops - mate, you are putting yourself on the line of FIRE (instead of PREVENTING errors your actions became a reason for errors).

murph_32952 Valued Member Points: 73 More actions · Answer 9

Grasshopper:

I don't disagree. Sometimes, however, when cleaning things up, it's almost impossible to tell whether certain objects are really being used or not. Once you have determined that something is almost assuredly not being used, this method helps confirm this, and it is very easy to undo.

Once the decision to clean something up is made, it must be recognized that some risk is involved. Everyone must decide for themselves what that risk is and how it can be mitigated. We have a database where the working analyst used the production dataset for dev and test. She worked directly in production and made changes on the fly during the day. The tables, views and stored procedures were a big mess. It was obvious that a large portion of the tables, views, and code were unsued (perhaps as much as half!).

Before actually deleting something from the db, when we ascertained that it was no longer useful, we renamed it. Out of over a hundred objects that we renamed, only one turned out to be used. We eventually deleted the renamed, unused objects, and created dev and test systems. The whole thing is much more manageable and easier to understand now. YMMV.

mpeters-878100 Right there with Babe Points: 716 More actions · Answer 10

I used the following method to remove any obsolete procedures

I ran a trace to pick up all stored procedures that were being referenced over 3 months. I then put the results into a table and used this table through queries to pick up any unused stored procedures. Scripted out then dropped all unused procedures, this seemed to work a treat

coleman.bill Newbie Points: 9 More actions · Answer 11

interesting that nobody has mentioned checking for external code - i once took over a db where the there were tasks defined to trigger a handful of various vb6 applications that used tables that looked otherwise completely unused. also check for dts packages and other legacy jobs - these can often turn out to support critical business functions... this is where you start to cross the line between db maintenance and application layer though.

often older legacy dbs are like a rat king with all sorts of VB6, MS Access, VBS, COM and other weirdy ticky tackies hanging off it, tied in to procs, scheduled "maintenance" tasks, functions - everything. And then there can be other weird crap like xp_sendmail requiring outlook to be installed on the server. You start to clean up your server environment and then KABOOM!!! :hehe:

TomThomson SSC Guru Points: 104773 More actions · Answer 12

People have talked about deletes and avoiding deleting things that are used once a year. Something else not to delete is error recovery and disaster recovery code - this may never be used but you had better not delete it if there is any.

Tom

Zach Austin Valued Member Points: 59 More actions · Answer 13

Don't know if this was mentioned in any of the previous replies, but I'd add "check indexing schemes" under the "Research" heading. Never assume previous developers have done a good job (or ANY job) with indexing.

bijayanix24 SSC-Addicted Points: 427 More actions · Answer 14

A lot of times we need a change . While working on an existing database, we may need to change the database name and in some cases want to rename existing database objects. This can be done in a few seconds.

In SQL Server this can be done in this manner :

1. Renaming a database :

The SQL Server command for renaming a database is :

Syntax: EXEC sp_renamedb 'oldName', 'newName' ;

eg: Suppose we have a database named "GPSTrainees" and we want to rename it to "KLMSTrainees"

We can write :

EXEC sp_renamedb 'GPSTrainees' , 'KLMSTrainees' ;

However, though this command works on SQL Server 2000 and SQL Server 2005, it will not be supported in future versions of SQL Server. The new command that should be used for SQL Server 2005 and beyond is:

ALTER DATABASE oldName MODIFY NAME = newName ;

eg: ALTER DATABASE GPSTrainees MODIFY NAME=KLMSTrainees

Hope it did answer your question.Please visit the link below for more details

Link

Cheers,
Bijayani
Proud to be a part of Team Mindfire.

Mindfire[/url]: India's Only Company to be both Apple Premier & Microsoft Gold certified.

JASONLMCLEAN SSC Rookie Points: 47 More actions · Answer 15

Fire up Red Gates Dependency tracker to get a graphical representation of the db. Turn on trace. Check permissions to see who/ what is updating the db. Finaloly write some custom .NET script to parse SSIS XML.

-Jason