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 transaction. Show all posts
Showing posts with label transaction. 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 the entries to Transaction Log
Hi All,
I have a DB of transaction log grown to 19GB. I have some questions.
1. Is there any way that we can minimize the entries to transaction log.
2. If i use Transactions in my stored procedure will it be usefull to automatically remove the transaction entries in transaction log once after the commit transaction is given.
3. Is there a way to reduce my current transaction log and not to grown in such a manner futher.
Thanks in Advance
Sri1. No. Every DML and DDL operation is logged.
2. Again, everthing is logged, whether you use explicit transactions (BEGIN TRAN... COMMIT TRAN) or implicit transactions.
3. Yes. You can back up the database on scheduled intervals. This will not shrink the physical file, but it will mark "old" backed up virtual log files as reusable, which allows SQL Server to reuse them insted of expanding the physical log file.
You could also set the recovery model to "Simple". This will allow SQL Server to reuse virtual log files once all transactions are committed/rolled back and the data in the data pages referred to by all transactions are written to disk.
Take a look at "transaction logs [SQL Server], architecture" to better understand what transaction logs are and how SQL Server uses them.
I have just given you a very simplified summary on how it works.
I have a DB of transaction log grown to 19GB. I have some questions.
1. Is there any way that we can minimize the entries to transaction log.
2. If i use Transactions in my stored procedure will it be usefull to automatically remove the transaction entries in transaction log once after the commit transaction is given.
3. Is there a way to reduce my current transaction log and not to grown in such a manner futher.
Thanks in Advance
Sri1. No. Every DML and DDL operation is logged.
2. Again, everthing is logged, whether you use explicit transactions (BEGIN TRAN... COMMIT TRAN) or implicit transactions.
3. Yes. You can back up the database on scheduled intervals. This will not shrink the physical file, but it will mark "old" backed up virtual log files as reusable, which allows SQL Server to reuse them insted of expanding the physical log file.
You could also set the recovery model to "Simple". This will allow SQL Server to reuse virtual log files once all transactions are committed/rolled back and the data in the data pages referred to by all transactions are written to disk.
Take a look at "transaction logs [SQL Server], architecture" to better understand what transaction logs are and how SQL Server uses them.
I have just given you a very simplified summary on how it works.
minimize transaction logging
We use SQL PE 2000 in our lab to store sampled data (lots of data collected at high sampling rate) in real-time. i know this may be an uncommon use of SQL server. For this particular application, performance is much more important than data recoverability. In other words, once-in-a-while data loss is acceptable. Actually we have never used the transaction log for recovery since we started using the SQL PE in this way a few years ago. Certain transaction logging such as bulk deletion is particularly wasteful of the resource. As my understanding from reading the online book and some threads on this forum, the best thing we can do is using bulk-logged recovery model. I am not sure if that helps us at all because it seems that bulk deletion (i.e. DELETE FROM MyTable WHERE ..., may affect hundreds of thousands of records) is not on the list of minimally logged transactions.
I am wondering if anyone could share some good advice.
Thanks in advance!
Correct Deletes (or any normal DML command) are fully logged transactions. There is no way to disable this behavor in Microsoft SQL Server.
In order to reduce logging when deleting large amounts of data do the deletes in smaller batches. Something like this should work.
Code Snippet
set rowcount 1000
select count(*) from sysdatabases --This is just to get the loop started
while @.@.ROWCOUNT <> 0
BEGIN
delete from table
where something = somethingelse
END
This will do small deletes of 1000 records at a time which while still logged will be done in small batches so with the log in simple recovery mode shouldn't get very large.
Doing deletes in this method will take longer than a single large delete, but you don't have to worry about a large transaction log.
|||Denny, thanks for clarifying this.
I am wondering if reducing the transactio log file size helps the performance. My understanding is that the same amount of logging still takes place. We are not concerned about storage space that we have plenty. We truncate the log file periodically.
|||Reducing the log file size shouldn't effect performance (unless you shrink the file to small and SQL has to grow the file often). It is recommended to pre-allocate the file size so that SQL Server doesn't ever have to grow the file.
Subscribe to:
Posts (Atom)