June 15, 2012 at 3:22 pm
SuperDBA-207096 (6/22/2009)
"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!
Agreed. This was an excellent article, but along with Mark I would urge caution on the drop obsolete objects step. It is definitely a good thing to do, just make certain that they are truly obsolete. (Having good backups and version control can provide some measure of protection here, but it is still a problem best avoided.)
I would also add "Read the documentation and comments in the code, if they exist." I know I still have a long way to go in writing documentation for my databases, but I do tend to heavily comment the code and that is the first place I would tell someone new on my team to look.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
June 16, 2012 at 2:14 am
mark hempen (6/15/2012)
Also don't forget to check out what user account owns the stored procedures and the security role attached to that login. I have had many instances in the past where dbo priveleges were necessary and didn't exist.
Don't you mean the other way around? I wouldn't be adding dbo permissions if your application is working without them - it just creates an uncessary security hole.
I have seen a number of instances where dbo permissions were added as a quick work around for a permissions issue and never removed, so I agree that it is a good thing to check for. If you can spare the time to audit what permissions the login actually needs, it is by far preferable to work out how to remove dbo. It could even be essential depending on the security profile of the system.
June 17, 2012 at 2:46 am
Nice article. But what I want to specify is a list of some 3rd party tools usage like
SQL Doc: for documenting the database.
Sybase Powerdesigner: for managing database as well as understanding schema/usage.
SQL Source Control Tool: for controlling schema as well as data.
and don't forget to use the SQL Compare/Data Compare tools to rapidly manage changes among different servers and/or identify any issue by comparing the different version of databases.
June 17, 2012 at 11:52 am
Very nice little article from some years ago! Thanks.
I agree with others that just dropping obsolete objects is a dangerous task, especially when you just got the job and don't know the schema very well.
I would also add a prefix to these obsolete objects (like another user suggested) so they will be grouped together in the Object Explorer or when querying the database. Also if a table has an "EntryDate" column it'd be a little easier to know when it was last used or not.
Now if it's absolutely necessary to drop those objects (because of disk space or something) I'd script them using the SSMS' "Generate Scripts Wizard" including structure and data to files so I could easily restore them if I delete something that shouldn't. If a database is not so big, I just save a private COPY_ONLY backup to a special place (or two) instead.
When there are humans involved there's always the risk of something going terribly wrong. 😀
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
June 18, 2012 at 4:58 am
I agree with everything except the Source Code... I really don't bother.
What I find important to add is:
- Document => we keep a template which we can complete with the most important things that we need to know about the system (eg: backup, logins, known issues, etc)
- passwords and security in general, is it set up? Who has the passwords, what privileges and do they need it? Let's face it, if there's someone else running around with dba privs, then who is responsible for the DB?
All in all a good blog.
Cheers!
Jo
June 23, 2012 at 9:43 pm
Big props for mentioning the most important th1ng, and putting it first: Backups.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 24, 2012 at 1:34 pm
On the topic of "obsolete" objects, dropping them if they haven't been accessed in 3 months or a year is a big mistake. I've seen objects that are only accessed on leap years and if dropped would cause serious implications. My advice on objects that you think are obsolete, leave them alone unless you are absolutely, 100% certain they will never be used again. Otherwise you could be setting yourself or someone else up for big problems.
June 25, 2012 at 9:50 am
I'd get the separate dev and test app and database environments set up and working and run the everyday, period end, month end, year end and any other time procedures on that with profiler to find what is used and document it as well as running profiler on live to find out what gets connected to from outside the apps.
September 11, 2012 at 9:55 am
Really good article. I guess list could be end less as the time pass by requirements or usage change. But at least when you just been handed a DB and if you know even the only options listed here should be ok for a start. Other good suggestions could follow as given by others...
Also this could be asked in DBA interviews as at times basics are not followed getting lost in advance features..
Thanks
Viewing 9 posts - 76 through 83 (of 83 total)
You must be logged in to reply to this topic. Login to reply