SQL Server 2008 Space release

Today, a former colleague called to say that their two tables add up to 1.2t (each table has thirty or forty fields, 600 million records), after the index was created, the space grew to 2.2t, and then no success was performed. The problem is that although the implementation is not successful, but the space is not released, the entire system only 2.2t of space, now is to want data backup out also can not engage. So there was a call to me so out.

The individual is not the main SQL Server, although the early also engaged in a period of time, there are also recent friendship to help some friends to optimize some of the case of SQL Server. To get to the bottom, check the space usage of the next db as follows:

EXEC sp_spaceused

Unallocated space has 900g, probably checked the meaning is space in the database that have not been reserved for database objects. Personal understanding is autoextend, but there is no space assigned to a specific object.

Then through the DBCC SHRINKDATABASE (dbname, 0), the space is reclaimed, a few minutes is over, and the 900GB space is released again.

The issue has been resolved.

PS: For the current mainstream RDBMS, tens of millions of records is really not a problem, reasonable use of zoning, indexing, application design, usually to meet business requirements.

SQL Server 2008 Space release