Solved

Is there a way to determine the last actual update to any table in a Database in Ms SQL 2005

Posted on 2012-03-18
35
255 Views
Last Modified: 2012-04-14
We are doing a comparison on 5 databases, doing restore from various years.  We came upon a problem as to what year is the database we are working in.  This is because we are working in various areas within SQL and has been over a week doing in all of these areas.  So we get confused as to what DB we are working in.

What we have done today is just rename the DB with by placing the year in from of it.  For example, the DB production DB is names DBcompany1, we restore X year as XXXX_DBcompany1 (ex.  2009_DBcompany1, 2009_DBcompany2, etc.)

We are looking for a script that we can run and know what is the last date the database has been updated any tables.
0
Comment
Question by:rayluvs
  • 24
  • 10
35 Comments
 
LVL 9

Expert Comment

by:keyu
ID: 37736457
SELECT DB_NAME(database_id) AS DatabaseName, last_user_update,*
 FROM sys.dm_db_index_usage_stats
 WHERE database_id = DB_ID( 'mydbname')
0
 

Author Comment

by:rayluvs
ID: 37736459
Thank you... where do I get Db id?
0
 

Author Comment

by:rayluvs
ID: 37736475
Ok found how

SELECT * FROM sys.databases d

Open in new window


However, noticed that the column 'last_user_update' is all NULL and the databse table has been access & modified.
0
 

Author Comment

by:rayluvs
ID: 37736490
Strange, I ran the script a DB not used and the  'last_user_update' display todays date and the current time.

Please explain???
0
 

Author Comment

by:rayluvs
ID: 37736529
Found another script:

select db_name(database_id) dbname,
object_name(object_id,database_id) oname,
MAX(CASE WHEN last_user_update < last_system_update THEN last_system_update ELSE last_user_update END) as LastUpdated 
from sys.dm_db_index_usage_stats 
WHERE database_id = DB_ID('Database_Name')
group by database_id,object_id
order by 3 DESC

Open in new window

0
 

Author Comment

by:rayluvs
ID: 37736536
Did noticed that when a database that has been restored (that is, create a new DB name and restore a last year DB on it, doesn't seem to identify the real time).   It's like the DB has to be the original or the Production ID.

We really need to know the last time a database that we restores from previous years has been really registered.

How can we do that?
0
 
LVL 25

Expert Comment

by:jogos
ID: 37736589
Why is sys.dm_db_index_usage_stats reporting today?
http://msdn.microsoft.com/en-us/library/ms188755.aspx
"Every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this view. Information is reported both for operations caused by user-submitted queries, and for operations caused by internally generated queries, such as scans for gathering statistics."

The information you want, why don't you put them there yourself on a clear and ever identifiable way?
After the restore you add an identifying table and insert there all the information that explains where the backup was comming from. Where to get all that info?
RESTORE HEADEROLY
http://msdn.microsoft.com/en-us/library/ms178536.aspx
0
 

Author Comment

by:rayluvs
ID: 37736629
Understood.  But the RESTORE HEADEROLY just displays the 'BackupFinishDate' and when all backups are processed, it is saved with backup date.  Appreciate the info, but it already has the date stamped on the file-name.

Read your links but can't seem to find where it says to displayed actual updated values and why I can't get the actual last update of a restored database.
0
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 37736893
You mention 2 problems
- confused in which database we are working in
- want to know last update of a restored db

Is that second a way to identify the database you are using?
Yes,
Isn't the backup-info (original dbname, date, .. ) not a good way to persist the info about the origin of the db at the moment of restore?
No,
Why did you mention the problem that you already solved by having it incorporated in the db-name?
Do you explicitly want to know last change on each table for any other reason? Explain

For the sys.dm_db_index_usage_stats that are statistics you can use to identify last usage, but that are not statistics that are kept in each db (and saved with backup) and in fact they are unreliable because initialized by a restart of the db-instance.
... and for you it's not the instance that is restarted but the database itself is new so DMV-statistics only start from your restore.
Double check my comment http://blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/ + certainly first comment on this article
0
 

Author Comment

by:rayluvs
ID: 37737320
Answers (hope we understood the direction of your entry and properly respond to it):
Is that second a way to identify the database you are using?
Yes

Isn't the backup-info (original dbname, date, .. ) not a good way to persist the info about the origin of the db at the moment of restore?
Agree.  That is why we placed the question

Why did you mention the problem that you already solved by having it incorporated in the db-name?
I haven't solved it, that is the way we have prior placing the question.  Just wanted to give more info on our current working process

Do you explicitly want to know last change on each table for any other reason? Explain

Yes.  Because we restore as we need, and it has been 3 or 5 withing a laps of 2 hours.  The purpose to explicitly  know the last change is becuase the criteria of analysis we are having is date sensitive.  We have noticed that we do a Restore, begin work, attend another matter for a couple of minutes.  When returning, it gets confusing which date is the restored

As for the last part of your entry, sys.dm_db_index_usage_stats:
Understood.  So would be safe to say the there is no way to know the latest update of a Restore DB?
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 500 total points
ID: 37737571
<<Isn't the backup-info (original dbname, date, .. ) not a good way to persist the info about the origin of the db at the moment of restore?
Agree.  That is why we placed the question>>
Why don't you use the restore headeronly-info i suggested?


If you change your restore to a 3 actions
a) restore
b)  make a table to contain the which db and version-info
c) fill previous table with the info from the restore headeronly of same file as in a)
    example on how to get that info http://dba.stackexchange.com/questions/12437/extracting-a-field-from-restore-headeronly-in-sql-server

And if you reverse the order, you even can have the db-name build from the header-info.


For getting info from inside your db to have a last updated table we are so spoilled with dmv's that at the moment I can't remember a valuable alternative that is usable for you.
0
 

Author Comment

by:rayluvs
ID: 37740832
You know, seeing it more and more, your advice looks more and more favorable as the way to go.  Tomorow we'll look into the link.

Thank you
0
 
LVL 25

Expert Comment

by:jogos
ID: 37740979
<<And if you reverse the order, you even can have the db-name build from the header-info.>>
For the record.
I mean a step restore headeronly first to get info for building the db-name.  Creating and filling a table in a db that not exists is not possible of course.
0
 

Author Comment

by:rayluvs
ID: 37742622
That is what we understood; every restore, take a moment & create a table to place the Restore info on it
0
 

Author Comment

by:rayluvs
ID: 37742690
We did review the link and that is what we were trying to do; only display the rows we need.  Unfortunately;y, both script seem to send back an error.  Can you verify?

The errors are:
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.


and the other error from the B script:

Msg 7202, Level 11, State 2, Line 4
Could not find server 'LOCALSERVER' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.


Scripts from links:

SELECT BackupStartDate 
FROM OPENROWSET('SQLNCLI',
                'Server=MARTINPC\MSSQL2008;Trusted_Connection=yes;',
'SET NOCOUNT ON;SET FMTONLY OFF;EXEC(''
RESTORE HEADERONLY 
FROM DISK = ''''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\Backup\DB1.bak''''
'')'
) 

-- Script B:
------------
EXEC sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',
                        @provider = 'SQLOLEDB', @datasrc = @@servername

SELECT BackupStartDate 
FROM OPENQUERY(LOCALSERVER, 
               'SET FMTONLY OFF;
               EXEC(''
               RESTORE HEADERONLY 
               FROM DISK = ''''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\Backup\DB1.bak''''
'')')

Open in new window

0
 

Author Comment

by:rayluvs
ID: 37742710
Found why... forgot to activate it:

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Open in new window

0
 

Author Comment

by:rayluvs
ID: 37742757
The first script gave me the following error message:

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 65535, Level 16, State 1, Line 0
Interfaces de red SQL: [xFFFFFFFF].


The script B, same error.

Please advice
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 25

Expert Comment

by:jogos
ID: 37743836
Remote connections is turned off by default, see   Surface Area Configuration.
If not wanted block it afterwards again.
0
 

Author Comment

by:rayluvs
ID: 37744080
Yes of course.... thank you
0
 

Author Comment

by:rayluvs
ID: 37744208
Just checked.  It's On.  Option chosen "Local and remote connections" and under that, "Using TCP/IP only"
0
 

Author Comment

by:rayluvs
ID: 37758033
Please advice on the topic

Thank you
0
 

Author Comment

by:rayluvs
ID: 37799594
Please advice if there a way to determine the last date the database has been updated.  Recently, we verified a n EE suggestion that the option "Local and remote connections" can be off.  We checked and its on.

Please advice.
0
 

Author Comment

by:rayluvs
ID: 37817496
We would like to close the question.  

Unfortunately, we have become a bit confused of stating what we need.  In our entry ID: 37742690, we say "We did review the link and that is what we were trying to do".  Actually, this is not what we want.  The link is talking about the backup file contents and since it doesn't displays the last date the DB has been modified, it's of no use for us.

In ID: 37736589 and ID: 37737571, EE recommends using an identfying table and make our entry and then use RESTORE HEADERONLY.  Though sounds logical, we would like just run a script to know the last tiime/date updated.  This this option, we have to make additonal entries.  The problem we face here is that still depend on the user and we can make a mistake by entering in the created identifyint table wrong infp

We have googled a lot and we're almost concluded that we cannot know the last time/date of any DB.  So we would like to know:

Is it possible, by script, to know the actual last time/date when a database has been updated?  (we do not need to know last access, we need to know if it's been updated)

thanx
0
 
LVL 25

Expert Comment

by:jogos
ID: 37817606
<<Is it possible, by script, to know the actual last time/date when a database has been updated? >>
Yes by use of the DMV's .... as far as the last reset of the DMV's (restart sql)  and for your restored db that means No not from the sitiation in the backup.

But don't you have any date in your database that indicates a moment of change? Things that only are stored with a date that is automaticly set on the day of insert or change. But that still only gives you a clue for that table.
0
 

Author Comment

by:rayluvs
ID: 37817627
We are not interested in identify the last update date in a specific Backup media.  We want to run a script or if not possible, usging SQL Studio, to determine the last date of any database of when was the last day it was updated.

You mention DMV, can you explain?
0
 
LVL 25

Expert Comment

by:jogos
ID: 37817800
Repeat: No not from the situation in the backup

<<You mention DMV, can you explain?>>
For the sys.dm_db_index_usage_stats that are statistics you can use to identify last usage, but that are not statistics that are kept in each db (and saved with backup) and in fact they are unreliable because initialized by a restart of the db-instance.
... and for you it's not the instance that is restarted but the database itself is new so DMV-statistics only start from your restore.
Double check my comment http://blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/ + certainly first comment on this article


Sql keeps the info you want for live databases until they are initialised manualy or automaticly (restart).  So it's not there anymore.  
So you have 2 paths I gave you
- use the backup-date info
- see if you have any value in your tables that can give some indication of last use
0
 

Author Comment

by:rayluvs
ID: 37817852
I understood it from when read your entry and we did tweak some of the scripts.  The thing is we are still looking for a solid solution for this problem and would like to come to a conclusion.

Let me put it this way and also to close this question:

Unless using DMV in SQL2005 and also the SQL server not being reset, is it accurate to say that there is no way to know the last date a database has been updated?
0
 

Author Comment

by:rayluvs
ID: 37825782
Please advice
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 500 total points
ID: 37826720
Another try

Not reporting last update of the content of  a table but the description of a table/view
http://weblogs.sqlteam.com/joew/archive/2007/10/11/60367.aspx

Looking for a column in your db that holds a 'run-date' or any date that indicates a moment of change (of that date, not last update in your db)
select isc.TABLE_SCHEMA, isc.TABLE_NAME,isc.COLUMN_NAME
,isc.DATA_TYPE,isc.IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS as isc
WHERE isc.DATA_TYPE like 'Date%'
order by isc.TABLE_NAME

Open in new window


And don't forget. No is also an answer.
0
 

Author Comment

by:rayluvs
ID: 37826842
understood.  But as soon as we were told in this question that the only way to know if a database has been updated is thru DMV (obviously, if SQL is reset, update info is lost).

Like you said, no is also an answer.

We just want to make sure and stop searching for something that is not available.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37827273
<<But as soon as we were told in this question that the only way to know if a database has been updated is thru DMV >>
Paitiently waiting with you.
0
 

Author Comment

by:rayluvs
ID: 37835244
by "Paitiently waiting with you", you yes?
0
 

Author Comment

by:rayluvs
ID: 37835539
by "Paitiently waiting with you", you mean "yes" that only thru DMV can we determine when the database was last updated?
0
 
LVL 25

Expert Comment

by:jogos
ID: 37841368
With my knowledge I've given different no's and different alternatives to get closer.      
I've never seen anyone to point to a magic date, even on the double-check link the weakness of DMV's is raised ..... and not even the SQL MVP pulls that magic date you're waiting for.  That article sais 'if you don't have a last update column in your db, there is a magic date in DMV'.

I it exists I want to know it too
0
 

Author Comment

by:rayluvs
ID: 37844689
Understood; passed on the info that only thu DMV we can know the last time a database has been updated.

Thanx
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now