How to send E-mail/E-mail with Attachments in SQL Server TIP #97

 

In last post, TIP#96 We have configured Database E-mail. Now in this post we will see how to send mail in SQL SERVER.

Sometimes we need to send mail for different requirements like

1) Notification mails like  data inserted /updated/deleted successfully

2)Send data reports like no of amount earned, failed transaction  etc.

for this SQL SERVER provides us stored procedure “sp_send_dbMail”.

The “SP_send_dbmail” has various parameters below are some important parameters like

@profile_Name : The profile which will use to send mail

@Recipients : To whom the mails need to be send

@body : This is message body

@Subject: Subject of the mail

@Query : SQL statement which you want to share

To send a simple E-mail  we can write following statements

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'IndiandotnetMailSmtp',
    @recipients = 'rajatjai@gmail.com',
    @body = 'Mail sent successfully.',
    @subject = 'Mail via SQL SERVER ' ;

We can also send a query result either inline text format or html format or as a  attachment

Below query will send mail to me with count of students of a class. the below mail is simple text format mail

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'IndiandotnetMailSmtp',
    @recipients = 'rajatjai@gmail.com',
    @query = 'SELECT COUNT(1), Class FROM IndiandotnetDB.dbo.tblStudent GROUP BY class' ,
    @subject = 'Mail via SQL SERVER ' ;

The above mail can also be send mail as a attachment with following command

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'IndiandotnetMailSmtp',
    @recipients = 'rajatjai@gmail.com',
    @query = 'SELECT COUNT(1), Class FROM IndiandotnetDB.dbo.tblStudent GROUP BY class' ,
    @subject = 'Mail via SQL SERVER ' ,
    @attach_query_result_as_file = 1 ;

We can add @body_format = 'HTML' and use various HTML tag in query or body parameters

A part from this you can cross check E-mail status whether mail is sent or not if it is not sent the what is the reason.

Below are the statements which can help to cross check mail sent status

-- Show all the emails

SELECT * FROM msdb.dbo.sysmail_allitems

-- Show all the  sent mails
SELECT * FROM msdb.dbo.sysmail_sentitems

-- show all the un sent mails
SELECT * FROM msdb.dbo.sysmail_unsentitems

-- show all the failed mail with reason
SELECT * FROM msdb.dbo.sysmail_faileditems

I hope this might help you somewhere.

Enjoy!!

RJ

How easy to determine table dependencies ? TIP # 86

 

Determine the table dependencies is challenging sometime but we can easily resolve this by using a simple stored procedure which  SQL Server provides.

By using this stored procedure we can easily determine all the dependencies of particular table.

The stored procedure is sp_msdependencies

We can use this stored procedure as shown below

Execute sp_msdependencies ‘tableName’

For example I am using Adventureworks2012 and I want to know the dependencies of product table then I have to write following command

Use AdventureWorks2012
Go
EXEC sp_msdependencies '[Production].[Product]'
GO

When I run this command I get result as shown in below figure

I hope this tip may help you somewhere.

Thanks for reading.

Enjoy !!!

Rj !!!

“sp_helptext“ – Useful command to view detail TIP #85

 

It might be already known to you but I thought for sharing because I frequently use this command and it is very useful command.

When someone wants to determine detail of a function or stored procedure he/she can use this useful command.

The syntax is very simple. Just write

sp_helptext Storedprocedure/ functionname

For example If I want to determine detail of a stored procedure “proc_FindStudentUsingCorrect” then I have to write following command

sp_helptext proc_FindStudentUsingCorrect

see below snap for detail

 

You can now copy the result text and check what exactly written in the stored procedure or function.

I hope you will use it in your day to day practice.

Enjoy!!!

RJ

Easy way to diagnose SQL Server “sp_Who2” TIP #84

 

Why the SQL Server is running slow ?

What are the processes running currently on SQL SERVER instance ?

Many other like the above which might help us to understand our current SQL Server instance health can be answered by a simple command “sp_who2”.

“sp_Who2”  is an Undocumented command. You can utilize this command to check current status of your SQL SERVER.

We can run this stored procedure directly in Management studio.

See below snap for detail

if you see above snap you will find the sp_who2 providing login detail, database name, command action which is currently applied , CPU time, DiskIO etc.

You can easily find which spId consuming highest CPU,DISKIO etc.

I hope this stored procedure might help you.

Thanks

RJ

Contained Database–A new feature in SQL SERVER 2012 TIP #81

While working on a project sometimes it happened that we need to shift the database from one server to another due to various reason (sometimes it is only money ).

We are aware that this easy step take backup and restore another machine but glitch is what about the users which have access to the database ?

Now , the next step of us to create each individual user on newly database server which is very tedious job sometimes.

In SQL SERVER 2012 a new feature introduced by Microsoft which is Contained Database.  This feature allows us to move meta data as well with database backup.

Let me explain it in more detail what I mean to say here. A contained database is a feature which isolate database from other database and from SQL SERVER host for authentication which means now the authentication can be done on database level.

For example you want to move a in house Employee management system which has around 100 users which access this database so if the database in contained enabled then in that case we do not have to create new users on another sever.

Now let me explain here how to enable this feature

Step 1:- Enable this feature on server level by right clicking on server and from property TAB

We can enable this feature by following command as well.

EXEC sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'contained database authentication', 1
GO
RECONFIGURE
GO
GO

Step 2:- Now right click and go to property TAB of database on which we have to enable Containment Type feature

We have to enable this feature and set the value Partial

We can enable this feature via SQL command

ALTER DATABASE IndiandotnetDB SET CONTAINMENT = PARTIAL

Once we have enable above this feature we are good to go.

Now when you create user on database you can easily move this database with the META DATA which means user.

Warning :- If you see the image below you will find Replication, change data Capture,Change Tracking are not supported contained databases.

so if you are using Replication, change data capture, change tracking please avoid enabling this feature and use traditional way of creating user.

I hope this feature might help you somewhere.

Thanks

RJ

Change Data Capture (CDC)–An easy way to track data changes of a database–TIP #79

 

In last TIP #78  we have discussed Change tracker (CT) which was introduced in SQL SERVER 2008. CT feature only tracks which row is changes means on which row Insert/update/delete operation is performed but it does not track what exact value is changed.

If we want an audit of database means whatever changes occurred in database we want to capture those changes and later on see what are changes made then before CDC feature we have to right triggers but now in this case we can use Change Data capture (CDC) feature.

By the name it is clear that it capture the data which is changed.

Lets understand here step by step how to enable this feature

Step 1:- The first important step is to enable CDC on selected database. To enable CDC we have to write following command

EXEC sys.sp_cdc_enable_db

Note:- sometime you may get error while doing this so just cross check your database owner.

We can cross check whether CDC enable or not on database by following command

SELECT [name], database_id, is_cdc_enabled  FROM sys.databases  WHERE is_cdc_enabled = 1

Step 2:- Once we have enabled CDC setting on Database then we need to enable same setting for table which we need to capture.

For this we have to write following command

EXEC sys.sp_cdc_enable_table
    @source_schema ='dbo',
    @source_name ='tblStudentCDC',
    @role_name ='StudentCDCRole',
    @supports_net_changes = 1

Now When we run above command, we will find following items added in our database. We will find new tables created automatically in our database under CDC schema as shown in below figure

Whenever we enable CDC for a table a new table is created in System tables (if already not created) under a new schema which is “CDC” which denote change data capture.

a) CDC.captured_Columns:- This table contains all the captured columns of a CDC enabled tables.

As shown in below figure:

SELECT * FROM [cdc].[Captured_Columns]

b) CDC.Change_tables:- By the name it is clear that this will table will contain list of all the tables on which we have enabled CDC feature.

SELECT * FROM [cdc].[change_tables]

c)CDC.Index_Columns:-  This table keeps information of all the Indexes of a table

SELECT * FROM [CDC].[Index_Columns]

d) [cdc].[ddl_history]:-  This table contains all the information of schema changes of a CDC enabled table.

SELECT * FROM [CDC].[ddl_History]

e)[CDC]. [lsn_time_mapping]:-  This table keeps all the LSN ( Log Sequence Number)related information. The base of this table is whatever the transaction done on the CDC enabled table that will be capture in this table.

SELECT * FROM [CDC].[lsn_time_mapping]

On the same time few jobs also created which you can find in SQL SERVER agent as shown in below figure

Step 3:-We can also cross check all CDC enabled tables by using following command

SELECT [name], is_tracked_by_cdc  FROM sys.tables  WHERE is_tracked_by_cdc = 1

Step 4:-  Every captured table has suffix _CT and  the name is same as enabled CDC table. In current scenario we have enabled CDC feature on tblStudentCDC so the capture table is tblStudentCDC_CT.

Now we can find all the data change reference of a CDC enabled table by following command.

SELECT * FROM [cdc].[dbo_tblStudentCDC_CT]

Sometimes it is also called mirror table which keeps all the track.We will find following extra columns

_$Start_lsn,_$end_lst_$sequal,_$Opeartion,_$updatemask

_$Operation column track all the operations on the table and the different values denotes  different operations status as mention below
Like if value is 1 then it is a Delete Statement (means record deleted from main table)
if value is 2 then it is Insert Statement (means record inserted in main table)
if value is 3 then it denotes Value before Update Statement
if value is 4 then it denotes Value after Update Statement

So, with this easy steps we can enable CDC on database and tables and can enjoy this feature.

We can easily disable this feature which we will discuss in the next post.

Till than enjoy CDC.

Enjoy !!!

RJ !!!

Change Tracking–an easy way to track your changes TIP#78

 

I am pretty much sure by the title of this post you had idea of the post content. Although I am late to post this feature but anyways Change Tracking (CT) is a feature came in SQL SERVER 2008.

As the name mention it track the changes like DELETE, INSERT , UPDATE type DML operations which we can synch to another database. (Microsoft Synch Framework).

In simple word what ever we change in table will be track by Change Tracking feature.

The main constraint which I would like say here is there should be a primary key in table for which we have enabled this CT (Change tracking) feature.

Now your next question is how to use this feature so not to worry below is step by step way to use this feature.

Step 1:- First step is enable Change Tracking property of database. you can enable this property by right clicking database property as shown in below figure.

Or you can write following query to enable CT (Change tracking) feature on database

ALTER DATABASE IndiandotnetDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

Now let me explain here what is CHANGE_RETENTION & AUTO_CLEANUP property here.

So change retention is feature which tell how many days we have to keep the change history. In above statement we have chosen for 3 days.

Now next feature is AUTO clean_up with this help of this feature we can clean garbage data from temporary storage where SQL SERVER keeping the data.

Step 2:- Now we have to enable same feature on SQL table which is tblStudentSource on which we have to track database.

If we see there are two property which is basically mapping  property for which we have enabled this property.

We can run following command as well

ALTER TABLE tblStudentSource
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

For tables which we want track we have to enable this feature. Currently I chosen only single table which is tblStudentSource.

Step 3:- We can cross check Whether feature is enabled or not on database and table by running following command

SELECT DB_Name(database_id), * FROM sys.change_tracking_databases
SELECT object_Name(object_Id), * FROM sys.change_tracking_tables

Step 4:- if we see result of step3 command we will find our CT (Change Tracking) feature is enabled on database & particular table.

Now if we make any changes in table tblStudentSource (or table on which we have enabled this feature).

Now suppose We have inserted,  deleted some records in tblStudentSource table

Before enable Change Tracking feature  we have following table data

Now once we have enabled this we have inserted a record and deleted a record from tblStudentSource

Now to track our changes we have to run following command

SELECT CTTable.StudentId ,
       ssource.FirstName , ssource.LastName,
       ssource.StudentId ,
       ssource.detail,
       ssource.Course,
       CTTable.SYS_CHANGE_OPERATION, CTTable.SYS_CHANGE_VERSION, CTTable.SYS_CHANGE_COLUMNS, CTTable.SYS_CHANGE_CONTEXT
FROM CHANGETABLE (CHANGES tblStudentSource, 1) AS CTTable
LEFT OUTER JOIN tblStudentSource AS ssource
ON ssource.StudentId = CTTable.studentId

 

Now if we see above snap we will find the all the changed row whether it is inserted or deleted is tracked by SQL SERVER and we can find it easily.

Here we would like to see some more important points.

So Whatever changes track by ChangeTracking feature of SQL Server is stored in a internal table which we can find with following command.

SELECT * FROM sys.internal_tables WHERE parent_object_id = OBJECT_ID('tblStudentSource')

If you see above query statement which we wrote to determine data which we have changed there is a function which we have used “CHANGETABLE”

The first parameter of this function is tablename with changes prefix and second parameter is Change tracking version number. Here Change tracking version number is nothing but a tracking of operation held on that particular version.

We can find the  current version by following command

SELECT  CHANGE_TRACKING_CURRENT_VERSION()

This version will helpful in synchronization.

I hope this might helpful to you somewhere in your project.

It is a good to know feature.

Thanks

Rj

Sequence feature TIP #76

Although it is a old feature for those who knows ORACLE but for SQL server developers it is a new feature.

Let understand it by an example. Suppose we want an auto incremented column a part from primary key which is a identity column,

then to achieve this we can use sequence feature.

We can create  sequence feature by following command

“CREATE SEQUENCE StudentEnrollmentId AS INT
START WITH 2014000
INCREMENT BY 1”

so if you see above statement we have created a sequence with name StudentEnrollmentId which is an integer type sequence and first value means starting point is 2014000 and each time when we call sequence it will be incremented by 1.

We can create same sequence by screen also as shown in below figure

We have other option also  as shown in below

CREATE SEQUENCE SEQUENCE_NAME
AS DATA_TYPE
START WITH <constant>
INCREMENT BY <constant>
MINVALUE value
MAXVALUE value
CYCLE | NO CYCLE
CACHE int | NO CACHE

as shown in above option we can provide minimum & maximum for sequence. We have cycle option mean restart again after reaching maximum or minimum.

Now we can use it with following way

“SELECT NEXT VALUE FOR StudentEnrollmentId”

I hope this might help you somewhere.

Enjoy !!!

Rj !!!

How to determine free space on each fixed drive of server machine using SQL SERVER ? TIP #75

 

When you do  SQL Server maintenance one of important aspect is available space on server drive because your SQL SERVER data is dependent on space .

Now what you need to do to get free space from each drive ?

Just create a simple job which run on daily basis which send you space report on daily basis.

This available space report help you to prepare yourself for next step.

The job will content a simple SQL statement which is

EXEC MASTER..xp_fixeddrives

When you run this command in your SQL Management Studio you will get a tabular result which have 2 columns which is drive & Free space in MB.

As shown in below figure

I hope this might help you somewhere.

Thanks & Enjoy!!!

RJ!!

Did I take right backup ! how to ensure backup can be restore ? TIP # 65

 

Problem:- We have seen last time how to take backup in tip # 64.   but sometimes it happened we took backup  and we are not able to restore it. It might be corrupted.

Now , Next step thought come in our mind how to ensure we took right backup which can be restore.

Solution:-  To ensure backup is correct. We can check following option as shown in below figure.

in Reliability section  check following option

1) Verify backup when Finished

2) Perform checksum before writing  to media

 

Once this option is check SQL server automatically cross check verify the backup when it is finished and by checking “Verify backup when Finished”

With CheckSum SQL Server cross check before taking backup.

We can also write following query

BACKUP DATABASE IndiandotnetDB
TO DISK = 'D:\Indiandotnet.bak'
WITH CHECKSUM;

Now to assure more we can write following command and verify whether the database can be restored or not whether the backup set is valid or not.

RESTORE VERIFYONLY
FROM DISK = 'D:\Indiandotnet.bak'
WITH CHECKSUM

I hope this might helpful you somewhere.

Thanks!!

RJ!!

Find last statistics updated date detail ?–Maintenance TIP #59

 

Problem:- One of the pain point in any SQL engineer  is “Performance”. There are various reasons due to which your SQL Server database is slow.

One of the possible reason is your maintenance.   You don’t know when statistics last updated and take further step if those are not updated

Solution:-

Here we have simple query to find when the statistics was last updated for a table.

SELECT o.name,
       i.name AS [Index Name], 
       STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
       s.auto_created,
       s.no_recompute,
       s.user_created
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)   ON i.[object_id] = s.[object_id]
                      AND i.index_id = s.stats_id
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;  

When you run it you will find last statistics update date if it is too old it means you have to run the maintenance for those tables.

see below screenshot which I run on my machines adventureworks2012 database.

I am sure you will analyze your database tables stats and run maintenance accordingly.

I hope this tip may help you some where.

Enjoy !!!

Rj!!

How Enable /Disable all CONSTRAINT on a table ? tip #53

 

Dear Friends,

Although , When we have designed our database he/she knows what type of constraints is require to make data perfect and the Database designer applies those constraints but it might be possible that sometime we have to disable those constraints make some tweak with the data in the table and then again enable the constraints.

Below are two simple command to Enable & Disable constraints of a table.

a) Disable constraints on a table

ALTER TABLE  tableName NOCHECK CONSTRAINT  ALL

b) Enable Constraints on a table

ALTER TABLE TABLE_NAME CHECK CONSTRAINT ALL

Lets understand this by an example suppose, I have city_seed table which having Foreign key constraint of country_seed table.

Now  when we run disable constraint command as shown above then you will find the constraint is disable  as shown in figure

Now when we run enable constraint then see below figure

I hope it might helpful you somewhere.

Thanks & Enjoy!!!

RJ!!!

Performance tips - How to determine last statistics update in table ? TIP #46

Hi,

For maintenance purpose we need to determine when the stats last update and analyze the data and if data is out dated then we need to update the stats.

To determine this we need to run the following scripts

SELECT OBJECT_NAME(s.object_id) AS [ObjectName]
      ,s.name AS [StatisticName]
      ,STATS_DATE(s.object_id, [stats_id]) AS [StatisticUpdateDate]
FROM sys.stats s
INNER JOIN sys.objects obj ON obj.object_id = s.object_id
AND obj.type IN ('U','V')
ORDER BY STATS_DATE(s.object_id, [stats_id]) Asc,obj.name

 

For example I run the following code in adventurework2012

 

So according to update status date we do update statistics of those tables in database.

I hope this tip will help you.

Enjoy !!!

RJ