How to Set Up Database Mail for SQL Server Job Failures

Hi Everyone!! There is one task I configure quite often as a Database Administrator… setting up Database Mail to send an email if a SQL Server job fails. It isn’t difficult, but if you miss one step, it won’t work. Ugh!!
I’ll list all the steps here so it can be done quickly and easily without losing all but one strand of hair on your head.
Here are the steps:
1. Configure Database Mail.
2. Create a SQL Server job.
3. Adjust the properties within the SQL Server Agent.
4. Create an Operator.
5. Adjust the SQL Server job to send on Failure.

Configuring Database Mail

To complete this step correctly, a properly configured mail server is needed. In most cases it is okay to use localhost, but that requires the installation of Microsoft IIS/SMTP. If your administrator will not allow the install of IIS on a server running SQL Server, get the name of a mail server that can be used.
Step. 1
Open SQL Server Management Studio (SSMS), connect to the Database Engine, expand Management, right-click on Database Mail and then click Configure Database Mail.

Step. 2
You will then see the splash screen below. Click Next.

Step. 3
Leave the default option of “Set up Database Mail by performing the following tasks” selected. Click Next.

Step. 4
You will get a pop-up below if mail hasn’t been configured yet. Click Yes and then click Next.

Step. 5Give the New Profile a name and then click Add.

Step.6
Fill in the correct information as show below.
· Email address– This should be the sender email address.
· Display name– This should be the name displayed when the email arrives.
· Reply email– This should be set properly too.
· Server name– The mail server address. In this case, I am using localhost.
· SMTP Authentication– I leave this section set to the default, but you can specify it to your needs if it is required.
Note that the OK button will remain grayed out until all required fields are completed. Once all the fields are completed, click OK.

Step. 7
Click Next.

Step. 8
Check the option to make the new profile Public. If the profile isn’t set to Public, permissions will need to be assigned properly. I am not going to discuss this here, but it will be important to note the difference.

Step. 9
Review the System Parameters. I always use the default settings, but they can be adjusted as needed. You can review the settings in depth here. Click Next.

Step. 10
Click Finish.

Step. 11
You should see that each Action was completed and has a Status of Success. Click Close.

Now let’s test.

Right-click Database Mail and select Send Test E-Mail… type in an email address to use for testing and click Send Test E-Mail.

You will then see the screen below.

If the email doesn’t arrive, check the mail server to make sure it is properly configured and will accept mail from the SQL Server. You can do this by sending an email via telnet.

Create SQL Server Job

At this point we know the SQL Server can send email. Now we need to setup a SQL Job and then set SQL so it sends emails if the job fails. The easiest way to test this is to setup a Backup job and set it to notify on “Success”. Once it is working properly, change the job to notify on “Failure”. To setup a simple Backup job, see instructions here. Note: The Maintenance Plan Wizardcan also be used to setup a backup job very quickly.

Adjust the properties within the SQL Server Agent

Right-click SQL Server Agent and select Properties.

Click on Alert System under Select a page.
· Under Mail session, select the option to Enable mail profile. Ensure the correct Mail profileis selected.
· Under Token replacement, enable Replace tokens for all job responses to alerts.
Click OK. Restart the SQL Server Agent service.

Create an Operator

Under the SQL Server Agent, right-click Operators and select New Operator…


Type in the recipient email address in the E-mail name and click OK.

Adjust the SQL Server job to send the email on Success (for testing purposes only).
Expand SQL Server Agent and click on Jobs. Find the job you created above, right click it and select Properties.
Under Select a page, select Notifications. Enable the first option, E-mail. Then select the Operator we just created and change the job to email When the job succeeds (for testing purposes only). Click OK.

That’s it!! Now we test. Run the job we create that will backup databases. You should receive an email similar to the one below once it completes:

If an email isn’t received, restart the SQL Server Agent once more and then test again.

SQL Server Management Studio Keyboard Shortcuts

SQL Server Management Studio Keyboard Shortcuts

Every day we are getting similar task , Like , health check , Backup Restore status and  many of them and what we do, we save all query is some text file or individual files and copy paste when ever we need.

But did we forget SSSM gives us a way to use these queries with shortcut. I use below shortcut for same

Alt + F1: sp_help

Ctrl + F1: Select @@SERVERNAME
Ctrl + 1: Sp_who
Ctrl + 2: sp_lock
Ctrl + 3: sp_who2 Active
Ctrl + 4: xp_readerrorlog
Ctrl + 5 : Declare @S_date datetime , @E_date datetime ;Select @E_date = getdate() ,  @S_date = @E_date -1 ;Exec xp_readerrorlog 0,1,null,null, @S_date , @E_date
Ctrl + 6: select * from master..sysprocesses where blocked <> 0
Ctrl + 7: Exec sp_msforeachdb 'use [?] dbcc opentran'
Ctrl + 8: dbcc sqlperf(logspace)
Ctrl + 9: exec msdb..sp_help_job @execution_status = 1
Ctrl + 0 : Select @@servername as Instance,ServerProperty('ServerName') as ServerName,            ServerProperty('ComputerNamePhysicalNetBIOS') as PhysicalHost,ServerProperty('Edition') as Edition,ServerProperty('InstanceName') as InstanceName,ServerProperty('MachineName') as MachineName,ServerProperty('ProcessID') as [ProcessID_check from taskmanager],ServerProperty('ProductVersion') as ProductVersion,ServerProperty('ProductLevel') as ProductLevel,ServerProperty('ResourceLastUpdateDateTime') as ResourceLastUpdateDateTime,@@VERSION VersionInfo

 

So now how to do this

1. Open SSMS -->Tools --> Options --> Keyboard 

 

2. Once you saved your queries with shortcut , Close SSMS and re-start. Now Shortcuts are ready to use.
 
3. Please always use select or similar queries (which dont do any update/ insert/update/ backup / any operation).

Note: We can use separate settings for separate Windows NT logins. We have to just use run as for separate logins while connecting SSMS


There is a partial list of keyboard shortcuts provided by SQl server

Effect
ALT + N	New query window
CTRL + F4	Close current query window
CTRL + W	Highlight the word where the cursor is currently located
CTRL + Shift + U	Convert selected text to UPPER CASE
CTRL + Shift + L	Convert selected text to lower case
F5	Execute the selected query or if nothing is selected, then execute the entire content
CTRL + F5	Parse the selected query or if nothing is selected, then parse the entire content
CTRL + T	Results to Text
CTRL + D	Results to Grid
CTRL + SHIFT + F	Results to File
CTRL + R	Toggle display of results pane
F7	Display Object Explorer Details
CTRL + ALT + G	Display Registered Servers pane
CTRL + K, C	Comment Selection
CTRL + K, U	Uncomment Selection
CTRL + TAB	Cycle through open query windows and panes
F6 / SHIFT + F6	Cycle through the query text, resultsets and messages pane inside a query window
CTRL + J	Display Intellisense menu (Only in SSMS in SQL Server 2008 and later)
CTRL + ALT + L	Display Solution Explorer
CTRL + SHIFT + R	Refresh Intellisense cache
ALT + F1	Execute sp_help in the current results pane
CTRL + 1	Execute sp_who
CTRL + 2	Execute sp_lock
CTRL + U	To go to drop down list of Databases
CTRL + L	Display the Execution Plan

SQL Server Management Studio Keyboard Shortcuts List
 

Write a T-SQL script to shrink the Transaction Log file of SQL Server Database

Write a T-SQL script to shrink the Transaction Log file of SQL Server Database

For shrinking the transaction log of any SQL Server database, simply run the below script on that database:

USE YourDatabase_Name

GO

/*Declaring Variables*/

DECLARE @Database_Name NVARCHAR(100)

DECLARE @SQLString NVARCHAR(1000)

/*Setting the Database Name to variable*/

SELECT @Database_Name = DB_NAME()

/*Checking whether your Database's Recovery Model is Simple or not*/

IF NOT EXISTS (SELECT 1 FROM SYS.DATABASES WHERE Name = @Database_Name AND recovery_model_desc = 'SIMPLE')

BEGIN

    /*Changing YourDatabase's Recovery Model to Simple*/

      SET   @SQLString = 'ALTER DATABASE ' + @Database_Name + '  SET RECOVERY SIMPLE'

      EXEC (@SQLString)

END

/* Declaring the variable to store the log*/

DECLARE @LogFileLogicalName SYSNAME

/* Setting the value to store the log*/

SELECT @LogFileLogicalName=Name FROM SYS.DATABASE_FILES WHERE TYPE=1

/*Shirnking the log file*/

DBCC Shrinkfile(@LogFileLogicalName,1)

/* ALTER DATABASE YourDatabase_Name SET RECOVERY FULL */

SET @SQLString = 'ALTER DATABASE ' + @Database_Name + '  SET RECOVERY FULL'

EXEC (@SQLString)

GO

How to script all the SQL Agent Jobs in a single script file

How to script all the SQL Agent Jobs in a single script file

Background
The purpose of this article is to provide a way of scripting out all the SQL Agent jobs in a single script file. If we simply go to SQL Server Agent node then Jobs folder in management studio for scripting all jobs or more than one jobs then management studio does not allow selecting more than one job for scripting.


Fig- showing user can select only one job at a time for scripting

Steps to script out all the Jobs available in an instance of SQL Server
We are going to use Object Explorer Details property that shows the objects with more details and allows selecting all the jobs together for creating the script.

•   Go to SQL Server Agent Node then Jobs. It will enlist all the SQL Agent jobs:    
  
  
• Go to View menu and select Object Explorer Details.
• It will enlist all the jobs in separate pane with more details:
  
  
• Select all the jobs using Ctrl + A then right click on the selected jobs then Script Jobs as
  
  It will script out all the Jobs in a single Query window or file.
  
  
  Conclusion :
  By using Object Explorer Details in management studio, we can script out all the Jobs available in an instance of SQL Server. Same solution can be utilized for below requirement also:
• How to script all the Linked Servers in a single script
• How to script all the logins in a SQL Server Instance