Database Technologies‎ > ‎MSSQL‎ > ‎

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


Comments