To optimize the database performance and size you can create a daily maintenance plan. This solves issues due to the fragmentation, caused by expiration rules & purge.

This plan have to have the following steps:

•Dump the database

BACKUP DATABASE [GN4_DATABASE] TO  DISK = N'C:\MSSQL\GN4.bak' WITH NOFORMAT, NOINIT,  NAME = N'GN4_DATABASE-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

•Rebuild all database indexes with this query

DECLARE @Database VARCHAR(255)   

DECLARE @Table VARCHAR(255)  

DECLARE @cmd NVARCHAR(500)  

DECLARE @fillfactor INT 

 

set @Database = 'GN4_DATABASE'

 

SET @fillfactor = 90 

 

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + 

table_name + '']'' as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE"   

 

-- create table cursor  

EXEC (@cmd)  

OPEN TableCursor   

 

FETCH NEXT FROM TableCursor INTO @Table   

WHILE @@FETCH_STATUS = 0   

BEGIN   

 

  IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)

    BEGIN

      -- SQL 2005 or higher command 

      SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' 

      print @cmd

      EXEC (@cmd) 

    END

  ELSE

    BEGIN

      -- SQL 2000 command 

      DBCC DBREINDEX(@Table,' ',@fillfactor)  

    END

 

    FETCH NEXT FROM TableCursor INTO @Table   

END   

 

CLOSE TableCursor   

DEALLOCATE TableCursor

•Shrink database file with this query

USE [GN4_DATABASE]

GO

DBCC SHRINKFILE (N'GN4_FILE' )

GO

Notes

•GN4_DATABASE is the GN4 database name (replace it with yours)

•GN4_FILE is the GN4 database LOGICAL file name NOT the real filename (replace it with yours). You can see the logical file name opening the database properties and selecting files.

•You may consider to shrink database before rebuilding indexes, as per Microsoft's recommendation http://msdn.microsoft.com/en-us/library/ms189035.aspx as data that is moved to shrink a file can be scattered to any available location in the file. This causes index fragmentation and can slow the performance of queries that search a range of the index. To eliminate the fragmentation, consider rebuilding the indexes on the file after shrinking.

Examples of procedure duration

•Size of the database 33Gb: dump duration 40 minutes; Rebuild all database indexes 1 minute; Shrink database 2 minutes. Final database size: 30Gb