Mirroring and Log File Growth

We currently have a 10GB database that is functioning properly mirrored. The only issue we have is that the log files grow very quickly during the early morning hours when a large number of transactions hit our DB from scheduled jobs. We have transaction log backup and shrink job that runs every 3 hours to backup the log and shrink the logical file to 10GB. In most cases this will shrink the log back down to it's desired size of 10GB. However, on some mornings it takes more repetitions of the log backup/shrink job to return the log size back to "normal". During this these times when it does not shrink the DB effectively, I get a report of the job process by email that states "Cannot shrink log file 2 (e_Log) because all logical log files are in use." I run a DBCC OPENTRAN command, and there are no open transactions. Eventually, the log file will return to it's normal size of 10GB through the log file backup job. I have a few questions though.

1. Is this normal behavior during moderate database use?
2. Does mirroring have any effect on the SIZE of the log file?
3. Is it normal for the size of the data file to be smaller than the size of the log file on a heavily used database?
4. Does anyone have any suggestions to better maintain the log file size?

Thank you,
Greg

Mirroring won't affect the log, unless the Principal can't connect to the mirror in which case transactions will queue in the log and may cause issues.

Why are you shrinking your log? It should be as big as it needs to be, i.e. large enough to hold the transactions from your scheduled jobs. Having it auto-grow then re-shinking it is a waste of time and resources (it will slow down your scheduled jobs while the log grows).

The reason you can't shrink it sometimes is because the log actually consists of Virtual Log Files (VLFs). If the active one of these is near the end of the log file, it won't be shrinkable until it moves back to the beginning of the log file due to transactions having happened.

Normally the log file is smaller than the data file, but there may be exceptions. The bottom line is, the log needs to be as big as is required by the size and volume of transactions using the database.

Ensure you have adequate log backups of a suitable frequency all the time, and especially during your batch processing.

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...
>
|||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:

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
>

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
>