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!
>

best bulk insert command

I have a 5 million row table that gets truncated and new values get imported
.
The new values are obtained by values that have changed in other tables.
Does anyone know the quickest way this can be acheived.
I have tried this took around 34mins
insert in attritable (attrivalue,attri_id,desc)
exec sp_insert
then
Theres a job that bcps the values out to text file in batches of 5000 ..then
inserts them into the table again in batches of 5000 and this takes around 2
8
mins.
Even though BCP is quicker it seems a waste to do it this way and more prone
to errors....Is BCP definately the quickest way to enter data this way does
anyone know'
Thanks for any help or suggestions
Sammy> insert in attritable (attrivalue,attri_id,desc)
> exec sp_insert
Instead of returning a result set that you insert, consider changing
sp_insert to create the new table with SELECT ... INTO and then create
constraints and indexes.

> Even though BCP is quicker it seems a waste to do it this way and more
> prone
> to errors....Is BCP definately the quickest way to enter data this way
> does
> anyone know'
Bulk Insert methods like command-line BCP, Transact-SQL BULK INSERT, DTS and
bulk copy APIs are the fastest way to get external data into SQL Server.

> Theres a job that bcps the values out to text file in batches of 5000
> ..then
> inserts them into the table again in batches of 5000 and this takes around
> 28
> mins.
This calculates to about 3000 rows per second. Not as fast as I would
expect with a narrow table on modern hardware (10,000+) but a lot depends
the size of your data and the kind of indexes you have on the table. You
may find it faster to drop indexes and recreate afterward. See Optimizing
Data Loads at
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx.[/url
]
Hope this helps.
Dan Guzman
SQL Server MVP
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:0B51C58B-7E10-4905-98CB-74CAA211622B@.microsoft.com...
>I have a 5 million row table that gets truncated and new values get
>imported.
> The new values are obtained by values that have changed in other tables.
>
> Does anyone know the quickest way this can be acheived.
> I have tried this took around 34mins
> insert in attritable (attrivalue,attri_id,desc)
> exec sp_insert
> then
> Theres a job that bcps the values out to text file in batches of 5000
> ..then
> inserts them into the table again in batches of 5000 and this takes around
> 28
> mins.
> Even though BCP is quicker it seems a waste to do it this way and more
> prone
> to errors....Is BCP definately the quickest way to enter data this way
> does
> anyone know'
> Thanks for any help or suggestions
> Sammy
>
>
>
>
>
>
>
>