Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
 Posted - 2009-08-07 : 09:20:18
|
| I have a load of old databases that have been used and butchered by a department for years. I now have a project to clean them up. Does anyone know of any tools I can use to find out what is being used? I thought about a trace, but would need to leave this running for too long, so not ideal. DMV views may help for some, but not all.Any help greatly appreciated. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-08-07 : 10:14:20
|
| What are you trying to figure out is unused, the databases themselves, objects in the databases, or both?CODO ERGO SUM |
 |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-08-07 : 10:25:27
|
Objects in the databases, I have about 30 or so databases.I need to find out what I can take offline in regards to objects (won't just delete them as not stupid.  , but will save them and then let the user moan if they do need it after all). |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-10 : 14:30:47
|
| sys.dm_index_usage_stats will help, but not foolproof. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-08-10 : 15:04:27
|
| You might start with a trace that you can use to identify the objects that are in use in each database. Join the textdata from the trace to the list of database object names using LIKE. That should eliminate a large number of objects that you know will stay.With the list of objects that are left, you can setup specific traces to look for them, and let them run for longer periods of time. You would also need to compare the source of stored procedures, functions, views, and triggers that are still in use to other objects to see what objects they are using.CODO ERGO SUM |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-08-11 : 08:46:42
|
| Thanks, thats what I thought. I was hoping there was a tool that would be light on resources, yet do the job for me.Back to the drawing board I guess.Thanks again. |
|
|
|
|
|
|
|
|
How to find out whats used?