How to get last insert/update/delete datetime on Sql Server 2005?

Question

not a duplicate of my previous question

Is there any way to get the latest datetime when a table/database had an insert/update/delete on Sql Server 2005? Preferably without creating triggers..

I know that when you need the last update per row, you need triggers. But I am not sure if they are needed when you just want to get the last update for the whole table.

Answer 1

Given there are no answers yet, here are my 2 cents:

• For Insert, I would use a DateTime field with default value GETDATE()
• For Update, I would use also a DateTime field modified by trigger each time there is an update.
• For Delete, the record won't be available, so you can't query it.

I thought about using Timestamps for avoinding triggers, but you can't convert Timestamp to Datetime.

EDIT: Or maybe you can use a "metatable" where in the triggers you will save the change dates

CREATE TABLE metatable (
table_name VARCHAR(40) NOT NULL PRIMARY KEY,
last_insert DATETIME NOT NULL,
last_update DATETIME NOT NULL,
last_delete DATETIME NOT NULL
)

INSERT metatable VALUES ('table1', GETDATE(), GETDATE(), GETDATE())

CREATE TRIGGER trg_table1_ins ON table1 FOR INSERT AS BEGIN
    UPDATE metatable SET last_insert = GETDATE() WHERE table_name = 'table1'
END

CREATE TRIGGER trg_table1_upd ON table1 FOR UPDATE AS BEGIN
    UPDATE metatable SET last_update = GETDATE() WHERE table_name = 'table1'
END

CREATE TRIGGER trg_table1_del ON table1 FOR DELETE AS BEGIN
    UPDATE metatable SET last_delete = GETDATE() WHERE table_name = 'table1'
END

I hope it be useful

Answer 2

without triggers: you could have a LastChgDate column on the table, and set it when you insert/update/delete a row. You'd have to "delete" by usig a status column set to "D" or something like that. Put an index on this column and select the MAX() to see when a change was made.

Answer 3

Well, you could keep a column with a "LastUpdateDate" that is set to the current server date/time on any insert or update. Then you can simply query for the row with the most recent LastUpdateDate.

Answer 4

As the previous two answers show, there's really no built-in functionality in SQL Server that's readily available for your requirements.

There are a ton of dynamic management views which can tell you some of your points of interest, e.g. sys.dm_db_index_usage_stats which tells you when a given index has had its last seek or update.

But there's really nothing in the box per se that you could leverage to get all the information you're looking for - you really have to do this yourself, adding e.g. datetime fields to your tables and filling them with triggers.

Sorry I can't give you any better news - that's just the way it is for now.

In SQL Server 2008, you have additional new features, that might cover some of your requirements - check out:

• Change Data Capture
• SQL Server Auditing

Marc

Answer 5

Over short periods (since server startup) you check sys.dm_db_index_usage_stats last_user_update column. But since this only counts updates since server startup, it cannot be used over a long period of time.

For long periods of time, if the table is not huge, your application can store the table CHECKSUM_AGG(ALL). You'd only need to recompute this once, at application start up, and compare it with the previously stored value. Further the application can detect changes using the DMV. At application shutdown it should store the current table checksum.

Answer 6

Simple! First add a "LastUpdated" column. Give it the default of GetDate(). This will take care of Insert Statements. Second, add an On Update Trigger that updates LastUpdated to GetDate(). Updates are now covered. Finally add a bit/boolean field IsDeleted with the default of 0. Should a user want to delete a row, flip the bit. Since when you "delete" a row, you are actually updating the IsDeleted field (and therefore are using an Update action), Deletes are now timestamped.

To get the most recent activity on the table: to only get the timestamp:

SELECT MAX(LastUpdated) FROM MyTable

To get more information:

SELECT MAX(LastUpdated), ID /*or whatever you need to know*/ FROM MyTable

Answer 7

I would add Change Tracking to the mix - as opposed to Change Data Capture, it is not an Enterprise only feature.

Read about it at http://msdn.microsoft.com/en-us/library/cc280462.aspx

Similar to sys.dm_db_index_usage_stats, the data is not there forever (though it survives server restarts and is configurable) and you'll have to extract and persist the particular piece of information you're looking for.

MDD

Answer 8

Hi, can somebody help me I a have a lot of Data Base SQl Server 2005 I need to know what is the size en GB for each one How I can do get this data Thanks in advance