|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
When was a database last used?How do I (programatically) find the last date/time the database was used. Even better would also be to find how much time the database has/is being used. Some background: I'd like to write a custom tool to clean out some of our development database servers. These constantly get filled up with autobuild restores for doing all sorts of tests, proof of concepts, or finding a bug with some scenario. We also sometimes restore customer databases. The problem is that these hardly ever get cleaned up, so ever so often, I need to send a 'request' to all the devs to clear up their stuff. I have an idea to create a tool that will clean them up, forcing the devs to use a tool to restore the database (also making sure the files are placed in a nice organized way), logging the start date, the dev, their use for the restore, and an expiration time, etc. I'd like to know/find an official way to find out this information. Possible hacks I'm thinking of: Looking at the modified date of the *.mdf/ldf files Capturing and analyzing SQL Trace info Polling using sp_who Thanks for any feedback Please post back to the newsgroup. Rudi SQL Server doesn't track this kind of information. I'd look at 3rd party
auditing tools, such as Lumigent's Entegra... Show quote "Rudi Larno" <x@y.com> wrote in message news:OEO7rHDdGHA.4532@TK2MSFTNGP02.phx.gbl... > Hi, > > How do I (programatically) find the last date/time the database was used. > Even better would also be to find how much time the database has/is being > used. > > Some background: > I'd like to write a custom tool to clean out some of our development > database servers. These constantly get filled up with autobuild restores > for doing all sorts of tests, proof of concepts, or finding a bug with > some scenario. We also sometimes restore customer databases. > > The problem is that these hardly ever get cleaned up, so ever so often, I > need to send a 'request' to all the devs to clear up their stuff. > I have an idea to create a tool that will clean them up, forcing the devs > to use a tool to restore the database (also making sure the files are > placed in a nice organized way), logging the start date, the dev, their > use for the restore, and an expiration time, etc. > > I'd like to know/find an official way to find out this information. > Possible hacks I'm thinking of: > Looking at the modified date of the *.mdf/ldf files > Capturing and analyzing SQL Trace info > Polling using sp_who > > Thanks for any feedback > Please post back to the newsgroup. > > Rudi > Hmm, given the background information that I gave, this is not really the
answer I was looking for. They do not even list their prices on the website, so I would think this product is way too much overkill. Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:Ow%233LSDdGHA.2188@TK2MSFTNGP05.phx.gbl... > SQL Server doesn't track this kind of information. I'd look at 3rd party > auditing tools, such as Lumigent's Entegra... > > > > > "Rudi Larno" <x@y.com> wrote in message > news:OEO7rHDdGHA.4532@TK2MSFTNGP02.phx.gbl... >> Hi, >> >> How do I (programatically) find the last date/time the database was used. >> Even better would also be to find how much time the database has/is being >> used. >> >> Some background: >> I'd like to write a custom tool to clean out some of our development >> database servers. These constantly get filled up with autobuild restores >> for doing all sorts of tests, proof of concepts, or finding a bug with >> some scenario. We also sometimes restore customer databases. >> >> The problem is that these hardly ever get cleaned up, so ever so often, I >> need to send a 'request' to all the devs to clear up their stuff. >> I have an idea to create a tool that will clean them up, forcing the devs >> to use a tool to restore the database (also making sure the files are >> placed in a nice organized way), logging the start date, the dev, their >> use for the restore, and an expiration time, etc. >> >> I'd like to know/find an official way to find out this information. >> Possible hacks I'm thinking of: >> Looking at the modified date of the *.mdf/ldf files >> Capturing and analyzing SQL Trace info >> Polling using sp_who >> >> Thanks for any feedback >> Please post back to the newsgroup. >> >> Rudi >> > > > Hmm, given the background information that I gave, this is not really the Well, sorry. You did post to .tools, and since SQL Server doesn't track > answer I was looking for. this information automagically... I don't know what answer you were looking for, and it's really too bad that you have to make me feel like *I* did something wrong here. > They do not even list their prices on the website, so I would think this Did you bother searching at all? This is not the only product available, > product is way too much overkill. after all, it was mentioned subsequent to "such as..." http://www.google.com/search?hl=en&q=auditing+tools+SQL+Server And given that you never mentioned your budget or that budget was an issue, how the heck am I supposed to know what is overkill for you? If you asked what kind of car you should buy, should I know through osmosis that only Hyundais and Kias are in your budget? If I mentioned a Porsche, would I get reprimanded for that too? Aaron,
I'm sorry if you feel offended by my response. That was certainly not the intention. Rudi Rudi,
You could do that fairly easily using Windows PowerShell. The basic command would be like get-childitem *.mdf | sort-object LastWriteTime | format-table Name, LastWriteTime That assumes the .mdf files are in the current directory. Use the -recurse parameter if appropriate. It could be readily tweaked to do the necessary relevant to your own circumstances. For example, substitute LastAccessTime in the second step of the pipeline if that is of more interest. Andrew Watt MVP Show quote On Wed, 10 May 2006 15:02:00 +0200, "Rudi Larno" <x@y.com> wrote: >Hi, > >How do I (programatically) find the last date/time the database was used. >Even better would also be to find how much time the database has/is being >used. > >Some background: >I'd like to write a custom tool to clean out some of our development >database servers. These constantly get filled up with autobuild restores for >doing all sorts of tests, proof of concepts, or finding a bug with some >scenario. We also sometimes restore customer databases. > >The problem is that these hardly ever get cleaned up, so ever so often, I >need to send a 'request' to all the devs to clear up their stuff. >I have an idea to create a tool that will clean them up, forcing the devs to >use a tool to restore the database (also making sure the files are placed in >a nice organized way), logging the start date, the dev, their use for the >restore, and an expiration time, etc. > >I'd like to know/find an official way to find out this information. >Possible hacks I'm thinking of: >Looking at the modified date of the *.mdf/ldf files >Capturing and analyzing SQL Trace info >Polling using sp_who > >Thanks for any feedback >Please post back to the newsgroup. > >Rudi Andrew Watt [MVP] (SVGDevelo***@aol.com) writes:
Show quote > You could do that fairly easily using Windows PowerShell. But that presumes that the databasees are in autoclose, doesn't it?`> > The basic command would be like > > get-childitem *.mdf | > sort-object LastWriteTime | > format-table Name, LastWriteTime > > That assumes the .mdf files are in the current directory. Use the > -recurse parameter if appropriate. > > It could be readily tweaked to do the necessary relevant to your own > circumstances. For example, substitute LastAccessTime in the second > step of the pipeline if that is of more interest. Else all you would see is when SQL Server was last started. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx On Wed, 10 May 2006 22:12:13 +0000 (UTC), Erland Sommarskog
<esq***@sommarskog.se> wrote: Show quote >Andrew Watt [MVP] (SVGDevelo***@aol.com) writes: Hi Erland,>> You could do that fairly easily using Windows PowerShell. >> >> The basic command would be like >> >> get-childitem *.mdf | >> sort-object LastWriteTime | >> format-table Name, LastWriteTime >> >> That assumes the .mdf files are in the current directory. Use the >> -recurse parameter if appropriate. >> >> It could be readily tweaked to do the necessary relevant to your own >> circumstances. For example, substitute LastAccessTime in the second >> step of the pipeline if that is of more interest. > >But that presumes that the databasees are in autoclose, doesn't it?` >Else all you would see is when SQL Server was last started. Yes, you're right it does assume autoclose. Andrew Watt MVP Rudi Larno (x@y.com) writes:
> How do I (programatically) find the last date/time the database was used. As Aaron said, there is nothing built-in, so either you need to buy> Even better would also be to find how much time the database has/is being > used. a monitoring tool, or build your own. Or just do a regular "detach a couple of databases, and see if anyone screams". > I'd like to know/find an official way to find out this information. As I said in another post, this would require the databases to be set> Possible hacks I'm thinking of: > Looking at the modified date of the *.mdf/ldf files to autoclose. This can cause sever performance issues in Enterprise Mangager for non-priv users, and also increase load on the server. I would not recommend this. > Capturing and analyzing SQL Trace info This is probably the way to do. You can get a whole lot of dataif you do it wrong. But if you filter for suspect databases only, you can keep down the volume. An outline: 1) Set up a server-side trace which captures RPC:Completed and BatchCompleted with a filter that includs all databases with dbid > 6. 2) Let it run for, say, one hour. 3) Get the tracefile into a table. 4) Regenerate the trace, but do now not include databases that actually appeared in the trace. 5) Iterate back to 2. After some time, two weeks or whatever, there are still some databases left. Detach these. If no one screams for the next two weeks, delete the files. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics