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 |
|
Ravikumarc
Yak Posting Veteran
51 Posts |
 Posted - 2011-08-19 : 09:24:04
|
| Dear All,Please advice on the below issue..We have 1000 + servers and we don’t know whether they are effectively been are in use.. So we planned to collect the latest date/time when the database was last modified.Here last modified means, if a database has 1000 table, we need to find out last modified date for 1000 tables and if any one of the table was updated recently in an year, we need to have the output with that date/time along that specific database name ... ( no need of records with all table and its modified date)In Short:: last updated or modified date of table along with the database name.Thanks & RegardsRavi |
|
|
Ravikumarc
Yak Posting Veteran
51 Posts |
Posted - 2011-08-19 : 09:44:49
|
| Dear All,Please find the script which i have used to collect the infoSET NOCOUNT ONselect name, crdate, refdate,@@servernamefrom sysobjectsorder by crdate descgoPlease advice to refine the code further..Thanks & RegardsRavi |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-19 : 10:17:53
|
| using sys.objects, instead of of sysobjects, will give you a modify_date column (in addition to create_date)however, data updates won't change that, only physical changes to the table will (or possibly some action such as index rebuild)If your user tables include a column for when the Row was updated you could query that.You could probably also use SQL Profiler to see if there is any traffic (queries and the like) to the databases on that server - and isolate which databases were used/unused by running that over a period of time.You could also do a Scream Test - stop the SQL Service on the server and see if anyone complains ... I think the TV company here did that a while back. They were broadcasting from a transmitter in London on a frequency that was really REALLY old. They broadcast a test card asking anyone seeing it to contact them, and got half-a-dozen responses. It was costing millions to run the service and had probably not been seen by significant numbers of people for years - decades maybe! |
|
|
|
|
|
|
|
|
Out of 1000 servers need query to get last updated