Can't delete secondary db file

Question

Can't delete secondary db file

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22471 More actions · Answer 1

I am not aware of a way to do this other than creating a new table having the same structure as the old one. Specify the desired file groups you want to use for the text and image columns. Then you insert all rows from the old table into the new table. Drop the old table and rename the new one.

Actually, if you do this you should be using varchar(MAX) and varbinary(MAX) instead of text and image because they are deprecated, so create the columns as these new types.

The probability of survival is inversely proportional to the angle of arrival.

Glenn Dorling SSCrazy Eights Points: 8032 More actions · Answer 2

You can do this using an ALTER TABLE command and specifying a different filegroup for the BLOB data. ALTER TABLE can also be used to move data between filegroups where you don't have a clustered index.

WayneS SSC Guru Points: 95461 More actions · Answer 3

sturner (12/23/2009)
I am not aware of a way to do this other than creating a new table having the same structure as the old one. Specify the desired file groups you want to use for the text and image columns. Then you insert all rows from the old table into the new table. Drop the old table and rename the new one.
Actually, if you do this you should be using varchar(MAX) and varbinary(MAX) instead of text and image because they are deprecated, so create the columns as these new types.

This worked, though I would have preferred a method that just transferred it over without having to use an intermediate table. Thanks for your help!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95461 More actions · Answer 4

Glenn Dorling (12/23/2009)
You can do this using an ALTER TABLE command and specifying a different filegroup for the BLOB data. ALTER TABLE can also be used to move data between filegroups where you don't have a clustered index.

I couldn't find in BOL where you could specify a filegroup for the BLOB data with the ALTER TABLE command. It also looks like you can only transfer the data to a different filegroup concurrent with dropping a constraint.

Thanks for your help!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2