SQL Server Performance Forum – Threads Archive

How to determine if a SQL database is accessed

We have 15-20 SQL Servers with 100-200 databases across the lot. We have a MAJOR problem with programmers creating databases and leaving them out on the servers taking up precious space. I am looking for something to montior the servers/databases and let me know what databases have NOT been accessed in xx months. Monitoring database changes will not work because many of the database are static in nature and only get queried against.
Any ideas? Jon Wright

If you can deply third party tool then the best is LUMIGENT’s ENTEGRA which has AUDIT facilities where you can get information easily
on all the database across all the servers. Refer tohttp://www.lumigent.com for more information. _________
Satya SKJ

You can also use the script I posted here:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=298 /Argyle

Valid reference by Argyle, it makes your job easy. _________
Satya SKJ

Except doing all this after the fact checking, you might also want to implement more stingent development procedures and permissions. The fact that developers can create databases almost at will apparently increases the chance that they are developing against a version of the database that is not the same as other developers on their team are developing against. Results: incompatible code, bugs, re-testing, re-desgin, re-development and a huge waste of time and money in general. Secondly, most application (VB/C++/Delphi/ASP etc) developers don’t have the skills to create a proper database design or write proper SQL code for anything but trivial stored procedures. It is the task of the DBA to review database changes created by these developers and adjust them where necessary before they go into testing and production. Instead of letting the developers run loose as now seems the case, you have to tighten up the environment. This might involve a lot of "political" work, but as long as you can convince your boss that you are wasting your time (and thus his/her budget) on cleaning up problems caused by other people that can be avoided, you should be able to get somewhere.

Sorry to butt in here off topic but that last post was a bit harsh looking at it as a developer, I think most developers have realised that understanding and being able to come up with a sound database design is probably the most important part of a system and alot of your application design also relies on it. Sure junior developers may create bad table structures but they shouldn’t be creating tables for major appolications at that level. Having DBA’s review every table, view, stored procedure etc.. that developers create will ultimately bog the DBA down with script vetting, there has to be some trust in the developers just as they trust the DBA’s to maintain the database, rebuild indexes etc… Sorry just had to get that off my chest, the post came accross quite condecending (even though I’m sure it wasn’t meant to be) <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Cheers Shaun World Domination Through Superior Software

Hi Shaun, Sadly enough my post sums up my real world experience. On my current project half the tables do not have Primary Keys, a lot of stored procedures use temporary tables and occasionally cursors where things could have been done with derived tables and CASE. On the project before that the database had no foreign keys at all, stored procedures basically returned SELECT *, Primary keys violated business rules etc. I also worked on a project where the architect, a C++ guy, decided to do all the data integrity checks in the application, even though the application covered the major business process of the client (so imports from other systems are quite likely) and the client had explicitly stated that lack of data integrity had been a major concern in the past (read: it had cost them bucket loads of money). All the people who were responsible for the architecture and design of these applications had at least 5 years experience as a developer, and probably a lot more. I also did a technical test for a senior VB/SQL developer role (Â£45K) where the SQL part consisted of what I considered a relatively trivial database design and some queries with outer joins, also not very taxing. I didn’t get the job because my VB was too rusty, but I was told that my SQL skills were by far the best they had come across, which meant that I got the results that were required and no one else did. If you work with developers who are capable enough to adjust the database schema properly and write correct and efficient stored procedures, count yourself lucky. As a DBA I prefer to be bogged down by having to review all the tables, views, stored procedures etc that the developers come up with, rather than having to fix bugs and performance problems when the application goes to testing or production, because that costs a lot more time in general. Jacco

It would be wise the run the script provided above by Argyle, take the necessary steps to the eliminate/reduce/control the unused databases and then allocate specific TEST server(s) space for your developers. That way, even the solution (the script or any third party tools) wouldn’t be necessary. Developers need to be ‘free’ to be creative…but creativity needs to be controlled for productivity to emerge. Your own productivity as a DBA is at stake. Peace.

Argyle,
You posted a link to another thread with a script for this. I would love to see what you came up with as I am in need of one.
Thanks,
ttucker

Which version of SQL you are using?
Do you have same requirement as the one above?

SQL 2000
I just need to know when the databases were last accessed because there are several on one server that appear to be old.

That information cannot be accessed easily with a few TSQL statement, probably you need to run a Server side trace for the database access to see what exactly is happening.
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
http://www.dbazine.com/sql/sql-articles/larsen6
The links above should help you to setup server side trace.

That was my belief as well. And then I came across the post from Argyle stating that he had some kind of script, so I was inquiring to see if I could get that script and see if it would work. When I attempted to pull up the link he posted, it says that the post was archived and didn’t exist any longer.

2003 is a long time and you will be good to email him using this forum’s option. Click on this name to go further.

hi argyle
this path is not available can you please provide me the same.
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=298
amit

]]>