June 22, 2011 at 11:17 am
EDIT: I guess I shouldn't have put in the title VLDB...more like a large database...so ignore that 🙂
Sorry for the long read...
If anyone can lend some much needed advice and direction I would appreciate it!
I have been assigned the task of migrating a SQL 2005 database to a new SQL 2008 R2 database.
Part of the migration testing is as follows
1. Restore to new server and run through the database upgrade process.
2. Change comp level
3. DBCC CHECKDB (FAILS HERE due to tempdb filling up)
4. DBCC updateusage with row_counts
5. Reindex database
6. drop old execution plans
The database in question is 700 gb's and one table in that database is 340 gb's with almost 95 million rows, the next closest table in size is about 75 gb's
Table Name Table Rows Reserved Space In MB Index Space In MB
------------ ----------- -------------------- ------------------
table_1 94,535,427 342498 46
table_2 95,326,353 76267 15417
table_3 80,333,606 28038 36
table_4 303,511,247 38746 27471
when I run the DBCC CHECKDB WITH ESTIMATEONLY on the 2005 server I get the following results for the 700 gb database
Estimated TEMPDB space needed for CHECKALLOC (kb)
115720 (0.11572 gb's)
Estimated TEMPDB space needed for CHECKTABLES (kb)
53020406 (53 gb's)
when I run the DBCC CHECKDB WITH ESTIMATEONLY on the 2008 R2 server I get the following results for the 700 gb restored database.
Estimated TEMPDB space needed for CHECKALLOC (kb)
523196 (0.523196 gb's)
Estimated TEMPDB space needed for CHECKTABLES (kb)
7702 (0.007702 gb's)
The SQL 2005 has an integrity check done on it weekly through a maintenance plan (created by the wizard)...upon first review no issues at all, hasn't failed since it was created.
Then I notice the ODD part...you go into the wizard to review the plan...it shows DBCC CHECKDB(N'database name') WITH NO_INFOMSGS...ok good so far...the report shows the same thing...with one exception...it completes in 1 minute.
Wait..WHAT? 1 minute for a 700 gb database? I go to the error log and it shows why...it does the CHECKDB but on the master database...6 times...instead of switching to each database and running it.
SO a checkDB has never been run on this datbase...so I have no idea how the server would react to running it...and am now trying to size the new server to run it...and no I don't have a test server to run it on.
SO I am TRYING to run a DBCC CHECKDB on the SQL 2008 R2 server and have as a result of multiple attempts blown up the temp db to a 120 gb's total size
2 files for the data 80 and 26 gb's on two different drives and a log file that is 14 gb's
The tempdb on this server is sized to over 100 gb data file and 14 gb log and it is STILL failing...
The two biggest ables schema is as follows...
CREATE TABLE [dbo].[Table_1](
[MessageID] [int] NOT NULL,
[Header] [image] NULL,
[Body] [image] NULL,
[BodyFormat] [tinyint] NULL,
[BodyLength] [int] NULL,
[HeaderLength] [int] NULL,
[BodyCodePage] [int] NULL
)
CREATE TABLE [dbo].[Table_2](
[MessageID] [int] IDENTITY(1,1) NOT NULL,
[Subject] [nvarchar](500) NULL,
[DateSent] [datetime] NULL,
[DateReceived] [datetime] NULL,
[DateProcessed] [datetime] NOT NULL,
[MessageDate] [datetime] NOT NULL,
[DataLoaderID] [smallint] NULL,
[Importance] [tinyint] NULL,
[Sensitivity] [tinyint] NULL,
[ThreadTopic] [nvarchar](200) NULL,
[ThreadIndex] [nvarchar](200) NULL,
[MessageTypeID] [tinyint] NULL,
[Size] [int] NULL,
[CheckSum] [uniqueidentifier] NULL,
[MessageCheckSum] [uniqueidentifier] NULL,
[ExternalStorageKey] [nvarchar](350) NULL,
[StorageLocationID] [tinyint] NULL,
[MessageClass] [nvarchar](100) NULL,
[OrigionalMessageID] [nvarchar](200) NULL,
[DateModified] [datetime] NULL,
[Pending] [int],
[SourceProperties_DataStreamID] [bigint] NULL,
[NoPurgeBefore] [datetime] NULL
)
What options do I have? run DBCC CheckTable on each table to find out where it is dying? I don't have another server at the moment due to the size of the database to test this on that is the same version 2005.
Any advice is appreciated...
Thanks,
Lee
June 22, 2011 at 11:53 am
TalkToLee (6/22/2011)
Then I notice the ODD part...you go into the wizard to review the plan...it shows DBCC CHECKDB(N'database name') WITH NO_INFOMSGS...ok good so far...the report shows the same thing...with one exception...it completes in 1 minute.Wait..WHAT? 1 minute for a 700 gb database? I go to the error log and it shows why...it does the CHECKDB but on the master database...6 times...instead of switching to each database and running it.
Known bug in SQL 2005 maint plans.
The tempdb on this server is sized to over 100 gb data file and 14 gb log and it is STILL failing...
Keep growing TempDB. CheckDB uses TempDB for a lot of things, keeping track of stuff it's seen, checking materialised views, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 22, 2011 at 12:18 pm
GilaMonster (6/22/2011)
TalkToLee (6/22/2011)
Then I notice the ODD part...you go into the wizard to review the plan...it shows DBCC CHECKDB(N'database name') WITH NO_INFOMSGS...ok good so far...the report shows the same thing...with one exception...it completes in 1 minute.Wait..WHAT? 1 minute for a 700 gb database? I go to the error log and it shows why...it does the CHECKDB but on the master database...6 times...instead of switching to each database and running it.Known bug in SQL 2005 maint plans.
The tempdb on this server is sized to over 100 gb data file and 14 gb log and it is STILL failing...
Keep growing TempDB. CheckDB uses TempDB for a lot of things, keeping track of stuff it's seen, checking materialised views, etc.
1. I found the bug you are referencing...thank you for pointing that out.
2. is the 'WITH ESTIMATEONLY' even a valid thing to use? It doesn't appear to be very reliable at this moment in time. I am the only one on the server so nothing is connected except me (no overhead to deal with).
Is the best method basically doing a trial and error on seeing 'how large' I need to have the data and log file for the CHECKDB to run successfully?
I am doing the top 5 tables (smallest to largest) one at a time to see which one blows up the tempdb and go from there as far as sizing is concerned just to get something to complete.
June 22, 2011 at 12:21 pm
TalkToLee (6/22/2011)
2. is the 'WITH ESTIMATEONLY' even a valid thing to use? It doesn't appear to be very reliable at this moment in time. I am the only one on the server so nothing is connected except me (no overhead to deal with).
From what Paul Randal said on Monday (or maybe yesterday) it's a conservative estimate. It's an estimate, so it could be miles wrong
Is the best method basically doing a trial and error on seeing 'how large' I need to have the data and log file for the CHECKDB to run successfully?
Personally I've never had to. Just left TempDB to autogrow and left it be.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 22, 2011 at 12:45 pm
When you leave the tempdb grow...what is your method? do you do it by a defined mb amount or do you use a %?
Thanks again Gail,
Lee
June 22, 2011 at 12:49 pm
Defined amount always, never a % for any database file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply