September 9, 2013 at 8:58 am
I have an 800GB data file that I want to shrink by adding another filegroup/data file and moving 300gb of tables/indexes into the new file. After moving the files, I planned on shrinking the 800gb file in increments. Are there any negative results in shrinking 300gb of space? My ultimate goal is to stop the file from growing any larger and speed up the nightly backups....but I don't want to cause any performance issues from shrinking the data file as it runs perfectly for users.
Can someone explain what may be expected from shrinking a large amount and if fragmentation will occur?
September 9, 2013 at 9:16 am
Fragmentation will occur (both within your databases and on the file system), and throughout the process it will cause some degree of contention, which depending on your system may impact users (slowness, etc). Once complete you can rebuild your indexes - all should be good there.
To keep your database from having to grow on a constant basis, be sure to size your files to accommodate your anticipated growth (better to have it already there and already available than to have it grow frequently). If you have the space available to you, leave a good chunk of the space there.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 9, 2013 at 10:16 am
MyDoggieJessie (9/9/2013)
Fragmentation will occur (both within your databases and on the file system), and throughout the process it will cause some degree of contention, which depending on your system may impact users (slowness, etc). Once complete you can rebuild your indexes - all should be good there.
Thank you for the response. Hmmm...There are almost 900 indexes in the database. Fairly new to SQL Server and rebuilding all of the indexes after the shrink is not something I want to tackle especially on an important production database. I'm thinking moving the designated indexes to the new file and then leaving the original file as is to stop the growth any further may be the best option for me.
September 9, 2013 at 10:21 am
If your DB is only 800GB and you have that many indexes, chances are the non-clustered indexes are pretty small so the rebuild process should go rather quickly (especially so if the new filegroup created for these indexes are on a different set of spindles).
Again if space is not an issue, leave the space there - it would be preferred
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 9, 2013 at 11:23 am
azenon (9/9/2013)
My ultimate goal is to stop the file from growing any larger and speed up the nightly backups....
Neither of those will be achieved by shrinking the file.
A file won't grow if there's no data being added to it. If there is data being added then the file will only grow if there's no free space. Since there's 300GN of free space, the file won't have to grow until around 300GB of data has been added. Once it has though, the file will be full and will need to grow.
Backups only include allocated extents, not free space, so shrinking the file will have no effect on backup speed or size.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply