Database retains size

Hello,
I have this 10 GB size database and unfortunately i'm running out of space
in my HDD. I'm using SQL Server 2000.
before i use dbcc shrink file on the database data file, the result of
sp_spaceused are the following:
db_name database_size unallocated space
abc 10881.70 MB -2526.48 MB
After I use: DBCC SHRINKFILE (abc_data, 10000)
I reduce the data file to aprox 10000, that is 800 Megs.
That all works fine, but next day as I check my DB the database size returns
to it's original size.
There's no way that in one night 800 megs of data is inserted in the
database.
I execute DBCC Reindex on 2 tables at night and do some backups..
Do you have any idea of what's maybe happening?
Any tip for regain some space?
Thanks
Eduardo SicouretWhat growth rate do you have specified in the database properties?|||What recovery model is the database in?
You can set up a trace to see when the database is growing. There are
database and log file growth events.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Eduardo Sicouret" <esicouret> wrote in message
news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
Hello,
I have this 10 GB size database and unfortunately i'm running out of space
in my HDD. I'm using SQL Server 2000.
before i use dbcc shrink file on the database data file, the result of
sp_spaceused are the following:
db_name database_size unallocated space
abc 10881.70 MB -2526.48 MB
After I use: DBCC SHRINKFILE (abc_data, 10000)
I reduce the data file to aprox 10000, that is 800 Megs.
That all works fine, but next day as I check my DB the database size returns
to it's original size.
There's no way that in one night 800 megs of data is inserted in the
database.
I execute DBCC Reindex on 2 tables at night and do some backups..
Do you have any idea of what's maybe happening?
Any tip for regain some space?
Thanks
Eduardo Sicouret|||File growth is automatically 64 megs at a time.
ESB
"Tracy McKibben" <tracy.mckibben@.gmail.com> escribi en el mensaje
news:1149779303.783251.118980@.j55g2000cwa.googlegroups.com...
> What growth rate do you have specified in the database properties?
>|||Recovery model is FULL and Autoshrink, Auto update statistics, auto create
statistics are checked
ESB
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escribi en el mensaje
news:%23zoKn2wiGHA.4776@.TK2MSFTNGP05.phx.gbl...
> What recovery model is the database in?
> You can set up a trace to see when the database is growing. There are
> database and log file growth events.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have this 10 GB size database and unfortunately i'm running out of space
> in my HDD. I'm using SQL Server 2000.
> before i use dbcc shrink file on the database data file, the result of
> sp_spaceused are the following:
> db_name database_size unallocated space
> abc 10881.70 MB -2526.48 MB
> After I use: DBCC SHRINKFILE (abc_data, 10000)
> I reduce the data file to aprox 10000, that is 800 Megs.
> That all works fine, but next day as I check my DB the database size
> returns
> to it's original size.
> There's no way that in one night 800 megs of data is inserted in the
> database.
> I execute DBCC Reindex on 2 tables at night and do some backups..
> Do you have any idea of what's maybe happening?
> Any tip for regain some space?
> Thanks
> Eduardo Sicouret
>
>|||I bet the reindex job is leaving unused space in your db. Run DBCC
UPDATEUSAGE after rebuilding the indexes and see if that helps. Ken.
Eduardo Sicouret wrote:[vbcol=seagreen]
> Recovery model is FULL and Autoshrink, Auto update statistics, auto create
> statistics are checked
> ESB
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escribi=F3 en el mensaje
> news:%23zoKn2wiGHA.4776@.TK2MSFTNGP05.phx.gbl...
ace[vbcol=seagreen]|||How big are the tables you're reindexing, and are there clustered
indexes on them? If these are large tables with clustered indexes, the
reindex operation is likely causing the database to grow.
Eduardo Sicouret wrote:[vbcol=seagreen]
> File growth is automatically 64 megs at a time.
> ESB
> "Tracy McKibben" <tracy.mckibben@.gmail.com> escribi=F3 en el mensaje
> news:1149779303.783251.118980@.j55g2000cwa.googlegroups.com...|||You shrink the file, then reindex which need a lot of working space which is
why the size blows up.
If you shrink again, the reindex is basically undone again. Bottom line is t
hat if you want best
performance, you need this working space. Some extra 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/
"Eduardo Sicouret" <esicouret> wrote in message news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl..
.
> Hello,
> I have this 10 GB size database and unfortunately i'm running out of space
in my HDD. I'm using
> SQL Server 2000.
> before i use dbcc shrink file on the database data file, the result of sp_
spaceused are the
> following:
> db_name database_size unallocated space
> abc 10881.70 MB -2526.48 MB
> After I use: DBCC SHRINKFILE (abc_data, 10000)
> I reduce the data file to aprox 10000, that is 800 Megs.
> That all works fine, but next day as I check my DB the database size retur
ns to it's original
> size.
> There's no way that in one night 800 megs of data is inserted in the datab
ase.
> I execute DBCC Reindex on 2 tables at night and do some backups..
> Do you have any idea of what's maybe happening?
> Any tip for regain some space?
> Thanks
> Eduardo Sicouret
>|||Should also probably point out that all this
shrinking/growing/shrinking/growing is going to severly fragment the
database file, eventually hurting performance.
Tibor Karaszi wrote:[vbcol=seagreen]
> You shrink the file, then reindex which need a lot of working space which
is why the size blows up.
> If you shrink again, the reindex is basically undone again. Bottom line is
that if you want best
> performance, you need this working space. Some extra 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/
>
> "Eduardo Sicouret" <esicouret> wrote in message news:esf4dxwiGHA.3848@.TK2M
SFTNGP04.phx.gbl...|||"Eduardo Sicouret" <esicouret> wrote in message
news:%234bwfGxiGHA.4372@.TK2MSFTNGP03.phx.gbl...
> Recovery model is FULL and Autoshrink, Auto update statistics, auto create
> statistics are checked
>
I'd disable Autoshrink btw.
Growing and shrinking your DB can lead to disk level fragmentation.