December 7, 2016 at 5:33 am
Hello Guys,
I a table of around 10 GB size. Recovery model of database (SQL Server 2005) is simple. For some requirement, I added a clustered index on table. Then, disk space started decreasing by 1 GB. I got worried and stopped the process. Later, I found that there were around 10 data file growth events occurred on that table. Could you please let me know what step should I take? How can I able to add an index?
Regards
VG
December 7, 2016 at 8:31 am
SQL Learner - VKG (12/7/2016)
Hello Guys,I a table of around 10 GB size. Recovery model of database (SQL Server 2005) is simple. For some requirement, I added a clustered index on table. Then, disk space started decreasing by 1 GB. I got worried and stopped the process. Later, I found that there were around 10 data file growth events occurred on that table. Could you please let me know what step should I take? How can I able to add an index?
Are those 10 GBs too much so you have to worry about?
Just try. The clustered index is the data of the table. While being created, the process must obtain some more space.
Igor Micev,My blog: www.igormicev.com
December 12, 2016 at 6:22 am
Thanks for update. That's fine for 10 GB as you are saying. But the thing is, I am running out of space. Is there any other way to achieve the same.
Regards
VG
December 12, 2016 at 6:36 am
SQL Learner - VKG (12/12/2016)
Thanks for update. That's fine for 10 GB as you are saying. But the thing is, I am running out of space. Is there any other way to achieve the same.
If you have databases in Simple recovery model then check their Log files and potentially shrink them. But shrink the log files only, don't do that with the data files.
If you have some backups on the disk which you don't need remove them.
Some other files that are not necessary can release you space too.
That's the procedure I do sometimes on the staging environments, whilst on the production envs I always have much more space.
Actually your environment should not live with such a small amount of free space.
Igor Micev,My blog: www.igormicev.com
December 12, 2016 at 7:27 am
Igor Micev (12/12/2016)
If you have databases in Simple recovery model then check their Log files and potentially shrink them. But shrink the log files only, don't do that with the data files.
In simple mode, your log file will grow to what it needs to complete an open transaction. Once the transaction is complete, the log file space is freed (not shrunk) for use by the next transaction. Only shrink the log file if you were running some unusual task on that table, such as a one time load or transformation. If you were running normal business tasks, shrinking the log file will not help as it will just grow again as soon as your business starts operating again.
Other things you can consider:
1. You can move the log file to a different drive (recommended even without space constraints).
2. Compress your database tables.
3. Check your tables for data that is beyond the retention period and delete it.
4. If you have multiple databases on the instance, spread them across multiple drives.
5. You can create multiple data files for a single database and spread them across drives.
#2 & 3 will not save space initially because the data file will not shrink when data is compressed or deleted. However, your ongoing growth will consume the newly available space within the data file before growing the file on disk.
Eventually, you will need to add space. Data volumes are constantly increasing. You need to plan for that as part of your normal maintenance.
Edit: And don't put your database files on the same drive as your OS files. You can cripple your server if the database uses all of the drive space and the OS can't get what it needs.
Wes
(A solid design is always preferable to a creative workaround)
December 12, 2016 at 7:56 am
SQL Learner - VKG (12/7/2016)
Hello Guys,I a table of around 10 GB size. Recovery model of database (SQL Server 2005) is simple. For some requirement, I added a clustered index on table. Then, disk space started decreasing by 1 GB. I got worried and stopped the process. Later, I found that there were around 10 data file growth events occurred on that table. Could you please let me know what step should I take? How can I able to add an index?
You are creating a clustered index. Until the operation is completed, you are going to need approximately same amount of additional space, so in your case the database may grow 10 GB more. You may be able to shrink the file after the operation is completed, but it is recommended not to do considering the fact that this can lead to fragmentation.
The reason for you to see the disk space decreasing by 1 GB might be due to the fact that you have auto growth set as 1 GB.
You can try manually growing the database and create the index if you have enough space on your disk.
December 14, 2016 at 12:39 am
In the nutshell, what I got from here is; changing any heap into clustered index requires about same amount of space as that of table until that operations is completed. To incorporate that operation, we should have enough space. One of the way to get space allocation is to shrink log files. We should not shrink data files which leads to fragmentation.
Conclusion : Clustered Index addition requires extra space other than existing table while its operation. Thank you guys 🙂
Regards
VG
December 14, 2016 at 7:20 am
SQL Learner - VKG (12/14/2016)
changing any heap into clustered index requires about same amount of space as that of table until that operations is completed. ... Conclusion : Clustered Index addition requires extra space other than existing table while its operation.
Creating a clustered index on the heap may require up to twice the size of the heap during the build operation. I doubt you'll see more than 10GB of growth events when creating the clustered index for your 10GB table. If TempDB is on a different drive, you can possibly reduce the size requirements by using SORT_IN_TEMPDB = ON when creating the index. That will move some of the activity to a different drive, reducing the space overhead on destination drive. Aside from the index build operations, a heap and a clustered index will generally take the same amount of space on disk.
SQL Learner - VKG (12/14/2016)
One of the way to get space allocation is to shrink log files.
This is a very short term solution. In simple mode, the log files will grow to accommodate the size of the largest transaction or concurrent transactions. In my experience, once normal operations resume, the log file will immediately grow again to nearly the same size. You can do this during your maintenance window to temporarily free up some space, but it will not solve your problem. It would be best to move your log files to a different drive so the log file space is not an issue. This has performance benefits, is fairly easy to do, and has minimal risk.
SQL Learner - VKG (12/14/2016)
We should not shrink data files which leads to fragmentation.
This can be used as a band-aid, but not a solution. Fragmentation negatively affects performance. However, if the choice is between running out of space and slow performance, shrinking the data files is an option. Understanding however that normal operations will eventually cause the files to grow again and the performance problems may cause additional paging to disk, making the space issues worse. If you do this, you'll want to rebuild all of the indexes again once you add more space.
Wes
(A solid design is always preferable to a creative workaround)
December 14, 2016 at 7:29 am
Just another thought.
If you're needing space to convert the heap to a clustered index, drop any non-clustered indexes on the table first. NCIs also take up space and they'll need to be rebuilt to use the clustered index anyway. Make sure you save the definition (Script Drop and Create).
You can also do this for NCIs on other tables as well. Drop the NCIs will free space in the data file (not on disk). Creating the clustered index will use the free space before growing the data file. Once the clustered index is created, re-create any NCIs you dropped.
Obviously, this is a maintenance window task or your users will experience performance impacts.
Wes
(A solid design is always preferable to a creative workaround)
December 20, 2016 at 5:41 am
Thanks a lot Guys. It makes sense and I will be taking care of the same.
Regards
VG
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply