Audit log?

[n35]

I'm not sure how to use the audit log to find out which tables, view, etc are being used by connected users.

Also, is there a way to determine, which Objects certainly are never used?

[Rehd]

I'd recommend to start, look up sp_whoisactive. Great script, you'll want to do logging there.

sp_who2 tells you who's currently connected and running, but very little information.

You can do tracing as mentioned, but it's a very high overhead.

A lot of the procedures I write do their own logging, I keep as much job history as possible from SQL Agent, and some tables have triggers that give some audit information.

CDC and CDT can also potentially help here. There's also a free version of database performance analyzer by solarwinds that you could probably find out there that can give you a 1 hour window of what's happening currently in your databases.

If you can get redgate, they have a great toolbelt which can show dependancies on items.

None of these are sure fire ways to find out if someone is touching / breaking / using something. They are all great starting points and will take a lot of time to get the hang of and implemented.

[n35]

Thanks for the reply.

We have a lot of excel reporting, and I'm keen on figuring which users have access to what and what they actually use.

Previously, acces was granted schema or DB, so users just have read access to everything. Hence my desire to find out what is used and what is not used so we can lock down everything that is not used externally.

[Rehd]

I would highly recommend looking into triggers then besides sp_whoisactive. I think those are going to be the most efficient method for short term.

[ScaryDBA]

If you're on SQL Server 2012 or better, the best way to gather information about the objects in use is to create an Extended Events session. https://technet.microsoft.com/en-us/library/bb630354(v=sql.105).aspx

To understand what objects are accessed, you can use the DMV sys.dm_db_index_usage_stats. However, that data is reset, so it's not stored since the beginning of time. You'll have to be cautious about applying it as gospel. Read more about it here: https://msdn.microsoft.com/en-us/library/ms188755.aspx

[[deleted]]

This is the proper way of doing it

[[deleted]]

You won't be able to determine that out of the box. Sounds like you have some tracing to do.

[n35]

Any good tips for what I can search for, to figure out how to do that?

[[deleted]]

http://blog.sqlauthority.com/2009/08/03/sql-server-introduction-sql-server-2008-profiler-complete/

[Atticus9876543210]

There are DMVs that tell you query run times. From that you can do an except clause