主页 / dba / 问题 / 2050
Accepted
jsauni
Asked: 2011-04-04 18:27:23 +0800 CST

如何判断 SQL Server 数据库是否仍在使用？

  • 772

我们希望停用一个 SQL Server 实例，该实例上仍保留有几个数据库。

我如何判断它们是否仍在被用户或 Web 应用程序使用？

我找到了一个论坛线程，它有一个 T-SQL 查询，您可以运行它来检索最后一个查询日期。它似乎有效，但我想知道这些信息是否足够有效以删除数据库。是吗？

如果您有其他方法也会有所帮助。

sql-server t-sql

7 个回答

  1. Best Answer

    您将不得不关注已从缓存中清除且您错过的项目，或者关注不经常使用的数据库。

    与其直接删除数据库，不如将它们置于 OFFLINE 以防止访问而不删除它们，或者将它们置于 RESTRICTED_USER 模式以限制访问。这样做，您可以将它们保持在该状态一两个月，以检查是否偶尔使用。

    您还可以考虑在该数据库上使用服务器端分析器跟踪过滤。

    • 32

  2. 这些是我过去使用的方法：

    1. 使数据库脱机/分离
    2. 拒绝用户/登录访问
    3. 探查器跟踪

    问题是：你要等多久才能确定没有人会访问数据？对于财务数据，您可以每天、每周、每月、每季度、每半年和每年运行一些项目。但是一年够吗？我还看到要求将数据保留至少 7 年的请求，在一个案例中，有人告诉我，一个系统中的数据需要永远存在，即使没有人使用它。

    最好的建议是：无论您做什么来关闭访问，请确保您可以立即将其重新打开。我发现这种分离效果最好。我会简单地编写重新附加的脚本并指示我的团队“如果有人问它在哪里，请运行这个脚本”。这给了我们最好的机会尽快把事情放回去。

    • 14

  3. 我同意尼克的建议。如果您需要确定，那么您将不得不使用 Profiler（服务端跟踪），因为某些 SQL 查询不会被缓存，或者出于任何原因可能会清除过程缓存。

    我通常还会检查虚拟文件统计信息，以查看操作系统文件级别是否发生了任何读取或写入。即使数据库未处于活动状态，如果您正在进行日志备份、完整备份等，您仍然会看到少量的读/写……但这也会让您了解该数据库上的读/写活动。

    在删除任何数据库之前，我会确保您在不同的位置至少有 2 或 3 个可读备份（测试它们）。你永远不知道什么时候需要它们。

    • 13

  4. 以下查询显示了自上次重启以来没有使用过的数据库，而不依赖于缓存中保存的查询计划，因为它显示了针对索引（和堆）的用户 IO。这有点类似于使用虚拟文件统计信息，但这里使用的 DMV 将 IO 活动排除在备份之外。无需保持探查器跟踪运行，无需触发器或审核。当然，如果你经常重启你的 SQL 服务器（或者你经常附加/关闭数据库），这可能不是要走的路:-)

    话虽如此，但仍然同意即使此查询似乎确认可以删除数据库，也一定要离线/分离或拒绝用户访问一段时间，以及在实际删除之前进行任何尽职调查！

    select [name] from sys.databases 
where database_id > 4
AND [name] NOT IN 
(select DB_NAME(database_id) 
from sys.dm_db_index_usage_stats
where coalesce(last_user_seek, last_user_scan, last_user_lookup,'1/1/1970') > 
(select login_time from sys.sysprocesses where spid = 1))
    • 9

  5. 我曾在一个拥有大量孤立和半孤立数据库的地方工作。很难判断他们是否真的是孤立的，因为许多任务是季节性的或年度的——因此网站每年只运行 3-4 个月（例如，W2 表格需要在 1/31 以电子方式提交，所以网站处理这些仅从 1 月中旬到 4 月底运行）。

    所做的工作结合了：
    * 询问每个开发人员是否在使用某个数据库或其他数据库（这些电子邮件会每月​​发送一次，或者每当备份时间过长时发送）。
    * 使数据库脱机，看看谁在抱怨。
    * 重命名服务器，看看谁在抱怨。

    由于尖头发的老板只愿意允许“完整和完整”的文档，因此明确禁止 wiki，并且人员减少导致符合标准的文档急剧下降。

    如果由我来决定，每台服务器都会有一个 wiki 页面，其中包含每个数据库的联系人姓名（也许是对数据库用途的简要描述）。任何未在 wiki 上记录的数据库都是公平的删除游戏。

    我们有一个大型金融客户端，直到 2009 年仍在使用 SQL Server 2000，因此我们必须保持一个 SQL Server 2000 实例运行，直到该客户端最终迁移到 SQL Server 2005。

    • 3

  6. 另外两个选择是：

    1. 在数据库上创建触发器，将通知您（或存储到表中）任何活动。

    2. 启用对 DB 的审核。

      • 取决于您的数据库版本。
    • 3

  7. 下一个解决方案显示您实例下特定数据库的临时总、干净和脏页（以 MB 为单位）（在 Internet 上找到并稍作修改）：

    SELECT
    (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
    COUNT(*) *8/1024 AS [TotalPages in MB],
    SUM(CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END) *8/1024 AS [CleanPages in MB],
    SUM(CASE WHEN ([is_modified] = 1) THEN 1 ELSE 0 END) *8/1024 AS [DirtyPages in MB]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY DB_NAME(database_id)

    或者

    select value [DBid],attribute, last_execution_time ,text
from
sys.dm_exec_query_stats
cross apply
sys.dm_exec_plan_attributes(plan_handle)
cross apply
sys.dm_exec_sql_text(plan_handle)
where  attribute = 'dbid' 
order by last_execution_time desc

    或者

    select value [DBid],attribute, last_execution_time ,text
from
sys.dm_exec_query_stats
cross apply
sys.dm_exec_plan_attributes(plan_handle)
cross apply
sys.dm_exec_sql_text(plan_handle)
--where dbid=8
where 
      text like '%idAdministrator%' and
      attribute = 'dbid' 
      and value>= 5 -- dbid >=5 for user databases but include resource database which
                     --you can exclude by its numer I don't remember at the moment
order by last_execution_time desc
    • 2

相关问题