Historical connection history

Question

Historical connection history

thtb

SSC Veteran

Points: 256
More actions
January 26, 2006 at 12:57 pm

#113180

I'm looking for a way to determine how often my databases are used. We have a handful of databases that I'm not sure if any applications currently use.
Is there a way to check how often a database has been connected to? Last connection? Number of connections in the last month? etc.
Thanks,
Tom

Viewing 8 posts - 1 through 8 (of 8 total)

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

ramses2nd Hall of Fame Points: 3720 More actions · Answer 1

I would set up a trace on that database that dumps the results to a table. You can then analyze connections whenever you like.

sqltung Hall of Fame Points: 3962 More actions · Answer 2

I've done this before in a situation where there was nearly 100% turnover in a company's IT department.

I just used Profiler (as ramses2nd is suggesting) and trace the databases in question. When you set up the trace, you can filter for specific databases. You may need to look up the database IDs in master.dbo.sysdatabases to do this. Save the trace to a table somewhere else, like a utility database.

Then just trace them for awhile.. if the server's not too busy, you could do this for a workday, and then take a look at the resulting data.

thtb SSC Veteran Points: 256 More actions · Answer 3

My concern are those applications that connect sproradically, that may be used only once or twice a month (or maybe just an year end!). Does SQL Server store usage statistics that would indicate the number of connections made, last connection, etc.?

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 4

Rename the database out of the way, and let the application fail and any concerned user will log the failure with your helpdesk (prime the helpdesk 1st!). We did something similar with printed reports some time back...we printed everthing and delivered nothing...until the calls came in "asking where's my report"....anything not asked for at the end of the day/week, was put on the "to be deleted" list. Time, effort, paper and money were saved by not creating the unwanted items.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

Here's something I posted under another thread:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=253481#bm253802

I prefer a less hands-on approach. First off communication to the user community. Then I'll put the databse into 'read only' mode for a few days, maybe a week. If the database is needed for update, it will usually come up pretty quickly and you can just change the option back. Now if a few days go by and no body complains about is then I'll create a new user and perform a changedbowner to that user (not mapping the existing dbo's) and then put the database into 'dbo use' only mode. This way you can catch those users and poorly written applications that have 'dbo' access (again, it's quick and easy to undo this). Again if a few days (or a week) go by with no issues my final step is to make a final backup of the database and make sure it gets to tape, then put the database in 'offline' mode. Now depending on the server and the database, I may leave it out there for 30 days or so just to avoid the unpleasant task of going through a tape restore. If no one raises a red flag, then it's time for the old 'drop database' and you're though.

All in all the steps above are probably about 15-20 minutes of work scattered over a time frame of 2 weeks to 2 months. I think it's less labor intensive than profiler and just as thorough.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

thtb SSC Veteran Points: 256 More actions · Answer 6

Thanks for the input. I'm looking for a less intrusive approach (trying to improve the IT reputation here). Plus I'd like to know more than just if the DB is being used.

Does SQL track any PAST activity like reads, writes, connections? In other words, is there a table that I can query that shows this type of info? My concern is that if the database is only used at quarter-end, I won't see anything for a few months.

For ongoing tracking, are there any triggers, stored procedures, or processes that track the number of reads, writes, connections by database and date?

Thanks,

Tom

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 7

There is no such table or mechanism to query for the 'PAST' information that you seek. Your only option is SQL Profiler - a great tool, but not for long term usage. There may be 3rd party tools available ...

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."