April 11, 2012 at 10:32 am
In a test environment I had two drives, E and F. F had an 80 GB data file, about 27GB of which was a table called Image.
Following steps similar to the ones outlined here http://www.sqlservercentral.com/articles/Administration/64275/[/url]
I moved the Image table to a new filegroup on the E drive. I set the initial size of this file to 30GB.
I expected this to free up space in the original data file, but it doesn't seem to have done so.
And when I run
SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB'--, *
FROM sys.database_files;
The new filegroup's file shows that it has 29710 free MB, even though I was expecting 30GB - 27GB of free space.
I'm clearly misunderstanding something here, can anyone explain this to me?
April 11, 2012 at 10:48 am
can you post the table definition and the exact command you used to move the table?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 11, 2012 at 10:51 am
Sounds like you may have LOB data in that table. You will need to account for that. Moving the table does not automatically move the LOB data.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 11, 2012 at 11:05 am
Perry Whittle (4/11/2012)
can you post the table definition and the exact command you used to move the table?
I was about to, wondering what you might learn from it when I saw:
SQLRNNR (4/11/2012)
Sounds like you may have LOB data in that table. You will need to account for that. Moving the table does not automatically move the LOB data.
Yes, you're totally right. I forgot there are BLOBs in this. Thanks a bunch.
April 11, 2012 at 11:06 am
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 11, 2012 at 11:13 am
I haven't found a better option, but this article sums it up pretty good. Basically you will need to re-create the table import the old data, drop the old table, and rename the new table to the old table name.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply