Transaction log growth creating partitioned index on table... db in simple mode.

Question

Transaction log growth creating partitioned index on table... db in simple mode.

NJ-DBA

SSChampion

Points: 13834
More actions
November 27, 2013 at 7:07 am

#282855

I'm trying to partition a huge table and running into trouble running out of transaction log space. Can anyone help me understand what's happening and how to estimate how much log space I"m going to need? The database is in simple mode and nothing else is working in the database. Thanks in advance for any help you can provide.
The database is a "Test" db with just one table. That table contains 33 million rows which consume about 250GB of "Space_used" and is essentially defined like this (some names changed to protect the innocent):
CREATE TABLE [dbo].
(
[ID] [int] NOT NULL,
[Type] [varchar](10) NOT NULL,
[Code] [varchar](10) NOT NULL,
[Done] [bit] NOT NULL,
[ID] [int] NULL,
[UserName] [varchar](44) NULL,
[Status] [varchar](20) NULL,
[data1] [varchar](30) NULL,
[int] NOT NULL,
[submittedTime] [datetime] NOT NULL,
[Message] [ntext] NULL)
Yes, I know all about how problematic Ntext can be... is that part of what's going on here?
What I want to is to paritioning this table by month like this:
CREATE PARTITION FUNCTION monthly_partition_function (DATETIME)
AS RANGE RIGHT FOR VALUES
(
'2012-09-01 00:00:00.000',
'2012-10-01 00:00:00.000',
'2012-11-01 00:00:00.000',
'2012-12-01 00:00:00.000',
'2013-01-01 00:00:00.000',
'2013-02-01 00:00:00.000',
'2013-03-01 00:00:00.000', -- partition by month
'2013-04-01 00:00:00.000', -- partition by month
'2013-05-01 00:00:00.000', -- partition by month
'2013-06-01 00:00:00.000', -- partition by month
'2013-07-01 00:00:00.000', -- partition by month
'2013-08-01 00:00:00.000', -- partition by month
'2013-09-01 00:00:00.000', -- partition by month
'2013-10-01 00:00:00.000', -- partition by month
'2013-11-01 00:00:00.000', -- partition by month
'2013-12-01 00:00:00.000' -- partition by month
)
CREATE PARTITION SCHEME monthly_partition_scheme AS PARTITION monthly_partition_function ALL TO ([PRIMARY])
I have enough space in the datafile for two copies of the table (another 300GB of free space), and about 60GB in the transaction log. I fill up the tranasction log when I try to create a clustered index on the partition like this:
ALTER TABLE [dbo].
ADD CONSTRAINT [PK_table_ID_requesttime] PRIMARY KEY CLUSTERED
(
[ID] ASC, [submittedTime]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
ON [monthly_partition_scheme](submittedTime)
The database is in simple mode and there is nothing else other than the index creation that is doing work in the database... the log just keeps growing, through the entire index creation process- it never truncates.... so what's using all that tranaction log space?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The index build is using the transaction log. It's a single transaction, the log can't be reused until it completes.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

NJ-DBA SSChampion Points: 13834 More actions · Answer 2

GilaMonster (11/27/2013)
The index build is using the transaction log. It's a single transaction, the log can't be reused until it completes.

Thanks Gail,

That makes sense, but is there any way to estimate how much log space I'm going to need? Is there any way to minimize the log space needed to partition a large table?

Is there a best practice for how to partition an existing huge table? Am I better off creating an empty table on the paritiion scheme and then BCPing the records from one table to the other?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Simple recovery it might be less if it can run minimally logged, but generally something like this is a size of data operation, so log space needed > data size.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

NJ-DBA SSChampion Points: 13834 More actions · Answer 4

Yeah, everything is in simple mode so that should work. Give give SSIS a try. Maybe with a small batch size I"ll get periodic commits.

Thanks