Dear Friends,
I have faced recently database size issue. In my current project I need to add a column bit field in table and update the bit field conditionally.
The table contains 547,173,777 rows. Before adding the column the database size in 177 GB and after adding and updating the Bit column for the table the database size increased 223 GB.
I am suppressed with that behavior.
So my main motto at that time first recovers space. I searched many articles for claim space. Firstly I am sharing the with you all the options related to space and later on I will share the steps which help me to recover space
1) Shrink File:-
Shrink file is the most used option to recover space again from database it mainly used to recover space from log files.
Its syntax is DBCC SHRINKFILE (Database_LogFileName, Target Size MB)
2) Shrink Database:-
Shrink Database is another option which is used to shrink Database. With this option we physically gain the size.
It syntax is DBCC SHRINKDATABASE (DatabaseName, Percentage)
3) Clean Table :-
Clean table option can be used in particular condition suppose you have added a VARCHAR column in table and added data also to that column now later on you want to remove the column then After removing the column you will see the table size is unchanged to recover VARCHAR column space you can use CLEANTABLE option.
Its syntax is DBCC CLEANTABLE (DatabaseName, TableName)
4) SP_SPACEUSED:
This option will provide you space used by table or database .sp space used column will return number of rows, reserved space, Data space, index space, unused.
Its syntax is SP_SPACEUSED ‘TableName’
5) UPDATEUSAGE:-
Update usage is another most important command which update accurate Rows & pages count. Its syntax is as follows DBCC UPDATEUSAGE (DATabaseName, tableName)
6) Index Defrag:
Index Defrag is one of the main commands. It should be used in maintenance plan also this command defrag the entire index and rearrange the index.
Its syntax is as follows DBCC INDEXDEFRAG (DatabaseName, TableName, IndexName)
Other than this re indexing, re organizing index are option which help in actual database size projection. So all the above statements are related to the space information for database.
Now in my case I got help from INDEXDEFRAG command.
I did defragmentation of all the index of a table on which I have added the bit column. After running defragmentation for each index I was amazed when I run SP_SPACEUSED it recovered almost 40 GB. But the problem not ends here when I saw my disk drive it not reflect actually on disk. Then I used shrink Database command which increase my server disk free space with 40%.
So here are the steps to recover space from a table or database
1) Check current space used by table with SP_SPACEUSED command
2) Run Index defragment command INDEXDEFRAG for each index on table
3) Shrink the Log file with SHRINKFILE command
4) Shrink the database using SHRINKDATABASE command
After running above steps you will surprised. I hope these steps will help you.
Thanks & Regards
Rajat Jaiswal
