Log file size

Hi there,
I have a SQL Server 2000 database running on a Windows 2000 Server. The data
file is about 1GB large. The size of the log file is allways about 2GB. When
it reaches this size, I run 'BACKUP LOG <database> WITH TRUNCATE_ONLY' to
empty the file. Then, the file keeps growing again until 2GB.
My question is, it is normal that the log file grows like this way?
I can also tell that there is an application that runs over the database,
and it's installed in 10/15 workstations. There are allways 8/9+
workstations working.
I would appreciate any commentary...
Regards..
Marco Pais> My question is, it is normal that the log file grows like this way?
Well, what is your recovery model? If you don't backup your database then
the log will continue to grow until you either backup the database or backup
the log. If you are not bothering to backup your database then switch to
simple recovery model and the effect on the log won't be so great (but of
course your recovery options become more limited).
In our environment, we leave enough room for the log files to grow as they
need to. If you are fretting over 1 GB now what's going to happen when you
have a real data store.
A|||Hello Aaron,
First of all, thanks for the answer.
The database is backuped once a day, every days of week.
Can the log file growing be caused by something else? Can it affect
performance?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ea7ka03PFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Well, what is your recovery model? If you don't backup your database then
> the log will continue to grow until you either backup the database or
> backup the log. If you are not bothering to backup your database then
> switch to simple recovery model and the effect on the log won't be so
> great (but of course your recovery options become more limited).
> In our environment, we leave enough room for the log files to grow as they
> need to. If you are fretting over 1 GB now what's going to happen when
> you have a real data store.
> A
>|||Apart from long running transactions in the application or badly configured
replication the most common cause of T-log becoming excessive is the
optimization job. This will make copies of existing data while rebuilding
and if you have particularly large tables this will cause a long running
transaction.
Providing the used part of the log stays reasonable small (say <100MB)
during your busy times you should not suffer performance degredation.
Regards,
Tim G-J
Professional Database Solutions Ltd.
www.pdbsolutions.co.uk
"Marco Pais" wrote:

> Hello Aaron,
> First of all, thanks for the answer.
> The database is backuped once a day, every days of week.
> Can the log file growing be caused by something else? Can it affect
> performance?
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:ea7ka03PFHA.1388@.TK2MSFTNGP09.phx.gbl...
>
>

Log file size

Hi there,
I have a SQL Server 2000 database running on a Windows 2000 Server. The data
file is about 1GB large. The size of the log file is allways about 2GB. When
it reaches this size, I run 'BACKUP LOG <database> WITH TRUNCATE_ONLY' to
empty the file. Then, the file keeps growing again until 2GB.
My question is, it is normal that the log file grows like this way?
I can also tell that there is an application that runs over the database,
and it's installed in 10/15 workstations. There are allways 8/9+
workstations working.
I would appreciate any commentary...
Regards..
Marco Pais> My question is, it is normal that the log file grows like this way?
Well, what is your recovery model? If you don't backup your database then
the log will continue to grow until you either backup the database or backup
the log. If you are not bothering to backup your database then switch to
simple recovery model and the effect on the log won't be so great (but of
course your recovery options become more limited).
In our environment, we leave enough room for the log files to grow as they
need to. If you are fretting over 1 GB now what's going to happen when you
have a real data store.
A|||Hello Aaron,
First of all, thanks for the answer.
The database is backuped once a day, every days of week.
Can the log file growing be caused by something else? Can it affect
performance?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ea7ka03PFHA.1388@.TK2MSFTNGP09.phx.gbl...
>> My question is, it is normal that the log file grows like this way?
> Well, what is your recovery model? If you don't backup your database then
> the log will continue to grow until you either backup the database or
> backup the log. If you are not bothering to backup your database then
> switch to simple recovery model and the effect on the log won't be so
> great (but of course your recovery options become more limited).
> In our environment, we leave enough room for the log files to grow as they
> need to. If you are fretting over 1 GB now what's going to happen when
> you have a real data store.
> A
>

Log file size

Hi there,
I have a SQL Server 2000 database running on a Windows 2000 Server. The data
file is about 1GB large. The size of the log file is allways about 2GB. When
it reaches this size, I run 'BACKUP LOG <database> WITH TRUNCATE_ONLY' to
empty the file. Then, the file keeps growing again until 2GB.
My question is, it is normal that the log file grows like this way?
I can also tell that there is an application that runs over the database,
and it's installed in 10/15 workstations. There are allways 8/9+
workstations working.
I would appreciate any commentary...
Regards..
Marco Pais
> My question is, it is normal that the log file grows like this way?
Well, what is your recovery model? If you don't backup your database then
the log will continue to grow until you either backup the database or backup
the log. If you are not bothering to backup your database then switch to
simple recovery model and the effect on the log won't be so great (but of
course your recovery options become more limited).
In our environment, we leave enough room for the log files to grow as they
need to. If you are fretting over 1 GB now what's going to happen when you
have a real data store.
A
|||Hello Aaron,
First of all, thanks for the answer.
The database is backuped once a day, every days of week.
Can the log file growing be caused by something else? Can it affect
performance?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ea7ka03PFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Well, what is your recovery model? If you don't backup your database then
> the log will continue to grow until you either backup the database or
> backup the log. If you are not bothering to backup your database then
> switch to simple recovery model and the effect on the log won't be so
> great (but of course your recovery options become more limited).
> In our environment, we leave enough room for the log files to grow as they
> need to. If you are fretting over 1 GB now what's going to happen when
> you have a real data store.
> A
>
|||Apart from long running transactions in the application or badly configured
replication the most common cause of T-log becoming excessive is the
optimization job. This will make copies of existing data while rebuilding
and if you have particularly large tables this will cause a long running
transaction.
Providing the used part of the log stays reasonable small (say <100MB)
during your busy times you should not suffer performance degredation.
Regards,
Tim G-J
Professional Database Solutions Ltd.
www.pdbsolutions.co.uk
"Marco Pais" wrote:

> Hello Aaron,
> First of all, thanks for the answer.
> The database is backuped once a day, every days of week.
> Can the log file growing be caused by something else? Can it affect
> performance?
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:ea7ka03PFHA.1388@.TK2MSFTNGP09.phx.gbl...
>
>

Log File Partition

I am innstalling SQL 2000 Enterprise on Windows 2000 server. I would like
to setup a separate partition for the log file. I would like to know how to
determine the partition size of the log file. Is it acceptable to setup
the partition size of the log file the same partition size as the the
operating system?
Thanks.Diane
A lot of that really depends on your DB. Do you expect a lot of transaction
? How large is the DB going to be? How large is your OS Partition?
I usually have an 8 gb OS
and if my Data Partition is around 100 GB
Then I put my Log Partition around 25 GB.
But that is my rule of thumb. If you have a lot of transactions that could
be large then you need to adjust accordingly.
Jeff|||Thanks for your quick response.
The SQL database will be for the Help Desk and inventory software. We are
going to implement this new software in May. I expect the database to be
10GB by the end of December. I don't expect the database to grow more than
20GB per year. I expect a lot of transaction. My OS partition is 10GB.
Do you install SQL on on OS partition or do you have a separate partition
for SQL?
Please let me know if you need additional information.
Thanks.
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:02034A00-FCD4-4C27-A7CA-DA1ABAA91571@.microsoft.com...
> Diane
> A lot of that really depends on your DB. Do you expect a lot of
transaction? How large is the DB going to be? How large is your OS
Partition?
> I usually have an 8 gb OS
> and if my Data Partition is around 100 GB
> Then I put my Log Partition around 25 GB.
> But that is my rule of thumb. If you have a lot of transactions that
could be large then you need to adjust accordingly.
> Jeff|||Diane,
The standard 3 drive setup is to put the operating system on the C drive, th
e application(SQL Server in this case) on the D drive and the data on the E
drive, in your case the .mdf files would be on E and your .ldf files would b
e on another drive( F for e
xample).
Ed|||Looks like what you recommend was to setup 4 partitions. I only have two
drives (72.8 GB each) and setup RAID 1.
My OS partition is 10GB. Would you setup the partition for the log file the
same size as your OS partition? In this case, it is 10 GB. Is there a
rule of thumb on how to setup the log file partition?
Thank you.
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed|||I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed|||I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed|||Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:eFvkrylJEHA.3628@.TK2MSFTNGP12.phx.gbl...
> I don't quite understand the reasons for the "standard 3 drive setup"? can
> someone explain it a bit more for me? (who came up with such "standard"?)
> if c is for OS
> d is for sql excutables
> e is for data, then my questions are:
> 1. in sql 2k, even specify program files being installed on d, there are
> some files still being installed on c:\program files\mssql
Yep. Most are system databases which are in simple mode anyway. The only
things you might want to move are the tempdb devices. Once SQL is up and
running you can change the default log and data file locations.

> 2. if all data go to e (both mdf, ldf i assume), is that considered a good
> performance (I/O) and fault tolerence (if the d drive gose bad, what
happen
> to trans log?) strategy?
Again, the system databases with the exception of tempdb are now volume and
simple recovery only. They are also fairly small and can be backed up
daily.
> at my previous company, the set up is
> C is for os and sql excutables
> D is for logs
> and F is for data and backup files.
> c and d are one partitioned mirror drive. (for fault tolerance)
> F drive is raid 5.
Ooh, lousy performance AND all the eggs in one basket. Worst practices run
rampant.

> can someone tell me if the "standard 3 drive setup" is better than this
set
> up? and why?
>
Three drives are for fault tolerance and performance. All transactions are
held up until the log entry is physically committed to disk. Also, log
files are sequential writes and database files tend to be random writes.
Disk head movement optimization algorithms can sometimes cause log writes to
be delayed if data is on the same partition.
The biggest reason to separate them is recovery. As long as you have a
decent backup rotation and FULL recovery for the user databases, a three
drive system will always allow you to recover all transactions up to the
last moment before a hardware failure if it is a single partition failure.

> Steve
>
> "Ed" <anonymous@.discussions.microsoft.com> wrote in message
> news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
drive,[vbcol=seagreen]
> the application(SQL Server in this case) on the D drive and the data on
the
> E drive, in your case the .mdf files would be on E and your .ldf files
would
> be on another drive( F for example).
>
>|||Steve
Most sites (and most people) have different views on the perfect disk set up
.
The way we do it.
'C' Operating system (NT4 or Windows 2000/2003)
'D' Install SQL Server, System databases, User databases primary filegroups
containing system tables only
'E' User databases user tables
'F' User databases transaction logs
'G' Backups
'H' Application Data
D, E and F are usually RAID 1 + 0 all other drives are mirrored.
We find this gives us good performance and good resilience.
Regards
Johnsql

Log File Partition

I am innstalling SQL 2000 Enterprise on Windows 2000 server. I would like
to setup a separate partition for the log file. I would like to know how to
determine the partition size of the log file. Is it acceptable to setup
the partition size of the log file the same partition size as the the
operating system?
Thanks.Thanks for your quick response.
The SQL database will be for the Help Desk and inventory software. We are
going to implement this new software in May. I expect the database to be
10GB by the end of December. I don't expect the database to grow more than
20GB per year. I expect a lot of transaction. My OS partition is 10GB.
Do you install SQL on on OS partition or do you have a separate partition
for SQL?
Please let me know if you need additional information.
Thanks.
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:02034A00-FCD4-4C27-A7CA-DA1ABAA91571@.microsoft.com...
> Diane
> A lot of that really depends on your DB. Do you expect a lot of
transaction? How large is the DB going to be? How large is your OS
Partition?
> I usually have an 8 gb OS
> and if my Data Partition is around 100 GB
> Then I put my Log Partition around 25 GB.
> But that is my rule of thumb. If you have a lot of transactions that
could be large then you need to adjust accordingly.
> Jeff|||Diane
The standard 3 drive setup is to put the operating system on the C drive, the application(SQL Server in this case) on the D drive and the data on the E drive, in your case the .mdf files would be on E and your .ldf files would be on another drive( F for example).
Ed|||Looks like what you recommend was to setup 4 partitions. I only have two
drives (72.8 GB each) and setup RAID 1.
My OS partition is 10GB. Would you setup the partition for the log file the
same size as your OS partition? In this case, it is 10 GB. Is there a
rule of thumb on how to setup the log file partition?
Thank you.
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed|||I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed

Log File Partition

I am innstalling SQL 2000 Enterprise on Windows 2000 server. I would like
to setup a separate partition for the log file. I would like to know how to
determine the partition size of the log file. Is it acceptable to setup
the partition size of the log file the same partition size as the the
operating system?
Thanks.
Diane
A lot of that really depends on your DB. Do you expect a lot of transaction? How large is the DB going to be? How large is your OS Partition?
I usually have an 8 gb OS
and if my Data Partition is around 100 GB
Then I put my Log Partition around 25 GB.
But that is my rule of thumb. If you have a lot of transactions that could be large then you need to adjust accordingly.
Jeff
|||Thanks for your quick response.
The SQL database will be for the Help Desk and inventory software. We are
going to implement this new software in May. I expect the database to be
10GB by the end of December. I don't expect the database to grow more than
20GB per year. I expect a lot of transaction. My OS partition is 10GB.
Do you install SQL on on OS partition or do you have a separate partition
for SQL?
Please let me know if you need additional information.
Thanks.
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:02034A00-FCD4-4C27-A7CA-DA1ABAA91571@.microsoft.com...
> Diane
> A lot of that really depends on your DB. Do you expect a lot of
transaction? How large is the DB going to be? How large is your OS
Partition?
> I usually have an 8 gb OS
> and if my Data Partition is around 100 GB
> Then I put my Log Partition around 25 GB.
> But that is my rule of thumb. If you have a lot of transactions that
could be large then you need to adjust accordingly.
> Jeff
|||Diane,
The standard 3 drive setup is to put the operating system on the C drive, the application(SQL Server in this case) on the D drive and the data on the E drive, in your case the .mdf files would be on E and your .ldf files would be on another drive( F for e
xample).
Ed
|||Looks like what you recommend was to setup 4 partitions. I only have two
drives (72.8 GB each) and setup RAID 1.
My OS partition is 10GB. Would you setup the partition for the log file the
same size as your OS partition? In this case, it is 10 GB. Is there a
rule of thumb on how to setup the log file partition?
Thank you.
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed
|||I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed
|||I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed
|||Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:eFvkrylJEHA.3628@.TK2MSFTNGP12.phx.gbl...
> I don't quite understand the reasons for the "standard 3 drive setup"? can
> someone explain it a bit more for me? (who came up with such "standard"?)
> if c is for OS
> d is for sql excutables
> e is for data, then my questions are:
> 1. in sql 2k, even specify program files being installed on d, there are
> some files still being installed on c:\program files\mssql
Yep. Most are system databases which are in simple mode anyway. The only
things you might want to move are the tempdb devices. Once SQL is up and
running you can change the default log and data file locations.

> 2. if all data go to e (both mdf, ldf i assume), is that considered a good
> performance (I/O) and fault tolerence (if the d drive gose bad, what
happen
> to trans log?) strategy?
Again, the system databases with the exception of tempdb are now volume and
simple recovery only. They are also fairly small and can be backed up
daily.
> at my previous company, the set up is
> C is for os and sql excutables
> D is for logs
> and F is for data and backup files.
> c and d are one partitioned mirror drive. (for fault tolerance)
> F drive is raid 5.
Ooh, lousy performance AND all the eggs in one basket. Worst practices run
rampant.

> can someone tell me if the "standard 3 drive setup" is better than this
set
> up? and why?
>
Three drives are for fault tolerance and performance. All transactions are
held up until the log entry is physically committed to disk. Also, log
files are sequential writes and database files tend to be random writes.
Disk head movement optimization algorithms can sometimes cause log writes to
be delayed if data is on the same partition.
The biggest reason to separate them is recovery. As long as you have a
decent backup rotation and FULL recovery for the user databases, a three
drive system will always allow you to recover all transactions up to the
last moment before a hardware failure if it is a single partition failure.
[vbcol=seagreen]
> Steve
>
> "Ed" <anonymous@.discussions.microsoft.com> wrote in message
> news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
drive,
> the application(SQL Server in this case) on the D drive and the data on
the
> E drive, in your case the .mdf files would be on E and your .ldf files
would
> be on another drive( F for example).
>
>
|||Steve
Most sites (and most people) have different views on the perfect disk set up.
The way we do it.
'C' Operating system (NT4 or Windows 2000/2003)
'D' Install SQL Server, System databases, User databases primary filegroups containing system tables only
'E' User databases user tables
'F' User databases transaction logs
'G' Backups
'H' Application Data
D, E and F are usually RAID 1 + 0 all other drives are mirrored.
We find this gives us good performance and good resilience.
Regards
John

log file on Windows striped volume

I'm using SQLServer 2000 on a Windows Server 2003 machine as my backend
database
for the SPECjAppServer benchmark. Originally I had the database's tables
and logs on
the same disk drive and noticed that drive activity on the DB machine was
quite high and I
supposed that this was causing long disk delays and my transaction timeouts.
I added 4
drives to the machine and created a Windows striped volume across those 4
disks. I
then move the DB log file to this striped volume. The individual disk
utilizations from the
striped volume disks are now much lower than the single disk was before but
my
SPECjAppServer transaction latency has not decreased. Any there any
optimizations hints
to maximize transaction throughput using log files on a striped volume?
thanks,
Todd
For log files you want to use either Raid 1 or 10 but in either case do not
use the OS to do the raid. Use hardware Raid instead. You say that you
"suppose this was causing delays". Don't suppose, check it with perfmon and
see if they are or not. Otherwise you can be chasing the wrong thing.
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Todd A. Anderson" <drtodd@.aaahawk.com.N0SPAM> wrote in message
news:chalsl$bsv$1@.news01.intel.com...
> I'm using SQLServer 2000 on a Windows Server 2003 machine as my backend
> database
> for the SPECjAppServer benchmark. Originally I had the database's tables
> and logs on
> the same disk drive and noticed that drive activity on the DB machine was
> quite high and I
> supposed that this was causing long disk delays and my transaction
timeouts.
> I added 4
> drives to the machine and created a Windows striped volume across those 4
> disks. I
> then move the DB log file to this striped volume. The individual disk
> utilizations from the
> striped volume disks are now much lower than the single disk was before
but
> my
> SPECjAppServer transaction latency has not decreased. Any there any
> optimizations hints
> to maximize transaction throughput using log files on a striped volume?
> thanks,
> Todd
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ubbXdyfkEHA.3848@.tk2msftngp13.phx.gbl...
> For log files you want to use either Raid 1 or 10 but in either case do
not
> use the OS to do the raid. Use hardware Raid instead. You say that you
> "suppose this was causing delays". Don't suppose, check it with perfmon
and
> see if they are or not. Otherwise you can be chasing the wrong thing.
I tried putting the database on the striped volume and the log on a single
physical disk.
Every configuration has behaved pretty much the same.
1) Everything on one physical disk.
2) Log on striped volume, tables on non-striped.
3) Log on non-striped and tables on striped volume.
I would lose to use a hardware RAID but I don't it. I want to get the best
performance
with the equipment that we currently have. Other people have said they've
got
performance gains from techniques such as these but I'm still not seeing it.
Todd
[vbcol=seagreen]
> "Todd A. Anderson" <drtodd@.aaahawk.com.N0SPAM> wrote in message
> news:chalsl$bsv$1@.news01.intel.com...
tables[vbcol=seagreen]
was[vbcol=seagreen]
> timeouts.
4[vbcol=seagreen]
> but
|||Again, are you using perfmon to see exactly what the disk queue's are? If
they are not very high it won't matter what drive configuration you choose,
you will still get the same performance. Maybe it's not the drives. Those
links I sent should get you started to see where the bottleneck is and how
to address it. Another thing, if you are using just striping (RAID 0) and
you loose a single drive you will loose everything so make sure you have
good and often backups. And make sure you don't backup to the same disks or
you will loose them as well.
Andrew J. Kelly SQL MVP
"Todd A. Anderson" <drtodd@.aaahawk.com.N0SPAM> wrote in message
news:chkv5o$3ar$1@.news01.intel.com...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ubbXdyfkEHA.3848@.tk2msftngp13.phx.gbl...
> not
> and
> I tried putting the database on the striped volume and the log on a single
> physical disk.
> Every configuration has behaved pretty much the same.
> 1) Everything on one physical disk.
> 2) Log on striped volume, tables on non-striped.
> 3) Log on non-striped and tables on striped volume.
> I would lose to use a hardware RAID but I don't it. I want to get the
best
> performance
> with the equipment that we currently have. Other people have said they've
> got
> performance gains from techniques such as these but I'm still not seeing
it.[vbcol=seagreen]
> Todd
backend[vbcol=seagreen]
> tables
> was
those[vbcol=seagreen]
> 4
before[vbcol=seagreen]
volume?
>

log file on Windows striped volume

I'm using SQLServer 2000 on a Windows Server 2003 machine as my backend
database
for the SPECjAppServer benchmark. Originally I had the database's tables
and logs on
the same disk drive and noticed that drive activity on the DB machine was
quite high and I
supposed that this was causing long disk delays and my transaction timeouts.
I added 4
drives to the machine and created a Windows striped volume across those 4
disks. I
then move the DB log file to this striped volume. The individual disk
utilizations from the
striped volume disks are now much lower than the single disk was before but
my
SPECjAppServer transaction latency has not decreased. Any there any
optimizations hints
to maximize transaction throughput using log files on a striped volume?
thanks,
ToddFor log files you want to use either Raid 1 or 10 but in either case do not
use the OS to do the raid. Use hardware Raid instead. You say that you
"suppose this was causing delays". Don't suppose, check it with perfmon and
see if they are or not. Otherwise you can be chasing the wrong thing.
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Todd A. Anderson" <drtodd@.aaahawk.com.N0SPAM> wrote in message
news:chalsl$bsv$1@.news01.intel.com...
> I'm using SQLServer 2000 on a Windows Server 2003 machine as my backend
> database
> for the SPECjAppServer benchmark. Originally I had the database's tables
> and logs on
> the same disk drive and noticed that drive activity on the DB machine was
> quite high and I
> supposed that this was causing long disk delays and my transaction
timeouts.
> I added 4
> drives to the machine and created a Windows striped volume across those 4
> disks. I
> then move the DB log file to this striped volume. The individual disk
> utilizations from the
> striped volume disks are now much lower than the single disk was before
but
> my
> SPECjAppServer transaction latency has not decreased. Any there any
> optimizations hints
> to maximize transaction throughput using log files on a striped volume?
> thanks,
> Todd
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ubbXdyfkEHA.3848@.tk2msftngp13.phx.gbl...
> For log files you want to use either Raid 1 or 10 but in either case do
not
> use the OS to do the raid. Use hardware Raid instead. You say that you
> "suppose this was causing delays". Don't suppose, check it with perfmon
and
> see if they are or not. Otherwise you can be chasing the wrong thing.
I tried putting the database on the striped volume and the log on a single
physical disk.
Every configuration has behaved pretty much the same.
1) Everything on one physical disk.
2) Log on striped volume, tables on non-striped.
3) Log on non-striped and tables on striped volume.
I would lose to use a hardware RAID but I don't it. I want to get the best
performance
with the equipment that we currently have. Other people have said they've
got
performance gains from techniques such as these but I'm still not seeing it.
Todd
> "Todd A. Anderson" <drtodd@.aaahawk.com.N0SPAM> wrote in message
> news:chalsl$bsv$1@.news01.intel.com...
> > I'm using SQLServer 2000 on a Windows Server 2003 machine as my backend
> > database
> > for the SPECjAppServer benchmark. Originally I had the database's
tables
> > and logs on
> > the same disk drive and noticed that drive activity on the DB machine
was
> > quite high and I
> > supposed that this was causing long disk delays and my transaction
> timeouts.
> > I added 4
> > drives to the machine and created a Windows striped volume across those
4
> > disks. I
> > then move the DB log file to this striped volume. The individual disk
> > utilizations from the
> > striped volume disks are now much lower than the single disk was before
> but
> > my
> > SPECjAppServer transaction latency has not decreased. Any there any
> > optimizations hints
> > to maximize transaction throughput using log files on a striped volume?|||Again, are you using perfmon to see exactly what the disk queue's are? If
they are not very high it won't matter what drive configuration you choose,
you will still get the same performance. Maybe it's not the drives. Those
links I sent should get you started to see where the bottleneck is and how
to address it. Another thing, if you are using just striping (RAID 0) and
you loose a single drive you will loose everything so make sure you have
good and often backups. And make sure you don't backup to the same disks or
you will loose them as well.
--
Andrew J. Kelly SQL MVP
"Todd A. Anderson" <drtodd@.aaahawk.com.N0SPAM> wrote in message
news:chkv5o$3ar$1@.news01.intel.com...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ubbXdyfkEHA.3848@.tk2msftngp13.phx.gbl...
> > For log files you want to use either Raid 1 or 10 but in either case do
> not
> > use the OS to do the raid. Use hardware Raid instead. You say that you
> > "suppose this was causing delays". Don't suppose, check it with perfmon
> and
> > see if they are or not. Otherwise you can be chasing the wrong thing.
> I tried putting the database on the striped volume and the log on a single
> physical disk.
> Every configuration has behaved pretty much the same.
> 1) Everything on one physical disk.
> 2) Log on striped volume, tables on non-striped.
> 3) Log on non-striped and tables on striped volume.
> I would lose to use a hardware RAID but I don't it. I want to get the
best
> performance
> with the equipment that we currently have. Other people have said they've
> got
> performance gains from techniques such as these but I'm still not seeing
it.
> Todd
> > "Todd A. Anderson" <drtodd@.aaahawk.com.N0SPAM> wrote in message
> > news:chalsl$bsv$1@.news01.intel.com...
> > > I'm using SQLServer 2000 on a Windows Server 2003 machine as my
backend
> > > database
> > > for the SPECjAppServer benchmark. Originally I had the database's
> tables
> > > and logs on
> > > the same disk drive and noticed that drive activity on the DB machine
> was
> > > quite high and I
> > > supposed that this was causing long disk delays and my transaction
> > timeouts.
> > > I added 4
> > > drives to the machine and created a Windows striped volume across
those
> 4
> > > disks. I
> > > then move the DB log file to this striped volume. The individual disk
> > > utilizations from the
> > > striped volume disks are now much lower than the single disk was
before
> > but
> > > my
> > > SPECjAppServer transaction latency has not decreased. Any there any
> > > optimizations hints
> > > to maximize transaction throughput using log files on a striped
volume?
>sql

Log Events is interrelated with Event Viewer Windows?

Hi everyone,

I was wondering how do for 'Log Events' info going to Event Viewer Windows visor.Is it possible or it's just only private info for you SSIS.

Thanks for your input,

Hello. If I follow, what your asking, its if in the SSIS 'Log Events' tab/window, you can see Windows Events, those not associated with SSIS? If so the answer is no. The Log Events window will show only events that SSIS generates, though it does not matter if you are currently using a SQL log provider or a windows event log provider.

Hope that helps.

|||Ok, it helped me.

Log Cache Hit Ratio + Buffer Cache Hit Ratio

Thanks in advance for any info.
SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB RAM.
RAID 5.
What does one do when the Buffer Cache is at (and stays at) 99% and the Log
Cache is at 99%? Faster processors, more memory, change the RAID config?
Note: this is not daily activity on the box, but a monthly process that has
gone from 18 hours to 28 hours {and counting} with no changes.
Thanks,
MorganIf you're talking about the hit ratios, those are both
excellent numbers. Anything over 95% is optimal. When
those numbers start to drop, in general more memory is
the key, but be sure to check into it more closely before
purchasing hardware.
>--Original Message--
>Thanks in advance for any info.
>SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I
think), 1.5GB RAM.
>RAID 5.
>What does one do when the Buffer Cache is at (and stays
at) 99% and the Log
>Cache is at 99%? Faster processors, more memory, change
the RAID config?
>Note: this is not daily activity on the box, but a
monthly process that has
>gone from 18 hours to 28 hours {and counting} with no
changes.
>
>Thanks,
>Morgan
>
>.
>|||Morgan,
> What does one do when the Buffer Cache is at (and stays at) 99% and the
Log
> Cache is at 99%? Faster processors, more memory, change the RAID config?
> Note: this is not daily activity on the box, but a monthly process that
has
> gone from 18 hours to 28 hours {and counting} with no changes.
99% cache hit ratios are optimal.
This means that the server is practically always getting data from the
caches, rather than going to the disk subsystem.
It sounds more like the work needs to be done in the monthly process, as far
as optimizing it goes. Your hardware seems to be performing optimally.
When did it go from 18 to 28 hours? Suddenly? Over time? I would start
suspecting one of the following:
That perhaps either:
A.) The data set has grown
B.) Someone changed one of the queries
C.) Someone dropped a key index
D.) One or more of the above. :-)
James Hokes|||In addition to the other comments the log can be a bottle neck if on a Raid
5 and especially with the data. The Log cache ration doesn't mean much for
writes so you may want to check your disk queues on the Raid that houses the
log file anyway and make sure it has no issues. TempDB can be another place
to look. Usually month end type process does a lot of activity that uses
temp tables and you can have bottlenecks there as well. Essentially you
need to monitor the cpu and disk counters while this process is happening to
see where the bottleneck is coming from. If the data cache ratio is 99%
chances are more memory won't help much. If your procedures are optimized
then it will boil down to disk or cpu. But how sure are you that they are
that optimized?
--
Andrew J. Kelly
SQL Server MVP
"Morgan" <mfears@.spamcop.net> wrote in message
news:OJWUJA%23yDHA.2540@.tk2msftngp13.phx.gbl...
> Thanks in advance for any info.
> SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB
RAM.
> RAID 5.
> What does one do when the Buffer Cache is at (and stays at) 99% and the
Log
> Cache is at 99%? Faster processors, more memory, change the RAID config?
> Note: this is not daily activity on the box, but a monthly process that
has
> gone from 18 hours to 28 hours {and counting} with no changes.
>
> Thanks,
> Morgan
>|||On Fri, 26 Dec 2003 13:42:08 -0500, "Morgan" wrote:
>SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB RAM.
>RAID 5.
>What does one do when the Buffer Cache is at (and stays at) 99% and the Log
>Cache is at 99%? Faster processors, more memory, change the RAID config?
>Note: this is not daily activity on the box, but a monthly process that has
>gone from 18 hours to 28 hours {and counting} with no changes.
In addition to what James, James and Andrew said, look at your
statistics. As your database grows over time, the distribution of values
across columns can change substantially. Out of date stats can
contribute to bad query plans, leading to long runs.
Are you able to pinpoint which part(s) of your monthly process chews the
most time?
cheers,
Ross
--
Ross McKay, WebAware Pty Ltd
"The lawn could stand another mowing; funny, I don't even care"
- Elvis Costello|||Thanks to everyone for their insight.
I was somewhat under the impression that if they're both max'd out, then
it's time to start looking at HW. I know for a fact the procedures need
"help", but it's an inherited process that for the first time this month,
took much longer than usual, which is why I knee-jerked my post. To the best
of my knowledge, none of the underlying objects has changed, but upon
further review (below), several tables are missing indicies where they are
required. I also saw quite a bit of parallelism happening via SP_WHO, and
have begun to question the multi-proc machine, which for better or worse, is
most likely fine.
I ran Profiler off-and-on (didn't want to make it any worse than it was),
and found a single procedure that appeared to be somewhat of a bottleneck,
lots of extensive reads for a single query, minimal (single, actually)
writes. Fortunately, a predecessor had the forethought to not use temp
tables, however, the tables in use are not properly indexed. Upon review of
the primary table in the afore mentioned procedure, it turns out there are
no indexes or unique constraints; along with a truncate table statement
against the table at the start of the procedure. I know the cost of a
properly placed index will fix the amount of reads against the suspect table
I saw in Profiler. Top all this off with cursors galore (note: not the
author, but the maintainer), and you can easily appreciate the mess I'm
dealing with. I'm reading this as the beginning of the end for the current
processes... ;)
Thanks again,
Morgan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uOYObWMzDHA.2408@.tk2msftngp13.phx.gbl...
> In addition to the other comments the log can be a bottle neck if on a
Raid
> 5 and especially with the data. The Log cache ration doesn't mean much for
> writes so you may want to check your disk queues on the Raid that houses
the
> log file anyway and make sure it has no issues. TempDB can be another
place
> to look. Usually month end type process does a lot of activity that uses
> temp tables and you can have bottlenecks there as well. Essentially you
> need to monitor the cpu and disk counters while this process is happening
to
> see where the bottleneck is coming from. If the data cache ratio is 99%
> chances are more memory won't help much. If your procedures are optimized
> then it will boil down to disk or cpu. But how sure are you that they are
> that optimized?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Morgan" <mfears@.spamcop.net> wrote in message
> news:OJWUJA%23yDHA.2540@.tk2msftngp13.phx.gbl...
> > Thanks in advance for any info.
> >
> > SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB
> RAM.
> > RAID 5.
> >
> > What does one do when the Buffer Cache is at (and stays at) 99% and the
> Log
> > Cache is at 99%? Faster processors, more memory, change the RAID config?
> > Note: this is not daily activity on the box, but a monthly process that
> has
> > gone from 18 hours to 28 hours {and counting} with no changes.
> >
> >
> >
> > Thanks,
> > Morgan
> >
> >
>