3

I would like to find (using SQL DMVs) whether a database is actually in use and if so then how much of that servers resources it is taking.

I need some ideas on how we can properly collect usage statistics on all of these databases across all of these servers and then create a simple report to understand resources usage.

The purpose of this report is to determine what are the critical counters that we need and what is the simplest and least impact method to collect them.

We don’t need minute to minute, I’m only looking for summary information once per day or so.

Can you please advise on this?

1

You can start with the automatic reports: Right click your DB, go to Reports and Standard Reports https://technet.microsoft.com/en-us/library/cc280385%28v=sql.105%29.aspx

You can also use perfmon https://msdn.microsoft.com/fr-fr/library/Aa645516%28v=VS.71%29.aspx

| improve this answer | |
1

Yes there are DMV's for this like sys.dm_db_index_usage_stats, but that alone will not help in analyzing the unused databases.

Here you can analyse on unused databases with help of SQL Server Audit as explained here by Aaron Bertrand When was my database / table last accessed?

In addition you may need various other methods to track the database usage like:

1) If there are any transactions count going up for the database in question with help of counters info, using below query:

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name like 'Transactions/sec%'
and instance_name like 'instance_name_here%';
GO

2) You can also ask the execution plan cache if queries have been running against the database. This query takes advantage of the dm_exec_text_query_plan DMV as explained in the article Querying Information from the Plan Cache, Simplified

3) You can check from the login history as well on those databases using Adam Mechanic's SP_WhoIsActive

Once you have the data collected all together ,and listed databases to be taken offline execute

ALTER DATABASE [database_name] SET OFFLINE;

Monitor for a duration and if an application goes down for the reason being database of that being taken offline, you can simply take those databases back online:

ALTER DATABASE [database_name] SET ONLINE;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.