Last DB Usage

Question

Last DB Usage

DBA_007

Ten Centuries

Points: 1180
More actions
February 28, 2019 at 5:46 am

#379535

How to check DB last used or how to remove orphaned databases based on the usage

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

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

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

DBA_007 - Thursday, February 28, 2019 5:46 AM
How to check DB last used or how to remove orphaned databases based on the usage

With the understanding that it's reset to nothing after a restart of the SQL Service, you could look at reads and writes captured sys.dm_db_index_usage_stats to see if a database is being used by non-system activities (which is something I do). Be advised that a job running against user databases to create reports will still constitute a "usage" even if the reports are never used so (as it does with me), it'll take some research to figure WHAT is creating the usage using this method before you kill a database.

When I do run across such a thing (which is very rare... we run a pretty tight ship), it's NOT a good idea to drop the database immediately. Even if I'm initially absolutely sure that a database can be dropped, I'll take a Tail Log backup and then set it to OFFLINE for several months and wait for the proverbial phone to ring. Once several months have passed, I get concurrence from all the possible stakeholders, do a final backup to tape of the old backups (from the Tail Log backup set), and then finally drop it.

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

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 2

I would search for any usage. If there is none, or you see no wait stats from queries, I'd set this offline first. See if you break anything, and I'd do this for at least a month, but probably a quarter or year to be sure someone doesn't use this once a year.

richardmgreen1 SSChampion Points: 10350 More actions · Answer 3

We do something similar but we don't set the database to OFFLINE.

We rename the database (F2 in SSMS) and put "zz" at the beginning of the database name.

If soemthing ends up breaking, we can just undo the rename quite quicfkly and then figure out what's going on.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 4

Rename is good, either way, disrupt access so someone calls you. The thing I worry about with renaming is that a smart ad hoc used might notice the new database and attempt to query it. They would still have access.

You could also set to restricted user with the rename.

richardmgreen1 SSChampion Points: 10350 More actions · Answer 5

Steve Jones - SSC Editor - Friday, March 1, 2019 7:48 AM
Rename is good, either way, disrupt access so someone calls you. The thing I worry about with renaming is that a smart ad hoc used might notice the new database and attempt to query it. They would still have access.
You could also set to restricted user with the rename.

Unfortunately (or fortunately, depending on your point of view), our users tend to shout first rather than go digging around if code breaks.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 6

I like the shouting. That let's me know what to do. If they poke around, we may get into more trouble.