How to Configure E-mail in SQL Server Step by Step–TIP #96

 

For different reason we need to send database report , data and other SQL Server database related stuff using E-mail . SQL  Server provides E-mail functionality using “Database Email” feature. We can setup any E-mail  in few simple steps.

Let’s follow below steps

Step 1:- Open “Management” option of object Explorer in SQL SERVER  management studio.

You will find “Database Mail” option. now click on configure Database mail option as shown below

 

Step 2:- When you click on Configure database mail you will get below wizard screen

Step 3:- Click on next button then you will below screen which is basically provide you option to setup or manage existing profile . In our case we are going to create a fresh database mail so let the first default option as is as shown in below figure and click on next button

Step 4:- You will get below screen. Just give a unique profile name and click on ADD button which is highlighted

Step 5: Now this is the main setting of mail “SMTP settings”

In this screen we have to give proper E-mail address, password , SMTP server name & Port number. In below screen I used gmail settings for a my gmail account. You can use any email address

Step 6:- Once you assure that everything is perfect in above screen then click on OK button. When click Ok button this SMTP profile will be saved and you can find in below screen now click on next button

Step 7:-  If you want you can make this profile as public default profile means default this profile will be use if someone wants to send mail from sql database. Now click Next button till the end of wizard you will following screens.

 

Step7:- Now click on Finish button then your setting will be actually saved in database and below screen will appear.

Now Once you close the above screen you are good to go.

You can test this by sending a test mail to your account using following screen

I hope this steps may help you to setup database email.

Enjoy Database E-mail and try to send a test mail to me also.

Enjoy !!

RJ

Data Compression– a unique feature of SQL Server TIP #91

A part from performance many times we faced challenges related to space  of our database. Sometimes our database is actually taking huge space and sometimes it our mistake due to which it took space.

It is worst condition when you are on a dedicated hosting or cloud hosting with limited space. In such case “Compression” is one of the best option.

This feature introduce in SQL SERVER 2008 onwards with Enterprise & Developer Edition.

By the name it is clear that it compress the data and provide more space on disk.

There are 2 options available in Data compression

Row level compression:-

 Row level compression primarily turns fixed-length data-types into variable data-types, thereby saving space. It also ignores zero and null values saving additional space. Because of this, more number of rows can be accommodated in a single data page.

Page level compression:-

Page level compression initially performs Row Level compression and adds two additional compression features – Prefix and Dictionary Compression. Page level compression offers better space saving than row level compression. Although the page level compression produce more space but obvious the CPU utilization is higher in page level compression. Overall we can say the page level compression is highly CPU consuming operation means it involves lot of  CPU efforts for compress & de compression. So choosing this option requires a high CPU & RAM configuration machine. Due to this feature it mostly used in data warehouse database where repeated values occurs. using this option with a table where lots of write operation occurs in day to day activity will be a bad idea.

Now we aware of data compression and its two options. Now lets discussed some of the major points before using this valuable feature

1) Data compression is SQL SERVER feature so we don’t need to do any tweak or rewrite our application code which is a good thing.

2) Compression is real time so again you don’t need to re run a maintenance job each time to compress the data.

3) As mention earlier you have to pay bit CPU cost for compression.

4) with Compression data files MDF files, ldf files and buffer cache size can be improve.

5) Compression not compress FILESTREAM data object which is obvious.

6) Although maintenance of compressed index is higher.

In next post I will show simple steps for implementing this best feature.

Enjoy !!!

Thanks

RJ!!!

Secure your password with HashBytes TIP #89

Dear Friends,

In my last article (TIP 87) I wrote about PWDENCRYPT.  I forgot to write few details but thanks to all my talented friends & blog readers who guided me by providing there feedback on post.

I respect their inputs and always interested to get more inputs. Thanks to all of you.

Now I would like to share information about “HASHBYTES”  function which is also available for secure your password using various Hash algorithms like MD2 , MD4 , MD5 , SHA ,SHA1 , SHA2_256 , SHA2_512.

The PWDENCRYPT  can be use but  HASHBYTES function provides you various options to make your content robust secure.

The Syntax is very easy as shown below

HASHBYTES (ALGORITHM, ‘INPUT WHICH YOU WANT TO SECURE’)

Now let see an example to understand it more

Just wanted to add here that SHA2_256, SHA2_512 available with 2012 version and above.

Someone said this line right “More option More confusion”

Now we have different algorithm then which one we have to use so the answer is (according to my knowledge) use the latest most secure one Like SHA1, SHA2 etc.

I hope this article may be useful to you.

Thanks !!!

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

How to insert value in Identity column ? TIP #80

 

Happy new year 2015 .

Suppose you have a source table and one destination table (Which is exact replica of source table) and you want to copy all the rows of source table into destination table. Now the challenging part here is that there is an identity column and in source & destination table and you want to insert same value of identity column in destination tables column.

Lets understand this by an example.

Suppose you have tblStudentSource table which have StudentId as primary key and with Identity feature

Now you have another table which is tblStudentDest with same column structure which tblStudentSource has

now you want to insert all the value of tblStudentSource into tblStudentDest with keeping the identity value.

In this case you have to write following command.

SET IDENTITY_INSERT  tblStudentDest ON;
INSERT INTO tblStudentDest (StudentId,FirstName,LastName,Course,detail) SELECT * FROM tblStudentSource
SET IDENTITY_INSERT  tblStudentDest OFF;

I hope this might help you somewhere.

Thanks

RJ

Happy new Year 2015.

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 !!!

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!!

How to copy table structure only from a SQL Query ? Tip #69

 

Recently , one of my friends shared that some interviewer asked him a question “How to copy table structure only from a  SQL Query?”

So,

Below is simplest query to copy structure only of a table into another table.

SELECT *
Into #tmpStudentStructure
FROM tblStudentSource
WHERE 1= 0

in the above query we want to copy structure of tblStudentSource.

see below snap which help you to understand it more

I hope this might help you if some asked you this question.

Thanks & Enjoy!!!

RJ!!!

How easy it is to check which statements consuming most of the CPU & RAM–TIP #68

 

Problem:-  One of the most important question comes in our mind what is the cause of slow  SQL SERVER.

We always struggle with following questions

Which is highly CPU consuming query ?

Which is highly RAM consuming query ?

Who is blocking the transaction ?

and many more other performance dragging questions.

Believe me if you know who is culprit of making your system slow, you will win half battle.

Solution:-  SQL Server provided an easy way to clear your all doubts related to above questions. This easy way is “STANDARD REPORTS”.

You can access Standard Reports option by right clicking the SQL SERVER instance. These Standard reports contains not only performance related reports but other useful reports also.

If you see below image you will find there are many other reports option available.

Now, suppose I am interested to know which query consuming high CPU , to achieve this I clicked on   Performance Query – TOP Queries by Average CPU time or Performance query – Top queries  by total CPU time.

When I clicked on any of this option ,I got a report.

This report contained a bar graph & query detail in tabular format which contains query & CPU consuming time as shown below.

in similar way we can get answer of our other performance related query also.

I hope this may help you somewhere.

Thanks & Best Regards,

RJ!!

How to take Transaction Log backup ? TIP# 68

 

As we discussed in earlier tips how to take full backup and differential backup now in this tip we will see how to take transaction backup .

Before taking transaction backup the first and mandatory condition is that you should have a full backup.

Just right click the database and go to backup option as you will get following screen. Now we need to select Transaction Log   option in   backup type dropdown.

Once this is selected we need to give a transaction log backup name as shown below. The extension should be .TRN

 

 

Once you provided all the mandatory values just click on then you will get your  Transaction Log backup.

We can take Log backup with  SQL command also

For example :-

BACKUP LOG IndiandotnetDB
TO DISK ='E:\IndiandotnetTransaction.trn'

When you run above command you will get following screen and your log backup is done.

I hope this will help you.

Thanks & Enjoy!!!

RJ!!

How to take differential backup ? TIP #67

Friends,
We were discussing backup from last few post. Now lets take a look one more feature of backup which is differential backup.
Differential backup  is backup after last full backup. Before taking Differential backup  it is a mandatory condition to take full backup.
Differential backup can speed up your recovery process or we can say restore process.
In below example we would understand how to take differential backup
To take differential backup we can run following command
BACKUP DATABASE IndiandotnetDB
TO DISK = 'D:\IndiandotnetDB.dif'
WITH DIFFERENTIAL;
GO
Or we can take differential backup by following steps also
Right click the database select database backup option you will get same screen which we explain at the time of full backup.
if you see below screen you will find we have change backup type to Differential and  taking backup to d drive with name Indiandotnetdb.dif

Once you click OK button. your differential backup will be completed.
I hope this tip might help you somewhere.
Thanks & Enjoy!!!
RJ!!

Oh! I forgot When I took last Database Backup ? How to determine this? TIP # 66

 

In last few tips TIP #64 & TIP #65  we get information  how to take backup & How to ensure it can be restore? Now lets me share some more information related to backup.

Problem:-   Sometimes , we are interested to know when last backup is taken for particular database ?

Solution:  The first thing I want to share here that  you should have proper maintenance plan to take backup of database and by job History you are able to know when last backup is taken but if this is not the case then not to worry SQL Server maintain itself information related to backup. to determine this you need to write a simple query as shown below

SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_start_date, 
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type 
       WHEN 'D' THEN 'Database' 
       WHEN 'L' THEN 'Log' 
   END AS backup_type, 
   msdb.dbo.backupset.backup_size, 
   msdb.dbo.backupmediafamily.logical_device_name, 
   msdb.dbo.backupmediafamily.physical_device_name,  
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

ORDER BY 
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date

  When I run above command I get information related backup like databasename, backup start date, Backup finish date, expiry date(if any) , backup type like whether it is database backup or Log backup , size of backup , path where database is taken, name of backup set.

see below snap for detail

I hope this tip might help you somewhere.

Thanks !!

Rj!!

How to take compressed backup in SQL Server TIP #64

Problem:-  A good maintenance plan always said to take backup but sometimes it is very difficult to take backup of database just because of space constraint.
We take backup but we don’t have enough space available.
Solutions:  SQL server provided  the best way and gave us solution of compressed backup. Now how to take compressed backup let see step by step here.
Step 1:-  Select the database whose backup you want. Just right click and select backup option as shown in below figure

Step 2:-  Once you click the backup option an new screen will appear as shown below

Step 3:-  Now click on  Add button as shown in above screen. Once you click on the add button a new screen will appear as shown below where you have to provide the path and file name of compressed backup file.

Step 4:-   Once you provided the filename click OK to the button now click on the options menu on right hand you will get new tab option here in the last you will get compress option as shown in below figure with arrow. Select compress backup option in dropown
once this done click on OK button now you are good to go. Your compressed backup is done. and you will get backup complete message as shown below

Or else you can use following command
BACKUP DATABASE INDIANDOTNETDB
TO DISK = 'E:\MyCompressedBackup.bak'
WITH COMPRESSION ;
You will get compressed backup.

I hope this will help you somewhere.
Enjoy!!!
RJ!!

Database Diagram support object cannot be install error-SSMS TIP# 61

 

Problem:-  It is general problem that you are interested in generating the database diagram of a database and when you right click on database diagrams folder of database and try to create a new database diagram you get following error as shown in below snap.

Solution:-

The above snap says that you are not authorize person to generate database diagram so to resolve this we have to do following steps and then you are good to go to create database diagram

step 1:- Go to database properties by right clicking on database and choose files option from left side bar your will get following screen.

If you see above screen you will find the owner text box is empty.

Step 2:-  Now click the button near by the textbox and you will get another popup window just select login “sa” user (or who has permission) as shown in below figure

Step 3:-  Once it is  selected  the owner text box will be filled  as shown in below figure now click on OK button

 

By following above 3 simple steps you will be able to create database diagram for particular database.

I hope this solution may help you if you stuck in such situation.

Thanks & Enjoy!!!

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!!

Maintenance of fragmented Heap table–TIP #55

 

Friends,

In last post  post #54  we understood what is a Heap table. Now the challenge is what if this table is highly fragmented so.

Now our task is to run maintenance of this highly fragmented table.

So you have to run following command

“ALTER TABLE  TABLENAME REBUILD;

For example  I run the Rebuild for  Adventureworks2012 database’s heap table which we found in post #54.

See below snap for detail

 

So, with this REBUILD command the table is rebuild and the fragmentation issue resolved.

I hope it may help you some where.

Thanks & 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!!!