Shulou(Shulou.com)06/02 Report--

First, according to different situations. You can try the following command to reduce unused space:

1. Space for deleted variable-length columns in a recycled table or indexed view:

DBCC CLEANTABLE (DBName, "[SalesLT]. [ProductModel]", 0)

two。 For heap tables:

To reduce the extent fragmentation of the heap, create a clustered index on the table, and then delete the index. The data is redistributed when the clustered index is created. For information about how to do this, see CREATE INDEX Please add a link description and DROP INDEX add a link description.

3. For indexes, indexes can be reorganized or rebuilt to reduce fragmentation:

Reassemble: if the fragmentation is 30%

ALTER INDEX PK_ProductModel_ProductModelID ON [SalesLT]. [ProductModel] REBUILD

In online mode

Rebuild index copies the old index to create a new index, and the old index can still be read and modified, but changes on the old index are synchronously updated under the new index. There will be some conflict resolution mechanisms, see the Build Phase section in Online Index Operations. Then when the rebuild process is complete, the table will be locked for a period of time, during which time the old index will be replaced with the new index, and the lock on the table will be released when the process is complete. Rebuild index online fails in SQL Server 2005/2008/R2 if the index column contains LOB objects. In sql server 2012, even if the index column contains LOB objects, you can also rebuild index online, you can refer to Online Index Operations for indexes containing LOB columns.

In offline mode

Rebuilde index locks the table, and all read and write operations to the table are blocked, during which the new index is created based on the old index, which is actually a replication process, but the new index is not fragmented, and finally the old index is replaced with the new index. When the entire rebuild process is complete, the lock on the table will be released.

You can check the degree of fragmentation (avg_fragmentation_in_percent column) with the following statement

DECLARE @ db_id SMALLINT

DECLARE @ object_id INT

SET @ db_id = DB_ID (NumbDBName')

SET @ object_id = OBJECT_ID (named DBName.SalesLT.ProductModel')

IF @ db_id IS NULL

BEGIN

PRINT N'Invalid database'

END

ELSE IF @ object_id IS NULL

BEGIN

PRINT N'Invalid object'

END

ELSE

BEGIN

SELECT * FROM sys.dm_db_index_physical_stats (@ db_id, @ object_id, NULL, NULL, 'LIMITED')

END

GO

Third, view the usage space

Sp_spaceused [SalesLT]. [ProductModel]

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.