how to track DB read-write operations for 30 days without using Profiler

Question

how to track DB read-write operations for 30 days without using Profiler

Shivrudra

SSCrazy

Points: 2835
More actions
January 28, 2019 at 6:09 am

#382647

Hi,
Before database decommission, there is task to trace that whether database is involving in any read-write operations or not for next 30 days, can you suggest better option for it other than Enabling profiler Trace.
Regards,
Shivrudra W

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125560 More actions · Answer 1

The DMV dm_db_index_usage_stats can be used to query reads and writes on indexes.
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?view=sql-server-2017

The columns you'll find useful are these:
last_user_seek    datetime    Time of last user seek
last_user_scan    datetime    Time of last user scan.
last_user_lookup    datetime    Time of last user lookup.
last_user_update    datetime    Time of last user update.

There is also a similar DMV that returns information on file usage.
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-io-virtual-file-stats-transact-sql?view=sql-server-2017

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jonathan AC Roberts SSCoach Points: 18334 More actions · Answer 2

I think you can use perfmon for this. Just select the statistics you want to track and you can get it to write to a flat file or a database using an ODBC connection.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Shivrudra - Monday, January 28, 2019 6:09 AM
Hi,
Before database decommission, there is task to trace that whether database is involving in any read-write operations or not for next 30 days, can you suggest better option for it other than Enabling profiler Trace.

Do you just want to know whether the operations are happening, or do you also need to know what operations are happening on what database objects?

John

Eric M Russell SSC Guru Points: 125560 More actions · Answer 4

Also, before you drop the database, take it offline and leave in place for a few days just to see if someone or something complains.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

Eric M Russell - Monday, January 28, 2019 6:23 AM
The DMV dm_db_index_usage_stats can be used to query reads and writes on indexes.
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?view=sql-server-2017
The columns you'll find useful are these:
last_user_seek    datetime    Time of last user seek
last_user_scan    datetime    Time of last user scan.
last_user_lookup    datetime    Time of last user lookup.
last_user_update    datetime    Time of last user update.
There is also a similar DMV that returns information on file usage.
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-io-virtual-file-stats-transact-sql?view=sql-server-2017

This is good advice, you will probably want to check these daily, not just once at the end of the 30 days, because these views will only show data since restart