SQL Database Growth Tracking

In this article I am sharing a simple T-SQL code to track database growth for specific database. This could be a very simple query for SMEs but it can really help newbies:

/*************************************************
Purpose : Track Database Growth for a specific DB
Create By : Hari Sharma
**************************************************/
SELECT
   BackupDate =
   CONVERT(VARCHAR(10),backup_start_date, 111)
   ,SizeInMBs=FLOOR(backup_size/1024000)
FROM msdb..backupset
WHERE
   database_name = DB_NAME() --Specify DB Name
   AND type = 'd'
ORDER BY
   backup_start_date desc

Dynamic Database Connection using SSIS ForEach Loop Container

Did you ever come across a requirement where you have to run the same SQL statement(s) in multiple database server/instances using SSIS?

Many of  us are worried about how to connect through multiple Databases from different Server using single Dynamic Connection. I want to explain this feature in this article. Basically, I want to execute one query (to calculate Record Counts for a given table) on a set of servers (which can be Dev, Test,UAT, PreProduction and Production servers). In my example, I am using ForEach Loop to connect to the servers one by one--> Execute the Query --> Fetch and store the data.

So here is the approach:

• Create a Table in your local database (whatever DB you want) and load all the connection strings. Within SSIS package, use Execute SQL Task to query all the connection strings and store the result-set in a variable of object type.
• Use ForEach Loop container to shred the content of the object variable and iterate through each of the connection strings.
• Place an Execute SQL task inside ForEach Loop container with the SQL statements you have to run in all the DB instances. You can use Script Task to modify your query as per your need.

Below is the details with an example:

STEP1:
To begin, Create two tables as shown below in on of the environment:

-- Table to store list of Sources
CREATE TABLE SourceList (
   ID [smallint],
   ServerName [varchar](128),
   DatabaseName [varchar](128),
   TableName [varchar](128),
   ConnString [nvarchar](255)
)

GO

-- Local Table to store Results
CREATE TABLE Results(
   TableName  [varchar](128),
   ConnString [nvarchar](255),
   RecordCount[int],
   ActionTime [datetime]
)
GO

STEP 2:
Insert all connection strings in SourceList table using below script:
INSERT INTO SourceList

SELECT 1 ID,
'(local)' ServerName, --Define required Server
'TestHN' DatabaseName,--Define DB Name
'TestTable' TableName,
'Data Source=(local);Initial Catalog=TestHN;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;' ConnString
 
Insert as many connections as you want.
 
STEP 3:
Add new package in your project and rename it with ForEachLoopMultipleServers.dtsx. Add following variable:
 

Variable	Type	Value	Purpose
ConnString	String	Data Source=(local); Initial Catalog=TestHN; Provider=SQLNCLI10.1; Integrated Security=SSPI; Auto Translate=False;	To store default connection string
Query	String	SELECT '' TableName, N'' ConnString, 0 RecordCount, GETDATE() ActionTime	Default SQL Query string. This can be modified at runtime based on other variables
SourceList	Object	System.Object	To store the list of connection strings
SourceTable	String	Any Table Name. It can be blank.	To store the table name of current connection string. This table will be queried at run time

STEP 4:
Create two connection managers as shown below:


Local.TestHN: For local database which has table SourceList. Also this will be used to store the result in Results table.
DynamicConnection: This connection will be used for setting up dynamic connection with multiple servers.
Now click on DynamicConnection in connection manager and click on ellipse to set up dynamic connection string. Map connection String with variable User::ConnString.

STEP 5:
Drag and drop Execute SQL Task and rename with "Execute SQL Task - Get List of Connection Strings". Now click on properties and set following values as shown in snapshot:
Result Set: Full Result Set
Connection: Local.TestHN
ConnectionType: Direct Input
SQL Statement: SELECT ConnString,TableName FROM SourceList

Now click on Result Set to store the result of SQL Task in variable User::SourceList.

STEP 6:
Drag and drop ForEach Loop container from toolbox and rename with "Foreach Loop Container - DB Tables". Double click on ForEach Loop container to open Foreach Loop Editor. Click on Collection  and select Foreach ADO Enumerator as Enumerator. In Enumerator configuration, select User::SourceList as ADO object source variable as shown below:

STEP 7: Drag and drop Script Task inside ForEach Loop container and double click on it to open Script Task Editor. Select User::ConnString,User::SourceTable as ReadOnlyVariables and User::Query as ReadWriteVariables. Now click on Edit Script button and write following code in Main function:

public void Main()

{
   try
   {
      String Table = Dts.Variables["User::SourceTable"].Value.ToString();
      String ConnString = Dts.Variables["User::ConnString"].Value.ToString();
      MessageBox.Show("SourceTable = " + Table + "\nCurrentConnString = " + ConnString);
      //SELECT '' TableName,N'' ConnString,0 RecordCount,GETDATE() ActionTime
      string SQL = "SELECT '" + Table + "' AS TableName, N'" + ConnString + "' AS ConnString, COUNT (*) AS RecordCount, GETDATE() AS ActionTime FROM " + Dts.Variables["User::SourceTable"].Value.ToString() + " (NOLOCK)";

      Dts.Variables["User::Query"].Value = SQL;
      Dts.TaskResult = (int)ScriptResults.Success;
   }
   catch (Exception e)
   {
      Dts.Log(e.Message, 0, null);
   }
} 
STEP 8:
Drag and drop Data Flow Task and double click on it to open Data Flow tab. Add OLE DB Source and Destination. Double click on OLE DB Source to configure the properties. Select DynamicConnection as OLE DB connection manager and SQL command from variable as Data access mode. Select variable name as User::Query. Now click on columns to genertae meta data.

Double click on OLE DB Destination to configure the properties. Select Local.TestHN as OLE DB connection manager and Table or view - fast load as Data access mode. Select [dbo].[Results] as Name of the table or the view. now click on Mappings to map the columns from source. Click OK and save changes.
Finally DFT will look like below snapshot:

STEP 9: We are done with package development and its time to test the package.
Right click on the package in Solution Explorer and select execute. The message box will display you the current connection string.

 Once you click OK, it will execute Data Flow Task and load the record count in Results table. This will be iterative process untill all the connection are done. Finally package will execute successfully.

You can check the data in results table:
Here is the result:

SELECT * FROM SourceList

SELECT * FROM Results

Database Snapshots To Test Loads

When testing data warehouse ETL routines it’s often necessary to be able to reload the same data several times before you get the code dialed in and ready for production work.  One way to be able to do this is to simply TRUNCATE the target table, assuming there are no foreign keys on it.
If you want to save data from previous loads and only remove the records from the most recent load you can DELETE the data.  Of course this is no big deal because you have the load date in the new records, right?
Yet another method to identify the newly added records would be to use an OUTPUT clause on the INSERT statement.  Then it would be easy to identify the newly inserted records from the OUTPUT table.  Likewise, any records that are UPDATED could also be restored to their original state from the OUTPUT table.  The downside is the INSERT and UPDATE statements would need to be modified to use the OUTPUT clause for testing purposes.  Then you would have to DELETE newly inserted records and UPDATE the updated records to restore the database to it’s original state.  If several tables are involved in the ETL process you have your hands full to capture the new and changed records and then reset them back.
A much easier way to reset the data warehouse to its original state after an ETL test load is to use a database SNAPSHOT.  To me this whole concept sounded kind of intimidating but after I got past my reluctance and gave it a try I realized the power of this tool in my SQL toolbox.
This post will provide a general introduction to what a database snapshot is…  To demonstrate how snapshots can be used to keep data fully recoverable, even in an environment where database backups are not an option… And ultimately, to increase our understanding and awareness of database snapshots.

Advantages of Snapshots

Easy method to roll back changes to the database to a previous point in time.

It is a suitable alternative to saving a table to a tmp_ table; saving data to an OUTPUT table; rolling back a transaction.

Snapshots are not limited to data.  Changes to stored procedures, tables, triggers and other DDL structures can also be reverted back to their original state.

Reverting the database to its original state from a SNAPSHOT is just one T-SQL command.

SNAPSHOT databases take up a minimal amount of space based on the amount of data that is changed or added.

Disadvantages of Snapshots

They do take up some space on the server.  The more the data changes, the more the snapshot will grow.

To revert the database back using the snapshot you’ll need to have exclusive access to the database.  Kick everyone out.  Still, this was also be the case if you needed to restore data from a backup.

Snapshots can be abused.  It could be very easy to have a bunch of ever growing snapshots that no body knows who owns them.

There is a minimal performance hit when writing the existing records out to the snapshot.

If a db is reverted back it could undo the DDL work of someone else.  This is all the more reason to save stored procedures and T-SQL to a source control system.

So let’s set how snapshots really work by doing a test run.
The first step is to take create the snapshot database.  Although you can see snapshots in SQL Server Management Studio (SSMS) there is no way to create a snapshot through the interface.   It can only be created by executing a simple T-SQL command.
Before we can create the snapshot we’ll need to know what data files make up the database we’re shooting.

USE master
GO

EXEC sp_helpdb AdventureWorks;

From this command we just learned that the data file for this database is on:
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SnapShots\AdventureWorks_Data.mdf
In a production database there will probably be several data files involved.  No worry.  For the purposes of snapshots they are all the same.
Now that we know the data files we can create the snapshot:

CREATE DATABASE AdventureWorks_SS ON
(  NAME = AdventureWorks_Data,
   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SnapShots\AdventureWorks_Data.mdf' )
AS SNAPSHOT OF AdventureWorks;

GO

In this case, all we did was create a database with the “_SS” extension and we stored the datafile in a SnapShots directory.  You can use whatever convention you want to name your database and storage location.  I like to keep my snapshot storage separate from my other storage locations.
Also, if the SnapShots directory does not exist the CREATE DATABASE command will fail.  You must have the directory created BEFORE executing the command.  If you don’t, the error message will be clear.
You have noticed that we are not executing this command from the AdventureWorks database itself.  You’ll need to run the command from any other database on the server.  Master is always a good choice for these type of activities.
At initial creation time the SNAPSHOT database does not have any records.  Any  INSERT, UPDATE and DELETE operations against the AdventureWorks database will result in the “original” records being written to the snapshot.

USE AdventureWorks;
GO

UPDATE    Person.Contact
SET        Suffix = 'XVIII'
WHERE    LastName = 'Powell';
--(116 row(s) affected)

SELECT    TOP (5) FirstName,
        LastName,
        Suffix
FROM    Person.Contact
WHERE    LastName = 'Powell';

Isabella    Powell    XVIII
Natalie    Powell    XVIII
Alexandra    Powell    XVIII
Sydney    Powell    XVIII
Katherine    Powell    XVIII

So we just updated all 116 of the Powells to have a suffix if XVIII.  I don’t know about you but I don’t know any XVIIIs… Except Louis.  This update was probably a mistake.
If we didn’t have a snapshot of this data we would have been in trouble.  We just don’t know how many of these folks had suffixes such as Sr., Jr., III, IV, MD, JD, etc.
Let’s use our snapshot to restore the database to it’s original state:

USE master;
GO

RESTORE DATABASE AdventureWorks FROM DATABASE_SNAPSHOT = 'AdventureWorks_SS';

This “restore” took 5 seconds on my slow, 4-year old laptop.  Certainly no worse than the amount of time it would have taken me to restore from a backup, if I had one, or from a temp table.
But did it work?

SELECT    COUNT(*)
 FROM    Person.Contact
 WHERE    LastName = 'Powell'
 AND    Suffix = 'XVIII';

This query returned ZERO, nary, nilch, 0.
Now for the last step, clean up your work and delete the snapshot database.

DROP DATABASE AdventureWorks_SS;

And you are finished.
Conclusion:

Although there are some limitations, snapshots offer a low cost option, in time and system resources, to rollback the state of an entire database to a previous point in time without the need to restore the database from backups.  The amount of disk space needed for snap shots is directly proportional to the amount of data that actually changed, not the size of the original database.  The amount of time to create the snapshot is minimal, only requiring the execution of a single T-SQL command.  The time and effort to restore the database to the point in time when the snapshot was taken is minimal, assuming millions of records have not changed.

Source: http://www.daveturpin.com/2009/09/using-database-snapshots-to-test-loads/

List of Databases

If you plug this query into a SQL Server Central Management server you’ll have a snapshot of all your instances that are part of the CMS.
This query will return the database name, the overall size of the database including all data and log files, the create date, owner, compatibility level, online vs offline, and update stats info.

It’s interesting to find databases owned by staff that are no longer around, compatibility levels to old versions for no obvious reason, and create and update stats that are turned off.

So here’s the query:

Select    Convert(int, Convert(char, current_timestamp, 112)) as CaptureDate
        , d.database_id
        , d.[name]
        , CAST(SUM(mf.size * 8096.0 / 1000000000) AS NUMERIC(18, 2)) AS [db_size (G)]
        , d.recovery_model_desc AS [Recovery Model]
        , d.create_date
        , suser_sname(d.owner_sid) AS [Owner]
        , d.[compatibility_level]
        , d.state_desc AS [State]
        , d.is_auto_create_stats_on
        , d.is_auto_update_stats_on
FROM        sys.databases d
        Inner Join sys.master_files mf
            ON    mf.database_id = d.database_id
WHERE    d.database_id > 4    — Exclude the system databases.
GROUP BY d.database_id
        , d.[name]
        , d.recovery_model_desc
        , d.create_date
        , d.owner_sid
        , d.[compatibility_level]
        , d.state_desc
        , d.is_auto_create_stats_on
        , d.is_auto_update_stats_on