I have a database that is 135 GB in space. About 65 GB of that is actual
data. About 55 GB is indexes (there are 2091 indexes but some of them are
huge) and the rest is free space.
I was in the process of performing online index defragmentation on about 170
of the 2091 indexes (the most fragmented ones) but I'm running into an issue.
The transaction log is growing extremely quickly during this process. By the
time the defragmentation was complete, the transaction log grew to 267 GB
(essentially twice the size of the database file), which ate up practically
all the remaining space on the server.
Are there any settings for the online index defragmentation that can
minimize the growth of the transaction log while the defragmentation is
taking place?
Any help would be appreciated.No there are no settings. When you use DBCC INDEXDEFRAG or ALTER INDEX REORG
in 2005 theya re always fully logged operations. Defragging an index can log
several times the size of the index depending on how it is fragmented. I
assume you are doing this beacuse you can not take the index off line. If s
the best approach is to issure regular log backups during the defrag
process. Since it defrags at the page level you can backup at any time. But
you may wish to defrag then backup the log, then defrag the next and so on.
That will not reduce the amount of logging but it will keep the log file at
a reasonable size.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:86D8CCC5-B6B8-4A3E-91E3-F1AED4C51844@.microsoft.com...
>I have a database that is 135 GB in space. About 65 GB of that is actual
> data. About 55 GB is indexes (there are 2091 indexes but some of them are
> huge) and the rest is free space.
> I was in the process of performing online index defragmentation on about
> 170
> of the 2091 indexes (the most fragmented ones) but I'm running into an
> issue.
> The transaction log is growing extremely quickly during this process. By
> the
> time the defragmentation was complete, the transaction log grew to 267 GB
> (essentially twice the size of the database file), which ate up
> practically
> all the remaining space on the server.
> Are there any settings for the online index defragmentation that can
> minimize the growth of the transaction log while the defragmentation is
> taking place?
> Any help would be appreciated.|||Hi Andrew,
Thanks for the response. For reference I am using SQL 2000 Enterprise with
SP4 installed. Not sure if that changes any part of the response that you
mentioned.
Yes, I am not able to take the database offline because of business needs.
I did have one more concern if you can address this. Because of other
business requirements, this server is performing log shipping every hour to a
backup server at a remote location for DR purposes. I was performing this
defragmentation on a seperate server to determine the amount of time it would
take and any other issues (i.e like the size of the transaction log growing
quickly).
With log shipping occurring every hour (normally these transaction logs
don't exceed 1-3 GB every hour they are sent) and wanting to defragment the
database with the transaction logs growing at their current rate while
defragmentation is occurring, should there be any concerns with both
operations simultaneously occuring - other than possibly slowing down the
frequency of the log shipping while the defrag is occurring to make sure
there is enough time to copy the larger log files? Because of a business
requirement the log shipping can't be disabled.
"Andrew J. Kelly" wrote:
> No there are no settings. When you use DBCC INDEXDEFRAG or ALTER INDEX REORG
> in 2005 theya re always fully logged operations. Defragging an index can log
> several times the size of the index depending on how it is fragmented. I
> assume you are doing this beacuse you can not take the index off line. If s
> the best approach is to issure regular log backups during the defrag
> process. Since it defrags at the page level you can backup at any time. But
> you may wish to defrag then backup the log, then defrag the next and so on.
> That will not reduce the amount of logging but it will keep the log file at
> a reasonable size.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
> news:86D8CCC5-B6B8-4A3E-91E3-F1AED4C51844@.microsoft.com...
> >I have a database that is 135 GB in space. About 65 GB of that is actual
> > data. About 55 GB is indexes (there are 2091 indexes but some of them are
> > huge) and the rest is free space.
> >
> > I was in the process of performing online index defragmentation on about
> > 170
> > of the 2091 indexes (the most fragmented ones) but I'm running into an
> > issue.
> > The transaction log is growing extremely quickly during this process. By
> > the
> > time the defragmentation was complete, the transaction log grew to 267 GB
> > (essentially twice the size of the database file), which ate up
> > practically
> > all the remaining space on the server.
> >
> > Are there any settings for the online index defragmentation that can
> > minimize the growth of the transaction log while the defragmentation is
> > taking place?
> > Any help would be appreciated.
>|||Log shipping is not a problem other than the size of the logs and the time
it takes to backup, copy and restore. You may want to increase the rate at
which you do the log backups and hence shipping to keep them at a reasonable
size. I would recommend that even if you were not doing log shipping. A log
every hour means that you have the potential to loose up to an hours worth
of data. For a business that can't afford to take an index offline that
seems odd to be willing to loose an hours worth of data. Just the fact the
logs are 1-3GB means that you will have an extended recovery time in the
event of a fail over. I would also recommend you look at upgrading to 2005
so you can take advantage of On-Line index rebuilds as well.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:1A0C8ADF-938C-417D-A079-B3B46620ED3F@.microsoft.com...
> Hi Andrew,
> Thanks for the response. For reference I am using SQL 2000 Enterprise with
> SP4 installed. Not sure if that changes any part of the response that you
> mentioned.
> Yes, I am not able to take the database offline because of business needs.
> I did have one more concern if you can address this. Because of other
> business requirements, this server is performing log shipping every hour
> to a
> backup server at a remote location for DR purposes. I was performing this
> defragmentation on a seperate server to determine the amount of time it
> would
> take and any other issues (i.e like the size of the transaction log
> growing
> quickly).
> With log shipping occurring every hour (normally these transaction logs
> don't exceed 1-3 GB every hour they are sent) and wanting to defragment
> the
> database with the transaction logs growing at their current rate while
> defragmentation is occurring, should there be any concerns with both
> operations simultaneously occuring - other than possibly slowing down the
> frequency of the log shipping while the defrag is occurring to make sure
> there is enough time to copy the larger log files? Because of a business
> requirement the log shipping can't be disabled.
>
> "Andrew J. Kelly" wrote:
>> No there are no settings. When you use DBCC INDEXDEFRAG or ALTER INDEX
>> REORG
>> in 2005 theya re always fully logged operations. Defragging an index can
>> log
>> several times the size of the index depending on how it is fragmented. I
>> assume you are doing this beacuse you can not take the index off line. If
>> s
>> the best approach is to issure regular log backups during the defrag
>> process. Since it defrags at the page level you can backup at any time.
>> But
>> you may wish to defrag then backup the log, then defrag the next and so
>> on.
>> That will not reduce the amount of logging but it will keep the log file
>> at
>> a reasonable size.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
>> news:86D8CCC5-B6B8-4A3E-91E3-F1AED4C51844@.microsoft.com...
>> >I have a database that is 135 GB in space. About 65 GB of that is actual
>> > data. About 55 GB is indexes (there are 2091 indexes but some of them
>> > are
>> > huge) and the rest is free space.
>> >
>> > I was in the process of performing online index defragmentation on
>> > about
>> > 170
>> > of the 2091 indexes (the most fragmented ones) but I'm running into an
>> > issue.
>> > The transaction log is growing extremely quickly during this process.
>> > By
>> > the
>> > time the defragmentation was complete, the transaction log grew to 267
>> > GB
>> > (essentially twice the size of the database file), which ate up
>> > practically
>> > all the remaining space on the server.
>> >
>> > Are there any settings for the online index defragmentation that can
>> > minimize the growth of the transaction log while the defragmentation is
>> > taking place?
>> > Any help would be appreciated.
>>|||Hi Andrew,
Thanks again for the reply.
The database we are running is for the ClarifyCRM product. The version of
Clarify we are running doesn't support SQL 2005. But we are looking into
upgrading Clarify to handle SQL 2005 and some other requirements.
I would agree about the 1 hr frequency of the transaction log. This was all
setup before I came onboard but it is something that needs to be adjusted.
You mentioned taking an index offline. I didn't realize that I could take a
specific index offline. I was looking at this more at the database being
offline. What is the syntax of the T-SQL statement to do this?
"Andrew J. Kelly" wrote:
> Log shipping is not a problem other than the size of the logs and the time
> it takes to backup, copy and restore. You may want to increase the rate at
> which you do the log backups and hence shipping to keep them at a reasonable
> size. I would recommend that even if you were not doing log shipping. A log
> every hour means that you have the potential to loose up to an hours worth
> of data. For a business that can't afford to take an index offline that
> seems odd to be willing to loose an hours worth of data. Just the fact the
> logs are 1-3GB means that you will have an extended recovery time in the
> event of a fail over. I would also recommend you look at upgrading to 2005
> so you can take advantage of On-Line index rebuilds as well.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
> news:1A0C8ADF-938C-417D-A079-B3B46620ED3F@.microsoft.com...
> > Hi Andrew,
> >
> > Thanks for the response. For reference I am using SQL 2000 Enterprise with
> > SP4 installed. Not sure if that changes any part of the response that you
> > mentioned.
> >
> > Yes, I am not able to take the database offline because of business needs.
> >
> > I did have one more concern if you can address this. Because of other
> > business requirements, this server is performing log shipping every hour
> > to a
> > backup server at a remote location for DR purposes. I was performing this
> > defragmentation on a seperate server to determine the amount of time it
> > would
> > take and any other issues (i.e like the size of the transaction log
> > growing
> > quickly).
> >
> > With log shipping occurring every hour (normally these transaction logs
> > don't exceed 1-3 GB every hour they are sent) and wanting to defragment
> > the
> > database with the transaction logs growing at their current rate while
> > defragmentation is occurring, should there be any concerns with both
> > operations simultaneously occuring - other than possibly slowing down the
> > frequency of the log shipping while the defrag is occurring to make sure
> > there is enough time to copy the larger log files? Because of a business
> > requirement the log shipping can't be disabled.
> >
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> No there are no settings. When you use DBCC INDEXDEFRAG or ALTER INDEX
> >> REORG
> >> in 2005 theya re always fully logged operations. Defragging an index can
> >> log
> >> several times the size of the index depending on how it is fragmented. I
> >> assume you are doing this beacuse you can not take the index off line. If
> >> s
> >> the best approach is to issure regular log backups during the defrag
> >> process. Since it defrags at the page level you can backup at any time.
> >> But
> >> you may wish to defrag then backup the log, then defrag the next and so
> >> on.
> >> That will not reduce the amount of logging but it will keep the log file
> >> at
> >> a reasonable size.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
> >> news:86D8CCC5-B6B8-4A3E-91E3-F1AED4C51844@.microsoft.com...
> >> >I have a database that is 135 GB in space. About 65 GB of that is actual
> >> > data. About 55 GB is indexes (there are 2091 indexes but some of them
> >> > are
> >> > huge) and the rest is free space.
> >> >
> >> > I was in the process of performing online index defragmentation on
> >> > about
> >> > 170
> >> > of the 2091 indexes (the most fragmented ones) but I'm running into an
> >> > issue.
> >> > The transaction log is growing extremely quickly during this process.
> >> > By
> >> > the
> >> > time the defragmentation was complete, the transaction log grew to 267
> >> > GB
> >> > (essentially twice the size of the database file), which ate up
> >> > practically
> >> > all the remaining space on the server.
> >> >
> >> > Are there any settings for the online index defragmentation that can
> >> > minimize the growth of the transaction log while the defragmentation is
> >> > taking place?
> >> > Any help would be appreciated.
> >>
> >>
>|||Well in 2005 there is an option to take an index offline but not in 2000.
In this case I simply meant that in order to use DBREINDEX the index is
completely unavailable to other users for the duration of the rebuild.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:287F48D9-2D65-4D63-8E40-AA09AD0B5C04@.microsoft.com...
> Hi Andrew,
> Thanks again for the reply.
> The database we are running is for the ClarifyCRM product. The version of
> Clarify we are running doesn't support SQL 2005. But we are looking into
> upgrading Clarify to handle SQL 2005 and some other requirements.
> I would agree about the 1 hr frequency of the transaction log. This was
> all
> setup before I came onboard but it is something that needs to be adjusted.
> You mentioned taking an index offline. I didn't realize that I could take
> a
> specific index offline. I was looking at this more at the database being
> offline. What is the syntax of the T-SQL statement to do this?
>
> "Andrew J. Kelly" wrote:
>> Log shipping is not a problem other than the size of the logs and the
>> time
>> it takes to backup, copy and restore. You may want to increase the rate
>> at
>> which you do the log backups and hence shipping to keep them at a
>> reasonable
>> size. I would recommend that even if you were not doing log shipping. A
>> log
>> every hour means that you have the potential to loose up to an hours
>> worth
>> of data. For a business that can't afford to take an index offline that
>> seems odd to be willing to loose an hours worth of data. Just the fact
>> the
>> logs are 1-3GB means that you will have an extended recovery time in the
>> event of a fail over. I would also recommend you look at upgrading to
>> 2005
>> so you can take advantage of On-Line index rebuilds as well.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
>> news:1A0C8ADF-938C-417D-A079-B3B46620ED3F@.microsoft.com...
>> > Hi Andrew,
>> >
>> > Thanks for the response. For reference I am using SQL 2000 Enterprise
>> > with
>> > SP4 installed. Not sure if that changes any part of the response that
>> > you
>> > mentioned.
>> >
>> > Yes, I am not able to take the database offline because of business
>> > needs.
>> >
>> > I did have one more concern if you can address this. Because of other
>> > business requirements, this server is performing log shipping every
>> > hour
>> > to a
>> > backup server at a remote location for DR purposes. I was performing
>> > this
>> > defragmentation on a seperate server to determine the amount of time it
>> > would
>> > take and any other issues (i.e like the size of the transaction log
>> > growing
>> > quickly).
>> >
>> > With log shipping occurring every hour (normally these transaction logs
>> > don't exceed 1-3 GB every hour they are sent) and wanting to defragment
>> > the
>> > database with the transaction logs growing at their current rate while
>> > defragmentation is occurring, should there be any concerns with both
>> > operations simultaneously occuring - other than possibly slowing down
>> > the
>> > frequency of the log shipping while the defrag is occurring to make
>> > sure
>> > there is enough time to copy the larger log files? Because of a
>> > business
>> > requirement the log shipping can't be disabled.
>> >
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> No there are no settings. When you use DBCC INDEXDEFRAG or ALTER INDEX
>> >> REORG
>> >> in 2005 theya re always fully logged operations. Defragging an index
>> >> can
>> >> log
>> >> several times the size of the index depending on how it is fragmented.
>> >> I
>> >> assume you are doing this beacuse you can not take the index off line.
>> >> If
>> >> s
>> >> the best approach is to issure regular log backups during the defrag
>> >> process. Since it defrags at the page level you can backup at any
>> >> time.
>> >> But
>> >> you may wish to defrag then backup the log, then defrag the next and
>> >> so
>> >> on.
>> >> That will not reduce the amount of logging but it will keep the log
>> >> file
>> >> at
>> >> a reasonable size.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >>
>> >>
>> >> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
>> >> news:86D8CCC5-B6B8-4A3E-91E3-F1AED4C51844@.microsoft.com...
>> >> >I have a database that is 135 GB in space. About 65 GB of that is
>> >> >actual
>> >> > data. About 55 GB is indexes (there are 2091 indexes but some of
>> >> > them
>> >> > are
>> >> > huge) and the rest is free space.
>> >> >
>> >> > I was in the process of performing online index defragmentation on
>> >> > about
>> >> > 170
>> >> > of the 2091 indexes (the most fragmented ones) but I'm running into
>> >> > an
>> >> > issue.
>> >> > The transaction log is growing extremely quickly during this
>> >> > process.
>> >> > By
>> >> > the
>> >> > time the defragmentation was complete, the transaction log grew to
>> >> > 267
>> >> > GB
>> >> > (essentially twice the size of the database file), which ate up
>> >> > practically
>> >> > all the remaining space on the server.
>> >> >
>> >> > Are there any settings for the online index defragmentation that can
>> >> > minimize the growth of the transaction log while the defragmentation
>> >> > is
>> >> > taking place?
>> >> > Any help would be appreciated.
>> >>
>> >>
>>|||> Well in 2005 there is an option to take an index offline but not in 2000.
> In this case I simply meant that in order to use DBREINDEX the index is
> completely unavailable to other users for the duration of the rebuild.
If you take an index offline for reindexing, is that then a minimally logged
operation?
/Sjang
MCTS|||You can't do *anything* with an index which is offline (if you mean DISABLE). In fact, the index
pages are deallocated (the index doesn't exist physically) when you DISABLE an index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Henrik Davidsen" <none@.none.dk> wrote in message news:476acb7f$0$90275$14726298@.news.sunsite.dk...
>> Well in 2005 there is an option to take an index offline but not in 2000. In this case I simply
>> meant that in order to use DBREINDEX the index is completely unavailable to other users for the
>> duration of the rebuild.
> If you take an index offline for reindexing, is that then a minimally logged operation?
> /Sjang
> MCTS
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment