数据库最后更新了吗？
我正在使用 SQL 2000，我需要确定实际使用的是这些数据库中的哪一个。
是否有一个 SQL 脚本可以用来告诉我上次更新数据库的时间？读？ ETC？
我用谷歌搜索了一下，但结果是空的。
I'm working with SQL 2000 and I need to determine which of these databases are actually being used.
Is there a SQL script I can used to tell me the last time a database was updated? Read? Etc?
I Googled it, but came up empty.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
如果您的数据库已完全记录，请创建新的事务日志备份，并检查其大小。如果自上次事务日志备份以来没有对数据库进行任何更改，则日志备份将具有固定的小长度；如果发生更改，则日志备份将更大。
这不是一个非常精确的方法，但可以轻松检查，并且可能适合您。
If your database is fully logged, create a new transaction log backup, and check it's size. The log backup will have a fixed small lengh, when there were no changes made to the database since the previous transaction log backup has been made, and it will be larger in case there were changes.
This is not a very exact method, but it can be easily checked, and might work for you.
编辑：以下目标是查找最后访问日期事后。对于确定谁在使用哪些数据库，可以使用 SQL 分析器中的正确过滤器进行明确监控。但请注意，当过滤器不足时，分析器跟踪可能会变得相当大（因此分析速度很慢/难以分析）。
对数据库架构的更改，即添加表、列、触发器和其他此类对象，通常会在系统表/视图中留下“过时的”跟踪（如果需要，可以提供更多详细信息）。
但是，除非数据本身包含某种时间戳，否则通常很少有可靠的方法可以知道数据何时更改，除非恢复模型涉及将所有此类更改保留到日志中。在这种情况下，您需要一些工具来“反编译”日志数据...
关于检测“读取”活动...这是一项艰巨的任务。可能有一些计算机取证之类的技巧，但同样，恐怕没有简单的解决方案（除了在服务器活动中查看所有仍然活动连接的最后一个查询的能力之外；显然这是一个非常短暂的事情;-)）
Edit: the following targets issue of finding, post-facto, the last access date. With regards to figuring out who is using which databases, this can definitively monitored with the right filters in the SQL profiler. Beware however that profiler traces can get quite big (and hence slow/hard to analyze) when the filters are not adequate.
Changes to the database schema, i.e. addition of table, columns, triggers and other such objects typically leaves "dated" tracks in the system tables/views (can provide more detail about that if need be).
However, and unless the data itself includes timestamps of sorts, there are typically very few sure-fire ways of knowing when data was changed, unless the recovery model involves keeping all such changes to the Log. In that case you need some tools to "decompile" the log data...
With regards to detecting "read" activity... A tough one. There may be some computer-forensic like tricks, but again, no easy solution I'm afraid (beyond the ability to see in server activity the very last query for all still active connections; obviously a very transient thing ;-) )
如果我怀疑数据库确实被使用，我通常会运行探查器。如果没有活动，则只需将其设置为只读或离线即可。
I typically run the profiler if I suspect the database is actually used. If there is no activity, then simply set it to read-only or offline.
您可以使用事务日志读取器来检查数据库中的数据上次修改的时间。
对于 SQL 2000，我不知道有什么方法可以知道数据何时被读取。
您可以做的是在数据库登录上放置一个触发器，并跟踪登录何时成功，并跟踪相关变量以找出谁/什么应用程序正在使用数据库。
You can use a transaction log reader to check when data in a database was last modified.
With SQL 2000, I do not know of a way to know when the data was read.
What you can do is to put a trigger on the login to the database and track when the login is successful and track associated variables to find out who / what application is using the DB.