Sql-server – Understanding “Date Last Modified” for “System Stored Procedures”

sql serversql-server-2008-r2

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.

Best Answer

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.

Related Question