auto-shrink on a write-once read-many daily recreated db?

Hi,
I have an application that feeds a mssql2000sp3 server
from 1 a.m. to 4 a.m. from monday to friday. It totally emptyies
and then re-populates (using bulk inserts) a 3 GB db (with just one
main table occupying 98% of it).
Then, during the days that db is accessed at 99%
only with a lot of read request (in most cases for this kind of
usage I use mysql, but this time I were forced to use
mssql server). For many reasons I cannot have the db dropped
and re-created from scratch. My problem is
that, in just 3 days, the transaction log wastes more than 6 GB
(reaching the disk space limit, but "effectively using" just 50
MB of it). I read in many posts that setting the auto-shrink flag
may reduce performance and this'll be an issue for me.
Does auto-shrink option imply very bad
performance even for such a "quite read-only" db?
Or may I set the auto-shrink flag for this db (and maybe
changing autoshrink frequency to something like 4 hours - I don't
know if it's possible and how to do it)?
In case I can change the db usage and prevent ANY db writing during
the working hours, may I expect better performance enabling the
read-only flag?
Thanks in advance for your help!
bye,
PiErre
This behavior of kill and fill is common in reporting or data warehouse
databases. Check your recovery mode on the database. Change it from full
to either simple or bulk logged. Full records every change in the
transaction log and while it can be recovered to the last transaction log
backup it takes a toll on the transaction log. If that doesn't save you
enough space, run a SQL script once per day after the load that truncates
the transaction log and shrinks the file.
<siggy2@.supereva.it> wrote in message
news:1130237889.329371.259120@.f14g2000cwb.googlegr oups.com...
> Hi,
> I have an application that feeds a mssql2000sp3 server
> from 1 a.m. to 4 a.m. from monday to friday. It totally emptyies
> and then re-populates (using bulk inserts) a 3 GB db (with just one
> main table occupying 98% of it).
> Then, during the days that db is accessed at 99%
> only with a lot of read request (in most cases for this kind of
> usage I use mysql, but this time I were forced to use
> mssql server). For many reasons I cannot have the db dropped
> and re-created from scratch. My problem is
> that, in just 3 days, the transaction log wastes more than 6 GB
> (reaching the disk space limit, but "effectively using" just 50
> MB of it). I read in many posts that setting the auto-shrink flag
> may reduce performance and this'll be an issue for me.
> Does auto-shrink option imply very bad
> performance even for such a "quite read-only" db?
> Or may I set the auto-shrink flag for this db (and maybe
> changing autoshrink frequency to something like 4 hours - I don't
> know if it's possible and how to do it)?
> In case I can change the db usage and prevent ANY db writing during
> the working hours, may I expect better performance enabling the
> read-only flag?
> Thanks in advance for your help!
> bye,
> PiErre
>
|||Yes, autoshrink is bad for that scenario. You don't know when the shrink will kick in, and it might
do some shuffling of data even if it is futile. In short, no control.
1. Run in simple recovery, and make sure that your imports are minimally logged. Drop the indexes,
add data, add the indexes.
2. If you *really* need to shrink after this, do it after the import (in between it is no meaning as
you don't modify data). Use DBCC SHRINKFILE and target only the log file.
Additional info here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<siggy2@.supereva.it> wrote in message news:1130237889.329371.259120@.f14g2000cwb.googlegr oups.com...
> Hi,
> I have an application that feeds a mssql2000sp3 server
> from 1 a.m. to 4 a.m. from monday to friday. It totally emptyies
> and then re-populates (using bulk inserts) a 3 GB db (with just one
> main table occupying 98% of it).
> Then, during the days that db is accessed at 99%
> only with a lot of read request (in most cases for this kind of
> usage I use mysql, but this time I were forced to use
> mssql server). For many reasons I cannot have the db dropped
> and re-created from scratch. My problem is
> that, in just 3 days, the transaction log wastes more than 6 GB
> (reaching the disk space limit, but "effectively using" just 50
> MB of it). I read in many posts that setting the auto-shrink flag
> may reduce performance and this'll be an issue for me.
> Does auto-shrink option imply very bad
> performance even for such a "quite read-only" db?
> Or may I set the auto-shrink flag for this db (and maybe
> changing autoshrink frequency to something like 4 hours - I don't
> know if it's possible and how to do it)?
> In case I can change the db usage and prevent ANY db writing during
> the working hours, may I expect better performance enabling the
> read-only flag?
> Thanks in advance for your help!
> bye,
> PiErre
>
|||First of all: thanks to all for the quick and helpful answers
Tibor Karaszi ha scritto:

> Yes, autoshrink is bad for that scenario. You don't know when
> the shrink will kick in, and it might
> do some shuffling of data even if it is futile.
> In short, no control.
ok - good to know it!

> 1. Run in simple recovery, and make sure that your
> imports are minimally logged. Drop the indexes,
> add data, add the indexes.
forget to mention I already did it that way (gh!)

> 2. If you *really* need to shrink after this, do it after
> the import (in between it is no meaning as
> you don't modify data). Use DBCC SHRINKFILE and target
> only the log file.
ok - I'm testing this solution...

> Additional info here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
already read it... but thank you anyway...
bye,
PiErre