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...
>
|||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...[vbcol=seagreen]
> 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:
|||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...
>
|||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...[vbcol=seagreen]
> 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:
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Wednesday, March 21, 2012
Minimizing Transaction Log growth during Index Defragmentation
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
>
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
>
Minimizing Transaction Log growth during Index Defragmentation
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 woul
d
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 REO
RG
> in 2005 theya re always fully logged operations. Defragging an index can l
og
> 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. Bu
t
> 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 a
t
> 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...
>|||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...[vbcol=seagreen]
> 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:
>|||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 reasonab
le
> size. I would recommend that even if you were not doing log shipping. A lo
g
> 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...
>|||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...[vbcol=seagreen]
> 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:
>|||> 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.sunsit
e.dk...
> If you take an index offline for reindexing, is that then a minimally logg
ed operation?
> /Sjang
> MCTS
>
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 woul
d
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 REO
RG
> in 2005 theya re always fully logged operations. Defragging an index can l
og
> 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. Bu
t
> 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 a
t
> 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...
>|||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...[vbcol=seagreen]
> 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:
>|||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 reasonab
le
> size. I would recommend that even if you were not doing log shipping. A lo
g
> 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...
>|||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...[vbcol=seagreen]
> 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:
>|||> 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.sunsit
e.dk...
> If you take an index offline for reindexing, is that then a minimally logg
ed operation?
> /Sjang
> MCTS
>
Minimizing Fragmentation of table having Non-clustered Index.
Hi All,
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.
> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the enti
re
> index using DBCC DBREINDEX. These apply to both clustered and non-cluster
ed
> indexes. See the Books Online for usage information.
>
> You can avoid page splits between between index reorgs by specifying a low
er
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate fr
ee
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
>
>
>
>
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.
> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the enti
re
> index using DBCC DBREINDEX. These apply to both clustered and non-cluster
ed
> indexes. See the Books Online for usage information.
>
> You can avoid page splits between between index reorgs by specifying a low
er
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate fr
ee
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
>
>
>
>
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:
Minimizing Fragmentation of table having Non-clustered Index.
Hi All,
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.
Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>
|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.
> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegr oups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>
|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
> index using DBCC DBREINDEX. These apply to both clustered and non-clustered
> indexes. See the Books Online for usage information.
>
> You can avoid page splits between between index reorgs by specifying a lower
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate free
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegr oups.com...
>
>
>
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.
|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.
Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>
|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.
> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegr oups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>
|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
> index using DBCC DBREINDEX. These apply to both clustered and non-clustered
> indexes. See the Books Online for usage information.
>
> You can avoid page splits between between index reorgs by specifying a lower
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate free
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegr oups.com...
>
>
>
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.
|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:
Minimizing Fragmentation of table having Non-clustered Index.
Hi All,
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.
> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > i am working on SQL Server 2000 EE. I want to know that, is there any
> > way to rebuild Non clustered index.
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
> index using DBCC DBREINDEX. These apply to both clustered and non-clustered
> indexes. See the Books Online for usage information.
> > How can we minimize fragmentation
> > of table having non clustered index.
> You can avoid page splits between between index reorgs by specifying a lower
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate free
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
>
> > Hi All,
> > i am working on SQL Server 2000 EE. I want to know that, is there any
> > way to rebuild Non clustered index. How can we minimize fragmentation
> > of table having non clustered index. can we defrag non clustred index.
> > Thanks & Regards,
> > Sajid C.- Hide quoted text -
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:
>> Hi All,
>> i am working on SQL Server 2000 EE. I want to know that, is there any
>> way to rebuild Non clustered index. How can we minimize fragmentation
>> of table having non clustered index. can we defrag non clustred index.
>>
>> Thanks & Regards,
>> Sajid C.
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.
> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > i am working on SQL Server 2000 EE. I want to know that, is there any
> > way to rebuild Non clustered index.
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
> index using DBCC DBREINDEX. These apply to both clustered and non-clustered
> indexes. See the Books Online for usage information.
> > How can we minimize fragmentation
> > of table having non clustered index.
> You can avoid page splits between between index reorgs by specifying a lower
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate free
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
>
> > Hi All,
> > i am working on SQL Server 2000 EE. I want to know that, is there any
> > way to rebuild Non clustered index. How can we minimize fragmentation
> > of table having non clustered index. can we defrag non clustred index.
> > Thanks & Regards,
> > Sajid C.- Hide quoted text -
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:
>> Hi All,
>> i am working on SQL Server 2000 EE. I want to know that, is there any
>> way to rebuild Non clustered index. How can we minimize fragmentation
>> of table having non clustered index. can we defrag non clustred index.
>>
>> Thanks & Regards,
>> Sajid C.
Subscribe to:
Posts (Atom)