April 17, 2013 at 12:48 pm
I am migrating a database from SQL 2005 to SQL 2008 R2. As soon as the database is restored over to the new server with SQL Server 2008 R2 enterprise, it starts Ghost Cleanup and CHECKPOINT process. It takes about an hour and disks are pegged, utilization is very high during this one hour. It doesn't even let me open database property and simple queries on this database takes forever or times out.
New server has a much better and the best possible hardware. Database size is 600 GB. There were lot of data deleted on the old server few months back. LDF file is 1 GB when restore completes but then it starts going up as big as to 60 GB. When I run sys.dm_db_index_physical_stats on the database, it doesn't give me HUGE number of ghost_record_count as well. Out of 500 tables in the database only one table has 300 ghost record count and few other tables have 1 or 2. Is it deleting those records after migration? Is it because Ghost cleanup doesnt currently run on my SQL 2005 box?
Any suggestions?
April 18, 2013 at 5:59 am
If you run DBCC LOGINFO('DatabaseNameHere') for the affected database how many records are returned?
Each record is a Virtual Log File (VLF) a high number of VLF's would impact recovery time.
Chris
April 18, 2013 at 7:14 am
When I run DBCC LOGINFO('DatabaseNameHere') on this database it returns 1700 records. I saw somewhere in error log that this database has over 1500 VLFs.
April 18, 2013 at 7:19 am
That will certainly impact recovery time, Below is a script that will create a script to update the initial log file size and the auto growth settings based on the knowledge of Kimberly L. Tripp. I would consider changing the file size and growth settings of the database before migration and this will reduce the time it takes to recover.
/*
Update initial log file size and auto growth settings
This script is based on the knowledge of Kimberly L. Tripp (See Below Link)
http://www.sqlskills.com/blogs/kimberly/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
*/
-- Set database context
USE master;
GO
-- Drop temporary tables if they exist
IF ( SELECT OBJECT_ID('tempdb..#tempDBLogSizeTable')
) IS NOT NULL
DROP TABLE #tempDBLogSizeTable;
GO
IF ( SELECT OBJECT_ID('tempdb..#tempDriveSizeTableCLU')
) IS NOT NULL
DROP TABLE #tempDriveSizeTableCLU;
GO
IF ( SELECT OBJECT_ID('tempdb..#stage')
) IS NOT NULL
DROP TABLE #stage;
GO
-- Create temporary tables
CREATE TABLE #tempDBLogSizeTable
(
ServerName VARCHAR(50) ,
DatabaseName VARCHAR(50) ,
Name VARCHAR(128) ,
[Filename] VARCHAR(260) ,
Size_MB FLOAT ,
UsedSpace_MB FLOAT ,
FreeSpace_MB FLOAT ,
Max_Size INT ,
Current_AutoGrowth VARCHAR(20) ,
Current_VLF_Count INT ,
New_Initial_Size_MB INT ,
New_AutoGrowth_MB INT ,
New_Expected_VLF_Count INT ,
[Type] VARCHAR(10) ,
ID INT
);
GO
CREATE TABLE #tempDriveSizeTableCLU
(
Drive VARCHAR(2) ,
MBFree INT
);
GO
CREATE TABLE #stage
(
FileID INT ,
FileSize BIGINT ,
StartOffset BIGINT ,
FSeqNo BIGINT ,
[Status] BIGINT ,
Parity BIGINT ,
CreateLSN NUMERIC(38)
);
GO
-- Insert file information
INSERT INTO #tempDBLogSizeTable
EXEC master.dbo.sp_msforeachdb '
use ?;
SELECT
@@servername,
''?'',
s.name AS [Name],
s.physical_name AS [FileName],
(s.size * CONVERT(float,8))/1024 AS [Size_MB],
(CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float)* CONVERT(float,8))/1024 AS [UsedSpace_MB],
((s.size * CONVERT(float,8)) - (CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float)* CONVERT(float,8)))/1024 AS [FreeSpace_MB],
(s.Max_Size * CONVERT(float,8))/1024 AS [Max_Size],
case when is_percent_growth = 1 then cast(s.growth as varchar(20)) + ''%''
when is_percent_growth = 0 then cast((s.growth * 8 )/ 1024 as varchar(20)) + ''MB''
end Current_AutoGrowth,
-1,
-1,
-1,
-1,
s.type as Type,
s.file_id AS [ID]
FROM
sys.master_files AS s
WHERE s.database_id = db_id()
ORDER BY
[ID] ASC
';
GO
-- Update current VLF count
EXEC sp_msforeachdb N'Use ?;
Insert Into #stage
Exec sp_executeSQL N''DBCC LogInfo(?)'';
Update #tempDBLogSizeTable
set current_vlf_count = (select Count(*) From #stage)
where databasename = ''?''
and type = 1;
Truncate Table #stage;';
GO
-- Update new log sizes
UPDATE #tempDBLogSizeTable
SET New_Initial_Size_MB = CASE WHEN Size_MB <= 500 THEN 500
WHEN Size_MB > 500
AND Size_MB <= 2000 THEN 1000
WHEN Size_MB > 2000
AND Size_MB <= 4000 THEN 2000
WHEN Size_MB > 4000 THEN 3000
END ,
New_AutoGrowth_MB = CASE WHEN Size_MB <= 500 THEN 200
WHEN Size_MB > 500
AND Size_MB <= 2000 THEN 2000
WHEN Size_MB > 2000
AND Size_MB <= 4000 THEN 3000
WHEN Size_MB > 4000 THEN 8000
END
WHERE [type] = 1;
GO
-- Update expected VLF count
UPDATE #tempDBLogSizeTable
SET New_Expected_VLF_Count = CASE WHEN New_Initial_Size_MB < 64 THEN 4
WHEN New_Initial_Size_MB >= 64
AND Size_MB < 1000 THEN 8
WHEN New_Initial_Size_MB >= 1000 THEN 16
END
+ CASE WHEN New_Initial_Size_MB < Size_MB
THEN ( CEILING(( Size_MB - New_Initial_Size_MB )
/ New_AutoGrowth_MB)
* CASE WHEN New_AutoGrowth_MB < 64 THEN 4
WHEN New_AutoGrowth_MB >= 64
AND Size_MB < 1000 THEN 8
WHEN New_AutoGrowth_MB >= 1000 THEN 16
END )
ELSE 0
END
WHERE [type] = 1;
GO
-- Return database file info
SELECT *
FROM #tempDBLogSizeTable
WHERE [type] = 1
AND DatabaseName NOT IN ( 'master', 'model', 'tempdb', 'distribution',
'reportserver', 'reportserver_tempdb' )
AND name NOT LIKE '%2%'
ORDER BY DatabaseName;
GO
-- Return SQL statement to update log sizes
SELECT 'USE ' + DatabaseName + '; dbcc shrinkfile (''' + Name + ''','
+ CAST(( New_Initial_Size_MB - 100 ) AS VARCHAR(15)) + '); '
+ 'ALTER DATABASE [' + DatabaseName + '] MODIFY FILE (NAME = '''
+ Name + ''',SIZE = ' + CAST(New_Initial_Size_MB AS VARCHAR(15))
+ 'MB' + ', MAXSIZE = ' + CASE WHEN max_size = 0 THEN 'UNLIMITED '
ELSE CAST(max_size AS VARCHAR(15))
+ 'MB'
END + ', FILEGROWTH = '
+ CAST(New_AutoGrowth_MB AS VARCHAR(15)) + 'MB)' AS AlterDatabaseStatement
FROM #tempDBLogSizeTable
WHERE [type] = 1
AND DatabaseName NOT IN ( 'master', 'model', 'tempdb', 'distribution',
'reportserver', 'reportserver_tempdb' )
AND name NOT LIKE '%2%'
ORDER BY DatabaseName;
GO
Chris
April 18, 2013 at 7:26 am
This will help. Thanks.
Do you know why GHOST clean up starts running right after migration to SQL 2008. I checked TRACESTATUS(611) on 2005 server and it is not disabled for sure. That means clean up is running on old server too. Right after clean up got over on new server after migration, performance came back to normal and when I ran dm_db_index_physical_stats, I can see less number of ghost_record_count too. Does this point there is some issue with the cleanup process on my current 2005 server and it kicks of as soon as database is gone to 2008 server and takes too much resources until it is over.
April 18, 2013 at 7:43 am
chris.mcgowan (4/18/2013)
That will certainly impact recovery time, Below is a script that will create a script to update the initial log file size and the auto growth settings based on the knowledge of Kimberly L. Tripp. I would consider changing the file size and growth settings of the database before migration and this will reduce the time it takes to recover....
That is an excellent script. Thanks very much for posting it here.
April 18, 2013 at 7:51 am
I imagine ghost cleanup is run when you migrate as some part of the recovery process trigger it. It may be worth posting the question on twitter using the #sqlhelp hashtag, someone will know the answer.
Chris
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply