Recompile across databases
-
I've read all the articles and it shows that sp_recompiles in the current database only. We had a production issue last night and were able to identify the proc causing the issues. This proc is a parent to another child proc and resided in DB1. It will do an insert into the history tables in DB2 with a select from the source tables in DB1.
i.e Insert into db2.tablenamehistory select * from db1.tablename.
The problem was solved when we forced a recompile on the parent proc. My question is .... Which objects will cause the parent proc to recompile and do the children automatically get recompiled as well.
Thanks in advance for any help
-
My test indicates recompile parent proc will NOT recompile the child proc even in the same database.
-
We use the following code to recompile everything on a weekly basis. Hope this helps. It's [very] old code, and it's not very elegant, but it seems to do the job.
Jon
-----------------
/*******************************************************************************
*
* File Name: Recompile.sql
*
* Description: Build a script to run sp_recompile against all
* non-system databases on a server
*
* Syntax: (Called by SQLWeeklyMaintenance.cmd)
*
* SQL Server: 6.5 SP5A, 7.0 SP3, 2000 SP2
*
********************************************************+**********************/
go
set nocount on
go
declare @Database_NME varchar(128) --Database name
,@Table_NME varchar(128) --Table name
,@ObjectOwner_NME varchar(128) --Object owner name
,@SQLString varchar(255) --SQL command line
print '/*******************************************************************************'
print '*'
select @SQLString = '* File name = \\' + @@servername + '\DBADATA\Recompile2.sql'
print @SQLString
print '*'
select @SQLString = '* Generated by Recompile.sql on ' + convert(char(10), getdate(), 101) + ' at ' + convert(char(5), getdate(), 114) + ' for \\' + @@SERVERNAME
print @SQLString
print '*'
print '********************************************************+**********************/'
print 'go'
set nocount on
/*******************************************************************************
*
* Create temporary table
*
********************************************************+**********************/
create table #DBA_Recompile
(
Database_NME varchar(128), --Database name
Table_NME varchar(128), --Table name
ObjectOwner_NME varchar(128) --Object owner name
 
/*******************************************************************************
*
* Use a cursor to create a script which will execute sp_recompile
*
********************************************************+**********************/
declare DBCursor cursor for
select name from master..sysdatabases
where
name not in ('master', 'model', 'msdb', 'pubs', 'tempdb')
order by
name
open DBCursor
fetch next from DBCursor into @Database_NME
while @@fetch_status = 0
begin
print ''
print '/*******************************************************************************'
print '*'
select @SQLString = '* Recompile ' + @Database_NME + ' database'
print @SQLString
print '*'
print '********************************************************+**********************/'
print 'go'
print ''
select @SQLString = 'use ' + @Database_NME
print @SQLString
print 'go'
--Null is used to keep the @SQLString small--SQL 6.5 can only handle 255 bytes
select @SQLString = 'insert into #DBA_Recompile select ''' + @Database_NME + ''', a.name, b.name from ' + RTRIM(@Database_NME) + '..sysobjects a, ' + RTRIM(@Database_NME) + '..sysusers b where a.type = ''U'' and a.uid = b.uid'
-- select @SQLString
exec (@SQLString)
-- select 'Contents of #DBA_Recompile: ', * from #DBA_Recompile order by Table_NME
/*******************************************************************************
*
* Read through the temporary table and insert go commands
*
********************************************************+**********************/
declare TableCursor cursor for
select Database_NME, Table_NME, ObjectOwner_NME from #DBA_Recompile
order by
Table_NME
open TableCursor
fetch next from TableCursor into @Database_NME, @Table_NME, @ObjectOwner_NME
while @@fetch_status = 0
begin
print ''
select @SQLString = 'exec sp_recompile ''' + @ObjectOwner_NME + '.' + @Table_NME + ''''
print @SQLString
print 'go'
fetch next from TableCursor into @Database_NME, @Table_NME, @ObjectOwner_NME
end
close TableCursor
deallocate TableCursor
delete from #DBA_Recompile
fetch next from DBCursor into @Database_NME
end
close DBCursor
deallocate DBCursor
/*******************************************************************************
*
* Drop temporary table
*
********************************************************+**********************/
drop table #DBA_Recompile
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply