As is true in most facets of life, things tend to get stale and old. Sometimes this staleness can be visibly represented as the wrinkles on your face. Other times, as with SQL Server, it may mean that a stored procedure or view stops working.

Unfortunately, when it comes to SQL Server, the symptoms are not as readily visible as aging lines would be. In SQL Server, the symptoms may be as random and difficult to recognize as a stored procedure just suddenly stops working.

What makes this even more difficult is that the stored procedure (that is no longer working) may appear to be entirely unchanged. These failures can occur when other changes have occurred to the system as well as when no change has occurred. Imagine the joys you could experience while trying to troubleshoot this kind of problem.

If you were keen, you would probably implement any number of Extended Event Sessions to help troubleshoot the issue. If you are unfamiliar, you might want a refresher course on how to setup an XE session which you could read from one of these articles, here and here.

If you are curious, there are more XE articles on my blog – here.

Metadata

There are many possible causes for the metadata to become fouled up inside the SQL modules on the database server. Invariably, according to the interested parties, nothing has changed in months! Right or wrong, there is still a problem to fix. Fortunately, the problem is easy enough to fix.

Sure, all of the modules could be re-deployed from source control. But, given that the modules don’t to appear to have lost any of the code within them, re-deploying code seems to be a bit of overkill. We don’t need to go that far. We can simply run sp_refreshsqlmodule for the stored modules (procs, triggers, functions, views etc). This would be pretty simple with the following script:

USE AdventureWorks2014;
GO
SELECT o.name AS ObjName, sm.definition, o.type_desc
,'EXECUTE sys.sp_refreshsqlmodule ''['+ SCHEMA_NAME(o.schema_id) + '].['  + o.name + ']'';' AS RefreshStmt
, CASEWHEN o.type IN ( 'TR','P', 'FN', 'FT', 'TF', 'PC', 'FS', 'AF', 'IF' )
THEN 1
WHEN o.type = 'V' THEN 2
WHEN o.type IN ( 'F', 'D' ) THEN 3
WHEN o.type = 'SO' THEN 4
WHEN o.type = 'UQ' THEN 5
WHEN o.type = 'PG' THEN 6
END AS ProcessOrder
FROM sys.all_sql_modules sm
INNER JOIN sys.objects o
ON sm.object_id = o.object_id
ORDER BY ProcessOrder DESC,o.type_desc, o.name
;

In this script, I am just going to refresh all modules in the database (Adventureworks2014 in this case). I could modify the script to trim it down to a specific module or set of modules. Instead, I leave that as homework for you.

That said, I do have a slightly different alternative that looks for any tables changed on a specific date. After finding those changed tables, then I update all modules related to the changed table(s). Here is how that would look:

USE AdventureWorks2014;
GO
DECLARE @ChangeDate DATE = '2014-07-17 16:11:39.583';
SELECT DISTINCT o.name AS ObjName, sm.definition, o.type_desc
,'EXECUTE sys.sp_refreshsqlmodule ''['+ SCHEMA_NAME(o.schema_id) + '].['  + o.name + ']'';' AS RefreshStmt
, CASEWHEN o.type IN ( 'TR','P', 'FN', 'FT', 'TF', 'PC', 'FS', 'AF', 'IF' )
THEN 1
WHEN o.type = 'V' THEN 2
WHEN o.type IN ( 'F', 'D' ) THEN 3
WHEN o.type = 'SO' THEN 4
WHEN o.type = 'UQ' THEN 5
WHEN o.type = 'PG' THEN 6
END AS ProcessOrder
FROM sys.all_sql_modules sm
INNER JOIN sys.objects o
ON sm.object_id = o.object_id
INNER JOIN sys.sql_expression_dependencies ed
ON o.object_id = ed.referencing_id
WHERE ed.referenced_id IN (
SELECT object_id
FROM sys.tables t
WHERE CONVERT(DATE,t.modify_date) = @ChangeDate)
ORDER BY ProcessOrder DESC,o.type_desc, o.name
;

From here, suppose you want to check for a range of dates where tables were modified. Again, I will leave that as homework for you.

After executing these scripts, I will have an output similar to the following:

This leaves you with one more step to perform – copy the values from the RefreshStmt column to a new query window and execute the statements.

The Wrap

In this article I showed a very simple solution to a problem that plagues some environments – out of date metadata. Forcing a module refresh will often resolve these types of issues.

With such a beautifully simple solution, I could have grouped this article into my “Back to Basics” series, but I did not. That said, there are some pretty interesting articles in the series including (but not limited to): Backups, backup history and user logins. Check them out!