1

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?

  • 1) Yes 2) Yes - but this will be limited to entire tables being moved, not sub-sets of them (that'd be partitioning) 3) That depends - you're not really archiving the data if you go this route, so is it a valid solution? If space isn't an issue, why not copy the data out and then purge as needed once tested in dev/qa/uat? – LowlyDBA Nov 19 '14 at 15:01
  • By entire tables you mean I cannot split a table across files, yes? That was my understanding of partitioning and I am not concerned with that. We have two tables alone that account for 150GB so I figured they could go in a file on one disk, then split the remainder between two files on two disks so that the level of transactions is roughly split between the two. That would be OK, yes? As for the third point, space is the primary issue. But I just don't think we have the resources to do enough testing to be certain we don't cause problems by purging. – Steve Pettifer Nov 19 '14 at 15:08
  • If you're just moving entire tables then yes, this should work for you. – LowlyDBA Nov 19 '14 at 15:10
1

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!

  • Yes we have Red Gate backup pro in place managing this and it would be the first thing we do for sure. Thanks for this, I think my boss will back me up on this but we have an infrastructure team which seems to have a huge amount of inertia over every single thing so I wanted to be sure I was suggesting something realistic and which involved as little effort for them as possible. – Steve Pettifer Nov 19 '14 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.