This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a demonstration on how to recover the occupied space by a SQL Server table, after dropping a big size of the column.
Many times, we are getting a request like “No Space in Development Server”. Developers are testing their code and design so they are dropping and creating columns in tables.
But after this, we should refresh the table by rebuilding their indexes.
Please check the below full demonstration on this, and try it yourself:
Create a table with sample data:
|
1 2 3 4 5 6 7 |
CREATE TABLE tbl_TableSize (ID BIGINT, Name CHAR(2000), CONSTRAINT pk_Id PRIMARY KEY(ID)) GO INSERT INTO tbl_TableSize VALUES (1,'Anvesh'),(2,'Roy'),(3,'Jenny') ,(4,'Martin'),(5,'Neevan'),(6,'Nupur') GO |
Check the average taken space of a table:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT page_count ,avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats(db_id('db_name'), object_id('tbl_TableSize'), NULL, NULL , 'Detailed') --Result: page_count avg_page_space_used_in_percent -------------------- ------------------------------ 2 74.7343711391154 1 0.395354583642204 |
Now, Drop a CHAR column:
|
1 2 |
ALTER TABLE tbl_TableSize DROP COLUMN Name GO |
Now check the size again:
No difference found!
|
1 2 3 4 5 6 7 8 9 10 |
SELECT page_count ,avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats(db_id('test'), object_id('tbl_TableSize'), NULL, NULL , 'Detailed') --Result: page_count avg_page_space_used_in_percent -------------------- ------------------------------ 2 74.7343711391154 1 0.395354583642204 |
Now, Rebuild the clustered index:
|
1 2 |
ALTER INDEX pk_Id ON tbl_TableSize REBUILD GO |
Check the size again:
Now, you can see the huge difference in the table size, and occupied space by dropped column has been recovered.
|
1 2 3 4 5 6 7 8 9 |
SELECT page_count ,avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats(db_id('test'), object_id('tbl_TableSize'), NULL, NULL , 'Detailed') --Result: page_count avg_page_space_used_in_percent -------------------- ------------------------------ 1 1.23548307388189 |
Leave a Reply