Prev: SQL 2005 Query Response Time
Next: deleting backup history
From: John230873 on 19 Dec 2009 05:42
I understand shrinkfile will cause fragmentation but I have read mixed psots
about using the "emptyfile option". My understanding is that it reads from
the front of the file and not the back like a normal shrink.

Is someone aware if this is bad, the reason for me using this option is
because we have a 24by 7 DB which is running out of space on one logical
drive so I have created another logical and would like to split half our
files to that drive (currently we have 13 data file on the one drive).


From: Erland Sommarskog on 19 Dec 2009 09:50
John230873 (John230873(a)discussions.microsoft.com) writes:
> I understand shrinkfile will cause fragmentation but I have read mixed
> psots about using the "emptyfile option". My understanding is that it
> reads from the front of the file and not the back like a normal shrink.
>
> Is someone aware if this is bad, the reason for me using this option is
> because we have a 24by 7 DB which is running out of space on one logical
> drive so I have created another logical and would like to split half our
> files to that drive (currently we have 13 data file on the one drive).

I have not used EMPTYFILE much, but since it moves the data from the
other files, SQL Server has to put the tables where there is space, so
it seems reasonable to assume that there will be some fragmentation at
least.

When in doubt - test first.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: SQL 2005 Query Response Time
Next: deleting backup history