Sql-server – Data Files Outgrowing Hardware – Split the File

filegroupsfilessql serversql-server-2005

Where I'm currently working, the primary business database is located on a virtual server (SQL Server 2005 standard). The temp DB and logs are on one disk, the database itself on another. The SAN is configured in such a way that they can only provision 512GB disks (4K blocks I'm told, but I nothing about SANs). There is about 4% free space on the disk that holds the DB.

I was tasked with working a way to archive off old data and purge it from the database into one year chunks for archive to tape (I'm a contractor and really I'm a developer although I have dabbled in DBA world in the past and have 15 or so years experience developing with and for SQL Server, not a DBA, and it was handed to me as soon as I walked in the door. We do have a DBA so I don't know why I'm doing this but never mind). That was about 6 months ago and since then I have, on and off, built something which will do just that. But I am increasingly concerned that we cannot possibly test the resulting database enough to verify that we really haven't broken something and I've been casting my mind around for an alternative solution.

I first thought about partitioning but that's out because we have SE and no chance of upgrading to EE as in 6 or so months the whole shebang will move to a dedicated data centre with larger, more modern infrastucture and the whole system will be replaced by new developments in the next 2-4 years. But then it occurred to me that surely we can split the database into multiple files within a filegroup and spread them across multiple disks (we have no problem provisioning more 512GB disks). This leaves the entire DB intact and may even have a slight performance benefit, although that isn't the primary objective here.

I'm pretty sure this is OK in SE (again, I'm not a DBA) and I see this as far less risky and potentially quicker to implement. so my questions:

  1. Is it possible to have a database split across multiple files in 2005 SE?

  2. Is it possible to split an existing DB that is currently in one file on a single disk into multiple files across multiple disks?

  3. Do I even need to present any more than this information to convince any sane person that it would be foolish to risk the disruption to a multi-million pound business of purging data from a production database?

Best Answer

1) Yes

2) Yes,

After creating the new files, you'll need to drop and re-create the clustered indexes of the target tables, moving them on to the new filegroup to move the data:

CREATE CLUSTERED INDEX CIX_YourTable
ON dbo.YourTable(YourClusteringKeyFields)
WITH DROP_EXISTING
ON [filegroup_name]

3) If you can get the DBA to back you up on this solution, I think you'll be OK. Based on your limitations this seems like a perfectly reasonable move - but remember to backup before trying to move the data!

Related Question