Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
andyhro
Starting Member
8 Posts |
 Posted - 2013-04-02 : 08:18:21
|
I have a table with 1 billion records - with a clustered Index (primary key = 54 GB) - and a non clustered Index (= 40 GB)At a defragmentation of 10% we start a REORG.(At a defragmentation of 30% we start REBUILD.)Unfortunately the TLog is growing on 260 GB while a REORG!(The Recovery Modell is BULK_LOGGED.)Can anybody help?
CREATE TABLE [dbo].[AMStatusHistorie]( [typ] [int] NOT NULL, [objektId] [int] NOT NULL, [datum] [datetime] NOT NULL, [mitarbeiter_ID] [int] NOT NULL, [status] [int] NOT NULL, [id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, CONSTRAINT [PK_AmStatusHistorie] PRIMARY KEY CLUSTERED ( [objektId] ASC, [typ] ASC, [id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO
CREATE NONCLUSTERED INDEX [AMStatusHistorieTypStatusIdx] ON [dbo].[AMStatusHistorie] ( [typ] ASC, [status] ASC, [datum] ASC)INCLUDE ( [objektId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [INDIZES]GO |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-04-03 : 12:25:21
|
| Something with a billion rows really needs to be partitioned: http://msdn.microsoft.com/en-us/library/ms190787.aspx |
 |
|
|
|
|
|
|
|
Giant TLog after Reorg