2

Whilst reviewing code on a SQL 2008R2 Instance I inspected the Date Last Modified of the System Stored Procedures of the databases. The ones in the DBO schema had a datetime that predates the installation however the ones in the dbo schema come later than the installation date. Specifically the machine was built December 2013 and the date of these SPs is 09/07/2014 17:16 (both for Date Last Modified and Create Date). The DB in question was also created December 2013.

Now I assume this was something benign, but how can I understand and determine what caused this date stamp?

I've checked the Windows logs but can't find any installations going on at that time.

| improve this question | |
  • Has the database been restored? Are you getting these dates from SSMS or querying the system object views? – user507 Sep 29 '14 at 9:10
  • What is the difference between "DBO schema" and "dbo schema"? – user507 Sep 29 '14 at 9:13
  • @ShawnMelton 1) The DB has been restored multiple times. More recently than the July date. 2) Typo – Paul Sep 29 '14 at 10:13
1

According to BOL, the modify_date from sys.all_objects shows:

Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or modified.

However, if you have a maintenance plan that recompiles stored procedures, using sp_recompile, or have system stored procedures that run with the WITH RECOMPILE option, this will also cause the modify_date to update.

There was a Connect Item raised about this, but it was closed as 'Won't Fix'.

This could potentially explain your Date Last Modified issue.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.