SQL SERVER 2008 – Database Mail Configuration – Send Email From SQL Database

This post will help to configure Database Mail which is used to send the Email using SQL Server. In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out.  

1. Create Profile and Account 

2. Configure Email 

3. Send Email.

Step 1) Create Profile and Account:

We need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail in Management Node. This wizard is used to manage accounts, profiles, and Database Mail global settings which are shown below:

Please follow the snapshots in sequence :-

Step 2) Configure Email:

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

SP_CONFIGURE 'show advanced', 1

GO

RECONFIGURE

GO

SP_CONFIGURE 'Database Mail XPs',  1

GO

RECONFIGURE

GO

Step 3) Send Email:

After all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

USEmsdb

GO

EXEC sp_send_dbmail @profile_name='demoprofile',

@recipients='raghwendra.mishra@spluspl.com',

@subject='Test mail--Demo mail configuration,

@body='This post will help to configure Database Mail which is used to send the Email using SQL Server. In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out.  

1.Create Profile and Account 

2.Configure Email 

3.Send Email. '

SQL server Migration from 2008 to 2012

The project that I work at the present time has received the new SQL Server hardware,

So now is the time to upgrade from the SQL server 2008 to the 2012.

An upgrade, in new environment, refers to the process of moving from the SQL server version 2008 to the new version 2012.

There are two approaches for upgrading database:

1.      In position: The SQL Server is upgraded where it is currently installed

2.      Migration: A new environment is installed; the data is copied to it and configured with the existing data.

The approach which I am going to take is the second one, since I have a new server and I am going to do a fresh SQL Server 2012 installation. 

There is an article on MSDN that explains the SQL Server Database Engine Backward Compatibility. We must read it here. The upgrade of the databases can be implemented in the following steps:

1.      Backup the SQL Server 2008 databases and restored them in SQL 2012.

2.      Change each database compatibility level from 2008 to 2012 and update statistics.

ALTER DATABASE Database SET COMPATIBILITY_LEVEL = 110;

DECLARE @sql NVARCHAR(MAX)

set @sql=  N'';

SELECT @sql = CHAR(13) + CHAR(10) + 'UPDATE STATISTICS '

  + QUOTENAME(SCHEMA_NAME(schema_id))

  + '.' + QUOTENAME(name) + ' WITH FULLSCAN;'

  FROM sys.tables;

PRINT @sql;

EXEC sp_executesql @sql;

where [database] is the database to change the compatibility level

We can do it in a more compact way with

EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

or go to the database properties and on the options select the Compatibility Level 110.

3.      Check the logical and physical integrity of all the objects in the upgraded databases:

DBCC CHECKDB([database]) WITH NO_INFOMSGS

Where [database] is the database to run the integrity checks NO_INFOMSGS option suppresses all informational messages.

If DBCC printed any error messages, we must fix them so that your database will work correctly.

The following script can be useful to check database compatibility level:

select name, compatibility_level , version_name =

CASE compatibility_level

    WHEN 65  THEN 'SQL Server 6.5'

    WHEN 70  THEN 'SQL Server 7.0'

    WHEN 80  THEN 'SQL Server 2000'

    WHEN 90  THEN 'SQL Server 2005'

    WHEN 100 THEN 'SQL Server 2008/R2'

    WHEN 110 THEN 'SQL Server 2012'

    WHEN 120 THEN 'SQL Server 2014'

END

from sys.databases order by compatibility_level

Find Database Space Details

Few days back my E+1 and TL ask me about database free space and it was bit difficult to get all database details at a time.

I was able to get single database details at a time, after some goggling I found 1 script on citagus and it was very helpful. The best part is this will work for all version like 2000, 2005.2008 and R2 etc

..

use master

go

if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForFileStats '))

DROP TABLE #TempForFileStats

if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForDataFile'))

DROP TABLE #TempForDataFile

if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForLogFile'))

DROP TABLE #TempForLogFile

DECLARE @DBName nvarchar(110)

DECLARE @SQLString nvarchar (2000)

DECLARE c_db CURSOR FOR

SELECT name FROM master.dbo.sysdatabases WHERE status&512 = 0

SET NOCOUNT ON

CREATE TABLE #TempForFileStats(

[Server Name]              nvarchar(40) ,

[Database Name]        nvarchar(110),

[File Name]            nvarchar(128),

[Usage Type]           varchar (6),

[Size (MB)]            real null default 0,

[Space Used (MB)]      real null default 0,

[MaxSize (MB)]         real null default 0,

[Next Allocation (MB)] real null default 0,

[Growth Type]          varchar (12),

[File Id]              smallint null default 0,

[Group Id]             smallint null default 0,

[Physical File]        nvarchar (260) ,

[Date Checked]         datetime)

CREATE TABLE #TempForDataFile (

[File Id]             smallint,

[Group Id]            smallint,

[Total Extents]       int null default 0,

[Used Extents]        int null default 0,

[File Name]           nvarchar(128),

[Physical File]       nvarchar(260))

CREATE TABLE #TempForLogFile  (

[File Id]             int,

[Size (Bytes)]        real null default 0,

[Start Offset]        real null default 0,

[FSeqNo]              int null default 0,

[Status]              int null default 0,

[Parity]              int null default 0,

[CreateTime]          varchar(55))

OPEN c_db

FETCH NEXT FROM c_db INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQLString = '

SELECT @@SERVERNAME as ''ServerName'', ' +'''' + @DBName + '''' + 'as  ''Database'',

'+'f.name, '+'CASE ' +' WHEN (64 & f.status) = 64 THEN ''Log'' '  +' ELSE ''Data'' ' +' END as ''Usage Type'',

'+'f.size*8/1024.00 as ''Size (MB)'',

'+' NULL as ''Space Used (MB)'',

'+'CASE cast(f.maxsize as real) '+' WHEN -1 THEN  -1 '+' WHEN  0 THEN  f.size*8/1024.00  '+

' ELSE cast(f.maxsize as real)*8/1024.00 '+'END as ''Max Size (MB)'',

'+' CASE '+'WHEN (1048576&f.status) = 1048576 THEN (growth/100.00)*(f.size*8/1024.00) '+'WHEN f.growth =0 THEN 0

'+'ELSE f.growth*8/1024.00 '+'END as ''Next Allocation (MB)'',

'+'CASE  '+'WHEN (1048576&f.status) = 1048576 THEN ''Percentage'' '+' ELSE ''Pages'' ' +

' END as ''Usage Type'',

'+'f.fileid,

'+' f.groupid,

'+'filename,

'+'getdate()'+ 'FROM [' + @DBName + '].dbo.sysfiles f'

INSERT #TempForFileStats

EXECUTE(@SQLString)

print @SQLString

SET @SQLString = 'USE [' + @DBName + '] DBCC SHOWFILESTATS  WITH NO_INFOMSGS '

INSERT #TempForDataFile

EXECUTE(@SQLString)

UPDATE #TempForFileStats SET [Space Used (MB)] = s.[Used Extents]*64/1024.00

FROM #TempForFileStats f,#TempForDataFile  s

WHERE f.[File Id]       = s.[File Id]

AND f.[Group Id]      = s.[Group Id]

AND f.[Database Name] = @DBName

TRUNCATE TABLE #TempForDataFile

SET @SQLString = 'USE [' + @DBName + '] DBCC LOGINFO WITH NO_INFOMSGS '

INSERT #TempForLogFile

EXECUTE(@SQLString)

UPDATE #TempForFileStats SET [Space Used (MB)] = (SELECT (MIN(l.[Start Offset]) +SUM(CASE

WHEN l.Status <> 0 THEN  l.[Size (Bytes)] ELSE 0 END))/1048576.00

FROM #TempForLogFile l

WHERE l.[File Id] = f.[File Id])

FROM #TempForFileStats f

WHERE f.[Database Name] = @DBName

AND f.[Usage Type]    = 'Log'

TRUNCATE TABLE #TempForLogFile

FETCH NEXT FROM c_db INTO @DBName

END

DEALLOCATE c_db

SELECT [Server Name],[Database Name],[File Name],[Usage Type],[Size (MB)],

[Space Used (MB)],[Size (MB)]-[Space Used (MB)] as [Free Space (MB)],[Physical File]

FROM #TempForFileStats

DROP TABLE #TempForFileStats

DROP TABLE #TempForDataFile

DROP TABLE #TempForLogFile

go

Reference taken from citagus

SQL Server Restart Date and Time

Problem:

I received a request from one of the developer saying that, the Staging DB was down yesterday evening, between 6:00 PM to 8:00 PM.

Due to this, he was not able to meet his deadline.

Solution:

Since it was staging server, we do not configure automate mail alert for service restart.

So, to answer such question, I have checked below things.

1.      Last SQL Server instance restart.

2.      Last DB server (windows services) restart.

HOW TO CHECK ?

 SQL Server Restart:

We can check it easily and by the Easiest ways (SQL Query)

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

SELECT 'Statistics since: ' + CAST(sqlserver_start_time AS VARCHAR) FROM sys.dm_os_sys_info

OR

EXEC SP_HELPDB 'Tempdb'

Enabling Service Broker in SQL Server

Today while creating a database for one of our new projects came across with an error:

“The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications”

Now, the question I had in front of me was………

How do I enable Service Broker in SQL server 2008 R2 database? 

I tried executing the below query…..

ALTER DATABASE SET ENABLE_BROKER but it went into an infinite loop.

Again executed the query and got error- ‘database is in use.’

Use Master

ALTER DATABASE [DBNAME] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

And after a long R&D, I killed the entire session id connected to the database, in which I was suppose to enable the broker.

So there are two options to enable the broker.  

Option 1-

Just run this script, it will kill all the process's that a database is using and then set the broker

USE master

go

DECLARE @dbname sysname

SET @dbname = 'YourDBName'

DECLARE @spid int

SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)

WHILE @spid IS NOT NULL

BEGIN

EXECUTE ('KILL ' + @spid)

SELECT @spid = min( spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid

END

ALTER DATABASE @dbname SET ENABLE_BROKER

Option 2 –

·         Connect SQL server management studio

·         Right click on your database

·         Go to  Properties->  Options ->  Service Broker

 Reference taken from stackoverflow.com