Under certain circumstances, SQL Server does not automatically reclaim space once used. If a table definition is altered to drop one or more variable length columns, the space consumed by those columns is not immediately made available for reuse by SQL Server. But that's to say that the space is forever lost. We can employee a DBCC utility reclaim the newly-freed space.
To Calculate space
(1) sp_spaceused
Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.
Syntax
sp_spaceused [[ @objname = ] 'objname' ]
[,[ @updateusage = ] 'updateusage' ]
(2) using DMV sys.dmdbindexphysicalstats
--check the size of the table
SELECT
alloc_unit_type_desc
,page_count
,avg_page_space_used_in_percent
,record_count
FROM
sys.dm_db_index_physical_stats(
DB_ID()
,OBJECT_ID(N'TableName')
,NULL
,NULL
,'Detailed') ;
Reclaim Space After Dropping Variable-Length Columns Using DBCC CLEANTABLE
Reclaims space from dropped variable-length columns in tables or indexed views.
Syntax
DBCC CLEANTABLE
(
{ database_name | database_id | 0 }
, { table_name | table_id | view_name | view_id }
[ , batch_size ]
)
[ WITH NO_INFOMSGS ]
All DBA and Developers must have observed when any variable length column is dropped from table, it does not reduce the size of table. Table size stays the same till Indexes are reorganized or rebuild. There is also DBCC command DBCC CLEANTABLE, which can be used to reclaim any space previously occupied with variable length columns. Variable length columns include varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml. Space can be reclaimed when variable length column is also modified to lesser length.
DBCC command for reclaiming space is very simple. Following example is for AdventureWorks database and Person.Contact table.
DBCC CLEANTABLE ('AdventureWorks','Person.Contact', 0)
The result of DBCC is displayed below.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC is fully logged operation. It also does not affect temp tables and system tables.
http://blog.sqlauthority.com/2008/01/11/sql-server-reclaim-space-after-dropping-variable-length-columns-using-dbcc-cleantable
- For huge tables it might use a lot of transaction log space, so you may want to divide this into smaller batches.
- DBCC CLEANTABLE is not supported on system tables or temporary tables.
Example
http://weblogs.sqlteam.com/joew/archive/2008/01/14/60456.aspx
Reclaim space after dropping fixed length column
fixed length columns are stored at a fixed offset in each record (as opposed to variable length columns, for
which the offset can vary and it is stored itself in each record). You can find the offset for each fixed length column by looking at the xoffset column in syscolumns. Since this offset is stored in only one
place for all the records, it means that new records should use the same structure as the old records, and that's why the space is also wasted for new rows.
The solution (besides recreating the table) is indeed to create and then drop (or drop and then recreate) a clustered index on that table, thus rebuilding the structure of the entire table.Creating/dropping Cluster index on a table to reclaim space from dropped column only for SQL Server 2005. On SQL Server 2000, you will have to recreate the table.
Reclaim space in SQL Server 2005 database when dropping tables permanently
DBCC Shrinkdatabase(0) -- Currently selected database
or
DBCC Shrinkdatabase() -- Named database
However, shrinking files will likely fragment your tables, particularly larger onces, as contents of tables get moved about within the file, so once shrunk it's a good idea to defragment your tables. This, of course, will make your files grow again, but probably not so large as they were before you dropped your old tables. (Err, that assumes that the dropped tables contained large quantities of data...)
Reclaim Transaction Log Space
USE [AdventureWorks]
GO
DBCC SHRINKFILE (N'AdventureWorks_log' , 0, TRUNCATEONLY)
GO
This command will break the chains of LSN but it should not matter as in simple recovery model.
Release space used after sql server delete in rows with BLOB varbinary(max) using TEXTIMAGE ON separate filegroup
- Rebuilt the clustered indexes of the table.
Execute the below DBCC Command
DBCC UPDATEUSAGE (0)
GO
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/f80ad8c7-e6c9-4538-a89b-8d6727050b9c
DBCC UPDATEUSAGE
Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.
Reclaiming freed space Example
A SQL Server database has this table:
CREATE TABLE MyTable
(Column1 int NOT NULL,
Column2 char(200) NOT NULL,
Column3 varchar(40) NOT NULL,
PRIMARY KEY NONCLUSTERED(Column1));
No constraints or indexes other then those indicated by the CREATE TABLE statement exist for this table.
The table holds a million rows. Since Column2 is no longer used, you decide to free up some space by dropping it:
ALTER TABLE MyTable
DROP COLUMN Column2;
However, running sp_spaceused shows that no space is freed at all. Which of the following strategies can you use to reclaim the space freed up by removing Column2?
Correct answer:
Copy the existing data to a new table. Drop MyTable, create it again (without Column2), than copy the data back in., Copy the existing data to a new table. Drop MyTable, than rename the new table to MyTable., Create a clustered index on Column1 of MyTable, then drop this index again.
Explanation:
Dropping a column is a metadata-only operation. That means that the pages are not rebuilt. This makes the operation very fast, but also means the space is not reclaimed. And since metadata is the same for all pages, the same amount of space will also be lost on pages that are later allocated to the table.
SQL Server will only free up this space if it has to rebuild all the pages of the table. For most tables, that is the case when you rebuild indexes (using ALTER INDEX .. REBUILD, or using the deprecated DBCC DBREINDEX option). But not on this table, since it is a heap. DBCC DBREINDEX will rebuild the nonclustered index on Column1, but not the actual data stored in the heap.
Creating a clustered index does involve a rebuild of all the data pages, so this will also change the metadata and free up the space previously taken by Column2.
Even though TRUNCATE TABLE does remove all the data pages and could theoretically be a great time to change the metadata, it doesn't work this way (as can easily be verified by testing).
Creating a new table does of course free up the space that was required to store Column2 (though it temporarily uses extra space to store the duplicated copy).
http://www.sqlservercentral.com/questions/Administration/67277
Reduce the Virtual Log Files (VLFs) from LDF file
If you have lots of VLFs, you can reduce them right away using the following method:
USE AdventureWorks
GO
BACKUP LOG AdventureWorks TO DISK='d:\adtlog.bak'
GO
-- Get Logical file name of the log file
sp_helpfile
GO
DBCC SHRINKFILE(AdventureWorks_Log,TRUNCATEONLY)
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(NAME = AdventureWorks_Log,SIZE = 1GB)
GO
DBCC LOGINFO
GO
http://blog.sqlauthority.com/2011/01/02/sql-server-reduce-the-virtual-log-files-vlfs-from-ldf-file
Good Article Changing Table Schema – what goes behind the scenes – Part II
http://www.sqlservercentral.com/articles/Design+and+Theory/67553