| Author |
Topic |
|
lauramayer
Posting Yak Master
152 Posts |
 Posted - 2006-02-23 : 14:43:33
|
| Afternoon,Is there a way i.e. a script to see the last time a database was accessed?Thanks in advanceLaura. |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-23 : 14:56:33
|
| Not that I can think of.When a record was "Updated" ... yes. When a record was "Selected" ... no!If all Selects are via Sprocs then ... yes!!Kristen |
 |
|
|
lauramayer
Posting Yak Master
152 Posts |
Posted - 2006-02-23 : 15:03:33
|
| I have a room full of developers that have hosed a server, someone gave them admin rights to. No it wasn't me :) I just get stuck cleaning up the mess. Now I have a bunch of databases and no one can tell me if they're still in use, even though they created them. I am starting to dislike developers immensely.ThanksLaura |
|
|
|
JamesH
Posting Yak Master
149 Posts |
Posted - 2006-02-23 : 19:55:05
|
| Set each database to read-only. I'll bet you'll find out which ones are used then...JamesH |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2006-02-23 : 22:36:37
|
| If you want faster response, put then offline, I bet you will get phone call like crazy :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-24 : 00:44:45
|
| Plan A: Restore all databases back to a week ago ... no, make that a month ago! ... and tell them not to do it again!Plan B: Public execution, culprit picked at randomI prefer Plan B <g>Kristen |
|
|
|
mallier
Starting Member
24 Posts |
Posted - 2006-02-24 : 06:46:58
|
| select db_name(dbid) as databaseName,max(last_batch) as LastRunTime from master..sysprocesseswhere dbid <>0group by dbidcheers,http://mallier.blogspot.com |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2006-02-24 : 11:22:22
|
| Just a thought. Set the databases to auto-close, and check the last modified date of the file. This will take a bit of testing. If I am right, there may be a checkpoint when the database is closed, which would update the OS last updated date. Of course, all databases will get "used" at server startup, so if the server is rebooted often, this may not help. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-24 : 12:52:07
|
| They'll get re-dated when they are extended too - but then of course they are being "used" at that time, so that's ok (just might need to be aware of it).AutoClose will probably annoy the users a fair bit though ...We have all our DEV databases set to much more frequent TLog backups than normal client databases, because we assume we are going to create a disaster and need the shortest route back to normal working. having said that we'd also be able to take a "once-more" TLog backup, and then restore to point-in-time just before the accident. Frequent TLog backups safeguards us better against the accidental DROP DATABASE I suppose ... probably other horrors that I can't think of at the moment too!Kristen |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2006-02-24 : 13:14:02
|
quote:
AutoClose will probably annoy the users a fair bit though ...
An added bonus, no doubt. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-24 : 13:53:56
|
 |
|
|
|
lauramayer
Posting Yak Master
152 Posts |
Posted - 2006-02-24 : 14:13:59
|
| Wow! Thanks for all the responses. Now they're asking what transaction logs are for and can you delete tempdb? We dont use that one. Oi vey. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-24 : 14:32:23
|
| I used to watch TV for entertainment ... and then I got:SQL TeamKristen |
|
|
|
|
Way to see last time database accessed?