How to span a database to another drive 1

[andreis]

Hello, we have a SQL Serer 2005 database with less than 3GB of space left on a 100GB partition.
We need to give it more space but it’s on a cluster so that makes it multiple times more difficult to increase the size of the disk. Any recommendations? Thanks.

 

[hmckillop]

The assumption I have is that you are storing all your data in a single physical file on the primary filegroup.

Look at creating new filegroups and files, setting the new files onto the new partition. You will need to define which objects you wish to place in the new file group and ideally will move some of the existing objects into new filegroups and files.

there are many articles on it and I have had the pleasure of doing this on a large scale database.

have a look at a few of this link, its a good starting point

http://www.mssqltips.com/tip.asp?tip=1112

"I'm living so far beyond my income that we may almost be said to be living apart

 

[andreis]

hmckillop, thanks, one of my fears is that I may lose some foreign keys, or they won't even let me to move a table... Is this an issue at all?

 

[hmckillop]

it does become a data migration exercise as you are physically moving data, but since the schema is the same there should be no issue.

You will need to drop FKs and recreate them etc, but if you use a schema comparison tool post implementation (e.g. redgate data compare) this should alleviate any business concern.

The way I see it, they dont have any choice because you will run out of space soon and everything will stop working.

The only other option is that you archive data, e.g. if your DB contains historic data which is not required through your application but you need to store in a DB, you can generate an exact schema replica (e.g. where you have table TABLEA on primary filegroup on your first data file, create a new table ARC_TABLEA on the new filegroup on the second partition file. Move all data which meets your archive rules.)

There are many things you could do, but ultimately splitting the data is going to need to happen if you cant increase the disk size. (Its also much better for performance )



"I'm living so far beyond my income that we may almost be said to be living apart

 

[andreis]

our situation is even worse - one image table takes 80GB out of DB's 95Gb. We can't split a table, can we?

 

[andreis]

I also noticed that sp_spaceused shows that 40GB ot of 95GB taken by this table are marked by the sp as unused. Is there a way to reclaim this space? Reorganize clustered index maybe?

 

[hmckillop]

You can split a table into seperate filegroups, but this is slightly more complex in that it is partitioning data.
Again, i have completed a similiar task,

There is a good article here describing exactly what you need

http://www.databasejournal.com/feat...-partitioning-in-SQL-Server-2005---Part-I.htm

http://www.databasejournal.com/feat...Partitioning-in-SQL-Server-2005---Part-II.htm

http://www.databasejournal.com/feat...artitioning-in-SQL-Server-2005---Part-III.htm

Regarding your unused space, there are a lot of factors, but things like fillfactor and how often you reindex etc can impact the space used.



"I'm living so far beyond my income that we may almost be said to be living apart