Determine Last Updated Date on SQL Database 1

[Steve101]

We are looking at decommissioning a SQL Server computer, and need to know when a number of databases on it were last accessed and/or changed.

Not too particular about which tables in the database were impacted; only when the database itself was changed.

Does such meta data exist in any of the systems tables, and if so, is there a simple SQL query I can run to tell me when all or a selected database on the server were last updated. Those that have not been accessed / changed in some time become obvious candidates for taking off-line as the first step.

Any help would be appreciated,

TIA





Steve Lewy
Solutions Developer
SimplyData
simplydata.com.au
(dont cut corners or you'll go round in circles)

 

[mrdenny]

Nope. You'll need to run profiler against the server and see what usage pops up.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Steve101]

Thanks Denny,
Is the datetime stamp on the log file likely be be of any use, or can this date change based on transactions other than changes to the database user tables?



Steve Lewy
Solutions Developer
SimplyData
simplydata.com.au
(dont cut corners or you'll go round in circles)

 

[mrdenny]

Nope, the last modified date on the files is worthless. I've had systems where the last modified date of the files wouldn't change for weeks even though the system was in use.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Steve101]

Can you briefly explain to me how to use the aforementioned profiler to determine when activity occurred against a SQL database; does this only indicate when the database was accessed, or does it also indicate whether changes occurred. Hope these questions are not too dumb, but I havnt done this before; perhaps just point me in the right direction.

I am surprised that there is nothing in SQL itself to provide this info; the requirement seems pretty fundamental.

Cheers,


Steve Lewy
Solutions Developer
SimplyData
simplydata.com.au
(dont cut corners or you'll go round in circles)

 

[mrdenny]

When you run profiler it will show you every command against the database. When you setup a new trace you can specify what kinds of commands you want to see. There are tons of filters you can setup. If you only want to see updates/inserts/deletes you can setup a filter to show all commands that have a write greater than 0.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Steve101]

Denny,
Sorry, can we take a step back. I have NEVER run profiler. From your help, I have figured out that it exists, and know how to invoke it; can read up about it now. Many thanks.

I just need to confirm now that I can only profile looking forward; there is no way to determine historic activity on the databases; hense in order to determine usage on databases, I will only be able to profile from NOW looking forward.

Many thanks,

Steve

Steve Lewy
Solutions Developer
SimplyData
simplydata.com.au
(dont cut corners or you'll go round in circles)

 

[SQLBill]

That's correct...Profiler will only 'catch' what happens from the time you start using it.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

dito

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLBill]

FYI-if you look at the date/time of the actual file (.mdf/.ldf), that is NOT the date/time that it was last accessed (as mrdenny said earlier). SQL Server ONLY updates that date/time when SQL Server is restarted. Most files have that date/time change because the file is closed (saved) and that is when the date/time change occurs. However, SQL Server files are never closed unless the database is taken offline or SQL Server is restarted.

-SQLBill

Posting advice: FAQ481-4875

 

[Steve101]

Thanks guys; I am still a little miffed as to why there is no "metadata" kept within a SQL database indicating when it was last accessed / updated; I would have thought that this was pretty fundamental stuff.

Anyway thanks both for putting me on the right track,
Cheers,
Steve

Steve Lewy
Solutions Developer
SimplyData
simplydata.com.au
(dont cut corners or you'll go round in circles)

 

[mrdenny]

The reason that this isn't kept anywhere is that if it was that would put a major additional load on the database. If a database has 10000 reads per minute and we were capturing the last time it was accessed that would then add an additional 10000 updates per minute to the database greatly slowing down the database server.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Steve101]

Thanks Denny, I realise all of that. However, still might be nice to hold some sort of last access'ed stats, maybe at the connection level. But I take your point, if someone wants this badly enough, use the profiler, and build it into the app.

Anyway its to the profiler for me (sigh),


Steve Lewy
Solutions Developer
SimplyData
simplydata.com.au
(dont cut corners or you'll go round in circles)