I wish to run DBCC FLUSHPROCINDB on a database called 'Batch'. I found the following exampleECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'database_name') DBCC FLUSHPROCINDB (@intDBID)But I don't understand it. Can someone please explain?Thanks
Hi, Thid dbcc command is used to clear out or free procedure cache for specific database by supplying database id. So in first statement you are setting the dbid to variable @intdbid and then passing it to dbcc command
From one of the tIPS on this website:DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a specific database on a SQL Server, not the entire SQL Server. The database ID number to be affected must be entered as part of the command.You may want to use this command before testing to ensure that previous stored procedure plans won't negatively affect testing results.Example: DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'database_name') DBCC FLUSHPROCINDB (@intDBID)
In any case refrain using this on the production server, as it plays wtih cache and running this will have slow performance on huge procedures.
I would like to know what is permission level to run this command as I intend to use in SP in certain situation as couldn't find in BOL.
