Andrew J. Kelly please

Since you are so helpful, here is another question regarding my original
post yesterday:
************************************************** ******************
My database has one data file only, and the data file was 900M of data after
I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space so
it does not have to expand in a while; I ran DBCC
DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
clustered index). I was expecting the database to expand 900 X 10% = 90M.
However, when it completed, the data file became 1.9G in total. When I view
the database from EM (taskpad view), looks like 50% free space. Why? Thanks.
************************************************** ******************
Question:
I actually have 100+ such databases on the server and it is running out of
space. I would like to somehow get some free space back to OS (leaving
10-20% of free space in data files instead of 50% right now). And we also
want to re-org data files as we have lots of insert/delete/updates going on
daily. Since DBCC DBREINDEX will not release free space, what are my
options? I don't care about log file growth as they are in seperate RAID
with plenty of spaces. Thank you very much.
John
Im not Andrew Kelly, but up until he replies, take a loot at DBCC Shrinkfile
and have your data files in there as parameters.
"john" <john@.aic.com> wrote in message
news:un9R5ZabEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Since you are so helpful, here is another question regarding my original
> post yesterday:
> ************************************************** ******************
> My database has one data file only, and the data file was 900M of data
after
> I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space
so
> it does not have to expand in a while; I ran DBCC
> DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
> clustered index). I was expecting the database to expand 900 X 10% = 90M.
> However, when it completed, the data file became 1.9G in total. When I
view
> the database from EM (taskpad view), looks like 50% free space. Why?
Thanks.
> ************************************************** ******************
> Question:
> I actually have 100+ such databases on the server and it is running out of
> space. I would like to somehow get some free space back to OS (leaving
> 10-20% of free space in data files instead of 50% right now). And we also
> want to re-org data files as we have lots of insert/delete/updates going
on
> daily. Since DBCC DBREINDEX will not release free space, what are my
> options? I don't care about log file growth as they are in seperate RAID
> with plenty of spaces. Thank you very much.
> John
>
>
|||Well to shrink only the data file you should use DBCC SHRINKFILE and specify
the appropriate parameters. But as noted in the earlier posts this will
undoubtedly fragment your existing tables again. If you shrink it to only
have 10 - 20% free space when you run DBREINDEX it will most likely expand
again. Remember the rule is 1.2 times the size of the object in free space.
Ideally you want that free space to be one big contiguous space on the drive
but it is unlikely to be that way with minimal free space all the time.
Even with 50% free it is most likely scattered and interleaved amongst all
the objects throughout the file. So the less free space you have the more
likely the newly defragmented object will still be interleaved amongst the
other objects but you will cut down on Logical fragmentation. If you have a
lot of Inserts and Updates that cause page splits and fragmentation you
should consider lowering the fill factor to minimize this effect and
increase the time between reindexing. What your asking for is your cake and
the ability to eat it too<g>. Having lots of free space in lots of DB's
does add up to lots of disk space and I can see why you want to cut it down.
But the less free space you have the more likely you will eventually wind up
with no room on the disk when you need to reindex and the file is forced to
grow. The real answer is not what anyone wants to hear and that is to get
more disk space. But in the mean time you can do one of several things to
see if it works for you. One is to only reindex your crucial tables or ones
that have high fragmentation to begin with vs. all the tables. You can also
try using DBCC INDEXDEFRAG instead of REINDEX.
Andrew J. Kelly SQL MVP
"john" <john@.aic.com> wrote in message
news:un9R5ZabEHA.3752@.TK2MSFTNGP12.phx.gbl...
> Since you are so helpful, here is another question regarding my original
> post yesterday:
> ************************************************** ******************
> My database has one data file only, and the data file was 900M of data
after
> I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space
so
> it does not have to expand in a while; I ran DBCC
> DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
> clustered index). I was expecting the database to expand 900 X 10% = 90M.
> However, when it completed, the data file became 1.9G in total. When I
view
> the database from EM (taskpad view), looks like 50% free space. Why?
Thanks.
> ************************************************** ******************
> Question:
> I actually have 100+ such databases on the server and it is running out of
> space. I would like to somehow get some free space back to OS (leaving
> 10-20% of free space in data files instead of 50% right now). And we also
> want to re-org data files as we have lots of insert/delete/updates going
on
> daily. Since DBCC DBREINDEX will not release free space, what are my
> options? I don't care about log file growth as they are in seperate RAID
> with plenty of spaces. Thank you very much.
> John
>
>
|||Thank you very much. Really appreciated.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ekL2m$cbEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Well to shrink only the data file you should use DBCC SHRINKFILE and
specify
> the appropriate parameters. But as noted in the earlier posts this will
> undoubtedly fragment your existing tables again. If you shrink it to only
> have 10 - 20% free space when you run DBREINDEX it will most likely expand
> again. Remember the rule is 1.2 times the size of the object in free
space.
> Ideally you want that free space to be one big contiguous space on the
drive
> but it is unlikely to be that way with minimal free space all the time.
> Even with 50% free it is most likely scattered and interleaved amongst all
> the objects throughout the file. So the less free space you have the more
> likely the newly defragmented object will still be interleaved amongst the
> other objects but you will cut down on Logical fragmentation. If you have
a
> lot of Inserts and Updates that cause page splits and fragmentation you
> should consider lowering the fill factor to minimize this effect and
> increase the time between reindexing. What your asking for is your cake
and
> the ability to eat it too<g>. Having lots of free space in lots of DB's
> does add up to lots of disk space and I can see why you want to cut it
down.
> But the less free space you have the more likely you will eventually wind
up
> with no room on the disk when you need to reindex and the file is forced
to
> grow. The real answer is not what anyone wants to hear and that is to get
> more disk space. But in the mean time you can do one of several things to
> see if it works for you. One is to only reindex your crucial tables or
ones
> that have high fragmentation to begin with vs. all the tables. You can
also[vbcol=seagreen]
> try using DBCC INDEXDEFRAG instead of REINDEX.
> --
> Andrew J. Kelly SQL MVP
>
> "john" <john@.aic.com> wrote in message
> news:un9R5ZabEHA.3752@.TK2MSFTNGP12.phx.gbl...
> after
space[vbcol=seagreen]
> so
90M.[vbcol=seagreen]
> view
> Thanks.
of[vbcol=seagreen]
also
> on
>