今天逛论坛,遇到一个经常问的问题,怎么判断某个库中的某张表在某个时间段有没有被使用过?记得以前在Aaron Bertrand的Blog上看到过,去翻一翻,找到了转过来.When was my database / table last accessed?
里面提到了几种方法,列举如下:
1.使用SQL Server审计功能,通过审计功能的记录看判断对应的数据库对象的最后访问时间。
USE master;
GO
CREATE SERVER AUDIT Test_Server_Audit
TO FILE ( FILEPATH = 'C:\Audits\' );
GO
ALTER SERVER AUDIT Test_Server_Audit
WITH (STATE = ON);
GO
USE AdventureWorks;
GO
CREATE DATABASE AUDIT SPECIFICATION Test_Database_Audit
FOR SERVER AUDIT Test_Server_Audit
ADD (SELECT ON Person.Address BY PUBLIC)
WITH (STATE = ON);
GO
SELECT *
FROM Person.Address;
GO
SELECT *
FROM fn_get_audit_file('C:\Audits\*', NULL, NULL);
GO
USE AdventureWorks;
GO
ALTER DATABASE AUDIT SPECIFICATION Test_Database_Audit
WITH (STATE = OFF);
GO
DROP DATABASE AUDIT SPECIFICATION Test_Database_Audit;
GO
USE master;
GO
ALTER SERVER AUDIT Test_Server_Audit
WITH (STATE = OFF);
GO
DROP SERVER AUDIT Test_Server_Audit;
GO
2.2008以后,可以使用查询动态视图sys.dm_db_index_usage_stats来实现。
USE AdventureWorks;
GO
SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO
WITH agg AS
(
SELECT
[object_id],
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM
sys.dm_db_index_usage_stats
WHERE
database_id = DB_ID()
)
SELECT
[Schema] = OBJECT_SCHEMA_NAME([object_id]),
[Table_Or_View] = OBJECT_NAME([object_id]),
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT [object_id], last_user_seek, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_scan, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_lookup, NULL FROM agg
UNION ALL
SELECT [object_id], NULL, last_user_update FROM agg
) AS x ([object_id], last_read, last_write)
GROUP BY
OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
ORDER BY 1,2;
3.通过SQL Agent创建对应的JOB或者数据快照,记录数据库中数据表的访问时间,然后通过查询这张表来实现对应的功能。
以上第一种方法,配置起来相当复杂一些,但是可以准确的记录数据库的操作,并且对一些非法操作可以起到预防作用。
第二种方法,需要指出的是,只能查询最后一次重启数据库后的数据,也就是说,数据库重启以后,该动态视图sys.dm_db_index_usage_stats里面的信息会被清空,所以查询出来的信息可能不准确。
第三种方法,需要额外创建JOB或者快照来实现,但是可以记录整个数据库的数据表访问变化。
三种方法各有利弊,在实际工作中,可以根据自己的情况来设定。
在文章的最后Q/A环节中,还提到了一些数据库的访问脚本:
例如:
1.去除系统对象的脚本
;WITH agg AS
(
SELECT
max(last_user_seek) last_user_seek,
max(last_user_scan) last_user_scan,
max(last_user_lookup) last_user_lookup,
max(last_user_update) last_user_update,
sd.name dbname
FROM
sys.dm_db_index_usage_stats i
JOIN master..sysdatabases sd on database_id = sd.dbid
where i.object_id > (select max(object_id) from sys.objects)
group by sd.name
)
SELECT
dbname,
last_read = MAX(last_read),
last_write = MAX(last_write)
,(select create_date from sys.databases where name='tempdb') as LastServerRestart
FROM
(
SELECT dbname, last_user_seek, NULL FROM agg
UNION ALL
SELECT dbname, last_user_scan, NULL FROM agg
UNION ALL
SELECT dbname, last_user_lookup, NULL FROM agg
UNION ALL
SELECT dbname, NULL, last_user_update FROM agg
) AS x (dbname, last_read, last_write)
GROUP BY
dbname
ORDER BY 2;
2.去除系统数据库和系统对象的查询数据库和数据库用户对象的脚本
; WITH STATS as (
SELECT D.name, D.database_id
, max(last_user_seek) as last_user_seek
, max(last_user_scan) as last_user_scan
, max(last_user_lookup) as last_user_lookup
, max(last_user_update) as last_user_update
FROM sys.databases D
LEFT OUTER JOIN sys.dm_db_index_usage_stats S on D.database_id = S.database_id
WHERE D.database_id > 4
AND S.object_id > 100
GROUP by D.name, D.database_id
)
SELECT * from STATS
UNION
SELECT D.name, D.database_id, NULL, NULL, NULL, NULL
FROM sys.databases D
WHERE D.database_id > 4
AND not exists (select * from STATS where name = D.name)
ORDER by 1