September 17, 2009 at 8:43 am
Sorry Steve,
Much as I'd like to vote for it I appear to be lost in Microsoft Passport - I can't get them to send me a new password because my email address doesn't exist, but I can't sign up because my email address already exists.
Could you create another entry asking why MS Passport is so useless plis?
September 17, 2009 at 8:45 am
Gianluca Sartori (9/17/2009)
I coded a tool to track dependencies between code and database. It comes vey handy when you have your house keeping to do, but it's even more useful when you have to identify objects that will be involved when changing code or db objects.Unfortunately it's modeled on my dev environment, so probably you won't find it useful. I will try to make it more flexible and maybe attach it here if I succeed.
Does the code handle cross database dependencies?
That would be very helpful.
Honor Super Omnia-
Jason Miller
September 17, 2009 at 8:53 am
Nope: it's modeled on my dev env, that means two databases, app code, reports.
It works for me, it won't work for you...
The thing I want to stress here is that I had to do it from scratch, there's no ready-made tool around (I mean, none that I know of!).
-- Gianluca Sartori
September 17, 2009 at 9:29 am
Jason Miller-476791 (9/17/2009)
Ben Moorhouse (9/17/2009)
Apologies Jason - I meant that I knew we could log changes to objects and data. It's the ability to log select statements which would be incredibly helpful to me.Not just for housekeeping either, but for identifying people who write bad/inefficient code.
You can capture that as well. We have a trace that runs on our production reporting server to capture every single statement that executes on the machine. Let's just say we had some values changed, and "nobody" did it...
Can you give more details about the trace you use? That sounds really useful.
September 17, 2009 at 9:36 am
I agree with the comment that before or during your migration is NOT the time to look for stuff that appears to be redundant and deletable, because your migration will break enough things, and you don't want a distraction of things that you broke. An exception, an ideal really, is if your system is SO well designed that you CAN identify and purge redundant objects. In which case, why are they there? On the other hand, if junk objects aren't 90% of database size, how about leaving them alone because they aren't doing much harm. You aren't paid to be a janitor. (Unless it's a very small company.)
A colleague regularly e-mails our team to say that some of our servers which should have all the same objects have some odd ones. I believe he enjoys doing this and feels a sense of productivity from it, so that's fine. Many cases look very much as though someone ran a query and storyed results in a table, sometimes a database, named [andy] - I think I know who, but instead of us all doing that, I considered the idea of creating schemas (we just went onto SQL Server 2005) named for instance [2009-08] or something like, and we put longish-term temporary objects for quick dirty jobs in that schema and write an automatic process to drop everything in the schema at the end of the month indicated. Other possible approaches are to use a database for that purpose or just tempdb explicitly, but of course then you lose the objects if you have to reboot. So how about the "Schema of Limited Life Expectancy" idea?
September 17, 2009 at 9:40 am
rja.carnegie (9/17/2009)
So how about the "Schema of Limited Life Expectancy" idea?
That's what I do. After 6 months in the 'drop candidates' schema, the object is dropped.
-- Gianluca Sartori
September 17, 2009 at 9:53 am
rja.carnegie (9/17/2009)
On the other hand, if junk objects aren't 90% of database size, how about leaving them alone because they aren't doing much harm. You aren't paid to be a janitor. (Unless it's a very small company.)A colleague regularly e-mails our team to say that some of our servers which should have all the same objects have some odd ones. I believe he enjoys doing this and feels a sense of productivity from it, so that's fine.
The problem is much more complex than "enjoying" to notify odd objects or "feel a sense of productivity" from it.
I think a short "real life" story is needed here:
Six months ago we were asked to change the modeling of a basic information in one of our applications, that was previously stored in 2 columns and now is stored in 4 columns. For some reasons that I won't explain here, we decided to add the two missing columns to all the tables that stored this information. This involved over 200 tables, but, even worse, it involved 200 views and 180 stored procedures.
What we did to identify the objects to change was a search for the 2 previous columns names through the databases that backed the application. The first run of the search matched over 1000 items! We decided to do some housekeeping, to avoid changing useless objects and we cut lots of work.
Keeping things tidy is not only about "feeling productive", I think it's the first rule for really being productive.
-- Gianluca Sartori
September 17, 2009 at 10:04 am
We just prefix suspect out of date objects (tables, SP, columns) etc with "zzz 20090917 " (date of deactivation of course).
So, if it turns out we where too amitious, it is easy to undo.
On the other hand, if after 6 months we see the object is still out there with its "to be deleted" tag, then we can complete the deleting job.
September 17, 2009 at 10:41 am
Reading this editorial and the replies posted, I am wondering how many people consulted their company/corporate legal council before removing things? Sure, you might remove users who are long departed; that's understandable - but table columns, jobs, objects? Depending on the business you are in, removing these things may be tantamount to "destroying" company property, and worse, were any legal eagles to come calling asking for something old, something you had deleted on a whim, you might find yourself answering uncomfortable questions.
Case in point; presently down here in Boston, City government emails have "vanished" because they were deleted and the mail system apparently did not have the proper constraints set up. As I have followed this somewhat local story, I cant help but think that some wet-behind-the-ears IT person thought of this data as "his/her data" - free to do with it as they wished - and now they are receiving FBI subpoenas to explain what the heck happened.
I would strongly recommend to any DBA that you be sure you fully understand what you can and cannot delete. Do not merely presume that it is "your" database and therefore you can do what you want. I realize this depends on the industry you are in, but checking what your legal representatives think is a prudent idea. It aint your data - its the company's.
September 17, 2009 at 11:08 am
blandry (9/17/2009)
It aint your data - its the company's.
Great point. Fortunately, in my specific case, I have a reporting environment that I keep regularly backed up. Backups are stored 30 days of daily, monthly backups indefinitely.
I do know that our EMR db is something completely different. (That's electronic Messaging Repository). It stores everything, emails, IMs, and web traffic, "forever".
But you are absolutely right, before you change anything make sure you have an audit trail, backups, and approvals from business owners and higher ups.
Or, at least some really incriminating photographs...
Honor Super Omnia-
Jason Miller
September 17, 2009 at 12:57 pm
Blandry has a great point. You definitely should be aware of retention of data that's needed, especially for legal reasons. That's one reason I'd like a backup of any items I'd "deprecated' so that I could archive that in a place in case it needed to be brought back.
September 17, 2009 at 3:46 pm
We use extended attributes to track things of interest to us like deprecation and TTL (time to live) on SQL objects...
September 18, 2009 at 12:42 am
Where I work and have been working for the last two years we are constantly working to shut down old systems. Guess I am not in the mainstream then huh.
September 18, 2009 at 12:44 am
Where I work and have been working for the last two years we are constantly working to shut down old systems. Guess I am not in the mainstream then huh.
September 18, 2009 at 1:48 am
. I was getting a lot of error messages, didnt realise a post actually got throu ^^
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply