Solved

MS SQL change alert.

Posted on 2013-05-21
13
300 Views
Last Modified: 2013-07-05
Dear all,

Any way to make MS SQL server send an email alert (by operators) if any database structure change and what it is ?

Any third party tools needs for this ?

DBA100.
0
Comment
Question by:marrowyung
  • 8
  • 5
13 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 39183623
Yes, you can do it via DDL Triggers.
Please go through one of a sample trigger to capture all DDL changes in the database.

http://blog.extreme-advice.com/2012/10/25/capture-schema-change-in-sql-server-to-maintain-history/
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39183862
thansk.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39183875
For this:

"DDL_DATABASE_LEVEL_EVENTS"

What it includes ?

"[HostName] [varchar](50) NULL"

What is the hostname is about ? the hostname the change initial from ?
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 1

Author Comment

by:marrowyung
ID: 39183884
Anyway to modify the information the trigger will add?  anyway to add more ?

"
[SystemUser] [varchar](100) NULL,

[CurrentUser] [varchar](100) NULL,

[OriginalUser] [varchar](100) NULL,
"
in what situation the 3 x users different ?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39183909
>> What is the hostname is about ? the hostname the change initial from ?

Yes, it is..

>> in what situation the 3 x users different ?

It will have difference if the login name and user name are different.
Kindly try running this for your SQL user session.

select SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN()
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39183918
ok.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39183923
do you know if the vesrion for SQL 2012 can use for any server running SQL server 2008 R2?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39183931
I've tried creating this trigger in SQL Server 2008 R2 and it works fine..
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39183963
ok, thansk.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39186672
do you have the SQL server 2005 try from their old article? Any difference in result ? it seems that the version for SQL 2005 is providing much limit information to return ?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39186689
Yes, SQL Server 2005 has limited set of events available for monitoring and the full set of events below for your reference:

SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms189871%28v=sql.90%29.aspx

SQL Server 2012
http://msdn.microsoft.com/en-us/library/bb522542.aspx
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39301559
rrjegan17,

it seems that the email format and email action is not here, right?

how can I customerizes the email message ?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39301758
You can use msdb.dbo.sp_send_dbmail procedure to send emails in the format you require.
More info about the procedure along with examples below:

http://msdn.microsoft.com/en-us/library/ms190307.aspx
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Generate Weekly Schedule 15 30
SQL 2008 with .NET 4.5.2 4 30
MS SQL + Insert Into Table - If Doesnt Exist 9 36
ms sql + get number in list out of total 7 29
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question