DBCC ShrinkDatabase

NOTRUNCATE

Freed space is retained in the database files.

TRUNCATEONLY

Free space in the data files is released to the operating system and shrinks the file to the last allocated extent,

TARGET_PERCENT is ignored when TRUNCATEONLY is used.

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

AS

CREATE TABLE #tblResults

(

[name] nvarchar(20),

[rows] int,

[reserved] varchar(18),

[reserved_int] int default(0),

[data] varchar(18),

[data_int] int default(0),

[index_size] varchar(18),

[index_size_int] int default(0),

[unused] varchar(18),

[unused_int] int default(0)

)

EXEC sp_MSforeachtable @command1=

"INSERT INTO #tblResults

([name],[rows],[reserved],[data],[index_size],[unused])

EXEC sp_spaceused '?'"

-- Strip out the " KB" portion from the fields

UPDATE #tblResults SET

[reserved_int] = CAST(SUBSTRING([reserved], 1,

CHARINDEX(' ', [reserved])) AS int),

[data_int] = CAST(SUBSTRING([data], 1,

CHARINDEX(' ', [data])) AS int),

[index_size_int] = CAST(SUBSTRING([index_size], 1,

CHARINDEX(' ', [index_size])) AS int),

[unused_int] = CAST(SUBSTRING([unused], 1,

CHARINDEX(' ', [unused])) AS int)

SELECT * FROM #tblResults order by rows desc.

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