NOTRUNCATE
Freed space is retained in the database files. TRUNCATEONLY TARGET_PERCENT : Specifies the % of the free space to be retained in the file DBCC SHRINKDATABASE (SALES, 10) , - - Retains 10 % of free space To get the list of the table sizes in the database CREATE PROCEDURE dbo.Tables_Space
CREATE TABLE #tblResults
EXEC sp_MSforeachtable @command1=
-- Strip out the " KB" portion from the fields
exec Tables_Space Name - the name of the table Rows - the number of rows in the table Reserved - amount of total reserved space for the table Data - amount of space used by the data for the table Index_Size - amount of space used by the table's indexes Unused - amount of usused space in the table Exec space_used 'Orders' To get information about the index on the table EXEC sp_helpindex 'Orders' To get the fragmentation details SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Orders'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; GO If avg_fragmentation_in_percent value > 5% and < = 30% ALTER INDEX REORGANIZE > 30% ALTER INDEX REBUILD WITH (ONLINE = ON)* DBCC LOGINFO |
Database Technologies > MSSQL >