November 27, 2007 at 9:20 am
I have been asked to examine databases on a new server (to me) as the server is being decommisioned. I need to know what databases have been used recently or not and possibly have not been used in weeks, months or longer.
Is there some administrative queries that I can run to determine usage or is there some monitoring I can set up to see how it is being used now?
Gary
November 27, 2007 at 9:40 am
Gary, are you interested in looking at activity that has already occurred or in setting something up to monitor activity over a period of time starting now? What sort of metadata are you interested in capturing?
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
November 27, 2007 at 1:02 pm
I have both things in mind. I would like to see what activity has already occurred and who has been accessing and using the databases but in reality I am thinking that I can only see this info once I set up some monitoring. However, I don't know what is available.
I have the feeling some of the databases are not being used any more and so I just want to verify what is in use and what is not first.
Then, I just want to find out to what extent and how the others are being used, what is being written or read from the databases and to what extent. Possibly starting to monitor the growth of the databases too.
Thanks,
G
November 27, 2007 at 10:22 pm
Easy way to see if they are used? Set Autoclose on and if anyone accesses the db, there will be a log message that the db is opened. This causes delays on the first access, so don't use it for databases you know are used.
For activity, set up a trace with Profiler.
November 28, 2007 at 7:14 am
Detach all the db's and see who/what complains! 😀
I was going to suggest profiler, but Steve beat me to it.
Also, if you can look through the databases and tables, you have be able to track activity by timestamp columns...But profiler is the easiest way. Don't pull back too much data though as this can flood the box and cause performance issues - but hey, when the complaints ring in, you'll know which db's are being used!
November 28, 2007 at 7:39 am
I won't recommend this, but I have worked in places where we've turned off servers to see if we got complaints. You're be surprised how many boxes can accumulate that aren't being used.
I'd recommend auto close before detach, but detach will certainly guarantee a phone call (and it's easy to undo!)
November 28, 2007 at 7:39 am
Steve, isn't that kind of like hitting a nail with a sledgehammer? Or more like hitting a cute, adorable puppy with one? Even if you are not sure that a DB is in use, setting autoclose on may cause more problems than it solves. I was going to suggest possibly setting up a SQL Agent job to query columns from sysprocesses and sysdatabases to determine current logins, inserting the records into a table if they do not exist along with a datetime value for the query date/time. You could run this for whatever length of time and frequency you deem fit and then query the table to determine what databases are being hit. Since you don't care about frequency of use (yet) your query can be quite simple.
------------------------------------------------------------------------------------------
NOTE: Code below was hastily put-together before the DBA had coffee. Take it as you will.
------------------------------------------------------------------------------------------
--Create the table to store activity results:
CREATE TABLE dbo.DB_Usage
(DB varchar(50) NOT NULL, LoginID varchar(100) NOT NULL, Poll_Date datetime NOT NULL)
ON [PRIMARY]
--Collect process results and insert them into a dbo.DB_Usage table
--NOTE: You can place this in the context of an Agent job to fire off as frequently as you like.
INSERT INTO dbo.DB_Usage (DB, LoginID, Poll_Date)
SELECT DISTINCT SD.name AS DB, SP.loginame, GETDATE() AS Poll_DateTime
FROM sysprocesses SP INNER JOIN sysdatabases SD
ON SP.dbid = SD.dbid
ORDER BY SD.name, SP.loginame
--Review results after the fact:
SELECT * FROM dbo.DB_Usage ORDER BY DB
You can expand on this in a variety of ways:
>> Add fields to the DB_Usage table and to the INSERT query to pull more details from sysprocesses
>> Only insert records if they do not exist in the DB_Usage table
>> Group results in your review query by database to get a sense of how frequently databases are being hit.
Keep in mind that I used compatibility views to collect the data versus DMVs since this will work for both 2005 and 2000. Also, your data is not going to be perfect since you'll be sure to miss results from quick hits against the databases in between polling times in your job. Setting up a trigger on sysprocesses would take care of that, but I recommend those extremes like I would recommend hitting a puppy with a sledgehammer, no matter how ugly the puppy is.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
November 28, 2007 at 7:36 pm
Timothy Ford (11/28/2007)
Steve, isn't that kind of like hitting a nail with a sledgehammer? Or more like hitting a cute, adorable puppy with one? Even if you are not sure that a DB is in use, setting autoclose on may cause more problems than it solves.
And surely if you are going to run profiler as well, you would be able to see what databases are being hit anyway, thereby negating the need to use autoclose?
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
November 28, 2007 at 7:39 pm
Profiler will do it. Autoclose is a quick and dirty way to check. Easy to scan logs, don't have to remember to turn it on, etc. If you're not experienced, I think autoclose is easy.
And it doesn't typically cause issues with apps. If the DBs aren't large, they'll open quickly.
Turning off servers is kind if using a sledgehammer, but it's effective.
November 29, 2007 at 6:20 am
Steve Jones - Editor (11/28/2007)
I won't recommend this, but I have worked in places where we've turned off servers to see if we got complaints.I'd recommend auto close before detach, but detach will certainly guarantee a phone call (and it's easy to undo!)
I like the auto_close option too, but rather than detach wouldn't offline be a little easier?
November 29, 2007 at 6:26 am
I will agree with Steve that auto close will work, and sledgehammers are fun to play with... just so long as these are not mission-critical databases. I work in the Healthcare industry so I tend to be more conservative than other DBAs that are not.
Hammer On!
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
November 29, 2007 at 7:54 am
Yeah, offline would be better ;), didn't think of that.
I'd do autoclose or offline because that way the db still is on the server, it keeps it in your mind, and forces you to look at it again in a few months before you remove it.
No matter what you do, be sure you save off a special tape with a backup of this database, script out logins used in this db and save that as well.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply