{"id":2322,"date":"2012-11-02T14:12:23","date_gmt":"2012-11-02T14:12:23","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2322"},"modified":"2012-11-02T14:12:23","modified_gmt":"2012-11-02T14:12:23","slug":"%e6%9f%a5%e8%af%a2%e6%9c%89%e6%95%b0%e6%8d%ae%e6%9b%b4%e6%96%b0%e7%9a%84%e8%a1%a8","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2012\/11\/02\/%e6%9f%a5%e8%af%a2%e6%9c%89%e6%95%b0%e6%8d%ae%e6%9b%b4%e6%96%b0%e7%9a%84%e8%a1%a8\/","title":{"rendered":"\u67e5\u8be2\u6709\u6570\u636e\u66f4\u65b0\u7684\u8868"},"content":{"rendered":"<pre class=\"brush:sql;first-line:1;pad-line-numbers:true;highlight:null;collapse:false;\">\r\nFor many reasons SQL Server DBAs need to find the last date and time of an update on a sql table. The SQL Server DMV sys.dm_db_index_usage_stats  can supply enough information to allow the DBA to make a good estimate of the time.\r\n\r\nIn this case I needed to create a list candidates for tables to be deleted. The data became bloated with a number of different temp tables .\r\n\r\n<strong>The one drawback of using the DMV is that data is refreshed after every SQL Server restart.\r\n<\/strong>\r\nThis script lists all tables in the current database, with various columns assisting in deciding the last update.\r\n\r\n \r\n\r\nSELECT\r\ntbl.name\r\n,ius.last_user_update\r\n,ius.user_updates\r\n,ius.last_user_seek\r\n,ius.last_user_scan\r\n,ius.last_user_lookup\r\n,ius.user_seeks\r\n,ius.user_scans\r\n,ius.user_lookups\r\nFROM\r\nsys.dm_db_index_usage_stats ius INNER JOIN\r\nsys.tables tbl ON (tbl.OBJECT_ID = ius.OBJECT_ID)\r\nWHERE ius.database_id = DB_ID()\r\nlast_user_update - Time of last user update.\r\nuser_updates - Number of updates by user queries.\r\nlast_user_seek - Time of last user seek .\r\nlast_user_scan - Time of last user scan.\r\nlast_user_lookup - Time of last user lookup.\r\nuser_seeks - Number of seeks by user queries.\r\nuser_scans - Number of scans by user queries.\r\nuser_lookups - Number of bookmark lookups by user queries\r\n\r\nRelated Posts\r\n\r\nSQL Server &ndash; Last DML operation\r\n\r\nSQL Server - Calculate table size with existing data\r\n\r\nHow to request SQL Server troubleshooting\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>For many reasons SQL Server DBAs need to find the last  [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2322","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2322","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2322"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2322\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2322"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2322"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2322"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}