查找最後一個使用數據庫的人的登錄名或使用者名

老闆要求提供數據庫名稱列表以及最後使用該數據庫的人的大小和姓名。我找到了最後一次訪問數據庫的時間但由誰訪問的資源。我將如何解決這個問題？

正在使用 SQL2008R2，到目前為止我有這個：

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

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

如果您的伺服器已經執行了一段可靠的時間（例如，至少一個業務週期），您可以使用諸如sys.dm_db_index_usage_stats.

沒有真正可靠的方法來判斷誰最後“使用”了數據庫。SQL Server 不會記錄這些資訊，除非他們做了一些實質性的事情（例如，您可能會看到使用者在預設跟踪中創建或刪除了一個對象，但這絕不會告訴您他們是最後一個訪問數據庫的人，或者如果那是他們最後一次訪問數據庫）。

對於數據庫文件大小，您根本不需要使用sp_MSforeachdb；無論如何你都不應該使用這種方法。原因如下：

• https://sqlblog.org/2010/12/29/a-more-reliable-and-more-flexible-sp_msforeachdb
• https://sqlblog.org/2010/02/08/bad-habits-to-kick-relying-on-undocumented-behavior
• http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/

在這種情況下，您無論如何都不需要遍歷所有數據庫；此資訊在視圖中複製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%'

最後，在開發環境中最安全的做法是將不確定的數據庫離線一周。如果沒有人抱怨，支持他們，然後放棄他們。如果某人需要超過一周的時間才能注意到他們失踪了，您可以隨時恢復他們（那里或其他地方）。

我也寫過一些關於這個的部落格：

• https://sqlblog.org/2008/05/06/when-was-my-database-table-last-accessed

引用自：https://dba.stackexchange.com/questions/62746