Setup SQL Server 2008 Maintenance Plan Email Notifications

To take backup most of the SQL installs that I maintain, SQL dumps to disk and then copy to tape. I use a simple maintenance plan that dumps all user databases to the local disk and then a cleanup task that purges backup files older than a set number of days. An email alert with either success or fail in the subject line is sent out after each maintenance plan task is completed. Here we will review step by step how to add email notifications to your existing SQL 2008 maintenance plan.


Setting up an alert to e-mail an operator with a message is a multiple step process. You can setup database mail, define an operator and an alert. So how do you send an alert to an operator?
Step: 1
Database Mail: First setup database mail with a profile named "TestDb/SQLAlerts". The profile can be named anything but in these instructions, the profile name SQLAlerts is referenced. If you wish to use a different profile name just substitute accordingly. For procedures in setting up database mail, see this Post First
Step: 2
Define Operator : Connect to the instance using Microsoft SQL Management Studio
Double Click SQL Server Agent-->Right Click on Operators and select New Operator

Specify an operator Name, E-mail name

Select Ok button. New operator will be create.

Step: 3

Setup SQL Agent Settings
Note: This step is often overlooked when creating an alert for the first time. SQL Agent must be setup correctly for operators to receive an alert e-mail.
Right Click SQL Server Agent > select Properties

Popup window will display select Alert System in the left pane then follow below steps.
Checkmark > Enable mail profile
Verify Mail system: Database Mail
Verify Mail Profile: TestDB
Checkmark > Include body of e-mail in the notification message
Click OK.

Restart SQL Agent to activate settings.
Warning: Restarting SQL Agent will cancel any executing jobs.

Next, right click on designated maintenance plan (assuming one is already configured) and select Modify

This should bring up the design window with the current tasks

From the Toolbox window Drag and drop Notify Operator Task to Design window twice. One for success and the other for fail. Notify Operator Task, it shows cross mark.  

We need to configure to that to do that Double click each Notify Operator Task > check which operators to notify if there are more than one > fill out Subject and Body fields > select OK

Design window with the current tasks will look like this.

Connect the backup database task to each Notify Operator Task and make sure the arrows are pointing down.  Designate one of the Notify Operator Task objects connection arrows as Failure. Right click on connection and select Failure. This will turn the connection arrow red.

Save maintenance plan and test it.

One of the nice features of the Notify Operator Task in SQL 2008 that wasn't an option in SQL 2005 is the ability to add a unique subject line to the message. Its helpful to be able to see the success or fail status at a glance from just the subject line. 

Scheduling automated backup using SQL server 2008

Are you backing up your SQL Server 2008 databases daily?  You should be, especially if you don't want to lose any of your precious data that you're storing.  It's incredibly easy to setup a maintenance plan in SQL Server 2008 to automatically back up one or more databases. To get started backing up your databases using SQL Server Management Studio follow the steps below.

1. Users to schedule the backup to be taken on a particular interval
2. Once scheduled backup task completion we can send alert message to DBA.
3. Delete the backup copies after a certain period of time

Here's how to setup automatic daily backups for SQL Server 2008 databases:
Schedule the database backup
Step: 1
Login to Sql Management studio and connect to the required database. Now from the object explorer, make sure SQL server agent is running, if not start SQL server agent(Right click and press start) as shown below screen shot.

Step: 2

Expand the Management Node from the object explorer, and then select the maintenance plan node. To schedule maintenance plan, you need to have “SYSADMIN” database role. If you dont see the maintenance node, make sure you have the necessary permission. As shown in the below screen. 

Step: 3

Right click the maintenance plan and then select “New Maintenance Plan”.

Step: 4

Enter the maintenance plan name in the popup box (This can be any name that identifies your task for ). This will identify your backup plan and you should choose a relevant name that suits your plan.

Step: 5

Now you will be in the configuration page for the maintenance plan. . Note the marked area, these are the areas you need to use for setting up the maintenance plan. The marked area in the right top will be used to configure the time that the plan executes. Choose a time so that the database is least used. The bottom left pane shows the tasks that can be utilized to create an sql maintenance plan.

Step: 6

Click on the calendar item shown in the right side top. This will bring the job schedule properties popup window that configure the execution time/frequency of the tasks. Configure the data carefully so that it suits your requirement. Usually database backups are taken daily basis. Make sure you are selecting proper time so that your database is least used. Click "Ok"once you finish.

Step: 7

From the maintenance plan tasks pane in the left side, select the Backup Up Database Task, this will be used to take backups for the databases. Drag and drop backup database task to the right side(as shown in the Screen shot). 

Step: 8

Form the above screen shot you can find Backup Up Databse Task is showing a Cross symbol. It means that it is not yet configured. To configure double click on the Backup Up Database Task, it will open up a new window that allows you to configure the database configuration for the backup. Here you configure the databases that you need to backup for one or more databases, then specify a location for the backup, specify the extension for the backup files etc.

From the pop up modal window, by clicking on “Databases” dropdown, you will be able to select the required databases. Also configure the file location, extension for the backup file etc.

Step: 8 

Click Ok once finished. Now backup plan configuration is over. The backup files will be created on the scheduled time to the mentioned folder. The name of the file will be created by appending the date so that you can identify the back up for a particular date.

Since the backup files are created frequently,… 

it is a good practice that you delete backup files after a certain period of time. For this you need to execute clean up task  along with the Maintenance plan. You can configure the clean up task as follows.

From the left side pane, drag and drop "Maintenance Cleanup Task."

Step: 9
Double click on the dropped item inorder to edit the clean up properties. Here you need to specify the backup location, and file extension for the back up files and specify the age of the file. It is a good practice that you keep one month old data, and delete anything prior to one month.

Once you click ok, then save the maintenance plan. 

Step: 10
After doing all these you can find scheduled backup task under Maintenance plan in the object folder. You can either wait till the next execution time or execute it manually in order to check whether everything is working fine. To execute manually select the plan and right click on it and then select "Execute" option. Check in the destination folder whether it is working fine or not.

Magic table in SQL ?

Magic tables are nothing but INSERTED, DELETED table scope level, These are not physical tables, only Internal tables.

This Magic table are used In SQL Server 6.5, 7.0 & 2000 versions with Triggers only. But, In SQL Server 2005, 2008 & 2008 R2 Versions can use these Magic tables with Triggers and Non-Triggers also.

Using with Triggers:If you have implemented any trigger for any Tables then,
1.Whenever you Insert a record on that table, That record will be there on INSERTED Magic table.
2.Whenever you Update the record on that table, That existing record will be there on DELETED Magic table and modified New data with be there in INSERTED Magic table.
3.Whenever you Delete the record on that table, That record will be there on DELETED Magic table Only.

These magic table are used inside the Triggers for tracking the data transaction.
Using Non-Triggers:You can also use the Magic tables with Non-Trigger activities using OUTPUT Clause in SQL Server 2005, 2008 & 2008 R2 versions.

Line Break In SQL Sever...

I need to format a string, like separate lines.  While displaying that string i need split into two lines. To do that follow the bellow steps. we have two different options to get new line.

What is difference between Line Feed (\n) and Carriage Return (\r)?

   1. Line Feed – LF – \n – 0x0a – 10 (decimal)
   2. Carriage Return – CR – \r – 0x0D – 13 (decimal)
Different operating systems have a different way of understanding new line. Mac only understands ‘\r’ as new line, while Unix and Linux understand ‘\n’ as new line character. Our favorite OS windows needs both the characters together to interpret as new line, which is ‘\r\n’. This is the reason why a file created in one OS does not open properly in another OS and makes it messy.

we can create a new line in SQL Server. It is a very simple script yet very useful when we have to do run print something or generate scripts. Here is two examples below that are very easy to understand. In the first example, there are no new line chars inserted and for the same, everything is displayed in a single line. However, in the second example, new line char is inserted and the lines are separated with a new line.

Example 1: No new line feed char

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT ('SELECT FirstLine AS FL SELECT SecondLine AS SL' )
GO

Example 2: With new line feed char

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT ('SELECT FirstLine AS FL ' +@NewLineChar +'SELECT SecondLine AS SL' )
GO

Claim DB Space After Deleting Records in Table - Reduce DB Space

Recently I have deleted unwanted records from my SQL server database table, what i realise is even after deleting records, space used by database is not reducing.  After browsing help available on Internet, I found out

1) Whenever we delete records from table, sql server doesn't reduce size of database immediately.
2) Even after deleting table , sql server doesn't reduce size of database.
3) Instead of Freeing space for deleted records, sql server marks pages containing deleted records as free pages, showing that they belong to the table. When new data are inserted, they are put into those pages first. Once those pages are filled up, SQL Server will allocate new pages.

So In order to claim database space after deleting records in Table, go through following steps:
1) Check what is Size of your Database using following command?EXEC SP_SPACEUSED

2) Delete Records from table, If you have already did that skip this step.

3) Run below command to claim unused database space.
DBCC SHRINKDATABASE(0)

DBCC SHRINKDATABASE command - Shrinks the size of the data and log files in the specified database.

Best Practise to use this command1. 
1.A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.

2. Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
3. A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.

More reading on this command
http://msdn.microsoft.com/en-us/library/ms190488.aspx 

http://dotnetguts.blogspot.in/2011/02/claim-db-space-after-deleting-records.html

http://blog.sqlauthority.com/2010/09/07/sql-server-index-levels-and-delete-operations-page-level-observation/

What is a Common Table Expression (CTE)

For any operation over a temporary result set, what are the options SEL Server has to offer? We do have a Temp table, a table variable, table valued parameters and of course not to forget table valued function. But with the onset of SQL Server 2005 and onwards, a very powerful feather has been added for the programmers' benefit: Common Table Expression (CTE). It simplifies complex queries and most importantly enables you to recurs.


A CTE can be thought of as a temporary result set and are similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. A CTE is generally considered to be more readable than a derived table and does not require the extra effort of declaring a Temp Table while providing the same benefits to the user. However; a CTE is more powerful than a derived table as it can also be self-referencing, or even referenced multiple times in the same query.



Background:
Most of the developers while writing the stored procedures they create the temp tables or table variables. They need some table to store the temporary results in order to manipulate the data in the other tables based on this temp result.
The temp variables will be stored on the tempdb and it needs to be deleted in the tempdb database.
The table variable is best when compare with the temp tables. Because the table variable initially will be there in the memory for the certain limit of size and if the size increase then it will be moved to the temp database. However the scope of the table variable is only up to that program. When compare with table variable the CTE is best. It just store the result set like normal view.
CTE (Common Table Expression):
The CTE is one of the essential features in the sql server 2005.It just store the result as temp result set. It can be access like normal table or view. This is only up to that scope. 


The syntax of the CTE is the following.
WITH name (Alias name of the retrieve result set fields)
AS
(
//Write the sql query here
)
SELECT * FROM name
Here the select statement must be very next to the CTE. The name is mandatory and the argument is an optional. This can be used to give the alias to the retrieve field of the CTE.
CTE 1: Simple CTE
WITH ProductCTE
AS(  SELECT ProductID AS [ID],ProductName  AS [Name],CategoryID  AS  [CID],UnitPrice  AS  [Price]
  FROM Products
)SELECT * FROM ProductCTE
Here all the product details like ID, name, category ID and Unit Price will be retrieved and stored as temporary result set in the ProductCTE.


This result set can be retrieved like table or view.


CTE2:Simple CTE with alias 
WITH ProductCTE(ID,Name,Category,Price)AS(  SELECT ProductID,ProductName,CategoryID,UnitPrice
  FROM Products
)SELECT * FROM ProductCTE
Here there are four fieds retrieves from the Products and the alias name have given in the arqument to the CTE result set name.


It also accepts like the following as it is in the normal select query.
WITH ProductCTE
AS(  SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price]
  FROM Products
)SELECT * FROM ProductCTE


CTE 3: CTE joins with normal table
The result set of the CTE can be joined with any table and also can enforce the relationship with the CTE and other tables.
WITH OrderCustomer
AS(  SELECT DISTINCT CustomerID FROM Orders
)SELECT C.CustomerID,C.CompanyName,C.ContactName,C.Address+', '+C.City AS [Address] FROMCustomers C INNER JOIN OrderCustomer OC ON OC.CustomerID = C.CustomerID
Here the Ordered Customers will be placed in the CTE result set and it will be joined with the Customers details.
CTE 4: Multiple resultsets in the CTE
WITH MyCTE1
AS(  SELECT ProductID,SupplierID,CategoryID,UnitPrice,ProductName FROM Products
), 
MyCTE2
AS(  SELECT DISTINCT ProductID FROM "Order Details"
)SELECT C1.ProductID,C1.ProductName,C1.SupplierID,C1.CategoryID FROM MyCTE1 C1 INNER JOINMyCTE2 C2 ON C1.ProductID = C2.ProductID
Here, there are two result sets that will be filtered based on the join condition.


CTE 5: Union statements in the CTE
WITH PartProdCateSale
AS(SELECT ProductID FROM Products WHERE CategoryID = (SELECT CategoryID FROM CategoriesWHERE CategoryName='Condiments')UNION ALL
SELECT ProductID FROM Products WHERE CategoryID = (SELECT CategoryID FROM CategoriesWHERE CategoryName='Seafood')
)SELECT OD.ProductID,SUM(OD.UnitPrice*OD.Quantity) AS [Total Sale] FROM "Order Details" ODINNER JOIN PartProdCateSale PPCS ON PPCS.ProductID = OD.ProductID
GROUP BY OD.ProductID
Normally when we combine the many result sets we create table and then insert into that table. But see here, we have combined with the union all and instead of table, here CTE has used.


CTE 6: CTE with identity column
WITH MyCustomCTE
   AS   (      SELECT CustomerID,row_number() OVER (ORDER BY CustomerID) AS iNo FROM 
         Customers
   )SELECT * FROM MyCustomCTE
Conclusion:
I hope that the CTE will be very useful in the sql server. When we work the web applications the performance must be consider in the both front-end and Back-end. First we must retrieve the result effectively in the back end.



Reference Links:
http://www.codeproject.com/Articles/275645/CTE-In-SQL-Server
http://msdn.microsoft.com/en-us/library/ms190766.aspx
http://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/
http://www.sqllion.com/2010/08/common-table-expressions-cte/
http://www.sqlservercentral.com/articles/Development/commontableexpressionsinsqlserver2005/1758/

SSIS to SQL Server Data Type Translations

While implementing SSIS package i have faced conversion problems SSIS data types. So here is bellow chart SSIS data types to SQL Server data types. 

SSIS Data Type	SSIS Expression	SQL Server
single-byte signed integer	(DT_I1)
two-byte signed integer	(DT_I2)	smallint
four-byte signed integer	(DT_I4)	int
eight-byte signed integer	(DT_I8)	bigint
single-byte unsigned integer	(DT_UI1)	tinyint
two-byte unsigned integer	(DT_UI2)
four-byte unsigned integer	(DT_UI4)
eight-byte unsigned integer	(DT_UI8)
float	(DT_R4)	real
double-precision float	(DT_R8)	float
string	(DT_STR, «length», «code_page»)	char, varchar
Unicode text stream	(DT_WSTR, «length»)	nchar, nvarchar, sql_variant, xml
date	(DT_DATE)	date
Boolean	(DT_BOOL)	bit
numeric	(DT_NUMERIC, «precision», «scale»)	decimal, numeric
decimal	(DT_DECIMAL, «scale»)	decimal
currency	(DT_CY)	smallmoney, money
unique identifier	(DT_GUID)	uniqueidentifier
byte stream	(DT_BYTES, «length»)	binary, varbinary, timestamp
database date	(DT_DBDATE)	date
database time	(DT_DBTIME)
database time with precision	(DT_DBTIME2, «scale»)	time(p)
database timestamp	(DT_DBTIMESTAMP)	datetime, smalldatetime
database timestamp with precision	(DT_DBTIMESTAMP2, «scale»)	datetime2
database timestamp with timezone	(DT_DBTIMESTAMPOFFSET, «scale»)	datetimeoffset(p)
file timestamp	(DT_FILETIME)
image	(DT_IMAGE)	image
text stream	(DT_TEXT, «code_page»)	text
Unicode string	(DT_NTEXT)	ntext

Reference Links
http://www.bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations
http://www.sqlservercentral.com/blogs/dknight/2010/12/22/ssis-to-sql-server-data-type-translations/