Multiple Files for database

So, due to some oversights in planning, I've got a 200gb database that
is working from 1 file. Performance is remarkeably good(fast server &
disks), but we can't defrag the drive since there's not 200gb of free
space. Regardless of that problem, it seems it would be wise to have
this database spread across multiple files. Is there any way to do
this? I realize I can add files to the existing database, but how do I
get the first file to shrink?
Ben Hanson wrote:
> So, due to some oversights in planning, I've got a 200gb database that
> is working from 1 file. Performance is remarkeably good(fast server &
> disks), but we can't defrag the drive since there's not 200gb of free
> space. Regardless of that problem, it seems it would be wise to have
> this database spread across multiple files. Is there any way to do
> this? I realize I can add files to the existing database, but how do
> I get the first file to shrink?
Is the file full of data or is there empty space? If there's sufficient
empty space, you can use DBCC SHRINKFILE to recover disk space. It may
take some time to run, so best to do this off-hours. If you just need to
move tables, you can create the new database file and recreate the
clustered indexes on the new data file for those tables you want to
move.
What's your drive array setup?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||On 2005-06-16 13:35:42 -0800, "David Gugick"
<david.gugick-nospam@.quest.com> said:

> Ben Hanson wrote:
> Is the file full of data or is there empty space? If there's sufficient
> empty space, you can use DBCC SHRINKFILE to recover disk space. It may
> take some time to run, so best to do this off-hours. If you just need
> to move tables, you can create the new database file and recreate the
> clustered indexes on the new data file for those tables you want to
> move.
> What's your drive array setup?
Details:
Win2k3 server running MSSQL server 2000 SP3
400gb RAID5, 4 x 146gb - Data
36gb RAID1, 2 x 36gb - System
This is a GIS server running ArcSDE. SQL is the database backend for
SDE in this case. The database is mostly read intensive, with few
writes/changes. There are two databases, one for vector data(300mb
used, 20gb allocated), and one for raster data(186gb used, 190gb
allocated). I just did a shrink database to free up 30gb of unused
space on the raster database.
What I'm trying to figure out is how to move from one database:one
massive file to one database:multiple small files. I can't split to
new databases without breaking current SDE connections which are
database specific.
This also begs the question, In a database with multiple files in one
file group, how does SQL decide which one to write to?
|||> What I'm trying to figure out is how to move from one database:one massive file to one
> database:multiple small files.
You can't do that without some free space. You can add a new file and shrink the original file.
Well, thinking about it, it is *possible* that the shrink can at the same time autogrow the other
file. But I believe that shrink doesn't shrink the physical file size until the very end, after
moving of the data. If that is the case, you need free space:
Add new file.
Shrink original file so that SQL server pushes the data to the new file.

> This also begs the question, In a database with multiple files in one file group, how does SQL
> decide which one to write to?
The one with more free space.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Hanson" <benhanson@.borough.kenai.ak.us> wrote in message
news:2005061615392575249%benhanson@.boroughkenaiaku s...
> On 2005-06-16 13:35:42 -0800, "David Gugick" <david.gugick-nospam@.quest.com> said:
>
> Details:
> Win2k3 server running MSSQL server 2000 SP3
> 400gb RAID5, 4 x 146gb - Data
> 36gb RAID1, 2 x 36gb - System
> This is a GIS server running ArcSDE. SQL is the database backend for SDE in this case. The
> database is mostly read intensive, with few writes/changes. There are two databases, one for
> vector data(300mb used, 20gb allocated), and one for raster data(186gb used, 190gb allocated). I
> just did a shrink database to free up 30gb of unused space on the raster database.
> What I'm trying to figure out is how to move from one database:one massive file to one
> database:multiple small files. I can't split to new databases without breaking current SDE
> connections which are database specific.
> This also begs the question, In a database with multiple files in one file group, how does SQL
> decide which one to write to?
>

Multiple Files for database

So, due to some oversights in planning, I've got a 200gb database that
is working from 1 file. Performance is remarkeably good(fast server &
disks), but we can't defrag the drive since there's not 200gb of free
space. Regardless of that problem, it seems it would be wise to have
this database spread across multiple files. Is there any way to do
this? I realize I can add files to the existing database, but how do I
get the first file to shrink?Ben Hanson wrote:
> So, due to some oversights in planning, I've got a 200gb database that
> is working from 1 file. Performance is remarkeably good(fast server &
> disks), but we can't defrag the drive since there's not 200gb of free
> space. Regardless of that problem, it seems it would be wise to have
> this database spread across multiple files. Is there any way to do
> this? I realize I can add files to the existing database, but how do
> I get the first file to shrink?
Is the file full of data or is there empty space? If there's sufficient
empty space, you can use DBCC SHRINKFILE to recover disk space. It may
take some time to run, so best to do this off-hours. If you just need to
move tables, you can create the new database file and recreate the
clustered indexes on the new data file for those tables you want to
move.
What's your drive array setup?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On 2005-06-16 13:35:42 -0800, "David Gugick"
<david.gugick-nospam@.quest.com> said:

> Ben Hanson wrote:
> Is the file full of data or is there empty space? If there's sufficient
> empty space, you can use DBCC SHRINKFILE to recover disk space. It may
> take some time to run, so best to do this off-hours. If you just need
> to move tables, you can create the new database file and recreate the
> clustered indexes on the new data file for those tables you want to
> move.
> What's your drive array setup?
Details:
Win2k3 server running MSSQL server 2000 SP3
400gb RAID5, 4 x 146gb - Data
36gb RAID1, 2 x 36gb - System
This is a GIS server running ArcSDE. SQL is the database backend for
SDE in this case. The database is mostly read intensive, with few
writes/changes. There are two databases, one for vector data(300mb
used, 20gb allocated), and one for raster data(186gb used, 190gb
allocated). I just did a shrink database to free up 30gb of unused
space on the raster database.
What I'm trying to figure out is how to move from one database:one
massive file to one database:multiple small files. I can't split to
new databases without breaking current SDE connections which are
database specific.
This also begs the question, In a database with multiple files in one
file group, how does SQL decide which one to write to?|||> What I'm trying to figure out is how to move from one database:one massive file to one[vbc
ol=seagreen]
> database:multiple small files.[/vbcol]
You can't do that without some free space. You can add a new file and shrink
the original file.
Well, thinking about it, it is *possible* that the shrink can at the same ti
me autogrow the other
file. But I believe that shrink doesn't shrink the physical file size until
the very end, after
moving of the data. If that is the case, you need free space:
Add new file.
Shrink original file so that SQL server pushes the data to the new file.

> This also begs the question, In a database with multiple files in one file
group, how does SQL
> decide which one to write to?
The one with more free space.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Hanson" <benhanson@.borough.kenai.ak.us> wrote in message
news:2005061615392575249%benhanson@.borou
ghkenaiakus...
> On 2005-06-16 13:35:42 -0800, "David Gugick" <david.gugick-nospam@.quest.co
m> said:
>
>
> Details:
> Win2k3 server running MSSQL server 2000 SP3
> 400gb RAID5, 4 x 146gb - Data
> 36gb RAID1, 2 x 36gb - System
> This is a GIS server running ArcSDE. SQL is the database backend for SDE
in this case. The
> database is mostly read intensive, with few writes/changes. There are two
databases, one for
> vector data(300mb used, 20gb allocated), and one for raster data(186gb use
d, 190gb allocated). I
> just did a shrink database to free up 30gb of unused space on the raster d
atabase.
> What I'm trying to figure out is how to move from one database:one massive
file to one
> database:multiple small files. I can't split to new databases without bre
aking current SDE
> connections which are database specific.
> This also begs the question, In a database with multiple files in one file
group, how does SQL
> decide which one to write to?
>

Multiple Files for database

So, due to some oversights in planning, I've got a 200gb database that
is working from 1 file. Performance is remarkeably good(fast server &
disks), but we can't defrag the drive since there's not 200gb of free
space. Regardless of that problem, it seems it would be wise to have
this database spread across multiple files. Is there any way to do
this? I realize I can add files to the existing database, but how do I
get the first file to shrink?Ben Hanson wrote:
> So, due to some oversights in planning, I've got a 200gb database that
> is working from 1 file. Performance is remarkeably good(fast server &
> disks), but we can't defrag the drive since there's not 200gb of free
> space. Regardless of that problem, it seems it would be wise to have
> this database spread across multiple files. Is there any way to do
> this? I realize I can add files to the existing database, but how do
> I get the first file to shrink?
Is the file full of data or is there empty space? If there's sufficient
empty space, you can use DBCC SHRINKFILE to recover disk space. It may
take some time to run, so best to do this off-hours. If you just need to
move tables, you can create the new database file and recreate the
clustered indexes on the new data file for those tables you want to
move.
What's your drive array setup?
David Gugick
Quest Software
www.imceda.com
www.quest.com