SQL: Recompile after DB restore

I have recently come to realise an issue with database restoring in SQL Server 2005.

Depending on how the database is restored from another database, there are chances for some stored procedures to stop working (AFAIK this could be random), because their execution plan isn’t relevant anymore. The typical symptom is that when you run a stored proc, it simply sticks in the running state forever. This requires recompilation of the stored proc.

Recompiling stored procs is quite simple. This can be setup as automatic SQL jobs after each restore to prevent broken stored procs.

Recompiling all stored procs that access a key table

EXEC sp_recompile 'table_name';

Otherwise we can also target a specific stored proc

EXEC sp_recompile 'sp_name';

If identifying these stored procs is not realistic (due to its randomness), we can also consider recompiling all DB objects by using sp_MSforeachtable

EXEC sp_MSforeachtable @command1="EXEC sp_recompile '?'";

What sp_recompile does is simply marking the target object for recompilation. The objects will be recompiled when the next time it is accessed. Even though, the last option could take a long time because it loops through each table in the DB and recursivly marks all objects that depends on their recursive parent objects for recompilation.

Source: http://www.sqlservercurry.com/2009/06/recompiling-stored-procedures-in-sql.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s