Lista nieużywanych baz SQL

Poniższe polecenie pokazuje listę baz które zostały użyte (poprzez wykonanie zapytania, update itd) od ostatniego uruchomienie bazy danych (restartu).

Najpierw sprawdzimy od kiedy działa nasza instancja

SET NOCOUNT ON;
SELECT
@@SERVERNAME AS [instance_name],
[si].[sqlserver_start_time],
CONVERT(varchar, DATEDIFF(SECOND, [si].[sqlserver_start_time], GETDATE()) / 86400) + '.' +
CONVERT(varchar, DATEADD(SECOND, DATEDIFF(SECOND, [si].[sqlserver_start_time], GETDATE()) % 86400, '19000101'), 108) AS [up_time (d.hh.mm.ss)]
FROM sys.dm_os_sys_info AS [si];

instance_name		sqlserver_start_time		up_time (d.hh.mm.ss)
------------------------------------------------------------------------------
MICEK1968		2021-03-15 08:58:05.930 	5.11:45:00

Completion time: 2021-03-20T20:47:06.7053501+01:00

A teraz sprawdzamy użycie baz od ostatniego startu serwisu MSSQLSERVER


SELECT
[db].[name] AS [database_name],
MIN(lu.[last_usage_time]) AS [last_usage_time],
MIN([lu].[ago]) AS [ago (d.hh.mm.ss)],
CASE
WHEN [agdb].[is_primary_replica] IS NULL THEN N'N/A'
WHEN [agdb].[is_primary_replica] = 1 THEN [aggroup].[name] + N' - Primary Replica'
WHEN [agdb].[is_primary_replica] <> 1 THEN [aggroup].[name] + N'- Secondary Replica'
END AS [always_on_ag],
CASE
WHEN [agdb].[is_primary_replica] IS NULL AND MIN(lu.[last_usage_time]) IS NULL THEN N'This database was not used by users since SQL Server service started'
WHEN [agdb].[is_primary_replica] <> 1 THEN N'Take a look at Primary Replica to find out last usage time'
END AS [description]
FROM
sys.databases AS [db]
LEFT JOIN (
SELECT
[database_id],
MAX([last_user_seek]) AS [last_usage_time],
CONVERT(varchar, DATEDIFF(SECOND, MAX([last_user_seek]), GETDATE()) / 86400) + '.' +
CONVERT(varchar, DATEADD(SECOND, DATEDIFF(SECOND, MAX([last_user_seek]), GETDATE()) % 86400, '19000101'), 108) AS [ago]
FROM sys.dm_db_index_usage_stats
GROUP BY [database_id]

UNION

SELECT
[database_id],
MAX([last_user_scan]) AS [last_usage_time],
CONVERT(varchar, DATEDIFF(SECOND, MAX([last_user_scan]), GETDATE()) / 86400) + '.' +
CONVERT(varchar, DATEADD(SECOND, DATEDIFF(SECOND, MAX([last_user_scan]), GETDATE()) % 86400, '19000101'), 108) AS [ago]
FROM sys.dm_db_index_usage_stats
GROUP BY [database_id]

UNION

SELECT
[database_id],
MAX([last_user_lookup]) AS [last_usage_time],
CONVERT(varchar, DATEDIFF(SECOND, MAX([last_user_lookup]), GETDATE()) / 86400) + '.' +
CONVERT(varchar, DATEADD(SECOND, DATEDIFF(SECOND, MAX([last_user_lookup]), GETDATE()) % 86400, '19000101'), 108) AS [ago]
FROM sys.dm_db_index_usage_stats
GROUP BY [database_id]

UNION

SELECT
[database_id],
MAX([last_user_update]) AS [last_usage_time],
CONVERT(varchar, DATEDIFF(SECOND, MAX([last_user_update]), GETDATE()) / 86400) + '.' +
CONVERT(varchar, DATEADD(SECOND, DATEDIFF(SECOND, MAX([last_user_update]), GETDATE()) % 86400, '19000101'), 108) AS [ago]
FROM sys.dm_db_index_usage_stats
GROUP BY [database_id]
) AS [lu]
ON [db].[database_id] = [lu].[database_id]
LEFT JOIN sys.dm_hadr_database_replica_states AS [agdb] ON [db].[database_id] = [agdb].[database_id] AND [agdb].[is_local] = 1
LEFT JOIN sys.availability_groups [aggroup] ON [agdb].[group_id] = [aggroup].[group_id]

WHERE [db].[name] NOT IN (N'master', N'model', N'msdb', N'tempdb', N'distribution')

GROUP BY [db].[name], [agdb].[is_primary_replica], [aggroup].[name]

ORDER BY [db].[name];

database_name			last_usage_time         	ago (d.hh.mm.ss)	always_on_ag	description
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AdventureWorks2012		2021-03-20 20:38:42.080 	0.00:07:48		N/A		NULL
AdventureWorksDW2012		NULL                    	NULL			N/A		This database was not used by users since SQL Server service started
AdventureWorksLT2012		NULL                    	NULL			N/A		This database was not used by users since SQL Server service started
BULKDB				2021-03-20 20:38:57.700 	0.00:07:33		N/A		NULL
Nauka_CSharp			NULL                    	NULL			N/A		This database was not used by users since SQL Server service started
Nauka_SSIS			NULL                    	NULL			N/A		This database was not used by users since SQL Server service started
Northwind			NULL                    	NULL			N/A		This database was not used by users since SQL Server service started
SSISDB				2021-03-20 17:44:29.540 	0.00:09:13		N/A		NULL
TSQL2012			NULL                    	NULL			N/A		This database was not used by users since SQL Server service started
Warning: Null value is eliminated by an aggregate or other SET operation.


Completion time: 2021-03-20T20:46:30.9598673+01:00
Ten wpis został opublikowany w kategorii Microsoft SQL. Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz