July 3, 2012 at 12:34 pm
Someone recently changed the storage location of certain indexes. These indexes that have been moved now take up roughly 500 GB of space, so the database is 500 GB larger. Should I do a shrinkfile? I know that's not best practice. This database is 1.3TB total, so a shrink would be serveral hours.
July 3, 2012 at 12:45 pm
I don't get it. Are you saying that moving the indexes created larger files? What is the issue at hand?
Jared
CE - Microsoft
July 3, 2012 at 12:47 pm
Yes they were changed from the defaul "PRIMARY" filegroup to a different filegroup. The old location never shrank in size, and the new location is 500 GB larger.
July 3, 2012 at 12:53 pm
Moving the objects from the PRIMARY file group to a new file group frees the space in PRIMARY but the space is still there for the database, it isn't going to shrink automatically (unless you have autoshrink on and that is a BAD idea). If you want to return the space freed in the PRIMARY file group to the OS you will ned to run DBCC SHRINKFILE on that file.
July 3, 2012 at 12:54 pm
Yes, you can shrink the PRIMARY, but I would ask first does this database grow? Will you shrink the file and then later need to have it expand? Doing this will cause file fragmentation and the penalty can be costly. Do you really need the disk space back?
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
July 3, 2012 at 12:55 pm
The old location is probably retaining size due to BLOBs that were in the primary filegroup.
If you have data that was in those tables that was in a blob data type, the primary filegroup will retain that size and you will need to rebuild the tables using the [ TEXTIMAGE_ON { filegroup | DEFAULT } ] option.
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
July 3, 2012 at 12:55 pm
Are you running out of space? If not, I wouldn't shrink the file. Just continue to use up the available space.
Jared
CE - Microsoft
July 3, 2012 at 1:03 pm
SQLKnowItAll (7/3/2012)
Are you running out of space? If so, I wouldn't shrink them. Just continue to use up the available space.
I'm going to throw out the old reliable "it depends" here again. What was moved from the PRIMARY file group? If all the user objects (tables, procs, etc) were moved and the PRIMARY file group is no longer marked as the DEFAULT file group then I would consider shrinking the PRIMARY file group.
One thing I have tried to do with new databases I create is to create at least one additional file group for data and make that the default file group. This separates user objects (DEFAULT file group) from system objects (PRIMARY file group). I have even created another file group for nonclustered indexes. If I know I will be creating indexed views, I have even created a separate file group for them. The idea behind it is that if I have the option to place these file groups on separate disks, I can, even if they all reside on the same disk(s) in a development environment.
July 3, 2012 at 1:10 pm
Lynn Pettis (7/3/2012)
SQLKnowItAll (7/3/2012)
Are you running out of space? If so, I wouldn't shrink them. Just continue to use up the available space.I'm going to throw out the old reliable "it depends" here again. What was moved from the PRIMARY file group? If all the user objects (tables, procs, etc) were moved and the PRIMARY file group is no longer marked as the DEFAULT file group then I would consider shrinking the PRIMARY file group.
One thing I have tried to do with new databases I create is to create at least one additional file group for data and make that the default file group. This separates user objects (DEFAULT file group) from system objects (PRIMARY file group). I have even created another file group for nonclustered indexes. If I know I will be creating indexed views, I have even created a separate file group for them. The idea behind it is that if I have the option to place these file groups on separate disks, I can, even if they all reside on the same disk(s) in a development environment.
I'll +1 that 🙂 My assumption based on the first post, though, was that only the mentioned objects (indexes) were moved. However, we all know what assuming does :hehe:
Jared
CE - Microsoft
July 3, 2012 at 1:16 pm
Lynn Pettis (7/3/2012)
SQLKnowItAll (7/3/2012)
Are you running out of space? If so, I wouldn't shrink them. Just continue to use up the available space.I'm going to throw out the old reliable "it depends" here again. What was moved from the PRIMARY file group?
Just some of the indexes were moved - no stored procs or anything...I'm going to play it safe and avoid shrinking since the database uses the space anyways. Thank you! As always!!!
July 3, 2012 at 1:22 pm
sqluser_8119 (7/3/2012)
Lynn Pettis (7/3/2012)
SQLKnowItAll (7/3/2012)
Are you running out of space? If so, I wouldn't shrink them. Just continue to use up the available space.I'm going to throw out the old reliable "it depends" here again. What was moved from the PRIMARY file group?
Just some of the indexes were moved - no stored procs or anything...I'm going to play it safe and avoid shrinking since the database uses the space anyways. Thank you! As always!!!
Based on size, it was more than likely clustered indexes. I am still curious to know the makeup of the tables involved in this move. If you had BLOBs in those tables, that space can only by reclaimed by moving the blobs using the option i stated earlier.
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
July 3, 2012 at 1:35 pm
SQLRNNR (7/3/2012)
sqluser_8119 (7/3/2012)
Lynn Pettis (7/3/2012)
SQLKnowItAll (7/3/2012)
Are you running out of space? If so, I wouldn't shrink them. Just continue to use up the available space.I'm going to throw out the old reliable "it depends" here again. What was moved from the PRIMARY file group?
Just some of the indexes were moved - no stored procs or anything...I'm going to play it safe and avoid shrinking since the database uses the space anyways. Thank you! As always!!!
Based on size, it was more than likely clustered indexes. I am still curious to know the makeup of the tables involved in this move. If you had BLOBs in those tables, that space can only by reclaimed by moving the blobs using the option i stated earlier.
True, but moving the blobs (if any) won't cause the PRIMARY file group to shrink unless autoshrink is enabled or DBCC SHRINKFILE is executed.
July 3, 2012 at 1:38 pm
Lynn Pettis (7/3/2012)
SQLRNNR (7/3/2012)
sqluser_8119 (7/3/2012)
Lynn Pettis (7/3/2012)
SQLKnowItAll (7/3/2012)
Are you running out of space? If so, I wouldn't shrink them. Just continue to use up the available space.I'm going to throw out the old reliable "it depends" here again. What was moved from the PRIMARY file group?
Just some of the indexes were moved - no stored procs or anything...I'm going to play it safe and avoid shrinking since the database uses the space anyways. Thank you! As always!!!
Based on size, it was more than likely clustered indexes. I am still curious to know the makeup of the tables involved in this move. If you had BLOBs in those tables, that space can only by reclaimed by moving the blobs using the option i stated earlier.
True, but moving the blobs (if any) won't cause the PRIMARY file group to shrink unless autoshrink is enabled or DBCC SHRINKFILE is executed.
True. Without moving the blobs, a shrink won't help.;-)
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
July 3, 2012 at 1:43 pm
SQLRNNR (7/3/2012)
Lynn Pettis (7/3/2012)
SQLRNNR (7/3/2012)
sqluser_8119 (7/3/2012)
Lynn Pettis (7/3/2012)
SQLKnowItAll (7/3/2012)
Are you running out of space? If so, I wouldn't shrink them. Just continue to use up the available space.I'm going to throw out the old reliable "it depends" here again. What was moved from the PRIMARY file group?
Just some of the indexes were moved - no stored procs or anything...I'm going to play it safe and avoid shrinking since the database uses the space anyways. Thank you! As always!!!
Based on size, it was more than likely clustered indexes. I am still curious to know the makeup of the tables involved in this move. If you had BLOBs in those tables, that space can only by reclaimed by moving the blobs using the option i stated earlier.
True, but moving the blobs (if any) won't cause the PRIMARY file group to shrink unless autoshrink is enabled or DBCC SHRINKFILE is executed.
True. Without moving the blobs, a shrink won't help.;-)
True, but the OP has already decided not to shrink the file group which sort of makes this irrelevent.
irrelevant
July 3, 2012 at 1:50 pm
Lynn Pettis (7/3/2012)
SQLRNNR (7/3/2012)
Lynn Pettis (7/3/2012)
SQLRNNR (7/3/2012)
sqluser_8119 (7/3/2012)
Lynn Pettis (7/3/2012)
SQLKnowItAll (7/3/2012)
Are you running out of space? If so, I wouldn't shrink them. Just continue to use up the available space.I'm going to throw out the old reliable "it depends" here again. What was moved from the PRIMARY file group?
Just some of the indexes were moved - no stored procs or anything...I'm going to play it safe and avoid shrinking since the database uses the space anyways. Thank you! As always!!!
Based on size, it was more than likely clustered indexes. I am still curious to know the makeup of the tables involved in this move. If you had BLOBs in those tables, that space can only by reclaimed by moving the blobs using the option i stated earlier.
True, but moving the blobs (if any) won't cause the PRIMARY file group to shrink unless autoshrink is enabled or DBCC SHRINKFILE is executed.
True. Without moving the blobs, a shrink won't help.;-)
True, but the OP has already decided not to shrink the file group which sort of makes this irrelevent.
irrelevant
It's very interesting though 🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply