Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / DB Engine / SQL Server / July 2008

Tip: Looking for answers? Try searching our database.

rebuilding indexes

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jay - 18 Jul 2008 08:52 GMT
I have a maintenance plan that has a step which rebuilds indexes.
While doing this the transaction log bloats to a huge size - 20gb.
As a result, available disk space is used up and the job fails.

Is this expected behaviour? (the log growth I mean).
Is there a way to prevent this or would doing so be dangerous?
Do I just allocate more disk space to this partition?
I have a vague knowledge of what the rebuilding means but can someone
explain how the transaction log is involved during this process please.

TIA
David Hay - 18 Jul 2008 13:22 GMT
Check your recovery model.  If it is set to full and you don't need
full recovery, set it to simple, back it up with truncate only then
shrink it.  This should then let it grow to a "normal" size.

This might help you out.  It will give you a good understanding of
what goes on under the hood so to speak.

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

When you start getting into issue with "maintenance plans" you will
soon come to realize it's better to get your own set of scripts.  Not
having full control leaves you vulnerable to the whims of the GUI.
There are many many versions on a number of sites.  I offer this one
as the basis for what I do with my servers.  I have tweaked it over
the past year or so to fit our environment, but it's a great base!

http://download.microsoft.com/download/4/0/C/40CBAD9A-D990-450B-8785-F288CEBFB44
8/AITScripts.zip


Another useful read, where I initially found the scripts

http://www.sqlmag.com/Article/ArticleID/96463/sql_server_96463.html

Good luck!

David Hay
TheSQLGuru - 18 Jul 2008 14:22 GMT
Are you doing rebuild or reorg?  reorg has the capability to actually log
FAR more stuff than the size of the indexes it rebuilds because it does page
swaps, and the same page can get swapped more than once.

Do you have any free space in your database, or were you a bad boy/girl and
left the default database growth settings in place?  If no free space,
consider doubling the size of your database then try maintenance.  Also, if
doing a reorg, try a rebuild instead after creating sufficient free space.

Likewise, find/develop a script to control indexing (there is a good start
of one in BOL) instead of using maint plan.  That will get you doing only
stuff that actually needs to be defragged, instead of everything.

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

>I have a maintenance plan that has a step which rebuilds indexes.
> While doing this the transaction log bloats to a huge size - 20gb.
[quoted text clipped - 7 lines]
>
> TIA
Jay - 19 Jul 2008 00:05 GMT
Thanks guys, I will look into both your recommendations when I return to
work.
The DB is a vendors back end and was installed and configged by them. As was
the maintenance plan.
This is a requirement of them fully supporting the application. I will need
to check with them with any changes I make to the plan.

The DB itself is set to autogrow and is around 18gb in size.

> Are you doing rebuild or reorg?  reorg has the capability to actually log
> FAR more stuff than the size of the indexes it rebuilds because it does
[quoted text clipped - 21 lines]
>>
>> TIA
Jay - 20 Jul 2008 03:29 GMT
I've been having a read up on indexing and specifically the options in 2005.
Given that this maintenance plan is 99.99% guaranteed to only run when there
is zero user access, should I set the online = off option when doing the
index rebuild?

If I'm reading correctly then this should greatly reduce the amount of
logging.
Is there any dangers involved with doing this?

> Thanks guys, I will look into both your recommendations when I return to
> work.
[quoted text clipped - 31 lines]
>>>
>>> TIA
Andrew J. Kelly - 20 Jul 2008 15:19 GMT
The default is to rebuild indexes off line any way. While this may reduce
some of the logging activity that will not be the deciding factor in how
much gets logged. If you rebuild then indexes you can change the db to
Simple recovery mode beforehand to minimize the logging. But you will need
to set it back when done and issue another FULL backup to restart the log
chain before doing log backups again. You should ensure there is plenty of
space on the drive to do what needs to be done. If you are rebuilding every
index in every table you can do one of two things. One is to run several log
backups during the rebuild process to allow the space to be reused and keep
the log file size in check. But You should really not be rebuilding indexes
that don't need rebuilding in the first place. Stop using the maintenance
plan and create your own job to only reindex indexes that have above a
certain amount of fragmentation. That will greatly reduce the number of
indexes that get rebuilt each night and thus less logging. You can find a
sample script in BooksOnLine under the sys.dm_db_index_physical_stats entry
to rebuild only ones that are above a certain fragmentation. Or have a look
at this article in SQL Magazine with a sp to do that and more as well.

http://www.sqlmag.com/Article/ArticleID/99019/sql_server_99019.html

Signature

Andrew J. Kelly    SQL MVP
Solid Quality Mentors

> I've been having a read up on indexing and specifically the options in
> 2005.
[quoted text clipped - 41 lines]
>>>>
>>>> TIA
Jay - 22 Jul 2008 08:57 GMT
> Are you doing rebuild or reorg?  reorg has the capability to actually log
> FAR more stuff than the size of the indexes it rebuilds because it does
[quoted text clipped - 9 lines]
> of one in BOL) instead of using maint plan.  That will get you doing only
> stuff that actually needs to be defragged, instead of everything.

Is a rebuild.
DB is set to autogrow. Have looked at the report and the data file seems to
grow either by 100 or 250mb as and when needed.
Log file grows by 100mb jumps.
I'll have to check with the app's vendor to ensure doing this (ditch maint
plan and develop scripts) doesn't void our SLA.

Thanks.
Jay

>>I have a maintenance plan that has a step which rebuilds indexes.
>> While doing this the transaction log bloats to a huge size - 20gb.
[quoted text clipped - 7 lines]
>>
>> TIA
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.