Is This Database Being Used?

Question

Post reply

Is This Database Being Used?

Viewing 5 posts - 16 through 19 (of 19 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Eric M Russell - Tuesday, December 5, 2017 12:21 PM

Jeff Moden - Tuesday, December 5, 2017 9:16 AM

Bert-701015 - Tuesday, December 5, 2017 8:45 AM

Just a heads up; some of the techniques discussed can be skewed (in use) by maintenance jobs like re-indexing.

Which techniques and how are they skewed?

I believe he's referring to scheduled index and statistics maintenance jobs performing reads and writes.

Agreed. I just hate it when someone drops partial information on the table. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Eric M Russell SSC Guru Points: 125255 More actions · Answer 2

There are a dozen different things you can do to help determine if a database is being used, but none of them are entirely reliable as a standalone solution. Just for completeness, another option is querying plan cache by database id or analyzing sql text looking for database references.

SELECT @@servername as servername , db_name(qp.dbid) dbname , object_name( qp.objectid, qp.dbid ) objectname , qs.creation_time AS plan_created , qs.last_execution_time AS last_executed , qs.execution_count from sys.dm_exec_cached_plans cp outer apply sys.dm_exec_query_plan ( cp.plan_handle ) as qp left outer join sys.dm_exec_query_stats AS qs on qs.plan_handle = cp.plan_handle outer apply sys.dm_exec_sql_text (cp.plan_handle) as st where db_name(qp.dbid) = 'MyDatabase' order by qs.last_execution_time;

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Eric M Russell - Wednesday, December 6, 2017 10:27 AM

There are a dozen different things you can do to help determine if a database is being used, but none of them are entirely reliable as a standalone solution.

There is one guaranteed way to determine if a database is being used... do the BSOFH thing. Take a full backup followed by a "Tail Log Backup" and then wait for the instant messages to start and trouble tickets to start popping up. 😉 If none of that happens for a year, drop the database and put the final backup files in long term storage.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

C64DBA SSC Veteran Points: 249 More actions · Answer 4

Jeff Moden - Wednesday, December 6, 2017 6:59 PM

Eric M Russell - Wednesday, December 6, 2017 10:27 AM

There are a dozen different things you can do to help determine if a database is being used, but none of them are entirely reliable as a standalone solution.

There is one guaranteed way to determine if a database is being used... do the BSOFH thing. Take a full backup followed by a "Tail Log Backup" and then wait for the instant messages to start and trouble tickets to start popping up. 😉 If none of that happens for a year, drop the database and put the final backup files in long term storage.

And this is what I call "User Driven Documentation" (UDD). When all indications suggest the database is not in use, I turn to UDD. 😉

TMiller 21949 SSC Veteran Points: 220 More actions · Answer 5

"One person suggested we just not move them, and see if anybody screamed or if it broke anything."

Years ago at a Giant Entity that was the method used in our department to see if various reports were being used. One time the department IT head announced that he was going to shut off printing hard copies of a certain report, and asked if anybody used it. One user declared "yes - I look at it every month." The IT head then stated that he had turned off printing hard copies 3 months ago. Doh!