Holy hordes of transactional data Batman

I have a LOG file of 113Gig with only 29 Gig of free space
available on the drive, I have no media that will hold
this as a backup.
I ran the "Backup Log" "with_truncate" and "no_truncate"
but nothing seems to be making this thing disappear.
What is my next step to dissolving this beast?
Truncating the log only marks space within the log as reusable. It does not
affect the physical size of the file.
Please read about DBCC SHRINKFILE to actually shrink the log file.
Also, always tell us what version you are using, and there are some specific
guidelines for shrinking that we can give you depending on the version.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
|||To shrink it you should use DBCC SHRINKFILE().
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
|||http://www.aspfaq.com/2471
http://www.aspfaq.com/
(Reverse address to reply.)
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
|||test
"Kalen Delaney" wrote:

> Truncating the log only marks space within the log as reusable. It does not
> affect the physical size of the file.
> Please read about DBCC SHRINKFILE to actually shrink the log file.
> Also, always tell us what version you are using, and there are some specific
> guidelines for shrinking that we can give you depending on the version.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "VidRo" <vid_ro@.myalias.postalias.com> wrote in message
> news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>
>
|||DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
from reoccuring, set up maintenance plan and check appropriate selctions
(Optimization tab).
"VidRo" wrote:

> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>
|||DBCC shrinkdatabase or DBCC shrinkfile will do it; however if you want to
prevent it from reoccuring, set up maintenance plan properly (Optimization
tab).
"VidRo" wrote:

> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>
|||Hi Adam
I suggested DBCC SHRINKFILE because you can control the files to be
affected. DBCC SHRINKDATABASE will shrink all the files. Shrinking and data
file is a very different operation than shrinking a log file, and it is NOT
something to be done on a regular basis. The overhead is far too high. I
would not recommended putting a shrink of the whole database in any kind of
regular maintenance plan.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Locus Adam" <Locus Adam@.discussions.microsoft.com> wrote in message
news:5132814B-3EEE-4AC7-A8DB-4588ADAE5D87@.microsoft.com...[vbcol=seagreen]
> DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
> from reoccuring, set up maintenance plan and check appropriate selctions
> (Optimization tab).
> "VidRo" wrote:

Holy hordes of transactional data Batman

I have a LOG file of 113Gig with only 29 Gig of free space
available on the drive, I have no media that will hold
this as a backup.
I ran the "Backup Log" "with_truncate" and "no_truncate"
but nothing seems to be making this thing disappear.
What is my next step to dissolving this beast?Truncating the log only marks space within the log as reusable. It does not
affect the physical size of the file.
Please read about DBCC SHRINKFILE to actually shrink the log file.
Also, always tell us what version you are using, and there are some specific
guidelines for shrinking that we can give you depending on the version.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?|||To shrink it you should use DBCC SHRINKFILE().
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?|||http://www.aspfaq.com/2471
http://www.aspfaq.com/
(Reverse address to reply.)
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?|||test
"Kalen Delaney" wrote:

> Truncating the log only marks space within the log as reusable. It does no
t
> affect the physical size of the file.
> Please read about DBCC SHRINKFILE to actually shrink the log file.
> Also, always tell us what version you are using, and there are some specif
ic
> guidelines for shrinking that we can give you depending on the version.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "VidRo" <vid_ro@.myalias.postalias.com> wrote in message
> news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>
>|||DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
from reoccuring, set up maintenance plan and check appropriate selctions
(Optimization tab).
"VidRo" wrote:

> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>|||DBCC shrinkdatabase or DBCC shrinkfile will do it; however if you want to
prevent it from reoccuring, set up maintenance plan properly (Optimization
tab).
"VidRo" wrote:

> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>|||Hi Adam
I suggested DBCC SHRINKFILE because you can control the files to be
affected. DBCC SHRINKDATABASE will shrink all the files. Shrinking and data
file is a very different operation than shrinking a log file, and it is NOT
something to be done on a regular basis. The overhead is far too high. I
would not recommended putting a shrink of the whole database in any kind of
regular maintenance plan.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Locus Adam" <Locus Adam@.discussions.microsoft.com> wrote in message
news:5132814B-3EEE-4AC7-A8DB-4588ADAE5D87@.microsoft.com...[vbcol=seagreen]
> DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
> from reoccuring, set up maintenance plan and check appropriate selctions
> (Optimization tab).
> "VidRo" wrote:
>sql

Hiya : Full msdb Log message

Could anyone tell me how to check the size of the current msdb and tempdb log files and how do I deal with the following alerts :
Full msdb log Error 9002
Full tempdb Error 9002
Mnay thanks
ZahedFist off, since this thread is much more technical than it is "Hello, world" in nature, I'm going to move it from the New Users and Introductions to the MS-SQL forum.

Next, there are multiple ways to check the size of the databases. Assuming that you are running SQL 2000, probably the easiest way is to open SQL Enterprise Manager, in the navigation pane (leftmost on most systems) you'll find a tree control... Pick your server, then databases, then the database that interests you (such as msdb or tempdb). Right click, and select Properites from the menu. You'll see tabs for data and log files.

If you need to "clean house" because of a log file being full, close the properties dialog, right click again, select All Tasks, then Shrink Database.

-PatP|||Refer to BOoks online for this error, that has complete information to recover it. I would also suggest to check what kind of operation is making this alerts raised and add more disks to accomodate the resource intensive operations.

Hight paging activities on the DB server

Hi,
I am experiencing suddent high paging activities on my
database server on regular bases. I setup an performance
monitor Counter log to monitor the db performance, which
showed that the "Memery-Page/sec" counter jumped from an
average of 50 to 2000 or even more at aound the same time
each hour and "PhysicalDisk-Avg. Disk Queue Length" jumped
from 0.3 to 30 or 40. The high paging lasted for 1 or 2
minutes, then the performance came back to normal. I have
run the profiler to trace all the SQL Batches sent to the
server and I couldn't see any abnormal SQL running which
might have caused the paging. More strange is the
counter "Buffer Cache Hit ratio" had remained 99% all the
time, which indicated that the high paging activities were
not caused by database read and I had very little write
activities on the database when it happened.
The server is dedicated to SQL Server. The server
enviroment I am using is:-
Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition
Windows NT 5.0 (Build 2195: Service Pack 4).
Have anyone experienced the same problem or known what
might have caused the problem.
Any suggestion is very much appreciated.
Cheers
Xiaobing
Try tracing those counters on a per-process basis. It'll point you toward
where the problem lies.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"xy" <xyu@.redfig.com> wrote in message
news:693901c4758f$069f8ff0$a601280a@.phx.gbl...
Hi,
I am experiencing suddent high paging activities on my
database server on regular bases. I setup an performance
monitor Counter log to monitor the db performance, which
showed that the "Memery-Page/sec" counter jumped from an
average of 50 to 2000 or even more at aound the same time
each hour and "PhysicalDisk-Avg. Disk Queue Length" jumped
from 0.3 to 30 or 40. The high paging lasted for 1 or 2
minutes, then the performance came back to normal. I have
run the profiler to trace all the SQL Batches sent to the
server and I couldn't see any abnormal SQL running which
might have caused the paging. More strange is the
counter "Buffer Cache Hit ratio" had remained 99% all the
time, which indicated that the high paging activities were
not caused by database read and I had very little write
activities on the database when it happened.
The server is dedicated to SQL Server. The server
enviroment I am using is:-
Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition
Windows NT 5.0 (Build 2195: Service Pack 4).
Have anyone experienced the same problem or known what
might have caused the problem.
Any suggestion is very much appreciated.
Cheers
Xiaobing

Hight paging activities on the DB server

Hi,
I am experiencing suddent high paging activities on my
database server on regular bases. I setup an performance
monitor Counter log to monitor the db performance, which
showed that the "Memery-Page/sec" counter jumped from an
average of 50 to 2000 or even more at aound the same time
each hour and "PhysicalDisk-Avg. Disk Queue Length" jumped
from 0.3 to 30 or 40. The high paging lasted for 1 or 2
minutes, then the performance came back to normal. I have
run the profiler to trace all the SQL Batches sent to the
server and I couldn't see any abnormal SQL running which
might have caused the paging. More strange is the
counter "Buffer Cache Hit ratio" had remained 99% all the
time, which indicated that the high paging activities were
not caused by database read and I had very little write
activities on the database when it happened.
The server is dedicated to SQL Server. The server
enviroment I am using is:-
Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition
Windows NT 5.0 (Build 2195: Service Pack 4).
Have anyone experienced the same problem or known what
might have caused the problem.
Any suggestion is very much appreciated.
Cheers
XiaobingTry tracing those counters on a per-process basis. It'll point you toward
where the problem lies.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"xy" <xyu@.redfig.com> wrote in message
news:693901c4758f$069f8ff0$a601280a@.phx.gbl...
Hi,
I am experiencing suddent high paging activities on my
database server on regular bases. I setup an performance
monitor Counter log to monitor the db performance, which
showed that the "Memery-Page/sec" counter jumped from an
average of 50 to 2000 or even more at aound the same time
each hour and "PhysicalDisk-Avg. Disk Queue Length" jumped
from 0.3 to 30 or 40. The high paging lasted for 1 or 2
minutes, then the performance came back to normal. I have
run the profiler to trace all the SQL Batches sent to the
server and I couldn't see any abnormal SQL running which
might have caused the paging. More strange is the
counter "Buffer Cache Hit ratio" had remained 99% all the
time, which indicated that the high paging activities were
not caused by database read and I had very little write
activities on the database when it happened.
The server is dedicated to SQL Server. The server
enviroment I am using is:-
Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition
Windows NT 5.0 (Build 2195: Service Pack 4).
Have anyone experienced the same problem or known what
might have caused the problem.
Any suggestion is very much appreciated.
Cheers
Xiaobing

Hight paging activities on the DB server

Hi,
I am experiencing suddent high paging activities on my
database server on regular bases. I setup an performance
monitor Counter log to monitor the db performance, which
showed that the "Memery-Page/sec" counter jumped from an
average of 50 to 2000 or even more at aound the same time
each hour and "PhysicalDisk-Avg. Disk Queue Length" jumped
from 0.3 to 30 or 40. The high paging lasted for 1 or 2
minutes, then the performance came back to normal. I have
run the profiler to trace all the SQL Batches sent to the
server and I couldn't see any abnormal SQL running which
might have caused the paging. More strange is the
counter "Buffer Cache Hit ratio" had remained 99% all the
time, which indicated that the high paging activities were
not caused by database read and I had very little write
activities on the database when it happened.
The server is dedicated to SQL Server. The server
enviroment I am using is:-
Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition
Windows NT 5.0 (Build 2195: Service Pack 4).
Have anyone experienced the same problem or known what
might have caused the problem.
Any suggestion is very much appreciated.
Cheers
XiaobingTry tracing those counters on a per-process basis. It'll point you toward
where the problem lies.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"xy" <xyu@.redfig.com> wrote in message
news:693901c4758f$069f8ff0$a601280a@.phx.gbl...
Hi,
I am experiencing suddent high paging activities on my
database server on regular bases. I setup an performance
monitor Counter log to monitor the db performance, which
showed that the "Memery-Page/sec" counter jumped from an
average of 50 to 2000 or even more at aound the same time
each hour and "PhysicalDisk-Avg. Disk Queue Length" jumped
from 0.3 to 30 or 40. The high paging lasted for 1 or 2
minutes, then the performance came back to normal. I have
run the profiler to trace all the SQL Batches sent to the
server and I couldn't see any abnormal SQL running which
might have caused the paging. More strange is the
counter "Buffer Cache Hit ratio" had remained 99% all the
time, which indicated that the high paging activities were
not caused by database read and I had very little write
activities on the database when it happened.
The server is dedicated to SQL Server. The server
enviroment I am using is:-
Microsoft SQL Server 2000 - 8.00.194 Enterprise Edition
Windows NT 5.0 (Build 2195: Service Pack 4).
Have anyone experienced the same problem or known what
might have caused the problem.
Any suggestion is very much appreciated.
Cheers
Xiaobingsql

highly appreciated !

You are the DBA for a busy order entry database. You want to provide a
method for automatically backing up the transaction log for you database in
case it becomes more than 90 percent full prior to the next regularly
scheduled transaction log backup. What is the best way to accomplish this?
1.. Create an alert in Performance Monitor to call an OSQL script to
backup log the transaction log when it becomes more than 90% full.
2.. Create al alert in SQL Enterprise Manager that will call a job to
backup the transaction log when it becomes more than 90% full.
3.. Create an alert in SQL Enterpise Manager that will call an OSQL script
to backup the transaction log when it becomes more than 90% full.
4.. Create an alert in Performance Monitor that will call SQLALRTR.EXE to
trigger a SQL Server alert to backup the transaction log when it becomes
more than 90% full.
5.. Create a schedule transaction log backup for every 10 minutes.
6.. You cannot do this.
"Roy" schrieb:

> You are the DBA for a busy order entry database. You want to provide a
> method for automatically backing up the transaction log for you database in
> case it becomes more than 90 percent full prior to the next regularly
> scheduled transaction log backup. What is the best way to accomplish this?
> 1.. Create an alert in Performance Monitor to call an OSQL script to
> backup log the transaction log when it becomes more than 90% full.
> 2.. Create al alert in SQL Enterprise Manager that will call a job to
> backup the transaction log when it becomes more than 90% full.
> 3.. Create an alert in SQL Enterpise Manager that will call an OSQL script
> to backup the transaction log when it becomes more than 90% full.
> 4.. Create an alert in Performance Monitor that will call SQLALRTR.EXE to
> trigger a SQL Server alert to backup the transaction log when it becomes
> more than 90% full.
> 5.. Create a schedule transaction log backup for every 10 minutes.
> 6.. You cannot do this.
6 is out of question!!!
2 sounds reasonable. Attention: That will not be an event alert (there is no
event / error code for 'log 90% full') but a performance condition alert!

High rate of log file growth

We added several indexs to a DB.
We have not changed our number of transactions.
The rate of log file growth has moved from about 320MB per hour before the
indexes to 2GB per hour after the indexes were added.
How would we go about finding the operations that are causing the increase
in log file growth?
OwenC"OwenC" <OwenC@.community.nospam> wrote in message
news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> We added several indexs to a DB.
> We have not changed our number of transactions.
> The rate of log file growth has moved from about 320MB per hour before the
> indexes to 2GB per hour after the indexes were added.
> How would we go about finding the operations that are causing the increase
> in log file growth?
Are you doing and DBCC dbreindex, etc on your indexes?
Are your indexes finished building, or still building?
> OwenC
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi, OwenC,
I understand that the transaction log file grew fast after the indexes were
added.
If I have misunderstood, please let me know.
This might be normal behavior if your index operations are large-scale,
since large-scale index operations will cause heavy data loads that can
cause the transaction log to fill quickly.
Please refer to the recommendations of this article:
Transaction Log Disk Space for Index Operations
http://msdn2.microsoft.com/en-us/library/ms184246.aspx
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||We are not doing a DBCC REINDEX. Once we applied the indexes we left them
alone.
The indexes have finished building.
OwenC
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Olqu6J%23eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> "OwenC" <OwenC@.community.nospam> wrote in message
> news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
>> We added several indexs to a DB.
>> We have not changed our number of transactions.
>> The rate of log file growth has moved from about 320MB per hour before
>> the indexes to 2GB per hour after the indexes were added.
>> How would we go about finding the operations that are causing the
>> increase in log file growth?
> Are you doing and DBCC dbreindex, etc on your indexes?
> Are your indexes finished building, or still building?
>
>> OwenC
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||Hi, OwenC,
I think that the data volume of your database is huge, right?
If you frequently perform database backup and the disk space is limited, I
recommend that set your database recovery model to simple to reduce logging
increasing speed;
If you want to leave database recovery model to FULL so that you can
restore your database to any time point that you want, I recommend that you
start a job to truncate your transaction log every day.
For more information, you may refer to "Recovery models" and "BACKUP LOG"
in SQL Server Books Online.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:APJ0YWMfHHA.6068@.TK2MSFTNGHUB02.phx.gbl...
> Hi, OwenC,
> I think that the data volume of your database is huge, right?
> If you frequently perform database backup and the disk space is limited, I
> recommend that set your database recovery model to simple to reduce
> logging
> increasing speed;
> If you want to leave database recovery model to FULL so that you can
> restore your database to any time point that you want, I recommend that
> you
> start a job to truncate your transaction log every day.
Umm... That's not great advice. That would invalidate his transaction log
backup chain.
And once truncated, he'd lose the ability to restore your database to any
time point you want.
> For more information, you may refer to "Recovery models" and "BACKUP LOG"
> in SQL Server Books Online.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>
>
>
>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi, Greg,
Thanks for your pointing out.
Yes, if the log file is truncated, he would lose the ability to restore his
database to any time point he want; however if their disk space is limited,
the truncate operation may be neccessary no matter in any recovery mode.
Simple recovery mode can produce less transaction logs than FULL recovery
mode. The logs increasing speed is slow, and this may be able to suit his
needs.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||I need to run the FULL recovery mode as this is the DB for an ERP
application. I can't afford to lose even any transactions!!!
We actually do transaction log backups and truncate them every 2 hours.
We use Backup Exec to do this.
It seems from further investigation that what caused the increased growth is
that the indexes that were added made some DB operations 20 times faster.
This caused some batch processes to run faster than they had been able to.
There was a backlog of these batches and so in clearing the backlog it
caused the growth rate of the log file to increase.
OwenC
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:YvPadJNfHHA.4368@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Greg,
> Thanks for your pointing out.
> Yes, if the log file is truncated, he would lose the ability to restore
> his
> database to any time point he want; however if their disk space is
> limited,
> the truncate operation may be neccessary no matter in any recovery mode.
> Simple recovery mode can produce less transaction logs than FULL recovery
> mode. The logs increasing speed is slow, and this may be able to suit his
> needs.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>
>
>
>|||Hi, OwenC,
Thanks for your updating and response.
I am glad to hear that you found the real cause in your situation. What is
the kind of the backlog as you mentioned? Have you found a way to resolve
it now?
Please feel free to let me know if you need any assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi, Owen
I am interested in this issue. Would you mind letting me know the result of
this issue? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

High rate of log file growth

We added several indexs to a DB.
We have not changed our number of transactions.
The rate of log file growth has moved from about 320MB per hour before the
indexes to 2GB per hour after the indexes were added.
How would we go about finding the operations that are causing the increase
in log file growth?
OwenC"OwenC" <OwenC@.community.nospam> wrote in message
news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> We added several indexs to a DB.
> We have not changed our number of transactions.
> The rate of log file growth has moved from about 320MB per hour before the
> indexes to 2GB per hour after the indexes were added.
> How would we go about finding the operations that are causing the increase
> in log file growth?
Are you doing and DBCC dbreindex, etc on your indexes?
Are your indexes finished building, or still building?

> OwenC
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi, OwenC,
I understand that the transaction log file grew fast after the indexes were
added.
If I have misunderstood, please let me know.
This might be normal behavior if your index operations are large-scale,
since large-scale index operations will cause heavy data loads that can
cause the transaction log to fill quickly.
Please refer to the recommendations of this article:
Transaction Log Disk Space for Index Operations
http://msdn2.microsoft.com/en-us/library/ms184246.aspx
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||We are not doing a DBCC REINDEX. Once we applied the indexes we left them
alone.
The indexes have finished building.
OwenC
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Olqu6J%23eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> "OwenC" <OwenC@.community.nospam> wrote in message
> news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> Are you doing and DBCC dbreindex, etc on your indexes?
> Are your indexes finished building, or still building?
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||Hi, OwenC,
I think that the data volume of your database is huge, right?
If you frequently perform database backup and the disk space is limited, I
recommend that set your database recovery model to simple to reduce logging
increasing speed;
If you want to leave database recovery model to FULL so that you can
restore your database to any time point that you want, I recommend that you
start a job to truncate your transaction log every day.
For more information, you may refer to "Recovery models" and "BACKUP LOG"
in SQL Server Books Online.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:APJ0YWMfHHA.6068@.TK2MSFTNGHUB02.phx.gbl...
> Hi, OwenC,
> I think that the data volume of your database is huge, right?
> If you frequently perform database backup and the disk space is limited, I
> recommend that set your database recovery model to simple to reduce
> logging
> increasing speed;
> If you want to leave database recovery model to FULL so that you can
> restore your database to any time point that you want, I recommend that
> you
> start a job to truncate your transaction log every day.
Umm... That's not great advice. That would invalidate his transaction log
backup chain.
And once truncated, he'd lose the ability to restore your database to any
time point you want.

> For more information, you may refer to "Recovery models" and "BACKUP LOG"
> in SQL Server Books Online.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>
>
>
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi, Greg,
Thanks for your pointing out.
Yes, if the log file is truncated, he would lose the ability to restore his
database to any time point he want; however if their disk space is limited,
the truncate operation may be neccessary no matter in any recovery mode.
Simple recovery mode can produce less transaction logs than FULL recovery
mode. The logs increasing speed is slow, and this may be able to suit his
needs.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Lines: 69
In-Reply-To: <YvPadJNfHHA.4368@.TK2MSFTNGHUB02.phx.gbl>
MIME-Version: 1.0
Content-Type: text/plain;
format=flowed;
charset="iso-8859-1";
reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
NNTP-Posting-Host: port0005-agk-adsl.cwjamaica.com 72.27.124.5
Xref: leafnode.mcse.ms microsoft.public.sqlserver.server:32327
I need to run the FULL recovery mode as this is the DB for an ERP
application. I can't afford to lose even any transactions!!!
We actually do transaction log backups and truncate them every 2 hours.
We use Backup Exec to do this.
It seems from further investigation that what caused the increased growth is
that the indexes that were added made some DB operations 20 times faster.
This caused some batch processes to run faster than they had been able to.
There was a backlog of these batches and so in clearing the backlog it
caused the growth rate of the log file to increase.
OwenC
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:YvPadJNfHHA.4368@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Greg,
> Thanks for your pointing out.
> Yes, if the log file is truncated, he would lose the ability to restore
> his
> database to any time point he want; however if their disk space is
> limited,
> the truncate operation may be neccessary no matter in any recovery mode.
> Simple recovery mode can produce less transaction logs than FULL recovery
> mode. The logs increasing speed is slow, and this may be able to suit his
> needs.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>
>
>
>|||Hi, OwenC,
Thanks for your updating and response.
I am glad to hear that you found the real cause in your situation. What is
the kind of the backlog as you mentioned? Have you found a way to resolve
it now?
Please feel free to let me know if you need any assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi, Owen
I am interested in this issue. Would you mind letting me know the result of
this issue? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

High rate of log file growth

We added several indexs to a DB.
We have not changed our number of transactions.
The rate of log file growth has moved from about 320MB per hour before the
indexes to 2GB per hour after the indexes were added.
How would we go about finding the operations that are causing the increase
in log file growth?
OwenC
"OwenC" <OwenC@.community.nospam> wrote in message
news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> We added several indexs to a DB.
> We have not changed our number of transactions.
> The rate of log file growth has moved from about 320MB per hour before the
> indexes to 2GB per hour after the indexes were added.
> How would we go about finding the operations that are causing the increase
> in log file growth?
Are you doing and DBCC dbreindex, etc on your indexes?
Are your indexes finished building, or still building?

> OwenC
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||We are not doing a DBCC REINDEX. Once we applied the indexes we left them
alone.
The indexes have finished building.
OwenC
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Olqu6J%23eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> "OwenC" <OwenC@.community.nospam> wrote in message
> news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> Are you doing and DBCC dbreindex, etc on your indexes?
> Are your indexes finished building, or still building?
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
|||Hi, OwenC,
I think that the data volume of your database is huge, right?
If you frequently perform database backup and the disk space is limited, I
recommend that set your database recovery model to simple to reduce logging
increasing speed;
If you want to leave database recovery model to FULL so that you can
restore your database to any time point that you want, I recommend that you
start a job to truncate your transaction log every day.
For more information, you may refer to "Recovery models" and "BACKUP LOG"
in SQL Server Books Online.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:APJ0YWMfHHA.6068@.TK2MSFTNGHUB02.phx.gbl...
> Hi, OwenC,
> I think that the data volume of your database is huge, right?
> If you frequently perform database backup and the disk space is limited, I
> recommend that set your database recovery model to simple to reduce
> logging
> increasing speed;
> If you want to leave database recovery model to FULL so that you can
> restore your database to any time point that you want, I recommend that
> you
> start a job to truncate your transaction log every day.
Umm... That's not great advice. That would invalidate his transaction log
backup chain.
And once truncated, he'd lose the ability to restore your database to any
time point you want.

> For more information, you may refer to "Recovery models" and "BACKUP LOG"
> in SQL Server Books Online.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
>
>
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Hi, Greg,
Thanks for your pointing out.
Yes, if the log file is truncated, he would lose the ability to restore his
database to any time point he want; however if their disk space is limited,
the truncate operation may be neccessary no matter in any recovery mode.
Simple recovery mode can produce less transaction logs than FULL recovery
mode. The logs increasing speed is slow, and this may be able to suit his
needs.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||I need to run the FULL recovery mode as this is the DB for an ERP
application. I can't afford to lose even any transactions!!!
We actually do transaction log backups and truncate them every 2 hours.
We use Backup Exec to do this.
It seems from further investigation that what caused the increased growth is
that the indexes that were added made some DB operations 20 times faster.
This caused some batch processes to run faster than they had been able to.
There was a backlog of these batches and so in clearing the backlog it
caused the growth rate of the log file to increase.
OwenC
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:YvPadJNfHHA.4368@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Greg,
> Thanks for your pointing out.
> Yes, if the log file is truncated, he would lose the ability to restore
> his
> database to any time point he want; however if their disk space is
> limited,
> the truncate operation may be neccessary no matter in any recovery mode.
> Simple recovery mode can produce less transaction logs than FULL recovery
> mode. The logs increasing speed is slow, and this may be able to suit his
> needs.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
>
>
>
|||Hi, OwenC,
Thanks for your updating and response.
I am glad to hear that you found the real cause in your situation. What is
the kind of the backlog as you mentioned? Have you found a way to resolve
it now?
Please feel free to let me know if you need any assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi, Owen
I am interested in this issue. Would you mind letting me know the result of
this issue? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
sql

High Log flush Wait Time

Hello,
We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
to an HP EVA 3000.
4 CPU with HyperTh enabled. 8 GB of RAM
Sqlserver 2000 (SP3a) with 180 Gb of DB.
Since 2 weeks, we have slow performance but CPU are not stressed.
We have High Log flush Wait time (>2000 ms) and high latch wait time (800
ms)
Anyone as an idea ?
THX
Message posted via http://www.sqlmonster.com
You probably also have a high disk queue length on the disk(s) where yout
LOG device lives. Make sure yout LOG files are on a RAID 1 or 1+0 device
with no other data files on those disks. This must be a physical disk set,
not just a logical partition of an underlying shared RAID set.
What is happening is that your SQL server cannot write log files to the disk
in a timely manner. SQL will not process a transaction unless the
write-ahead transaction log has the start transaction marker committed to
disk. Thus, your CPUs are stuck waiting on this particular disk function.
Therefore, you make log writes function as fast as possible. Since logs are
written sequentially in relatively small chunks, RAID 1 or1+ 0 is ideal.
RAID 5 will cause performance bottlenecks on log devices.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"John via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:61f18ba8c87443539797e121a8dd7f14@.SQLMonster.c om...
> Hello,
> We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
> to an HP EVA 3000.
> 4 CPU with HyperTh enabled. 8 GB of RAM
> Sqlserver 2000 (SP3a) with 180 Gb of DB.
> Since 2 weeks, we have slow performance but CPU are not stressed.
> We have High Log flush Wait time (>2000 ms) and high latch wait time (800
> ms)
> Anyone as an idea ?
> THX
> --
> Message posted via http://www.sqlmonster.com

High Log flush Wait Time

Hello,
We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
to an HP EVA 3000.
4 CPU with HyperTh enabled. 8 GB of RAM
Sqlserver 2000 (SP3a) with 180 Gb of DB.
Since 2 weeks, we have slow performance but CPU are not stressed.
We have High Log flush Wait time (>2000 ms) and high latch wait time (800
ms)
Anyone as an idea ?
THX
--
Message posted via http://www.sqlmonster.comYou probably also have a high disk queue length on the disk(s) where yout
LOG device lives. Make sure yout LOG files are on a RAID 1 or 1+0 device
with no other data files on those disks. This must be a physical disk set,
not just a logical partition of an underlying shared RAID set.
What is happening is that your SQL server cannot write log files to the disk
in a timely manner. SQL will not process a transaction unless the
write-ahead transaction log has the start transaction marker committed to
disk. Thus, your CPUs are stuck waiting on this particular disk function.
Therefore, you make log writes function as fast as possible. Since logs are
written sequentially in relatively small chunks, RAID 1 or1+ 0 is ideal.
RAID 5 will cause performance bottlenecks on log devices.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"John via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:61f18ba8c87443539797e121a8dd7f14@.SQLMonster.com...
> Hello,
> We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
> to an HP EVA 3000.
> 4 CPU with HyperTh enabled. 8 GB of RAM
> Sqlserver 2000 (SP3a) with 180 Gb of DB.
> Since 2 weeks, we have slow performance but CPU are not stressed.
> We have High Log flush Wait time (>2000 ms) and high latch wait time (800
> ms)
> Anyone as an idea ?
> THX
> --
> Message posted via http://www.sqlmonster.com

High Log flush Wait Time

Hello,
We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
to an HP EVA 3000.
4 CPU with HyperTh enabled. 8 GB of RAM
Sqlserver 2000 (SP3a) with 180 Gb of DB.
Since 2 weeks, we have slow performance but CPU are not stressed.
We have High Log flush Wait time (>2000 ms) and high latch wait time (800
ms)
Anyone as an idea ?
THX
Message posted via http://www.droptable.comYou probably also have a high disk queue length on the disk(s) where yout
LOG device lives. Make sure yout LOG files are on a RAID 1 or 1+0 device
with no other data files on those disks. This must be a physical disk set,
not just a logical partition of an underlying shared RAID set.
What is happening is that your SQL server cannot write log files to the disk
in a timely manner. SQL will not process a transaction unless the
write-ahead transaction log has the start transaction marker committed to
disk. Thus, your CPUs are stuck waiting on this particular disk function.
Therefore, you make log writes function as fast as possible. Since logs are
written sequentially in relatively small chunks, RAID 1 or1+ 0 is ideal.
RAID 5 will cause performance bottlenecks on log devices.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"John via droptable.com" <forum@.droptable.com> wrote in message
news:61f18ba8c87443539797e121a8dd7f14@.SQ
droptable.com...
> Hello,
> We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
> to an HP EVA 3000.
> 4 CPU with HyperTh enabled. 8 GB of RAM
> Sqlserver 2000 (SP3a) with 180 Gb of DB.
> Since 2 weeks, we have slow performance but CPU are not stressed.
> We have High Log flush Wait time (>2000 ms) and high latch wait time (800
> ms)
> Anyone as an idea ?
> THX
> --
> Message posted via http://www.droptable.comsql

high log flush wait

I am seeing what appears to be file contention in the
form of log flush waits (consistently longer than 1/sec).
Other than separating the .ldf and .mdf on different
physical devices, is there anything that can be done to
minimize this?
TIA,
AJSeparating the devices is precisely what you should do. Your problem
clearly demonstrates why the recommendation exists in the first place.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:423501c49030$78833070$a601280a@.phx.gbl...
> I am seeing what appears to be file contention in the
> form of log flush waits (consistently longer than 1/sec).
> Other than separating the .ldf and .mdf on different
> physical devices, is there anything that can be done to
> minimize this?
> TIA,
> AJ|||Is there really nothing else that can be done?
I have PLANS to separate them, but I have to wait for new
hardware to arrive. In the meantime, performance is
seriously suffering. Any additional suggestions would be
MOST appreciated.
AJ
>--Original Message--
>Separating the devices is precisely what you should do.
Your problem
>clearly demonstrates why the recommendation exists in
the first place.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"AJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:423501c49030$78833070$a601280a@.phx.gbl...
1/sec).[vbcol=seagreen]
>
>.
>|||You can try and see if there are other performance limitations on your
system, but high log flush wait times won't get better without faster
hardware.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:0aa401c4903b$c7b032a0$a401280a@.phx.gbl...[vbcol=seagreen]
> Is there really nothing else that can be done?
> I have PLANS to separate them, but I have to wait for new
> hardware to arrive. In the meantime, performance is
> seriously suffering. Any additional suggestions would be
> MOST appreciated.
> AJ
> Your problem
> the first place.
> message
> 1/sec).

high log flush wait

I am seeing what appears to be file contention in the
form of log flush waits (consistently longer than 1/sec).
Other than separating the .ldf and .mdf on different
physical devices, is there anything that can be done to
minimize this?
TIA,
AJSeparating the devices is precisely what you should do. Your problem
clearly demonstrates why the recommendation exists in the first place.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:423501c49030$78833070$a601280a@.phx.gbl...
> I am seeing what appears to be file contention in the
> form of log flush waits (consistently longer than 1/sec).
> Other than separating the .ldf and .mdf on different
> physical devices, is there anything that can be done to
> minimize this?
> TIA,
> AJ|||Is there really nothing else that can be done?
I have PLANS to separate them, but I have to wait for new
hardware to arrive. In the meantime, performance is
seriously suffering. Any additional suggestions would be
MOST appreciated.
AJ
>--Original Message--
>Separating the devices is precisely what you should do.
Your problem
>clearly demonstrates why the recommendation exists in
the first place.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"AJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:423501c49030$78833070$a601280a@.phx.gbl...
>> I am seeing what appears to be file contention in the
>> form of log flush waits (consistently longer than
1/sec).
>> Other than separating the .ldf and .mdf on different
>> physical devices, is there anything that can be done to
>> minimize this?
>> TIA,
>> AJ
>
>.
>|||You can try and see if there are other performance limitations on your
system, but high log flush wait times won't get better without faster
hardware.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:0aa401c4903b$c7b032a0$a401280a@.phx.gbl...
> Is there really nothing else that can be done?
> I have PLANS to separate them, but I have to wait for new
> hardware to arrive. In the meantime, performance is
> seriously suffering. Any additional suggestions would be
> MOST appreciated.
> AJ
> >--Original Message--
> >Separating the devices is precisely what you should do.
> Your problem
> >clearly demonstrates why the recommendation exists in
> the first place.
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >I support the Professional Association for SQL Server
> >www.sqlpass.org
> >
> >"AJ" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:423501c49030$78833070$a601280a@.phx.gbl...
> >> I am seeing what appears to be file contention in the
> >> form of log flush waits (consistently longer than
> 1/sec).
> >>
> >> Other than separating the .ldf and .mdf on different
> >> physical devices, is there anything that can be done to
> >> minimize this?
> >>
> >> TIA,
> >> AJ
> >
> >
> >.
> >

high log flush wait

I am seeing what appears to be file contention in the
form of log flush waits (consistently longer than 1/sec).
Other than separating the .ldf and .mdf on different
physical devices, is there anything that can be done to
minimize this?
TIA,
AJ
Separating the devices is precisely what you should do. Your problem
clearly demonstrates why the recommendation exists in the first place.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:423501c49030$78833070$a601280a@.phx.gbl...
> I am seeing what appears to be file contention in the
> form of log flush waits (consistently longer than 1/sec).
> Other than separating the .ldf and .mdf on different
> physical devices, is there anything that can be done to
> minimize this?
> TIA,
> AJ
|||Is there really nothing else that can be done?
I have PLANS to separate them, but I have to wait for new
hardware to arrive. In the meantime, performance is
seriously suffering. Any additional suggestions would be
MOST appreciated.
AJ
>--Original Message--
>Separating the devices is precisely what you should do.
Your problem
>clearly demonstrates why the recommendation exists in
the first place.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"AJ" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:423501c49030$78833070$a601280a@.phx.gbl...
1/sec).
>
>.
>
|||You can try and see if there are other performance limitations on your
system, but high log flush wait times won't get better without faster
hardware.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:0aa401c4903b$c7b032a0$a401280a@.phx.gbl...[vbcol=seagreen]
> Is there really nothing else that can be done?
> I have PLANS to separate them, but I have to wait for new
> hardware to arrive. In the meantime, performance is
> seriously suffering. Any additional suggestions would be
> MOST appreciated.
> AJ
> Your problem
> the first place.
> message
> 1/sec).

High CPU usage help

Im at my wits end. The SQL server (SQL2000 using 2005 Studio to view )
activity log is showing several ProcessIDs that say "AWAITING COMMAND" and it
also says the Status is SLEEPING, However the CPU# is 235789 and the Phyical
I/O is 36345 and Memory is 912
So if the status is Sleeping and AWAITNG COMMAND, why are these numbers not
0? If its waiting it shouldnt be doing much of anything. Our SQL server will
just randomly start crawling
JP
..NET Software Developer
The information you are seeing is cumulative for that spid. It isnt what it
is using at that point in time, but those statistics are the resources used
for the life of that spid.
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"JP" wrote:

> Im at my wits end. The SQL server (SQL2000 using 2005 Studio to view )
> activity log is showing several ProcessIDs that say "AWAITING COMMAND" and it
> also says the Status is SLEEPING, However the CPU# is 235789 and the Phyical
> I/O is 36345 and Memory is 912
> So if the status is Sleeping and AWAITNG COMMAND, why are these numbers not
> 0? If its waiting it shouldnt be doing much of anything. Our SQL server will
> just randomly start crawling
> --
> JP
> .NET Software Developer

High CPU usage help

Im at my wits end. The SQL server (SQL2000 using 2005 Studio to view )
activity log is showing several ProcessIDs that say "AWAITING COMMAND" and it
also says the Status is SLEEPING, However the CPU# is 235789 and the Phyical
I/O is 36345 and Memory is 912
So if the status is Sleeping and AWAITNG COMMAND, why are these numbers not
0? If its waiting it shouldnt be doing much of anything. Our SQL server will
just randomly start crawling
--
JP
.NET Software DeveloperThe information you are seeing is cumulative for that spid. It isnt what it
is using at that point in time, but those statistics are the resources used
for the life of that spid.
--
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"JP" wrote:
> Im at my wits end. The SQL server (SQL2000 using 2005 Studio to view )
> activity log is showing several ProcessIDs that say "AWAITING COMMAND" and it
> also says the Status is SLEEPING, However the CPU# is 235789 and the Phyical
> I/O is 36345 and Memory is 912
> So if the status is Sleeping and AWAITNG COMMAND, why are these numbers not
> 0? If its waiting it shouldnt be doing much of anything. Our SQL server will
> just randomly start crawling
> --
> JP
> .NET Software Developer

High CPU usage help

Im at my wits end. The SQL server (SQL2000 using 2005 Studio to view )
activity log is showing several ProcessIDs that say "AWAITING COMMAND" and i
t
also says the Status is SLEEPING, However the CPU# is 235789 and the Phyical
I/O is 36345 and Memory is 912
So if the status is Sleeping and AWAITNG COMMAND, why are these numbers not
0? If its waiting it shouldnt be doing much of anything. Our SQL server will
just randomly start crawling
JP
.NET Software DeveloperThe information you are seeing is cumulative for that spid. It isnt what it
is using at that point in time, but those statistics are the resources used
for the life of that spid.
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"JP" wrote:

> Im at my wits end. The SQL server (SQL2000 using 2005 Studio to view )
> activity log is showing several ProcessIDs that say "AWAITING COMMAND" and
it
> also says the Status is SLEEPING, However the CPU# is 235789 and the Phyic
al
> I/O is 36345 and Memory is 912
> So if the status is Sleeping and AWAITNG COMMAND, why are these numbers no
t
> 0? If its waiting it shouldnt be doing much of anything. Our SQL server wi
ll
> just randomly start crawling
> --
> JP
> .NET Software Developer

High CPU during transaction log backups!

High CPU during transaction log backups!
I'm having issues with a SQL server where during
transaction log backups, the CPU usage us on average 20%-
25% higher than during normal. We have timed this out
using performance monitor and know for sure it's the
transaction logs at this point. If we turn off transaction
logs, the CPUs continue normal without the 20%-25% spike.
We have our data volume physically separate from our log
volume, and our backup volume again separate from both of
these. The log volume and the backup volume also are on
separate scsi channel.
While watching the performance monitor, the physical drive
stats are flat line with no queued requests on any of the
volumes so it does not appear to be disk io issue. All
raid is HW raid on a controller with 128 meg of cache,
which according to its stats, the controller is barely
breathing hard.
When watching the sysprocess, the last wait type is always
ASYNC_DISKPOOL_LOCK, which I cannot find much info on
other than it occurs during backups, and look at disk io
issues, which again, don't appear to be the issue.
I'm at a total loss. I have 8 other SQL servers with
similar configs that do not show this behavior. All boxes
are configed the same, meaning HW, software an settings.
Any other ideas?!?!
Configuration:
HP Proliant
Quad 700 Xeons
2.5 gig ram
Data drive 4 * 72 gig 10k scsi drives raid 5
Log drive 2 * 72 gig 10k scsi drives raid 1
Backup drive 3 * 72 gig 10k scsi drives raid 5
Win2000 SP2
SQL2K SP3a
-RossRoss
Is it sill under 80%-90% ? If it is, don't worry.
"Ross" <RMNornesNOSPAM@.NOSPAMjtmd.com> wrote in message
news:1e2d101c45578$fa214f60$a601280a@.phx.gbl...
> High CPU during transaction log backups!
> I'm having issues with a SQL server where during
> transaction log backups, the CPU usage us on average 20%-
> 25% higher than during normal. We have timed this out
> using performance monitor and know for sure it's the
> transaction logs at this point. If we turn off transaction
> logs, the CPUs continue normal without the 20%-25% spike.
> We have our data volume physically separate from our log
> volume, and our backup volume again separate from both of
> these. The log volume and the backup volume also are on
> separate scsi channel.
> While watching the performance monitor, the physical drive
> stats are flat line with no queued requests on any of the
> volumes so it does not appear to be disk io issue. All
> raid is HW raid on a controller with 128 meg of cache,
> which according to its stats, the controller is barely
> breathing hard.
> When watching the sysprocess, the last wait type is always
> ASYNC_DISKPOOL_LOCK, which I cannot find much info on
> other than it occurs during backups, and look at disk io
> issues, which again, don't appear to be the issue.
> I'm at a total loss. I have 8 other SQL servers with
> similar configs that do not show this behavior. All boxes
> are configed the same, meaning HW, software an settings.
> Any other ideas?!?!
> Configuration:
> HP Proliant
> Quad 700 Xeons
> 2.5 gig ram
> Data drive 4 * 72 gig 10k scsi drives raid 5
> Log drive 2 * 72 gig 10k scsi drives raid 1
> Backup drive 3 * 72 gig 10k scsi drives raid 5
> Win2000 SP2
> SQL2K SP3a
> -Ross