December 29, 2015 at 4:41 am
Hello Masters,
Is low disk space can affect the rebuilding of Indexes? I am going to rebuild index of database with size of 50 GB and current free space on disk is around 13 GB. So would it affect rebuilding?
December 29, 2015 at 4:59 am
To rebuild a 50GB index, you need 50GB free in the data file (at least) and more than 50GB free space in the log file. If you don't have that, then the files need enough space to grow to get that free space. If they can't, the rebuild will fail.
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
December 29, 2015 at 9:59 am
You can use tempdb database instead of the db containing the index to hold most of the data for the rebuild if you prefer. But you will need enough log space in the original db to do the rebuild.
Check to see if you can shrink any log files. Or, if you still need disk space and there is significant unused data space available (which you can determine using DBCC SHOWFILESTATS), see if you can shrink the data files, although it could cause fragmentation of tables in that file.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 29, 2015 at 10:59 am
You can sort in tempDB, which removes the need for the sort space in the user database, but you still need space for SQL to put down the new index, and that has to be in the user database.
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
December 30, 2015 at 10:27 am
Shrink the data files before rebuilding the indexes doesn't sound like a good advice
December 30, 2015 at 11:25 am
I guess I wasn't clear enough. The idea is shrinking other databases on the same drive set(s) to free disk space, not on the db that needs the index rebuilt.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply