Compress/Shrink SQL Server Database/Table/Index, tSql

When our database size was too large, we need to compress or shrink data to reduce the disk/file/database size. I will provide some guidance to reduce the database/index size to the minimal which ensure the high availability of disk space.

I will discuss below points to Compress as much as possible ...

1. Find the Tables need to Compress
2. Estimate the Compression rate/size.
3. Compress the Table Data in Page Level. (it can be in ROW level)
4. Compress the Index size in Page Level. (it can be in ROW level)
5. Compress/Shrink full database.

NOTE: If any table has Sparse Column, we can't compress that table/index directly.

1. First of all, we have to find the affected/large in size Tables. Use below SQL to Find the large Tables.

----------------------------------------------------

-- Determine which tables to compress

SELECT  

    t.NAME AS TableName, 

    i.name as indexName, 

    sum(p.rows) as RowCounts, 

    sum(a.total_pages) as TotalPages, 

    sum(a.used_pages) as UsedPages, 

    sum(a.data_pages) as DataPages, 

    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 

    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 

    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB  

FROM 

    sys.tables t 

INNER JOIN      

    sys.indexes i ON t.OBJECT_ID = i.object_id 

INNER JOIN 

    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id 

INNER JOIN 

    sys.allocation_units a ON p.partition_id = a.container_id 

WHERE 

    t.NAME NOT LIKE 'dt%' AND 

    i.OBJECT_ID > 255 AND   

    -- i.index_id <= 1 

GROUP BY  

    t.NAME, i.object_id, i.index_id, i.name  

ORDER BY  

    object_name(i.object_id) 

----------------------------------------------------

2. After getting the table and index list; Now we have to estimate the size compression, how much size we can reduce.

Use below SQL to check the reduced size.
----------------------------------------------------
-- For Table Data Compression 
EXEC sp_estimate_data_compression_savings 
              @schema_name = 'dbo', 
              @object_name = 'DATABASE_NAME', 
              @index_id = NULL, -- For Table Data Compression
              @partition_number = NULL,
             @data_compression = 'Page' ;

-- For Index Data Compression 
EXEC sp_estimate_data_compression_savings 
              @schema_name = 'dbo', 
              @object_name = 'DATABASE_NAME', 
              @index_id = 1, -- Index ID -- For Index Data Compression
              @partition_number = NULL,

             @data_compression = 'Page' ;
----------------------------------------------------

3. After making the final list of Table, we can now compress the table using below SQL
----------------------------------------------------
ALTER TABLE dbo.TABLE_NAME REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
----------------------------------------------------

4. After Table Compression, we now can compress the Indexes (one by one) ..
----------------------------------------------------
ALTER INDEX INDEX_NAME ON dbo.TABLE_NAME REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE); 
----------------------------------------------------

5. Finally we can Shrink/Compress the full database .. use below SQL ..
Bellow command have some other parameters, for details please check DBCC SHRINKDATABASE (Transact-SQL)
----------------------------------------------------
DBCC SHRINKDATABASE(N'DATABASE_NAME');
----------------------------------------------------
NOTE: You may use the procedure sp_spaceused to track the data size

all above is the example which I did for a database of 1 TB. You can choose your own compression strategy to compress the database or files.

Backup SQL Server DB Objects (user defined) as script

using below tool, you can backup a database, with all user defined objects, as a script and save it to the file.... without any third party/extra tool.

NOTE: download the ZIP file "DBScriptGenerator.zip"

After download, extract the zip and place it anywhere you want.
in my case, location is -- "C:\DBScriptGenerator\"

this zip file contains an EXE file named "DBScriptGenerator.exe", along with the other DLL files.

now we have to call this executable from anywhere we can. of course, we need to pass all the parameters.
in my case, I am calling from CMD...

Format :
DBScriptGenerator.exe "DatabaseServerName" "DatabaseName" "DatabaseUser" "DatabasePassword" "FullPathToSaveWithFileName"

Example:
DBScriptGenerator.exe "PRODSV" "EMPMAS" "UADMIN1" "u@Dwin!" "C:\DBScriptGenerator\EMPMAS.sql"

NOTE:::
for Advanced users only ...

There is a DLL file, DBScriptGenerator.dll, available to use programmatically. we can use this DLL from C# classes and/or SSIS scripts also.

Object List: Table, Procedure, Function, View, Trigger, Schema, assembly .. all user objects

SQL Script to retrieve metadata of a Sql Statement and/or Table of SQL Server

This script will help us to retrieve metadata/column information from a SQL Statement and/or a Table. Download this script, RETRIVE_METADATA_SCRIPT.sql.

This script will provide the below information .. 

1. TABLE_OWNER
2. ORDINAL_POSITION
3. COLUMN_NAME
4. TYPE_NAME
5. LENGTH
6. PRECISION
7. IS_NULLABLE

See the below table to understand parameters …

Parameter Name	Type	IN/OUT	Description
@SQLStm	VARCHAR(MAX)	INPUT	Put fully qualified table name, i.e. [dbo].[TEST], luckily you can use DB name with table name, i.e. [TEST_DB].[dbo].[TEST]. OR you can pass a SQL statement to check duplicate output, i.e. "select * from [dbo].[TEST]"
@isTable	BIT	INPUT	Mention @SQLStm contains a Table name or SQL stm. TRUE for table and FALSE for SQL Stm.

see also -- SQL script to check duplicate record existence of a table/SQL statement of SQL Server

                 -- SQL script to dynamically UnPivot any Pivot table of SQL Server with example