Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ramaiah
Starting Member
27 Posts |
 Posted - 2010-09-07 : 06:51:18
|
| Is there some inherent mechanism that will show the last time a database was accessed? required the results still if the sql service is restarted.my sql server having 300 databases. Due to disk space issue, we want to take the database offline which db is used from last 6 months. But monthly we have restarted the server. How we can know the list of databases which are last accessed?Thanks in Advance.Rams |
|
|
DuncanP
Starting Member
12 Posts |
Posted - 2010-09-13 : 10:21:17
|
| You might have to start a SQL trace and leave it running for a while in order to get this information. You would only need to capture the database id column so it needn't be to heavy on performance. You can check sp_who2 first to find databases currently in use, then add the database ids of those databases to a filter in the trace definition. That will get the trace to ignore the most-used databases. Then run the trace for a bit to find the some other commonly used databases. If you set a maximum file size, it'll just stop after a while on its own, which means you don't risk running out of space if there's alot of activity. If you leave it to fill the trace file, add any databases ids to the filter, rerun and repeat, you should over time be able to get a list together of all used databases.The easiest way to set up a trace is to start with Profiler, but instead of running the trace, script the trace definition, then change the location of the file to be saved and run it on the server.Duncan |
 |
|
|
|
|
|
Last Time database access in sql server 2008