Thursday, April 11, 2013

[MS SQL Server] Create DML trigger for single table in SQL 2008 and send email alert to group

[MS SQL Server] Create DML trigger for single table in SQL 2008 and send email alert to group

Create DML trigger for single table in SQL 2008 and send email alert to group

Posted: 10 Apr 2013 07:48 AM PDT

Hi,i have a requirement to Create DML trigger for single table ( check for any updates ) in SQL 2008 and send email alert to group if there is any activity in the table. I have the following the script . This trigger is sending email when there is any activity in table but the output has nothing in it. I guess problem is with @Message statement. Any help would be greatly appreciated.Thanks in advanceCREATE TRIGGER [dbo].[triggername]ON [dbo].[tablename]AFTER INSERT, UPDATE, DELETEASDECLARE @EventData XMLDECLARE @Message nvarchar(max);DECLARE @DDLStatement nvarchar(max); DECLARE @UserName nvarchar(max); -- To hold the user that execute the commandDECLARE @DatabaseName nvarchar(max); SET @EventData = EVENTDATA() SET @UserName = @EventData.value( '(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)' ); -- Get the database that the change was to affect SET @DatabaseName = @EventData.value( '(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(max)' ); SET @DDLStatement = @EventData.value( '(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)' ); SELECT @Message = 'DATABASE: ' + @DatabaseName + CHAR(13) + 'EXECUTED BY: ' + @UserName + CHAR(13) + '----- BEGIN DDL Statement --------------' + CHAR(13) + CHAR(13) + @DDLStatement + CHAR(13) + CHAR(13) + '----- END DDL Statement ----------------' + CHAR(13) + CHAR(13) + CAST(@EventData as nvarchar(max));EXEC msdb.dbo.sp_send_dbmail@profile_name = 'sqlserver',@recipients = 'XXX@.com',@subject = 'DML change on server name',@body = @Message

Backup compression

Posted: 11 Apr 2013 05:01 AM PDT

Every one is using compression backup in SQL server 2008 enterpise?My question is if one of the backup for example full backup is using compression backup, the transaction log or diff is using non- compressed, can the restore be completed using them?Currently we have enough space on disks, I wonder if I should use compression or not?The problem by using compression is sometimes people will be not aware of it's done that way, and misunderstand its real size.One DBA is OK, but if some other users do backup using non-compression, they will be wonder why it it is different.Thanks

Log shipping and CDC

Posted: 19 Mar 2013 10:41 PM PDT

I come seeking the wisdom of the collective. I have searched to no avail to try and understand the implication of using CDC on a database that uses log shipping. In a weird way I also want to understand if CDC will affect log shipping and vice versa.After much head scratching I am assuming that the truncation point in the log will move if CDC is deployed. So instead of the last commited transaction it will be the last commited transaction that has been harvested?So if I have log shipping every 10 minutes does this mean I need to get the CDC data out of my source system before that 10 minutes cut off. Will it make my log shipping out of date??So many questions. Pointers to good articles gratefully received.Many thanks:w00t:

partitioning issue

Posted: 10 Apr 2013 06:17 AM PDT

HiHere are some details of structure before I state the problem:Table Employee (dummy name) is of size 750GB on SQL Server 2008. There is clustered index on ID column and nonclusterd index on WeekOfJoining(int). WeekofJOining has increasing values like 1,2,3....52,53,54.... Table is RIGHT partitioned on WeekOfJoining. Each partition has around 10 million rows. At then beginning of the year, 52 new partitions are added for new weeks of new year. Old data is not archived and hence we keep on increasing the partitions. Currently there are 650 partitions.Now when we split the partition function at the beginning of year, it is taking ages because of huge data movement that split causes. So it is becoming impossible to add new partitions.So some questions are here:1.What's wrong with existing structure? 2.Table is partitioned on WeekOfJoining, which has nonclustered index.( the index itself is not partitioned). Is it better to partition on clustered index column? Will it help if I partition the Nonclustered index as well?3. What can I do to make the data movement lesser while doing split?4.Any other design changes that can make life easier?thanks

unique constraint on a large column

Posted: 10 Apr 2013 12:45 PM PDT

I have a table with a column of NVARCHAR(2000) in size.There is a requirement to ensure the data in the column is unique.The table contains 170,550 rows.A unique constraint fails because it violates the 900 bytes max key size field.So what database options do I have?I tried using the CHECKSUM function on the offending field but I can only guess this is only a few bytes in size because over the 170,550 rows there is 128 collisions.So I tried a SHA1 checksum and this seemed to work OkALTER TABLE [testtable] ADD SHA1 AS HashBytes('SHA1', CONVERT(VARCHAR(1000),big_field))Would this be the best way to enforce uniqueness of have I missed something simple..thanks

Transactional replication not replicating one table properly

Posted: 10 Apr 2013 07:43 AM PDT

HiI've 2008 R2 transactional replicatin establishe. One table is cauing an issue which is out of sync by 30K rows out 1.7 mil. But the other tables are doing fine and the agent seems to be running with few sec latency. Any suggestions to troubleshoot are welcome .

Question on validation of "datetime" format column to sysdate

Posted: 10 Apr 2013 07:01 AM PDT

Hello,I am trying to perform a select based on a condition that the "datetime" formatted column is today's date2012-12-17 13:57:38.057what would be the syntax?thank you
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog