SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Giant TLog after Reorg
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

andyhro
Starting Member

Germany
8 Posts

Posted - 04/02/2013 :  08:18:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2075 Posts

Posted - 04/03/2013 :  11:57:41  Show Profile  Visit jackv's Homepage  Reply with Quote
It's tricky when the tables are very large. the Index Rebuild for one table runs as one transaction.Regular transaction log backups can assist - but as mentioned - one table is one transaction.


Read this post for some ideas: http://www.sqlserver-dba.com/2013/04/manage-transaction-logs-during-index-rebuild.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

robvolk
Most Valuable Yak

USA
15679 Posts

Posted - 04/03/2013 :  12:25:21  Show Profile  Visit robvolk's Homepage  Reply with Quote
Something with a billion rows really needs to be partitioned: http://msdn.microsoft.com/en-us/library/ms190787.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000