Best Practices for Reducing Transaction Log Sizes?

Hi All,
I have a bunch of SQL servers that have a high volume of Bulk Inserts
happening every day (Millions of records per insert)
The most heavily used server has DBs in Simple Recovery Mode. However
both for the database and log files which are set to autogrow 10%, I am
seeing the following characteristics:
database files have about 10-15% free space every night.
log files however are about 70-90% free for all DBs
Now this is a problem since the log file size is 10gb and its using
only 2-3gb of it.
I tried using DBCC shrinkfile TRUNCATEONLY however it not only freed
the space but also reduced the log filesize to 0 and also one of my
developers complained of a table getting truncated during a stored proc
execution at that time.
1. is DBCC shrinkfile a good way to truncate free space from logs? How
could it have truncated the log to a size of 0.
2. What would be a good strategy to free up unused space from DB and
Log files every day?
Thanks all!
Hi
Good stuff to start
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
<theonlyrashid@.gmail.com> wrote in message
news:1128600308.204142.67240@.g44g2000cwa.googlegro ups.com...
> Hi All,
> I have a bunch of SQL servers that have a high volume of Bulk Inserts
> happening every day (Millions of records per insert)
> The most heavily used server has DBs in Simple Recovery Mode. However
> both for the database and log files which are set to autogrow 10%, I am
> seeing the following characteristics:
> database files have about 10-15% free space every night.
> log files however are about 70-90% free for all DBs
> Now this is a problem since the log file size is 10gb and its using
> only 2-3gb of it.
> I tried using DBCC shrinkfile TRUNCATEONLY however it not only freed
> the space but also reduced the log filesize to 0 and also one of my
> developers complained of a table getting truncated during a stored proc
> execution at that time.
> 1. is DBCC shrinkfile a good way to truncate free space from logs? How
> could it have truncated the log to a size of 0.
> 2. What would be a good strategy to free up unused space from DB and
> Log files every day?
> Thanks all!
>

Best Practices for Reducing Transaction Log Sizes?

Hi All,
I have a bunch of SQL servers that have a high volume of Bulk Inserts
happening every day (Millions of records per insert)
The most heavily used server has DBs in Simple Recovery Mode. However
both for the database and log files which are set to autogrow 10%, I am
seeing the following characteristics:
database files have about 10-15% free space every night.
log files however are about 70-90% free for all DBs
Now this is a problem since the log file size is 10gb and its using
only 2-3gb of it.
I tried using DBCC shrinkfile TRUNCATEONLY however it not only freed
the space but also reduced the log filesize to 0 and also one of my
developers complained of a table getting truncated during a stored proc
execution at that time.
1. is DBCC shrinkfile a good way to truncate free space from logs? How
could it have truncated the log to a size of 0.
2. What would be a good strategy to free up unused space from DB and
Log files every day?
Thanks all!Hi
Good stuff to start
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
<theonlyrashid@.gmail.com> wrote in message
news:1128600308.204142.67240@.g44g2000cwa.googlegroups.com...
> Hi All,
> I have a bunch of SQL servers that have a high volume of Bulk Inserts
> happening every day (Millions of records per insert)
> The most heavily used server has DBs in Simple Recovery Mode. However
> both for the database and log files which are set to autogrow 10%, I am
> seeing the following characteristics:
> database files have about 10-15% free space every night.
> log files however are about 70-90% free for all DBs
> Now this is a problem since the log file size is 10gb and its using
> only 2-3gb of it.
> I tried using DBCC shrinkfile TRUNCATEONLY however it not only freed
> the space but also reduced the log filesize to 0 and also one of my
> developers complained of a table getting truncated during a stored proc
> execution at that time.
> 1. is DBCC shrinkfile a good way to truncate free space from logs? How
> could it have truncated the log to a size of 0.
> 2. What would be a good strategy to free up unused space from DB and
> Log files every day?
> Thanks all!
>

Bench Mark on Performance

Hi
I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
I understand that the profiler looks at the logical reads and physical
writes.
What I'm trying to understand is what people use as a guide as to
whether something should be dedicated or if it could be shared on an
active/passive clustered environment.
If I'm recieving a million reads per hour, they are logical, hence in
the buffer cache, but does that mean the server is performing well? I
know that it's not always down to just read and writes but that is the
essence of a db system... so that's where I start.
Your thoughts would be most appreciated.
SimonHi
I'd also look at Performance Monitor to examine PhysicalDisk Object: Avg.
Disk Queue Length
http://www.sql-server-performance.com/performance_monitor_counters_io.asp
Per a query you can use SET STATISTICS IO command
When looking at individual queries ,the Logical Reads is more interesting
because the ibfo is consistent
Pay close attention as well to chache-hit ratio. (>=98%)
<simonaarons@.yahoo.co.uk> wrote in message
news:1129024856.929599.263150@.o13g2000cwo.googlegroups.com...
> Hi
> I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
> I understand that the profiler looks at the logical reads and physical
> writes.
> What I'm trying to understand is what people use as a guide as to
> whether something should be dedicated or if it could be shared on an
> active/passive clustered environment.
> If I'm recieving a million reads per hour, they are logical, hence in
> the buffer cache, but does that mean the server is performing well? I
> know that it's not always down to just read and writes but that is the
> essence of a db system... so that's where I start.
> Your thoughts would be most appreciated.
> Simon
>|||Simon,
This is where a benchmark for your system would help. This would provide
you with how the system operates during normal operations. The performance
values derived from the benchmark can be compared against over time to
establish changes in the environment and to proactively predict future HW
upgrades/additions.
HTH
Jerry
<simonaarons@.yahoo.co.uk> wrote in message
news:1129024856.929599.263150@.o13g2000cwo.googlegroups.com...
> Hi
> I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
> I understand that the profiler looks at the logical reads and physical
> writes.
> What I'm trying to understand is what people use as a guide as to
> whether something should be dedicated or if it could be shared on an
> active/passive clustered environment.
> If I'm recieving a million reads per hour, they are logical, hence in
> the buffer cache, but does that mean the server is performing well? I
> know that it's not always down to just read and writes but that is the
> essence of a db system... so that's where I start.
> Your thoughts would be most appreciated.
> Simon
>

Bench Mark on Performance

Hi
I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
I understand that the profiler looks at the logical reads and physical
writes.
What I'm trying to understand is what people use as a guide as to
whether something should be dedicated or if it could be shared on an
active/passive clustered environment.
If I'm recieving a million reads per hour, they are logical, hence in
the buffer cache, but does that mean the server is performing well? I
know that it's not always down to just read and writes but that is the
essence of a db system... so that's where I start.
Your thoughts would be most appreciated.
Simon
Hi
I'd also look at Performance Monitor to examine PhysicalDisk Object: Avg.
Disk Queue Length
http://www.sql-server-performance.co...ounters_io.asp
Per a query you can use SET STATISTICS IO command
When looking at individual queries ,the Logical Reads is more interesting
because the ibfo is consistent
Pay close attention as well to chache-hit ratio. (>=98%)
<simonaarons@.yahoo.co.uk> wrote in message
news:1129024856.929599.263150@.o13g2000cwo.googlegr oups.com...
> Hi
> I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
> I understand that the profiler looks at the logical reads and physical
> writes.
> What I'm trying to understand is what people use as a guide as to
> whether something should be dedicated or if it could be shared on an
> active/passive clustered environment.
> If I'm recieving a million reads per hour, they are logical, hence in
> the buffer cache, but does that mean the server is performing well? I
> know that it's not always down to just read and writes but that is the
> essence of a db system... so that's where I start.
> Your thoughts would be most appreciated.
> Simon
>
|||Simon,
This is where a benchmark for your system would help. This would provide
you with how the system operates during normal operations. The performance
values derived from the benchmark can be compared against over time to
establish changes in the environment and to proactively predict future HW
upgrades/additions.
HTH
Jerry
<simonaarons@.yahoo.co.uk> wrote in message
news:1129024856.929599.263150@.o13g2000cwo.googlegr oups.com...
> Hi
> I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
> I understand that the profiler looks at the logical reads and physical
> writes.
> What I'm trying to understand is what people use as a guide as to
> whether something should be dedicated or if it could be shared on an
> active/passive clustered environment.
> If I'm recieving a million reads per hour, they are logical, hence in
> the buffer cache, but does that mean the server is performing well? I
> know that it's not always down to just read and writes but that is the
> essence of a db system... so that's where I start.
> Your thoughts would be most appreciated.
> Simon
>

Bench Mark on Performance

Hi
I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
I understand that the profiler looks at the logical reads and physical
writes.
What I'm trying to understand is what people use as a guide as to
whether something should be dedicated or if it could be shared on an
active/passive clustered environment.
If I'm recieving a million reads per hour, they are logical, hence in
the buffer cache, but does that mean the server is performing well? I
know that it's not always down to just read and writes but that is the
essence of a db system... so that's where I start.
Your thoughts would be most appreciated.
SimonHi
I'd also look at Performance Monitor to examine PhysicalDisk Object: Avg.
Disk Queue Length
http://www.sql-server-performance.c...counters_io.asp
Per a query you can use SET STATISTICS IO command
When looking at individual queries ,the Logical Reads is more interesting
because the ibfo is consistent
Pay close attention as well to chache-hit ratio. (>=98%)
<simonaarons@.yahoo.co.uk> wrote in message
news:1129024856.929599.263150@.o13g2000cwo.googlegroups.com...
> Hi
> I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
> I understand that the profiler looks at the logical reads and physical
> writes.
> What I'm trying to understand is what people use as a guide as to
> whether something should be dedicated or if it could be shared on an
> active/passive clustered environment.
> If I'm recieving a million reads per hour, they are logical, hence in
> the buffer cache, but does that mean the server is performing well? I
> know that it's not always down to just read and writes but that is the
> essence of a db system... so that's where I start.
> Your thoughts would be most appreciated.
> Simon
>|||Simon,
This is where a benchmark for your system would help. This would provide
you with how the system operates during normal operations. The performance
values derived from the benchmark can be compared against over time to
establish changes in the environment and to proactively predict future HW
upgrades/additions.
HTH
Jerry
<simonaarons@.yahoo.co.uk> wrote in message
news:1129024856.929599.263150@.o13g2000cwo.googlegroups.com...
> Hi
> I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
> I understand that the profiler looks at the logical reads and physical
> writes.
> What I'm trying to understand is what people use as a guide as to
> whether something should be dedicated or if it could be shared on an
> active/passive clustered environment.
> If I'm recieving a million reads per hour, they are logical, hence in
> the buffer cache, but does that mean the server is performing well? I
> know that it's not always down to just read and writes but that is the
> essence of a db system... so that's where I start.
> Your thoughts would be most appreciated.
> Simon
>