主页 / dba / 问题 / 62746
Accepted
Paul
Asked: 2014-04-09 02:57:42 +0800 CST

查找最后一个使用数据库的人的登录名或用户名

  • 772

老板要求提供数据库名称列表以及最后使用该数据库的人的大小和姓名。我找到了最后一次访问数据库的时间但由谁访问的资源。我将如何解决这个问题？

正在使用 SQL2008R2，到目前为止我有这个：

exec sp_MSForEachDB '
use ?
select ''?'', (SUM(df.size)*8)/1024 as ''Size (MB)''
from sys.database_files as df
'

答案的最后一个人方面模糊或松散是可以接受的。即只要是最近某个时候模糊地靠近它的人，那是“好的”。

sql-server sql-server-2008-r2

1 个回答

  1. Best Answer

    如果您的服务器已经运行了一段可靠的时间（例如至少一个业务周期），您可以使用诸如sys.dm_db_index_usage_stats.

    没有真正可靠的方法来判断谁最后“使用”了数据库。SQL Server 不会记录这些信息，除非他们做了一些实质性的事情（例如，您可能会看到用户在默认跟踪中创建或删除了一个对象，但这绝不会告诉您他们是最后一个访问数据库的人，或者如果那是他们最后一次访问数据库）。

    对于数据库文件大小，您根本不需要使用sp_MSforeachdb；无论如何你都不应该使用这种方法。原因如下：

    在这种情况下，您无论如何都不需要遍历所有数据库；此信息在视图中复制master.sys.master_files。

    SELECT 
  d.name, 
  [Size (MB)] = SUM(mf.size)*8/1024
FROM master.sys.databases AS d
INNER JOIN master.sys.master_files AS mf
ON d.database_id = mf.database_id
GROUP BY d.name;

    如果您必须通过一堆不受master 支持的数据库做某事，我宁愿使用这种技术而不是使用sp_MSforeachdb：

    DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'SELECT ''' + name + ''', (SUM(df.size)*8)/1024 as [Size (MB)]
  from ' + QUOTENAME(name) + '.sys.database_files as df;';
EXEC sp_executesql @sql;

    （顺便说一句，不要'single quotes'用来分隔别名，请使用[square brackets]。前者在某些形式中已被弃用 - 请参阅此处和此处- 并且无论如何都会使您的别名看起来像字符串文字。）

    对于“何时”部分，请从我的回答中复制：

    SQL Server 并没有真正按照您想要的方式跟踪数据库访问，至少是向后跟踪（您可以设置诸如服务器端跟踪、扩展事件、审计等之类的东西。向前）。

    您可以使用一件大致的事情：跟踪索引使用情况和过程/触发器/查询统计信息的 DMV。例如：

    ;WITH d AS
(
  SELECT d = database_id, name FROM sys.databases
  WHERE state = 0 AND database_id BETWEEN 5 AND 32766
),
index_usage(d,lsk,lsc,llk,lupd) AS
(
  SELECT database_id, MAX(last_user_seek), MAX(last_user_scan),
    MAX(last_user_lookup), MAX(last_user_update)
  FROM sys.dm_db_index_usage_stats
  WHERE database_id BETWEEN 5 AND 32766
  GROUP BY database_id
),
proc_stats(d,lproc) AS
(
  SELECT database_id, MAX(last_execution_time) 
    FROM sys.dm_exec_procedure_stats
    WHERE database_id BETWEEN 5 AND 32766
    GROUP BY database_id
),
trig_stats(d,ltrig) AS
(
  SELECT database_id, MAX(last_execution_time)
    FROM sys.dm_exec_trigger_stats
    WHERE database_id BETWEEN 5 AND 32766
    GROUP BY database_id
),
query_stats(d,lquery) AS
(
  SELECT t.[dbid], MAX(s.last_execution_time) 
    FROM sys.dm_exec_query_stats AS s
    CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS t
    WHERE t.[dbid] BETWEEN 5 AND 32766
    GROUP BY t.[dbid]
)
SELECT d.name,i.lsk,i.lsc,i.llk,i.lupd,p.lproc,t.ltrig,q.lquery
FROM d LEFT OUTER JOIN index_usage AS i ON d.d = i.d
LEFT OUTER JOIN proc_stats  AS p ON d.d = p.d
LEFT OUTER JOIN trig_stats  AS t ON d.d = t.d
LEFT OUTER JOIN query_stats AS q ON d.d = q.d;

    请注意，这些统计信息并不完全可靠，因为您可能没有任何存储过程，并且在其中找到的查询sys.dm_exec_query_stats可能引用多个数据库，并且可能永远不会反映您关注的那个。

    此外，它们会在 SQL Server 重新启动、数据库分离/附加或恢复，或者数据库自动关闭时重置，并且在某些情况下还可能取决于仍在缓存中的计划（另一个数据库可以在几分钟内完全接管）。因此，如果您回顾过去，除非您知道在整个业务周期中没有发生这些事情，否则我不会仅依靠这些数字来确定是否使用了数据库（也可能有自动化流程正在数据库看起来是最新的，即使您不关心删除数据库时这些自动化过程会失败）。

    另一个注意事项是在索引使用视图中可能无法跟踪某些索引访问；例如，在添加内存优化表的 SQL Server 2014 中，不会以这种方式捕获针对这些哈希索引的活动（并且您认为将捕获活动的视图，例如sys.dm_db_xtp_hash_index_stats，不包含任何日期/时间列）。如果您使用 SQL Server 2014 和内存中 OLTP（“Hekaton”），您可能需要添加一些研究来涵盖这些对象（以防它们是数据库中唯一被引用的对象）。

    还有一点需要注意的是，捕获的查询sys.dm_exec_query_stats可能是误报。例如，如果您的数据库有filestream/ filetable，您将看到系统偶尔运行这些查询：

    select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid
from [database].[sys].[filetable_updates_<some_id>] with (readpast) order by table_id

    因此，您可能希望在上述查询中添加额外的过滤来过滤掉那些（只要过滤器不会意外过滤掉您关心的查询）。这可能是对该派生表的安全补充：

    AND t.[text] NOT LIKE N'%oplsn_fseqno%'

    最后，在开发环境中最安全的做法是将不确定的数据库离线一周。如果没有人抱怨，支持他们，然后放弃他们。如果某人需要超过一周的时间才能注意到他们失踪了，您可以随时恢复他们（那里或其他地方）。

    我也写过一些关于这个的博客：

    • 4

相关问题