Migrating to SQL 2005: log

Showing posts with label log. Show all posts

Friday, March 30, 2012

Mirroring error filling up event log

Hi,
I'm getting this error in the application event log roughly every second:
Database Mirroring login attempt failed with error: 'Connection handshake
failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed).
This is in the event log of the primary mirror sever. The setup is 2
identical sql servers (SP1), in the same domain, using the same domain
account for all services. I've confirmed the sql service accounts are local
admins on both boxes. So I'm not sure what 'login attempt' is failing. Can
anyone shed some light on this?
Thanks in advance.Any help here Microsoft?
"sqlboy2000" wrote:
> Hi,
> I'm getting this error in the application event log roughly every second:
> Database Mirroring login attempt failed with error: 'Connection handshake
> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed).
> This is in the event log of the primary mirror sever. The setup is 2
> identical sql servers (SP1), in the same domain, using the same domain
> account for all services. I've confirmed the sql service accounts are local
> admins on both boxes. So I'm not sure what 'login attempt' is failing. Can
> anyone shed some light on this?
> Thanks in advance.|||SQL Server Database Mirroring uses SQL Server Service Broker (SSB) under the
covers, and this message is logged by SSB as a result of an attempt to
connect by a mirroring session. You should either set up security correctly
or disable the mirroring session from the mentioned host (the IP is in the
error log, but apparently the IP was truncated in your post).
Please refer to http://msdn2.microsoft.com/en-us/library/ms179306.aspx; see
the Setting Up Database Mirroring topic.
--
Don Vilen
This posting is provided "AS IS" with no warranties, and confers no rights
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:D7781F35-88F5-4D87-836D-0FC37D6ECC4C@.microsoft.com...
> Any help here Microsoft?
> "sqlboy2000" wrote:
>> Hi,
>> I'm getting this error in the application event log roughly every second:
>> Database Mirroring login attempt failed with error: 'Connection handshake
>> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt
>> failed).
>> This is in the event log of the primary mirror sever. The setup is 2
>> identical sql servers (SP1), in the same domain, using the same domain
>> account for all services. I've confirmed the sql service accounts are
>> local
>> admins on both boxes. So I'm not sure what 'login attempt' is failing.
>> Can
>> anyone shed some light on this?
>> Thanks in advance.|||Security was setup correctly, this post wasn't accurate.
I'm going to post the actual problem now that I understand what's happening.
"Don Vilen [MSFT]" wrote:
> SQL Server Database Mirroring uses SQL Server Service Broker (SSB) under the
> covers, and this message is logged by SSB as a result of an attempt to
> connect by a mirroring session. You should either set up security correctly
> or disable the mirroring session from the mentioned host (the IP is in the
> error log, but apparently the IP was truncated in your post).
> Please refer to http://msdn2.microsoft.com/en-us/library/ms179306.aspx; see
> the Setting Up Database Mirroring topic.
> --
> Don Vilen
> This posting is provided "AS IS" with no warranties, and confers no rights
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:D7781F35-88F5-4D87-836D-0FC37D6ECC4C@.microsoft.com...
> > Any help here Microsoft?
> >
> > "sqlboy2000" wrote:
> >
> >> Hi,
> >> I'm getting this error in the application event log roughly every second:
> >>
> >> Database Mirroring login attempt failed with error: 'Connection handshake
> >> failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt
> >> failed).
> >>
> >> This is in the event log of the primary mirror sever. The setup is 2
> >> identical sql servers (SP1), in the same domain, using the same domain
> >> account for all services. I've confirmed the sql service accounts are
> >> local
> >> admins on both boxes. So I'm not sure what 'login attempt' is failing.
> >> Can
> >> anyone shed some light on this?
> >>
> >> Thanks in advance.
>

Mirroring confusion.

SQL2K5
SP1
I am testing out Synchronous Mirroring and have a question about failover.
It seems the only way I can failover to the Mirror box is to log onto the
Principal box and hit the Failover button? Isn't the whole point of
mirroring to be able to handle the Principal not being available? Is there
another way? To clarify, I have no witness and am attempting a manual
failover.
TIA, ChrisRHi Chris,
I believe the option you are looking for is as follows from bol:
1.. Connect to the mirror server.
2.. Issue the following statement:
ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
where <database_name> is the mirrored database.
The mirror server immediately transitions to principal server, and
mirroring is suspended.
Regards
Jamie
"ChrisR" <NotAChance@.ms.com> wrote in message
news:O9tTWmHyGHA.4232@.TK2MSFTNGP05.phx.gbl...
> SQL2K5
> SP1
> I am testing out Synchronous Mirroring and have a question about failover.
> It seems the only way I can failover to the Mirror box is to log onto the
> Principal box and hit the Failover button? Isn't the whole point of
> mirroring to be able to handle the Principal not being available? Is there
> another way? To clarify, I have no witness and am attempting a manual
> failover.
> TIA, ChrisR
>sql

Mirroring confusion.

I am testing out Synchronous Mirroring and have a question about failover. It seems the only way I can failover to the Mirror box is to log onto the Principal box and hit the Failover button? Is the whole point of mirroring to be able to handle the Principal not being available? Is there another way?

TIA, ChrisRIn the event of a failure, the "witness" does that automagically.

-PatP|||Yes, but I am using "no witness" mirroring.|||You have to have a witness server to automatic failover.
The witness can be a stripped down server running SQL Server Express. It does not need to be a powerful platform or have a licensed install.|||Yes, but I am attempting a manual failover.

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.

mirroring & backups

Hi,
I have a mirrored databased, which is in FULL recovery mode. I take log
backups every 1 hour and db backups every night. However, the log file keeps
increasing and due to mirroring I can’t shrink the log with shrinkfile. Is
this the recommended set-up for mirroring, am I trying to be too safe? Thanks.
Panos
You might want to try to backup more frequently. See if you have any open
transactions which might be causing log bloat.
DBCC Opentran
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:EDBAC239-0B9A-4A2F-BE3B-D5A2C60A03D0@.microsoft.com...
> Hi,
> I have a mirrored databased, which is in FULL recovery mode. I take log
> backups every 1 hour and db backups every night. However, the log file
> keeps
> increasing and due to mirroring I can't shrink the log with shrinkfile. Is
> this the recommended set-up for mirroring, am I trying to be too safe?
> Thanks.
> Panos
>
|||Hilary, are you talking about backing up the log or the database? or both?
There are no open transactions I've checked that before. I assume that when
the backup log happens there are no open transactions.
Do you know how many rows I should get when I do dbcc loginfo? I get 397, 6
are of status 2, 4 rows at the beginning and 2 close to the end. When I
backup manually it goes to 1 close to the end. but always have at least one
row with status 2 close to the end. Thanks.
Panos.
"Hilary Cotter" wrote:

> You might want to try to backup more frequently. See if you have any open
> transactions which might be causing log bloat.
> DBCC Opentran
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:EDBAC239-0B9A-4A2F-BE3B-D5A2C60A03D0@.microsoft.com...
>
>
|||OK that seems to work fine. Even though my transaction log has stayed the
same size it hasn't increased since I've increased the frequency of the Log
backups. This is what I wanted.
"Panos Stavroulis." wrote:
[vbcol=seagreen]
> Hilary, are you talking about backing up the log or the database? or both?
> There are no open transactions I've checked that before. I assume that when
> the backup log happens there are no open transactions.
> Do you know how many rows I should get when I do dbcc loginfo? I get 397, 6
> are of status 2, 4 rows at the beginning and 2 close to the end. When I
> backup manually it goes to 1 close to the end. but always have at least one
> row with status 2 close to the end. Thanks.
> Panos.
> "Hilary Cotter" wrote:
sql

Monday, March 26, 2012

Mirror Backup

Hello All!

I have a very simple question.
Im studying for 40-431 and just finished reading about Log Shipping.
It says in the book that when using Log Shipping you cannot run any other log backups because that would affect the LSNs. Can one do log backups with mirroring - principle and mirror?
Because mirroring uses log to keep the mirror up-to-date and synchs on LSN.

Thank youof course,you can backup your log in mirror.mirror and log shipping work in different way.

thanks.

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
>

Minimizing Transaction Log growth during Index Defragmentation

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.

Friday, March 30, 2012

Mirroring error filling up event log

Mirroring error filling up event log

Mirroring confusion.

Mirroring confusion.

Mirroring and Log File Growth

mirroring & backups

Monday, March 26, 2012

Mirror Backup

Wednesday, March 21, 2012

Minimizing Transaction Log growth during Index Defragmentation

Minimizing Transaction Log growth during Index Defragmentation

Minimizing Transaction Log growth during Index Defragmentation

Minimizing the entries to Transaction Log

Migrating to SQL 2005

Blog Archive

About Me