Procedure Cache was very low when altering a database

• Mani-584606

  SSChampion

  Points: 11788

  More actions

  March 30, 2010 at 5:47 pm

  #140136

  Hi,

  We have SQL Server 2005 EE x64 with SP3 and MOSS 2007 databases in it. After the installation of MOSS, we are moving the .ldf files to different log drive

  1. We stopped all the MOSS 2007 Services connect to the SQL instance

  2. Used the below steps to move the ldf files to Different drive

  Steps: from the link http://msdn.microsoft.com/en-us/library/ms345483(SQL.90).aspx

  --To move a data or log file as part of a planned relocation, follow these steps:

  --Run the following statement.

  ALTER DATABASE database_name SET OFFLINE

  --Move the file or files to the new location.

  --For each file moved, run the following statement.

  ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' )

  --Run the following statement.

  ALTER DATABASE database_name SET ONLINE

  --Verify the file change by running the following query.

  SELECT name, physical_name AS CurrentLocation, state_desc

  FROM sys.master_files

  WHERE database_id = DB_ID(N'<database_name>');

  Here when ever I ran the ALTER DATABASE database_name SET OFFLINE, the Procedure Cache value dropping to 40 - 50 %.

  and immediately after couple of seconds it's value reaching to 80 %

  So dropping the Procedure Cache value, while running ALTER DATABASE database_name SET OFFLINE is an indication of Memory bottleneck??

  Please advice

• Mani-584606

  SSChampion

  Points: 11788

  More actions

  April 1, 2010 at 12:24 pm

  #1144369

  Is this normal for the procedure cache value to go lower when ALTER DATABASE database_name SET OFFLINE command runs?