Is This Database Being Used?

Question

Is This Database Being Used?

Ginger Daniel

Mr or Mrs. 500

Points: 525
More actions
December 3, 2017 at 8:44 pm

#326726

Comments posted to this topic are about the item Is This Database Being Used?

Viewing 15 posts - 1 through 15 (of 19 total)

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

David.Poole SSC Guru Points: 76000 More actions · Answer 1

Nice article. We used traces on DBs we believed to be inactive. We worried about mystery monthly processes so needed a long running trace to be certain we identified this.

One of the biggest challenges was to get prioritisation agreed to carry out remedial work done to applications to deprecate old DBs once and for all. As far as a DBA is concerned a database is either in use or it is not. If it is in use then it needs backup/archive, suitable HADR, maintenance jobs etc.
As far as a business person is concerned DB usage is shades of grey. The impact (and costs) of their "hardly ever used" DB probably won't be understood and on shared servers difficult to assign a cost.

LinkedIn Profile

mauriciorpp Default port Points: 1472 More actions · Answer 2

great article, getting evidence of things that are NOT in use is lways a complicated matter, mostly because nobody wants to think about that.

does someone have a stored procedure that can capture data from time to time and can share here too?

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

No matter which method you use to determine if a database is being used, it's not a good idea to immediately kill the database. To Dave's point, I'll typically take a final backup along with a "Tail Log" backup, which also takes the database offline and then I'll leave it like that for at least two monthly cycles and a quarterly cycle, whichever is the furthest out. And then I guard the final MDF and LDF locally (as well as on tape) until the year end cycle has passed. If we needed to drop the database because of space issues, having the MDF and LDF locally might not be feasible so I'll have a special tape made and keep that locally.

--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)

Yuri Padorin SSC-Addicted Points: 414 More actions · Answer 4

Surprised you didn't mention it, but I use an extended events session that I leave running to capture the login and source (among a few others) for a lock_acquired event. I filter by database and I filter out our maintenance and sysadmin accounts.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 5

To complicate things, there could be legacy jobs or reports that robotically access the database on a schedule, but it's defunct and no end user or business process really depends on it or even looks at it. That's why it's a best practice to state the purpose or business justification for a job within it's description, and also have one or more operators setup for each job, even if there are no routine email notifications. In this case the operators are really business owners who are there in case someone needs an actual human contact to vouch for the purpose or continued need of a process when it comes under scrutiny for some reason.

I'm speaking from the perspective of someone who helps manage a database environment containing 100+ production servers and 1000+ jobs. The first logic branch on my mental flowchart for troubleshooting jobs is: "Who who created this thing?" and the 2nd branch is "Do we actually need it?". Only when those questions are answered can I justify spending any serious time trying to fix or optimize it. Nothing makes me happier than to give the ax to some old resource hog of a job that's wasting server resources in addition to wasting DBA time.

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 6

Yuri Padorin - Monday, December 4, 2017 7:32 AM
Surprised you didn't mention it, but I use an extended events session that I leave running to capture the login and source (among a few others) for a lock_acquired event. I filter by database and I filter out our maintenance and sysadmin accounts.

If you measure something, it costs in clock cycles and memory used. While people say that EE is more efficient than a server side SQL Profiler Trace, I find there's not really much difference especially if someone forgets to turn the damned thing off. Also, as Eric suggested above, even if something isn't used, there may be some forgotten job somewhere that really serves no purpose. One does require some bit of esoteric knowledge of such things to either prevent something valuable from being turned off or, to reverse it all, something that is being used that actually provides no value from being turned off.

--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)

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 7

A lot of those counters have values since the last start time of the server. If you can restart the server, then track the database specific counters to see if any don't increase

David.Poole SSC Guru Points: 76000 More actions · Answer 8

If we are trying to detect activity in a "unused" DB then the overhead of a trace or extended event should not be of concern.
I'd be more worried about the app that doesn't handle faulty connections gracefully

LinkedIn Profile

Bert-701015 SSCommitted Points: 1615 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 10

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?

--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)

Eric M Russell SSC Guru Points: 125561 More actions · Answer 11

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.

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

Bert-701015 SSCommitted Points: 1615 More actions · Answer 12

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.

Yes. And depending on when you run the query, all techniques listed except the dependency tracker could generate a false positive. I haven't found a foolproof way to determine if a database is used. These are all good options. Another option, if it's running, is the built in Data Collection.

Yuri Padorin SSC-Addicted Points: 414 More actions · Answer 13

Bert-701015 - Tuesday, December 5, 2017 12:57 PM
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.
Yes. And depending on when you run the query, all techniques listed except the dependency tracker could generate a false positive. I haven't found a foolproof way to determine if a database is used. These are all good options. Another option, if it's running, is the built in Data Collection.

at least with extended events you can exclude events raised by specific logins, i.e. your agent service account or maintenance job proxy.

Sue_H SSC Guru Points: 90860 More actions · Answer 14

Bert-701015 - Tuesday, December 5, 2017 12:57 PM
Yes. And depending on when you run the query, all techniques listed except the dependency tracker could generate a false positive. I haven't found a foolproof way to determine if a database is used. These are all good options. Another option, if it's running, is the built in Data Collection.

I don't believe there is a foolproof way. That's why saving the last backup and data, log files off somewhere is a good idea. You need a way to bring it back online if needed. And that can be more than a years time away. There are ways to make guesses if a database is needed but that's about it.

Sue