sql data query

Hi,

I will decommission some old SQL server  that believed to be no longer needed but I need to know where to start and what to check first, How can I ensure when data last was used or entered ?
they are all sql 2005 servers.

thanks
kuzumAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZberteocCommented:
1. Make sure you backup all the databases on the servers you want to decommission
2. Set the databases offline and see if anyone complains in the next couple of week or so.
3. If no one does you can remove them and/or the server

There is no safe way to tell that the databases are in use or not but if the last modified date on the database files is in the past is pretty much a sign they haven't been used since then. However if there are backups, index rebuilds, or other scheduled tasks active on the servers those dates will be up to date but that is no indication that the databases are actually currently used.
0
Scott PletcherSenior DBACommented:
>> ... if the last modified date on the database files is in the past is pretty much a sign they haven't been used since then. <<

100% NOT true.

The file date often doesn't change until SQL closes the file ... but SQL never closes the file until/unless you do something exceptional to the db, such as take it offline, that forces SQL to close the file(s) (AUTOCLOSE is never used on normal production SQL dbs).

There could have been literally millions of uses of, or even changes to, a file since SQL started and the file date would not necessarily have changed.
0
ZberteocCommented:
"The file date often doesn't change until SQL closes the file ... "

That is not the case. Database files do not behave any different than any other windows file. As soon as the file was accessed/modified the corresponding dates change. It is an OS thing, not SQL. You can try to see for yourself.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Scott PletcherSenior DBACommented:
That's just NOT true -- or at least it didn't use to be last time I checked this.

Think about it: wouldn't it be ridiculously inefficient for the file date to be modified every time SQL Server made a change to a row?!
0
Scott PletcherSenior DBACommented:
When I look at my tempdb files, I see the data that SQL was last started -- on one of my prod servers, that was 12/25/2012.  There's zero chance on that server that nothing has been modified on any tempdb file since last Christmas :-) .

There may be certain actions in SQL which trigger an update of the file mod datetime, but I just can't believe it's continuously updated -- that would just be too much overhead.
0
ZberteocCommented:
I never said that. What I said was when file is accessed and/or modified at OS level. I doubt an active database will not cause the dates update for, let's say, a whole day. It has to commit at some point it if any changes were made. Even if is read only it HAS to access the file in order to read from it, which will affect the file's last access date.

Anyway the idea was that if the dates were old enough then the database can safely be regarded as not in use but the actual recommendation was to set the database offline and see if anyone notices. I can't think of any sound way to actually get the "real" last database use date-time, for the reasons I explained above.
0
ZberteocCommented:
I apologize, and I retract that. I just learned that the only time when SQL causes the dates to change on the database files, beside setting offline, restart, is when they grow/shrink. So it is possible to have old dates on active databases if they din't change in size.

You can disregard that but the offline suggestion remains.
0
Scott PletcherSenior DBACommented:
That makes sense.  Thanks for the follow-up info.

It also explains why my tempdb files still show the original SQL startup datetime -- I size my temp files so they NEVER need to grow, barring something extraordinary happening.
0
kuzumAuthor Commented:
thanks for your valuable inputs gents and time, what shall we say to do best in this instance?

can you please provide me step by step guide?

thanks
0
ZberteocCommented:
Kuzurm,

I already did in my first post. There is really not much else you can do in this case. I assume that if you even consider to decommission those servers they are not by any mean critical. In this case the best approach is the "offline" method.

There are ways to query the index usage DMV in order to have an idea of when a db was last used but for reasons I already explained these methods are not 100% reliable.

Here is an article that probably can help you:

http://mattvelic.com/db-last-used/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Legacy OS

From novice to tech pro — start learning today.