|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
When was a database accessed lastI am looking for a way to query a database to see when the last time any of
the tables were accessed (select, insert, update, or delete). Basically I am trying to determine which databases are no longer being used. Thanks Fredrick,
You would need to have had Profiler or some third-party auditing tool running to determine this. Another option would be to use some extra auditing code in a heavily used proc or use a trigger. HTH Jerry Show quote "Fredrick A. Zilz" <fzilz@NOSPAM.interhealthusa.com> wrote in message news:OM8oOChuFHA.2312@TK2MSFTNGP14.phx.gbl... >I am looking for a way to query a database to see when the last time any of >the tables were accessed (select, insert, update, or delete). Basically I >am trying to determine which databases are no longer being used. > > Thanks > This is not supported natively by MS SQL. You'd have to implement it
yourself, either by adding a LastChangedDateTime column to each table, yet I wouldn't recommend it. Plus you'd have to design a SELECT trigger on your own, to track reads from each table (which can be done, by the way). Start by tracing the database in profile for an average turn-over period (e.g. a month), but only trace object names (event: "Object:Opened", data column: "ObjectName"). After the turn-over period rename objects that haven't been used (if applicable), before you remove them. ML Explain how you would implement this SELECT trigger.
Show quote "ML" <M*@discussions.microsoft.com> wrote in message news:0D577D84-2590-4A50-BBBF-6423EDA30DC5@microsoft.com... > This is not supported natively by MS SQL. You'd have to implement it > yourself, either by adding a LastChangedDateTime column to each table, yet > I > wouldn't recommend it. Plus you'd have to design a SELECT trigger on your > own, to track reads from each table (which can be done, by the way). > > Start by tracing the database in profile for an average turn-over period > (e.g. a month), but only trace object names (event: "Object:Opened", data > column: "ObjectName"). > > After the turn-over period rename objects that haven't been used (if > applicable), before you remove them. > > > ML One way would be to have profiler running all the time, and logging to a
trace table, and then a job could watch the trace table for any selects and in turn log those elsewhere. Show quote :-) "JT" <some***@microsoft.com> wrote in message news:e3$usbhuFHA.3452@TK2MSFTNGP14.phx.gbl... > Explain how you would implement this SELECT trigger. ....and here's the link:
http://solidqualitylearning.com/Blogs/dejan/archive/2004/11/25/214.aspx ....and there *it* is. ML Butt it ainta select trigga. Itsa insert trigga.
Show quote "ML" <M*@discussions.microsoft.com> wrote in message news:DB8F0F5E-E1D3-46FE-AC68-4661E8151511@microsoft.com... > ...and here's the link: > http://solidqualitylearning.com/Blogs/dejan/archive/2004/11/25/214.aspx > > ...and there *it* is. > > > ML You can use SQL Profiler to maintain a trace log of when objects are
referenced over a period of time. Also, you can list object dependencies using [sp_MSdependencies]. http://www.microsoft.com/sql/techinfo/tips/development/displayingdependencies.mspx Show quote "Fredrick A. Zilz" <fzilz@NOSPAM.interhealthusa.com> wrote in message news:OM8oOChuFHA.2312@TK2MSFTNGP14.phx.gbl... >I am looking for a way to query a database to see when the last time any of >the tables were accessed (select, insert, update, or delete). Basically I >am trying to determine which databases are no longer being used. > > Thanks > Once you start droping tables, you may want to setup an alert on the
'Invalid object name' error. Show quote "JT" <some***@microsoft.com> wrote in message news:OqW0kdhuFHA.2948@TK2MSFTNGP15.phx.gbl... > You can use SQL Profiler to maintain a trace log of when objects are > referenced over a period of time. > > Also, you can list object dependencies using [sp_MSdependencies]. > http://www.microsoft.com/sql/techinfo/tips/development/displayingdependencies.mspx > > "Fredrick A. Zilz" <fzilz@NOSPAM.interhealthusa.com> wrote in message > news:OM8oOChuFHA.2312@TK2MSFTNGP14.phx.gbl... >>I am looking for a way to query a database to see when the last time any >>of the tables were accessed (select, insert, update, or delete). >>Basically I am trying to determine which databases are no longer being >>used. >> >> Thanks >> > > Thank you everyone for your assistance.
I will setup the alert on the 'Invalid object name' error. Something I would not have thought to do. Show quote "JT" <some***@microsoft.com> wrote in message news:OCDip6huFHA.3104@TK2MSFTNGP10.phx.gbl... > Once you start droping tables, you may want to setup an alert on the > 'Invalid object name' error. > > "JT" <some***@microsoft.com> wrote in message > news:OqW0kdhuFHA.2948@TK2MSFTNGP15.phx.gbl... >> You can use SQL Profiler to maintain a trace log of when objects are >> referenced over a period of time. >> >> Also, you can list object dependencies using [sp_MSdependencies]. >> http://www.microsoft.com/sql/techinfo/tips/development/displayingdependencies.mspx >> >> "Fredrick A. Zilz" <fzilz@NOSPAM.interhealthusa.com> wrote in message >> news:OM8oOChuFHA.2312@TK2MSFTNGP14.phx.gbl... >>>I am looking for a way to query a database to see when the last time any >>>of the tables were accessed (select, insert, update, or delete). >>>Basically I am trying to determine which databases are no longer being >>>used. >>> >>> Thanks >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics