This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(60.8, 8%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
Would like to know is there any way -I wanted to know who access the database and that has only heap tables on what date/time,user,hostname,apps name....without running a trace/extended events.
SQL is 2012..
Query stats-seems not giving properly.
If no options exists apart from is there a way can be filter that database alone to capture for a month without running trace?.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 61%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
see the sqlserver audit features.
note: the docs don't go as far back as 2012 (which is in only extended support), but the major features were added in SQL Server 2008
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(163.2, 84%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYM%3C/text%3E%3C/svg%3E)
Hi @S_NO ，
Yes, you can try to use SQL Server Audit or SQL profiler for this purpose
This can help you enable SQL Server Audit:
https://blog.netwrix.com/2019/05/23/how-to-enable-sql-server-audit-and-review-the-audit-log/
-------------
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Is there any way..can I use DMV's (or) sysprocess to see the connections using automation way like instead of trace..like load connections data in to any of the database(i.e for this purpose) at least to get loaded 1month.
server and database is not using much..but still wanted to see any connections flows, as we have plan to decomission the database.
I think SQL Audit is your best bet. Next to that is Trace or Extended Events filtered for the database.
Of the DMVs, sys.dm_exec_query_stats is probably the best to look in, but as you noted it is not fully reliable. For various reasons, not all queries are cached.
Another DMV is sys.dm_os_buffer_descriptors which reflects the buffer cache. The theory would be that if the database is not accessed, there would be no pages in the cache, so the database would not appear with this query:
SELECT db_name(database_id), COUNT(*)
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
Alas, when I run it on my SQL 2012 instance, every database is listed, although about all are junk databases I don't touch. So you may have to find a level above which a database has to count as "active". But if you have maintenance jobs etc set up, the database may be touched anyway.
Maybe the best strategy is to set the database as offline, and if no one screams for a month, just drop it. (And delete the files!)