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.

Import CSV file to SQL Server, Mule 4 (MuleSoft)

To Import a CSV file (with header) to the SQL Server database, I will take an HTTP Listener to call the process then read the CSV file and insert to DB as BULK INSERT. 

Import CSV File to SQL Server DB

To do this, I will follow the below steps ...

1. add a Flow of HTTP Listener
2. add a Fiel Read Component
3. add a Bulk Insert component

Before Start, our example CSV file will look like as below formate

CSV file Contant (DataLoad.csv)

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

ID,NAME

1,Azam

2,Saiful

Create a Blank Mule Project First :)

STEP 1: Add HTTP Listener and Configure it to invoke from browser or postman.

Double Click on HTTP Listener component to Configure

Provide a Name for your HTTP Listener - in my case, /insertcsv

Provide the Path as "/yourpath" - in my case, /insertcsv

Click on "+" Button to Create and Configure the HTTP Listener Connection

Provide a Name for your Connector  - in my case, HTTP_Listener_config

Provide the Host IP - in my case, I used 0.0.0.0 to invoke from anywhere of my network

Provide the Port Number -  - in my case, 8081

Set Base Path as "/"

Test the Connection to check the port is open to use - Click on "Test Connection..." Button

Click "OK"

Add a Response Message to Display in Browser, if everything works fine - in my case, the message is "Data Inserted ..."

HTTP Listener Configuration

HTTP Response

STEP 2: Add File-Read Component and Configure it to Read the CSV file.

Provide the Display name - in my case, Read CSV File

To add a Connector Configuration, Click "+" button

Give a name for this Configuration - "File_Config"

Set Working Directory (where is your file located) - "E:\something"

Test the Connection and Click OK

Finally, provide the File Name OR Fully Qualified Name -  "DataLoad.csv"

File-Read Component Configuration

STEP 3: Add a Bulk Insert Component and Configure it to get data and insert.

Now, First add a Connection of SQL Server Database.

Click the "+" button of "Connector Configuration".

Give a Connection name - Database_Config

Choose "Microsoft SQL Server Connection" as Connection

If required, Click on "Modify dependency" then click "Install" then click "Finish"

Now provide Connection details ...

Host Name - SQL Server instance name OR IP

Port - 1433 (default port of SQL Server)

User - SQL Server User

Password - Password of above SQL Server User

Database Name - which database you want to import CSV File.

SQL Server Connection Conf

After Create the Connection, we have to add INSERT SQL and Parameter to read the Payload message (data object)

Add below Insert SQL at "SQL Query Text:" section/field

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

insert into dbo.DataLoad (ID,NAME) values (:IN_ID,:IN_NAME)

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

Here :IN_ID and :IN_NAME are the parameters for this INSERT command

After adding the SQL text, now we will add a MAP code to pass the value to these parameters.

Add below code to "Input Parameters:" section/field ...

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

#[%dw 2.0

output application/json

---

payload map {

'IN_ID': $.ID,

'IN_NAME': $.NAME

}]

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

here  :IN_ID and :IN_NAME are the parameter of INSERT Command.

Input Parameters and SQL Query Text

Thats All :)

now run the project/application - Right click on the Flow and select "Run Project Dataloadcsv"

once the application is run, open the browser and hit the url - "http:/localhost:8081/insertcsv"

you will get the response message "Data Inserted ..." once all done.

Please feel free to comments.

Check INDEX Status and REBUILD Index, SQL Server

When you need to check the health or status of all INDEXs and need to REBUILD the required INDEXs depends on the Fragmentation level (%), you can get help from below SQL queries.

In my example, I will show how to get the fragmented indexes which have above 20% fragmentation level.

INDEX STATUS check

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

SELECT dbschemas.[name]  AS 'SchemaName',

       dbtables.[name]          AS 'TableName',

       dbindexes.[name]         AS 'IndexName',

       ROUND(indexstats.avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent,

       indexstats.page_count      

FROM   sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

       INNER JOIN sys.tables dbtables

            ON  dbtables.[object_id] = indexstats.[object_id]

       INNER JOIN sys.schemas dbschemas

            ON  dbtables.[schema_id] = dbschemas.[schema_id]

       INNER JOIN sys.indexes   AS dbindexes

            ON  dbindexes.[object_id] = indexstats.[object_id]

            AND indexstats.index_id = dbindexes.index_id

WHERE  indexstats.database_id = DB_ID()

       AND dbindexes.type_desc = 'NONCLUSTERED'

       AND dbindexes.[name] IS NOT NULL

       AND indexstats.avg_fragmentation_in_percent > 20

ORDER BY

       ROUND(indexstats.avg_fragmentation_in_percent,2) DESC

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

Now using below SQL, I will create the REBUILD command to Defragment the infected indexes...

SQL to generate Rebuild Command

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

SELECT 

'ALTER INDEX '+dbindexes.[name]+' on '+dbschemas.[name]+'.'+ dbtables.[name]+' REBUILD;',

dbschemas.[name]  AS 'SchemaName',

       dbtables.[name]          AS 'TableName',

       dbindexes.[name]         AS 'IndexName',

       ROUND(indexstats.avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent,

       indexstats.page_count

FROM   sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

       INNER JOIN sys.tables dbtables

            ON  dbtables.[object_id] = indexstats.[object_id]

       INNER JOIN sys.schemas dbschemas

            ON  dbtables.[schema_id] = dbschemas.[schema_id]

       INNER JOIN sys.indexes   AS dbindexes

            ON  dbindexes.[object_id] = indexstats.[object_id]

            AND indexstats.index_id = dbindexes.index_id

WHERE  indexstats.database_id = DB_ID()

       AND dbindexes.type_desc = 'NONCLUSTERED'

       AND dbindexes.[name] IS NOT NULL

       AND indexstats.avg_fragmentation_in_percent > 20

ORDER BY

       ROUND(indexstats.avg_fragmentation_in_percent,2) DESC

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

SSIS Task to Create Excel Report/ Export Data to Excel with Header using SQL Statement, With Example

When we plan to automate a Process/System; sometimes we need to generate an Excel report to check the process status or create QC report using Excel to deliver or for internal use. Also somewhere we need formatted excel report for quick check the failure/warning status. For this, I have created an SSIS Custom Task to create a formatted excel report with/o header.

See Also: Create SSIS Custom Task with a complete example

I am sharing this Task (DLL) and will discuss the use. click below link to download the .dll files.

I have created two version of this Task depends on .NET version.

1. Download for .NET version 3.5 - ExcelReport_NET-V3.5.zip
2. Download for .NET version 4 - ExcelReport_NET-V4.zip (Updated UI)

Extract it and install. Please check my post to install SSIS Custom Task. 
Install supporting dlls also, comes with the ZIP.

After the installation, you will get the SSIS task in the Tool list.

After Install the Excel Report Task

Description of the Task with Example

Now, consider the below example to understand the use of this Task.

Let I have to create an Excel report of Employee List where I would mark all names as RED if his/her age is Above/Equal 40.

All employee information is available in a DB table named EMP_INFO.

Also, I have to put report information at the top of the Excel sheet, like Report Name, Report Creation Date etc.

So that, technically I will create an Excel Report which will contain the report information at the top of the sheet followed by the Employee information with the RED mark depends on the emp age (40).

Let's start to Configure the Task to generate

1. Double Click on the Task to Edit the Property using UI.

Excel Report Task UI

2. There are 5 sections available to give parameters value.

First one is the General Section, where you can change the Task name and description

The second one is DB info, Where I will put DB connection related info like Server Name, Database Name etc.

The third is Query, which is the SQL statement for the Report Header and Data/Body information.

The Forth is Excel Info, which is Excel related info, like Excel File name, Path to save the file, is the Excel file is New or Old, which sheet you want to modify if the Excel File already exists.

The Fifth and the Last one is Excel Formatting, by which we will Format the Excel Row/Column/Cell depends on some condition.

3. Now I am filling all the required field to generate the example employee report.

First, I will provide the DB info to connect the Database and run the SQL statement.

Second, I will provide two SQL. One for the Report information (Header) and the other for the Employee Information

Select "Employee List with Age Information" [Report Name], getutcdate() as [REPORT Creation Date]

Select ID as EMP_ID, Name, Age from [dbo].[EMP_INFO]

Third, I will put the Excel File Information

• OutputFilePath -> Excel File Path -> E:\TMP (in my case)
• OutputFileName -> Excel File Name -> Emp_Report.xls (in my case)
• IsFileNewOrOld -> New or Existing -> new (in my case)
• SheetName -> Output Sheet Name -> Emp List  (in my case)
• SheetNumber -> Position Of the Sheet -> 1  (in my case, I like to see as the first sheet)

Lastly, I will provide the Column and Row Formatting clause.

• ColumnFormatString -> Clause to Formate Full Column/Cell with a condition -> HN,Age,GE,40,HN,Name,FM,B; (in my case, see description section of UI for the menning of HN,GE,FM etc)
• RowFormatString -> HN,Age,GE,40,CL,250;10;10; (in my case)

After setting all the property, it will look like below image ...

After Set all Property

4. Now all is set to run the task. After running the Task, I have got a new Excel File in E:\TEM location as EMP_REPORT.xls. See below image (Excel Report with all Condition.)

Excel Report with all Condition

NOTE: For Property Description, please select the property and look at the description section of UI.

Property Description