Replication and DDL Triggers - DO NOT MIX

So, we had started rolling out DDL Triggers, and then today replication broke.

How? A weird ARITHABORT error trying to add a table via the GUI. Weird. So I disable the trigger and add it - at which point replication itself starts throwing the error :

Target string size is too small to represent the XML instance (Source: MSSQLServer, Error number: 6354)
Get help: http://help/6354


Well, it turns out I have an XML trigger on the TARGET database, and it can't deal with the large commands involved in transactions.

How to diagnose and find the exact commands causing problems?

use [replicated_table]
go
sp_helparticle @publication = N'publication_name', @article = 'article_name'
go
use distribution
go
sp_browsereplcmds @article_id = 77 --where 77 is the article_id from above

DDL Triggers

We're slowly starting to roll this out, based on the below code. A very well written article; our concern is on performance.

http://www.sql-server-performance.com/articles/audit/ddl_triggers_p1.aspx


--------------------
UPDATE:
Below is the code we originally rolled out, then rolled back due to XML errors with replication (see other posts with tag DDL Triggers)

With my luck it was something stupid in my code, but I haven't gone back and looked - I'm using Event Notifications now.



--1.1 version MDB 20091119.  Removed the XML field as that's a lot of data being held for no reason.
/*
use dba_repo
If Object_ID('dba_repo.dbo.DDL_Event_Log') IS NOT NULL
DROP TABLE dbo.DDL_Event_Log
CREATE TABLE dbo.DDL_Event_Log(

ID int IDENTITY(1,1) NOT NULL,
EventTime datetime NULL,
EventType varchar(15) NULL,
LoginName VARCHAR(50),
ServerName varchar(25) NULL,
DatabaseName varchar(25) NULL,
ObjectType varchar(25) NULL,
ObjectName varchar(60) NULL,
UserName varchar(15) NULL,
CommandText varchar(max) NULL
--,Entire_Event_Data XML
)
go
*/
CREATE TRIGGER [ddltrg_Audit_Log] ON DATABASE -- Create Database DDL Trigger
FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE,
CREATE_INDEX, DROP_INDEX, ALTER_INDEX,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
CREATE_SCHEMA, ALTER_SCHEMA, DROP_SCHEMA,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_USER, ALTER_USER, DROP_USER
/*
CREATE TRIGGER ddltrg_Server_Audit_Log ON ALL SERVER -- Create Database DDL Trigger
FOR
CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE
*/
AS
--http://www.sql-server-performance.com/articles/audit/ddl_triggers_p1.aspx
--http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1346274,00.html for event types
--See http://msdn.microsoft.com/en-us/library/ms189871%28SQL.90%29.aspx for event types
SET NOCOUNT ON
If Object_ID('dba_repo.dbo.DDL_Event_Log') IS NOT NULL
BEGIN
DECLARE @xmlEventData XML
-- Capture the event data that is created
SET @xmlEventData = eventdata()
-- Insert information to a Event_Log table
INSERT INTO dba_repo.dbo.DDL_Event_Log
(
EventTime,
EventType,
LoginName,
ServerName,
DatabaseName,
ObjectType,
ObjectName,
UserName,
CommandText
-- , Entire_Event_Data
)

SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),
CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/LoginName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ServerName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),
CONVERT(VARCHAR(60), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),
CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/UserName)')),
CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
-- , @xmlEventData
END

Piecemeal (aka Partial) filegroup restores from litespeed (sorta)

Okay, I wanted to write this down while I'm thinking about it.

I use filegroups and do filegroup backups. Extensively. Gives you more control and power, since you can backup each one individually, put it in its own location, give it its own schedule, etc, etc. Litespeed helps with this, obviously, since it'll shrink each file.

The downside is that restoring with Litespeed is an all-or-nothing ordeal. You can't restore just the primary, or just one of the filegroups, or just a table (no, object-level restore doesn't work on filegroup backups). Fortunately, SQL 2005 has a new "PARTIAL" option that allows you to restore just the primary filegroup, and then restore others in addition, so I'm using that.

I've already requested this feature for Litespeed, as well as the PARTIAL option, but here's what I had to do to restore some tables from my filegroup backup

0. Take filegroup backups. Take a TLOG backup if needed.

1. Decompress the files I need. Litespeed _does_ offer an "extractor" utility. This will turn your Litespeed BKP into an uncompressed SQL backup. One note: it creates 7 files, and the total will be the same as the uncompressed backup. So make sure you have room. I barely did - thank god for filegroups (or maybe not, since the whole purpose of this post is working around it). So, extract the primary and whichever filegroup you need.
Here's the code to restore a file:

extractor.exe -Fc:\temp\Northwind.bak -Ec:\temp\NorthwindNative.bak

On my system it converts to native at about .75gb per minute (uncompressed size). It's extremely disk-bound. You need to extract the log file too, if you're compressing it (you might not be! Check and see....).


2. Restore the primary filegroup WITH PARTIAL. Make sure to keep your Primary filegroup small. Mine isn't - I need to fix that. Here's the command I used:
RESTORE DATABASE main
FILE = 'main_system_01'
FROM DISK = 'K:\primary.bak0',
DISK = 'K:\primary.bak1',
DISK = 'K:\primary.bak2',
DISK = 'K:\primary.bak3',
DISK = 'K:\primary.bak4',
DISK = 'K:\primary.bak5',
DISK = 'K:\primary.bak6'
WITH partial, move 'main_system_01' TO 'E:\MSSQL_Data\main.mdf' ,
move 'main_log_01' to 'F:\MSSQL_Log\main_log.ldf', --without this, it will fail
FILE = 1, norecovery, nounload, stats = 10


3. Wait a couple hours for the primary to restore. (See? This is why you have a small primary filegroup.)


4. Restore the additional filegroup. You do _not_ use the PARTIAL keyword here. Why? Don't know - using PARTIAL caused it to fail, though my lab notes say to use it. My bet is that you don't need PARTIAL if you're restoring the only filegroup within a backup.
RESTORE DATABASE Main
FILE = 'Main_Secondary_01'
FROM DISK = 'K:\FG_Main_Secondary.bak0',
DISK = 'K:\FG_Main_Secondary.bak1',
DISK = 'K:\FG_Main_Secondary.bak2',
DISK = 'K:\FG_Main_Secondary.bak3',
DISK = 'K:\FG_Main_Secondary.bak4',
DISK = 'K:\FG_Main_Secondary.bak5',
DISK = 'K:\FG_Main_Secondary.bak6'
WITH --partial,
move 'Main_Secondary_01' TO 'E:\MSSQL_Data\Secondary.ndf' ,
FILE = 1, norecovery, nounload, stats = 10


5. Restore the log. I'd say to use use STOPAT so you don't have to keep restoring TLOGs, but I can't find anywhere how to get that to work with Litespeed's restore - I tried adding it to the @with, but then it just failed. Odd. Also, if I extract and restore a log, I get error messages about having to roll the LSN forward to a certain point, whereas I don't get that when running the below command.

EXEC master.dbo.xp_restore_log
@database = 'Main'
, @filename = '\\myserver\SQL_Backups\Main\Main_tlog_200802041030.TRN'
, @filenumber = 1
,@WITH ='RECOVERY'


6. Keep running restores until you get current. The way I did it, I kept running TLOG restores until I got an error message telling me the database is offline. It does this once you hit the last log file prior to the last database backup finishing.

7. Bring database ONLINE. I do it via the GUI.

8. Bring database from SINGLE-USER to MULTI-USER.

[Tuning] Getting file disk usage

Short and simple. Uses the DMV to tell you how much IO each file is using. What you think may be the case is not necessarily the case. And remember that backups count. I'd recommend doing 2+ samples during the day, and comparing. That'll help remove the backups from the end totals. Sample_MS is the number of milliseconds the computer has been up.

SELECT 
  master_files.NAME, 
  master_files.type_desc,
  master_files.physical_name, 
  vfs.sample_ms,
  vfs.num_of_bytes_written, 
  num_of_bytes_read, 
  size_on_disk_bytes/1024/1024/1024 AS Size_in_GB
FROM sys.dm_io_virtual_file_stats(null, null) vfs
INNER JOIN MASTER.sys.master_files 
 ON vfs.[file_id] = master_files.[file_id] 
 AND vfs.database_id = master_files.database_id
ORDER BY (num_of_bytes_read + num_of_bytes_written) desc

[Compression] Seeing how much savings you'll get

If you meet all sorts of criteria (SQL Server 2008, Enterprise Edition) then you can use row or page level compression on your tables. But, you ask: will it make a difference in space used?

Fortunately, and a bit surprisingly, Microsoft came up with a way to do so.
sp_estimate_data_compression_savings

Sample usage:

sp_estimate_data_compression_savings 
       @schema_name =  'dbo'  
     , @object_name =  '20090820__abc' 
     , @index_id = NULL             --NULL does all.
     , @partition_number = null     --if you use partitioned tables
     , @data_compression = 'PAGE'   --can also use ROW or NONE
go

It looks like it takes roughly 40mb of data, copies it to TEMPDB, and compresses that. It then returns the results, including comparing the size to what the current compression is.

For us, mixed results. One set of EDI data, which uses certain characters to split out values, gets roughly 25% compression. A different set of EDI data that uses XML (with huge swathes of repeating data) get a whopping 1% savings.

I love the idea. I want to use it everywhere - since most systems are IO bound (not CPU bound) it seems a home run. But the requirement of Enterprise Edition lessens its usefulness by a _lot_.

[Jobs] Search job history for a particular date range

Pieces cribbed off several people. Enjoy.

select job_name, run_datetime, run_duration from (
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name, 
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
WHERE run_datetime between '2009/07/24 01:00' AND '2009/07/24 01:59'
order by job_name, run_datetime

[Servers] No linked server? Need inserts? No problem!

Linked servers can be handy. But, also occasionally annoying, and not necessarily where you need them. Fortunately, OPENQUERY to the rescue. Yes, you can use it to do an adhoc query from another server. But, you can also use it to do an insert into another server.

Code:

INSERT INTO OPENROWSET('SQLNCLI', 'Server=yourserversname;Trusted_Connection=yes',
 'SELECT * FROM northwind.dbo.targettable') SELECT FieldA, FieldB, FieldC
FROM sourcetable 

2016/05/11 And here's the generic openrowset syntax you have to use to get SQL authentication.  Posting it here since I just blew 30 minutes trying to get it to work; despite what the documentation says, you have to use (at least on SQL 2012 SP3 querying SQL 2008 SP4)

SELECT * FROM 
OPENROWSET('SQLNCLI', 'Server=myserver;UID=mylogin;PWD=mypassword;',
 'select @@version') 

[Tip] getting local context from sp_ in master

In SQL 2005, if you create an SP in master that queries from system tables (or the INFORMATION_SCHEMA views), it'll return the details from the Master database - regardless of where you're running it. In SQL 2000, it worked.

So, an unsupported workaround, pointed out to me by Erland Sommarskog, SQL MVP & SQL God:

EXEC sp_MS_marksystemobject [your_sp_name]

[Cruft] New DMV in 2008 - SP details

One of the things I've been hoping/waiting/praying for, in SQL 2008, is the ability to see when an SP was last run. In 2000 and 2005 you can do it, so long as it's still in the cache. Which means that things only run sporadically won't occur.

Imagine my happiness:

SELECT * FROM sys.dm_exec_procedure_stats

Extra credit: join to sys.procedures (also new) to get the object.

So, now you can see, since the server was started, what runs. And since your server stays up for several months at a time, you have a good "hit list" of procs that can be scripted out and removed.

I'll update this post when we get a good working version.
(Thanks to Hammer for his help)

[Cruft] Fixing dependencies

EXEC sys.sp_refreshsqlmodule 'dbo.MyProcFnOrView'

That will update your dependencies table, allowing you to use sp_depends even if the objects weren't inserted in order. New to 2005, thankfully someone at MS realized and fixed sp_depends' weakness.

When objects are added, rows are added to the internal dependency table. However, it's very easy to get out of sync - say if you add SP A that calls SP B, but add them in the reverse order. Which _never_ happens. Sure.

So, anyhow. Use that, then use one of the handy pieces of code online that assume that the dependency table always works.


UPDATE 2012:
or, you could always use the versions that 2008+ have (don't bother on 2005; not there):

sys.dm_sql_referenced_entities
sys.dm_sql_referencing_entities


sys.sql_expression_dependencies