question

zoeOhara-0533 avatar image
0 Votes"
zoeOhara-0533 asked Cathyji-msft answered

How to find out if a database is being used?

Hi,
We have a database on a server, but we are unsure if anything is actually using it. Is there a way to monitor and store connections to the database? I was thinking of running it for about three weeks and if nothing connects, then taking it offline.

Thanks

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Maybe it is used monthly or quarterly? Did you find any date column (like “ModifiedOn”) that contains a recent date?

You can also stop (detach) it and wait few days. If nobody complains, then it seems unneeded. But such approach (attitude) is probably not recommended.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

You can set up a trace or an X-Event session capturing RPC:Starting and SQL:BatchStarting and which is filtered for these databases. You may also have to filter out things like the Telemetry session if you are on SQL 2016 or later.

Beware that even if the trace/X-Event session is filtered it will affect all processes, and if this is a system with very many, but, short commands, this can incur a significant overhead.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
1 Vote"
Cathyji-msft answered

Hi @zoeOhara-0533,

Is there a way to monitor and store connections to the database?

You can try below method.

•Capture login/connections over period of time
•Observe index usage
•Look at transaction count
•Find dependencies with Jobs or other DBs

Refer to the blogs Is This Database Being Used and 4 Lightweight Ways to Tell if a Database is Used to get more detail information.

Please also check if this similar thread How can I tell if a SQL Server database is still being used could help you.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.