SQL SERVER – How Do We Find Deadlocks? – Notes from the Field #086

[Note from Pinal]: This is an 86th episode of Notes from the Fields series. Deadlock is very annoying when they happen in our database. In my early career my biggest frustration was I had no idea why the deadlocks are happening and because I did not know the root cause, I was never able to solve them. As time passed by, now I have a better understanding of this situation.

JohnSterrett SQL SERVER   How Do We Find Deadlocks?   Notes from the Field #086

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very interesting subject of how to find deadlocks in SQL Server. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


For one reason or another I tend to find out that DBAs in the field have a hard time detecting and understand how deadlocks occur.  A deadlock occurs when two or more sessions are blocked on each other and one statement has to be terminated to prevent the sessions from being hung on each other.

Let’s take a quick look at locking, blocking and deadlocks with a real-world example that is included in the video below. In the simple example provided in the video there are two sessions each with statements that update the same tables concurrently with explicit transactions that are held until the statements are committed or rollback. For example, session one updates all the records in table one while session two updates all the records in table two. At this point we have locking. Session one has a lock on table one and session two has a lock on table two, but neither session is blocking each other from doing work.  Next while our statements are not committed in either session we have session one update the same records being updated on session two. This is known as blocking because session two is blocking session ones update table two.  Next we have session two attempting to update table one which is currently being locked by session one. Now we have a deadlock because session one is blocked by session two and session two is blocked by session one. To prevent both sessions from being hung one of them becomes the deadlock victim while the other get to continue its work.

How Do We Find Deadlocks?

Before SQL Server 2008 we would have to rely on configuring trace flags, service broker, or a server side traces to capture deadlock information so we could be proactive and take action to prevent the deadlocks from reoccurring. In SQL Server 2008 we were given extended events and could leverage a default system health extended event which is automatically running in the background to identify deadlocks. This is a great addition because we didn’t have to manually configure anything to catch the deadlocks. In SQL Server 2012 this got easier because we could rely on the graphical user interface inside of SQL Server Management Studio to filter our system health extended event to focus straight on our deadlocks.

In the video below you will be able to create deadlocks and be able to proactively monitor them with the native tools provided with SQL Server Management Studio in SQL Server 2012 or 2014.

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Database Snapshots and Replica Causing Write Delays

For folks who have not worked with SQL Server Database snapshot feature, please refer to the blog I wrote sometime back (SQL SERVER – 2008 – Introduction to Snapshot Database – Restore From Snapshot). I am giving this reference because some might not be aware of this powerful capability with SQL Server.

In the above article, one of the readers did ask if there is any performance overhead of having multiple Database snapshots with SQL Server.  Well, database snapshots are built on the principle of COW (Copy On Write) where a database page is copied to the snapshots when they are written in the primary database. That is the whole idea of Database snapshots. Hence, if you have multiple database snapshots configured, for each write on the primary DB, we will be indirectly getting “n” more write based on the number of snapshots made on the DB. It is as simple as that.

In one of my customer locations, they were getting heavy performance issue on their writers and when I got an option to inspect, I found there were close to 6 database snapshots that were created in that database. When I asked, they said it is some sort of backup copy so that they can come back onto that version. Being a heavy transactional system, this showed up easily when the load was high.

The immediate question was, how can I identify we have far too many Snapshots created on the databases? I suggested a simple script so that they can start looking into it from time to time:

IF EXISTS( SELECT source_database_id
FROM sys.databases
WHERE source_database_id IS NOT NULL
GROUP BY source_database_id
HAVING COUNT(*) > 1)
AND EXISTS(
SELECT waiting_tasks_count FROM sys.dm_os_wait_stats
WHERE wait_type = 'replica_writes' AND waiting_tasks_count > 0)
BEGIN
SELECT
DB_NAME(source_database_id) AS 'SourceDB',
COUNT(*) AS 'NumSnapshots'
FROM sys.databases
WHERE source_database_id IS NOT NULL
GROUP BY DB_NAME(source_database_id)
HAVING COUNT(*) > 1
SELECT * FROM sys.dm_os_wait_stats
WHERE wait_type = 'replica_writes' AND waiting_tasks_count > 0
END

In their environment this showed up, having 6 snapshots for their main transactional database and they were able to delete some of them based on age.

Our database systems are sensitive to such mistakes and can cause us huge performance overhead. The development team thought that the system was deteriorating over a period of time and were taking this performance overhead without analyzing what could be the problem. I am a firm believer that one should always know the root cause for a problem rather than just solving the problem superficially.

Do let me know if you use Snapshots actively in your environments and have you faced such issues in your SQL Server box? One of the key places where I have seen people use Database Snapshots is when Mirroring is configured and we want to query from our Mirror Server. Are there other places where you have relied on Database Snapshots? Let me know via your comments.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Stress free life with Spotlight for SQL Server DBA

Being on the cutting edge of technology is something that we need to be prepared as IT professionals. Newer capabilities get into the software almost every single day and it is difficult to keep track of what is getting added into the software’s we work with. I am a firm believer that we need to innovate and keep learning every single day. That is the best way we can be competitive and make our lives easier.

I get an opportunity to speak at multiple conferences. Though the presentations range from short tips to full-fledged presentations for hours. More than the presentation time, it is the process of preparing and adding a new concept in every session is something I cherish a lot. Most of my writings are based on what people ask me from time-to-time.

In a recent conversation I had with a junior DBA, it taught me something interesting that I thought was worth a share. The DBA had emailed me the below screen shot:

 SQL SERVER   Stress free life with Spotlight for SQL Server DBA

He was complaining that he was not able to take the Transaction Log backup, but it was encountering an error. I said, most of the times it is important to watch the error message because in the recent past, I have seen the error messages give us the solution too. These errors are simple and a little search on MSDN can give the answer. Having said that, I did talk about other things that involve around the error. To get started, I would like to list all the backup types we can do with SQL Server:

  • Full backups
  • Differential backups
  • File backups
  • Filegroup backups
  • Partial backups
  • Copy-Only backups
  • Mirror backups
  • Transaction log backups

We are not going to look at each of the types in this blog, but this was important to mention all of them for a future reference.

Find when the last backup done

I wrote back to the DBA stating there is something bigger problem in hand. I always recommend as a seasoned DBA, you need to know when the last backup was taken on the server. This becomes useful to restore the same incase of any disaster:

SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus
ON bus.database_name = sdb.name
GROUP BY sdb.Name

As a contingency plan, we need to be always aware when a backup was taken. It is critical that as a DBA, all critical production databases are monitored from time to time.

In the above error, even though we are on a FULL recovery model – if we try to take a TLog backup without a FULL backup, the error is encountered. So to mitigate this, we need to be taking a FULL backup before the TLog backups can be initiated. The concept is simple right.

SQL Server Dashboard with Spotlight

Ask a naval officer about how a command center looks and how critical it is for them? A better view of the battle field and the strategies for the field is always decided in the command center. In a similar way, a DBA can use Spotlight Dashboard to get a high level understanding of what happens on his servers.

 SQL SERVER   Stress free life with Spotlight for SQL Server DBA

In our example, you can see that our servers don’t have a backup being taken till date. Incase the FULL backup is not taken from our databases over, this alert is being raised. The same thing is shown in a visual form to in the tiles view.

If you ever get a chance to play around with the Spotlight dashboard, I would highly recommend watching some of the out-of-box warnings they arise and take corrective actions immediately.

Note: A database even in the FULL recovery model will act like SIMPLE recovery model till the first FULL backup is taken.

So this error / behavior can get any seasoned DBA into trouble. The Dashboard is a big time savior and can save your job if gone un-noticed. If you want to lead a stress free DBA life, then such early warnings can save you big time.

Final Words

As we wrap up, sometimes we are likely to miss some of the basic principles that we assume from the system. Having something that can remind us of these basics can get us out of trouble in many ways. Most the tools we work give us early warnings and recommendations. It is up to a DBA to look at them and take necessary actions. If you would like to try out these tools, then you can use the SQL Server Evaluation Version to try them out.

Click here for a free 30 day trial of Spotlight on SQL Server

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #024 – What is the Best Recovery Model?

Here is one of the most popular questions I often see people asking on the internet.

Question: What is the best recovery model for my database?

Answer: Every situation is different and each situation has different needs for the recovery model.

SQL Server offers three recovery models: full recovery, simple recovery and bulk-logged recovery. The recovery models determine how much data loss is acceptable and determines whether and how transaction logs can be backed up.

Select Simple Recovery Model if:
* Your data is not critical.
* Losing all transactions since the last full or differential backup is not an issue.
* Data is derived from other data sources and is easily recreated.
* Data is static and does not change often.

-- Set the recovery model to Simple
ALTER DATABASE Advetureworks SET RECOVERY SIMPLE
GO

Select Bulk-Logged Recovery Model if:
* Data is critical, but logging large data loads bogs down the system.
* Most bulk operations are done off hours and do not interfere with normal transaction processing.
* You need to be able to recover to a point in time.

-- Set the recovery model to Bulk Logged
ALTER DATABASE Advetureworks SET RECOVERY BULK_LOGGED
GO

Select Full Recovery Model if:
* Data is critical and no data can be lost.
* You always need the ability to do a point-in-time recovery.
* Bulk-logged activities are intermixed with normal transaction processing.
* You are using replication and need the ability to resynchronize all databases involved in replication to a specific point in time.

-- Set the recovery model to Full
ALTER DATABASE Advetureworks SET RECOVERY FULL
GO

You can switch from any recovery model to another recovery model, but prior to or after the switch, you may need to issue additional transaction log or full backups to ensure you have a complete backup set.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Know Your Backup Before Deleting Database

Earlier last week I wrote a blog around, SQL SERVER – FIX – Msg 3702, Level 16, State 3 – Cannot Drop Database “DB_Name” Because it is Currently in Use. The premise of that blog was driven by some of the demo’s I show at conferences. During one of the UG Meets, I met my good friend Balmukund doing something different and that inspired me to write that blog.

When I wrote that, many people did write back stating it can be dangerous etc. I sort of agree if you are on a production box. As my usecase was based on Demo environment for sessions, lesser did I think about it. Having said that, immediately I thought it would be good to back-it-up with a blog that will prevent us from getting into trouble.

So I wrote back to my friend who wrote an email to me about the dangers. Yes, we need to be careful while dropping databases but be prepared for contingencies. If you have taken a backup of your database, make sure to check if the same is available by querying the MSDB. I gave a typical script would look like this:

USE MSDB
GO
SELECT
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE msdb..backupset.TYPE
WHEN
'D' THEN 'Database'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'Differential'
WHEN 'F' THEN 'Filegroup'
END AS backup_type,
msdb.dbo.backupmediafamily.physical_device_name
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
-- Add the WHERE condition if you want it for a specific database
-- WHERE msdb.dbo.backupset.database_name = 'AdventureWorks2014'
ORDER BY msdb.dbo.backupset.backup_finish_date

 

The above script is a classic way to find all the backups done for a given database or the databases on a given instance.

As a best practice, I would like to figure out from these DMV’s if any backups were taken on every single database before working on them. I am sure as a seasoned DBA, you all are always aware of this important steps when working on production databases.

I know each organization has a restore strategy, so can you let me know about your restore strategies for critical databases? When do you take FULL, Differential and how often you take TLog backups in your production environments? Sharing this is a great way to tell the blog readers about typical usage patterns.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Backup Failure – Error: 3203, Severity: 16, State: 1

In the recent past, I have been writing about different error messages on SQL Server that one encounters. Thanks to my blog readers who have been sharing a lot of errors with me either by email or by blog comments and asking for solution. If I find something interesting and more helpful to other, I write a blog for that. This is one of such blog.

2015-05-05 10:00:00.440 Backup       Error: 3203, Severity: 16, State: 1.
2015-05-05 10:00:00.440 Backup       Read on “M:\MSSQL\TLog\Production_1.ldf” failed: 1(Incorrect function.)
2015-05-05 10:00:00.440 Backup       Error: 3041, Severity: 16, State: 1.
2015-05-05 10:00:00.440 Backup       BACKUP failed to complete the command BACKUP LOG ProductionDB. Check the backup application log for detailed messages.

When I was searching for other emails with error message, I found another one as below.

2014-01-05 16:07:38.19 Backup   Error: 3203, Severity: 16, State: 1.
2014-01-05 16:07:38.19 Backup   Read on “M:\DatabaseName_DefaultData.ndf” failed: 1117(failed to retrieve text for this error. Reason: 15100)

This error would vary based on the actual cause. In first error message, it is operating system error 1 and in second case, it is operating system error 1117. To convert any operating system error number to text, we can use windows net command (net helpmsg) from command prompt as shown below

nethelpmsg 01 SQL SERVER   Backup Failure   Error: 3203, Severity: 16, State: 1

If you notice the second message, we are not seeing the text of the message 1117 but we are seeing “failed to retrieve text for this error. Reason: 15100”

What you should do in case of such error?

You should start looking at system and application event log based in operating system error. The example error which I have shown above are due to hardware level issues. Here is what we found in event log:

Log Name: System
Source: Disk
Date: 5/5/2015 09:32:11 AM
Event ID: 51
Task Category: None
Level: Warning
Keywords: Classic
User: N/A
Computer: SQLServerMachine
Description: An error was detected on device \Device\Harddisk4\DR4 during a paging operation.

Here is an article which explained event ID 51 https://support.microsoft.com/en-us/kb/244780/

Have you ever seen any similar errors? Put into the comment section to help others and share your knowledge. The best thing about these errors is that we get to learn from each other interesting concepts.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Recovering from Snapshots – Notes from the Field #078

[Note from Pinal]: This is a 78th episode of Notes from the Fields series. What do you do when data is deleted accidentally or you are facing disaster? Well, there are plenty of the things, you can do, but when in panic quite often people make mistakes which just intensify the disaster. Database snapshot is very important but less frequently used feature.

JohnSterrett SQL SERVER   Recovering from Snapshots   Notes from the Field #078

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very interesting subject of how to recover the database from snapshots. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


One of the most common – and most forgotten – scenarios in disaster recovery plans is data being updated or deleted accidentally. This surprises me because, in the field, I see accidental data changes as a common disaster. If you have enterprise edition you can leverage database snapshots as a step in your disaster recovery plan to help recover faster, or at least allow you to pull back the majority of your data while you concurrently restore backups (to bring the rest of your data back). Restoring most of your data quickly can be critical with very large databases (VLDBs), especially if you have tight Recovery Time Objectives (RTOs).

Database snapshots require sparse files and store the original pages in these files when they are changed. Therefore, you will want to manage the sparse files and make sure you purge database snapshots as needed. You might also be using database snapshots without even knowing it. If you are doing database integrity checks with the default values, a special snapshot is taken in the background and your integrity checks are executed against that snapshot.

HOW DO WE CREATE A SNAPSHOT?

The following example uses the AdventureWorks2014 sample database. We need to use the CREATE DATABASE syntax, include the names of your data files, and include the file location of the sparse files. Finally, include AS SNAPSHOT OF database syntax to define the database as a snapshot.

CREATE DATABASE [AW2014_Snapshot_morning] ON
( NAME = AdventureWorks2014_Data, FILENAME =
'C:\Demo\AW_data.ss')
AS SNAPSHOT OF AdventureWorks2014

REAL-WORLD ACCIDENTAL DATA CHANGE STATEMENT

Here is a common case of a mistaken DELETE statement. We have a DELETE statement with the primary key included to delete a single row. By mistake we didn’t highlight the filter so all the rows will be deleted.

If you execute this statement, you will see an unexpected result: we deleted all rows in the table as shown below.

notd 78 SQL SERVER   Recovering from Snapshots   Notes from the Field #078

(19972 row(s) affected)

HOW DO WE REVERT FROM SNAPSHOT?

You have two options if you created a database snapshot earlier.

First, you could insert the data back from your snapshot database as shown below. This could be done with SSIS, BCP or many other tools. For this quick example we will do this with an INSERT INTO SELECT statement.

SET IDENTITY_INSERT Person.EmailAddress ON
INSERT INTO
Person.EmailAddress (BusinessEntityID, EmailAddressID, EmailAddress, rowguid, ModifiedDate)
SELECT *
FROM AW2014_Snapshot_morning.Person.EmailAddress
SET IDENTITY_INSERT Person.EmailAddress OFF

Second, you can revert the database from the snapshot. Keep in mind this second option will revert all data changes in the database not just the data deleted in your accidental data change statement.

USE MASTER;
RESTORE DATABASE AdventureWorks2014 FROM
DATABASE_SNAPSHOT = 'AW2014_Snapshot_morning';
GO

From these examples, you can see database snapshots are a tool to help you recover data quickly. Please note that you wouldn’t want database snapshots to be your sole plan for disaster recovery and unplanned data change statements. If your snapshot or the original database suffers corruption, you wouldn’t be able to recover. So make sure you add snapshots into your existing disaster recovery plans, which should – at a minimum – include database backups to give you a better recovery point objective.

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to View the Dirty Pages In Memory of a Database?

I always get an opportunity to talk on topics that are basic from time to time. As I was preparing for GIDS 2015 (Great Indian Developer Summit) this year, I was vividly remembering one of the conversations I had with an attendee last year at our Pluralsight booth. Most of these conversations are forgotten, but this one was unique. During this conversation, the attendee was trying to learn the basics and was curious to listen to me. I always get carried away and spend quite some time with folks who are eager to learn. In this context, I was talking how RDBMS’s use the concept of Write-Ahead Transaction Log for  maintaining ACID properties. The response I got was interesting – “Hey Pinal, is there a way to know which of the pages in memory are currently dirty? Do you know how to get this?”

As I was preparing for this year’s session, this question was on top of my mind and I thought let me take a moment in this blog to show you how this can be achieved. Again, everything we do inside SQL Server can be accessed by the Dynamic Management views and it is important for us to know which ones to use.

T-SQL Scripts

Create the dummy database for test and we will enable few traceflags for this demo.

CREATE DATABASE DirtyPagesDB
GO
USE DirtyPagesDB
GO
-- Disable automatic checkpoint so that data isn't flushed
--
DBCC TRACEON(3505, -1);
DBCC TRACESTATUS();
GO

Let us create a table and enter some values. We will also flush the page using Checkpoint.

CREATE TABLE t1 (Speaker_Bio CHAR(8000))
GO
INSERT INTO t1 VALUES ('SQL'),('Authority')
GO
CHECKPOINT

Let us check if there are any pages dirty currently in our buffer. Since we have done a checkpoint, there will be no rows for the below query.

-- Get the rows of dirtied pages
SELECT
database_name = d.name,
OBJECT_NAME =
CASE au.TYPE
WHEN
1 THEN o1.name
WHEN 2 THEN o2.name
WHEN 3 THEN o1.name
END,
OBJECT_ID =
CASE au.TYPE
WHEN
1 THEN p1.OBJECT_ID
WHEN 2 THEN p2.OBJECT_ID
WHEN 3 THEN p1.OBJECT_ID
END,
index_id =
CASE au.TYPE
WHEN
1 THEN p1.index_id
WHEN 2 THEN p2.index_id
WHEN 3 THEN p1.index_id
END,
bd.FILE_ID,
bd.page_id,
bd.page_type,
bd.page_level
FROM sys.dm_os_buffer_descriptors bd
INNER JOIN sys.databases d
ON bd.database_id = d.database_id
INNER JOIN sys.allocation_units au
ON bd.allocation_unit_id = au.allocation_unit_id
LEFT JOIN sys.partitions p1
ON au.container_id = p1.hobt_id
LEFT JOIN sys.partitions p2
ON au.container_id = p2.partition_id
LEFT JOIN sys.objects o1
ON p1.OBJECT_ID = o1.OBJECT_ID
LEFT
JOIN sys.objects o2
ON p2.OBJECT_ID = o2.OBJECT_ID
WHERE is_modified = 1
AND d.name = 'DirtyPagesDB'
AND
(
o1.name = 't1'
OR o2.name = 't1'
);
GO

Since we got no rows, let us insert a row to check.

INSERT INTO t1 VALUES ('Pinal')
GO

Go ahead and run the above big query again. You are likely to see 2 rows, one for IAM and one for Datapage. If you run the CHECKPOINT, these will also get flushed to the data disk.

-- Clean up
USE MASTER
GO
DROP DATABASE DirtyPagesDB
GO

This was in my mind for a long time to research about this and write. I am sure this will be a good learning for my blog readers too. Do let me know if this was an interesting learning today for you.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Script – Removing Multiple Databases from Log Shipping

Have you ever come across a situation where you have large number of databases in log shipping and you have to remove all of them? If you use SQL Server Management Studio, it would take a long time because you have to Right Click on each database, go to properties, choose Transaction Log Shipping tab, choose remove for secondary, uncheck the box and then hit OK. Though monotonous, these are painful when the number of databases we are really huge.

In the background, it executes stored procedures to remove the metadata from the log shipping related tables in MSDB database.

Below is the sample which runs on primary. I have changed input parameters.

-- primary
EXEC MASTER.dbo.sp_delete_log_shipping_primary_secondary
@primary_database = N'Primary_Database_Name'
,@secondary_server = N'Secondary_Server_Name'
,@secondary_database = N'Secondary_Database_Name'
GO
EXEC MASTER.dbo.sp_delete_log_shipping_primary_database @database = N'Primary_Database_Name'
GO

And below runs on secondary (here also I have changed input parameter)

-- secondary
EXEC MASTER.dbo.sp_delete_log_shipping_secondary_database
@secondary_database = N'Seconday_Database_Name'

Essentially, if we want to remove log shipping we need to get primary database name, secondary database name and secondary server name. I have used metadata table to find that details.

-- Script for removal of Log Shipping from primary
SET NOCOUNT ON
GO
DECLARE @ExecString VARCHAR(MAX)
SELECT @ExecString = 'EXEC master.dbo.sp_delete_log_shipping_primary_secondary
@primary_database = N'''
+ pd.primary_database +'''
,@secondary_server = N'''
+ ps.secondary_server+ '''
,@secondary_database = N'''
+ ps.secondary_database + ''''
+'
go'
FROM   msdb.dbo.log_shipping_primary_secondaries ps,
msdb.dbo.log_shipping_primary_databases pd
WHERE ps.primary_id = pd.primary_id
SELECT @ExecString
GO
DECLARE @ExecString VARCHAR(MAX)
SELECT @ExecString = 'EXEC master.dbo.sp_delete_log_shipping_primary_database @database = N'''+primary_database+'''
go'
FROM msdb.dbo.log_shipping_primary_databases
SELECT @ExecString
GO

Once you run the script, you would get output with execute statement, just copy paste and run into new query window.

Here is the similar script for secondary server.

-- Script for removal of LS from Secondary
DECLARE @ExecString VARCHAR(MAX)
SELECT @ExecString = 'EXEC master.dbo.sp_delete_log_shipping_secondary_database @secondary_database = N'''+secondary_database+'''
go'
FROM log_shipping_secondary_databases
SELECT @ExecString
GO

Note: Above scripts would generate which you need to copy paste in new query window on respective servers.  Please verify before running the output on production server.

Trying to automate and use the power of T-SQL is one of the best things I have always felt. Do let me know if you did these sort of things in your environments? Can you share some of the experiences?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – The Basics of the File System Task – Part 2 – Notes from the Field #075

[Note from Pinal]: This is a new episode of Notes from the Field series. SQL Server Integration Service (SSIS) is one of the most key essential part of the entire Business Intelligence (BI) story. It is a platform for data integration and workflow applications.

andyleonard SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075

In this episode of the Notes from the Field series I asked SSIS Expert Andy Leonard a very crucial question – What are the Basics of the File System Task and where do we start with it? Andy was very kind to answer the questions and provides plenty of information about how a novice developer can learn SSIS from the beginning and become expert in the technology.


Many data integration scenarios involve reading data stored in flat files or performing extracts from a relational (or legacy) system into flat files. Learning how to configure and use the SQL Server Integration Services (SSIS) File System Task will support your efforts when loading data to and from flat files. In a previous article, I described configuring the File System Task to archive a file. In this article, I will repeat the exercise, but I will add flexibility (and complexity – the two always go together) by using SSIS Variables to manage the Source File and Destination Directory locations. This article is an edited version of The Basics of the File System Task, Part 1. I chose to write it this way for those who find this article but haven’t read Part 1.

Remember: SSIS is a software development platform. With “SQL Server” included in the name, it is easy for people to confuse SSIS as a database tool or accessory, but Control Flow Tasks put that confusion to rest.

SSIS provides several Control Flow tasks. Here is a list that provides a good approximation of which tasks I use most, from most-used to least-used:

In this article I provide an advanced example of configuring the SSIS File System Task, shown in Figure 1:

notes 75 1 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 1: SSIS File System Task

The File System Task provides one way to implement an SSIS Design Pattern for source file archival. When you first open the File System Task Editor, you will note several properties in the property grid. Whenever you see an Operation property in an SSIS task editor, know that that property drives the other property selections. Options for the Operation property of the SSIS File System Task are shown in Figure 2:
notes 75 2 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 2: SSIS File System Task Operation Property Options

The Operation options are:

  • Copy directory
  • Copy file (default)
  • Create directory
  • Delete directory
  • Delete directory content
  • Delete file
  • Move directory
  • Move file
  • Rename file
  • Set Attributes

I stated the Operation property drives the other property selections. Take a look at the File System Task Editor when I change the Operation option from “Copy file” (Figure 2) to “Delete file” as shown in Figure 3:
notes 75 3 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 3: The File System Task Editor with the “Delete file” Operation Selected

See? There are less properties required for the “Delete file” operation. The available properties are even more different for the “Set Attributes” operation, shown in Figure 4:
notes 75 4 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 4: The File System Task Editor with the “Set Attributes” Operation Selected

The Operation property changes the editable properties, exposing some and hiding others. With flexibility come complexity. Even though the File System Task is complex, I’ve found the task is stable and extremely useful. Let’s look at a practical example; using the File System Task to archive a flat file.

To begin configuring the SSIS File System Task for file archival, select the “Move file” operation as shown in Figure 5:
notes 75 5 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 5: SSIS File System Task with the “Move file” Operation Selected

Using the IsSourcePathVariable and IsDestinationPathVariable properties extends the flexibility of the File System Task and further changes the list of available properties in the property grid, as shown in Figure 6:
notes 75 6 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 6: Opting to Use Variables for Source and Destination Paths

Note the SourceConnection and DestinationConnection properties are hidden and the SourceVariable and DestinationVariable properties are available in their place. Click the SourceVariable property dropdown, and click “<New variable…>” as shown in Figure 7:
notes 75 7 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 7: Selecting “<New variable…>” from the SourceVariable Property

When the Add Variable window displays, enter “SourceFilePath” for the variable name property and a full path to your source file in the Value textbox, as shown in Figure 8:
notes 75 8 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 8: Configuring the SourceFilePath SSIS Variable

Click the OK button to close the Add Variable window and return to the File System Task Editor. Click the DestinationVariable property dropdown, and then click “<New variable…>” to open a new Add Variable window. Configure the new variable by setting the Name property to “DestinationFolder” and the Value property to a location you wish to move the file, as shown in Figure 9:
notes 75 9 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 9: Configuring the DestinationFolder SSIS Variable

Click the OK button to close the Add Variable window and return to the File System Task Editor. You have configured an SSIS File System Task to move a file using SSIS Variables to manage the source and destination of the file, as shown in Figure 10:
notes 75 10 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 10: An SSIS File System Task Configured to Move a File Using SSIS Variables

The SSIS File System Task is now configured to archive a file. Let’s test it! Click the OK button to close the File System Task Editor. Press the F5 key or select SSIS->Start Debugging to test your work. My result is shown in Figure 11:
notes 75 11 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 11: Successful Test Execution of the SSIS File System Task

Viewing the source and destination directories, we see the file was successfully moved – shown in Figure 12:
notes 75 12 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 12: The File, Moved!

One tricky part when configuring the SSIS File System Task to move a file is realizing that you need to select the actual file for the source and the directory for the destination.

As I stated earlier, the SSIS File System Task is powerful, flexible, and robust. This article has demonstrated another way you can use the File System Task to archive files. Archiving files after loading the data they contain is a common practice in data integration.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)