How to find executed time

Question

Post reply

How to find executed time

spectra

SSCarpal Tunnel

Points: 4120
More actions
November 4, 2016 at 2:54 am

#331514

How do I find when was the last time :

1) I executed insert script in DB

1) I executed update script in DB

DB: sqlserver 2008 R2

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Unless you have some custom monitoring in place, you don't. SQL doesn't track that information.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

I agree 100% with Gail. To do this accurately, you need to have monitoring in place.

However, you could look to the Dynamic Management View, sys.dm_exec_query_stats. That does have the last executed time for all the queries that are currently in cache on your system. Assuming you search this for INSERT or UPDATE (you'll have to combine it with sys.dm_exec_sql_text), you can sort of find what you're looking for. However, it is dependent on the query being in cache. If it's aged out of cache or was removed for any other reason, you won't find the information you're looking for.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

With the understanding that it clears out on a restart of the SQL Service, you could also look at sys.dm_db_index_usage_stats. It won't differentiate between INSERTs and UPDATEs but it will tell you when the last time either one of those happened.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

Jeff Moden (11/4/2016)

With the understanding that it clears out on a restart of the SQL Service, you could also look at sys.dm_db_index_usage_stats. It won't differentiate between INSERTs and UPDATEs but it will tell you when the last time either one of those happened.

And it also won't say anything about who ran the insert/update.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

GilaMonster (11/4/2016)

Jeff Moden (11/4/2016)

With the understanding that it clears out on a restart of the SQL Service, you could also look at sys.dm_db_index_usage_stats. It won't differentiate between INSERTs and UPDATEs but it will tell you when the last time either one of those happened.

And it also won't say anything about who ran the insert/update.

Correct. Missed the "I" in the original post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions