November 16, 2007 at 1:35 pm
Hello all.
We're using SQL Server 2005 sp3.
We've recently added a database trigger for some of our databases. The triggers fires on any DDL event and writes a record to a table in a separate database (which I call DB_AUDIT).
Whenever we restore one of our databases with this new database trigger onto another machine, we get the following error ...
Restore failed for Server 'MYSERVER'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: Target string size is too small to represent the XML instance (Microsoft.SqlServer.Smo)
The DB_AUDIT database exists on the machine we are restoring to.
Has anybody come across this before?
November 19, 2007 at 12:41 am
It looks like in the trigger is trying to insert xml data into a column. However the length of the column is too small to hold the xml data. Therefore the trigger is failing.
Increase the legnth of that column and restore again.
November 19, 2007 at 5:22 am
Thanks for the response. However, I am still unclear why this would be happening during a database restore.
November 19, 2007 at 7:41 am
The database restore would trigger a DDL event since there is essentially a new database being created (and one being dropped).
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 19, 2007 at 10:43 am
Hi Steve,
Thanks for responding.
I must admit that I am still confused, however. If I have set up a DDL trigger with database scope (as opposed with server scope), then how is it that a database restore if causes the trigger to fire?
I must be missing something.
- Mike
November 20, 2007 at 4:39 am
What type of DDL trigger did you create?
Remember, even if you're talking about the trigger being set up on a database scope, if the DDL trigger has anything to do with dropping tables / creating tables, dropping triggers / creating triggers, anything of that nature, then a restore, which drops ALL objects and then recreates them, will trigger your DDL trigger.
Is the DDL trigger stored in the database you're restoring? Easiest way to check (if you still have access to the original DB) is to search sys.Triggers in that database. If so, you're stuck. The trigger will probably fire no matter what you do. If you've put the DDL trigger on your audit DB, though, try disabling it during the restore. See if that works.
November 20, 2007 at 6:46 am
Hi Brandie. Thanks for the response.
My approach was to create a database DDL trigger on select databases and have the trigger log the changes to a common database, DB_AUDIT.
I didn't want to have a server scope DDL trigger (just yet) but I'm thinking that would be the better way to go.
It would be nice to be able to set a server scope trigger that only fires for events from select databases. I don't think this is possible.
Just to reiterate, the problem occurs when I try to restore one of the databases that has this trigger onto a test machine (which also has the DB_AUDIT database). Or stated another way, I have the DDL trigger set up on 3 databases on the production server (call it server A) and I want to backup and restore one of these database on a test server (call it server B). Both server A and B have the DB_AUDIT database.
The trigger is as follows ...
USE [OPS_TOFS]
GO
/****** Object: DdlTrigger [ddltrg_CREATE_LOG] Script Date: 11/20/2007 09:39:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [ddltrg_CREATE_LOG] ON DATABASE -- Create Database DDL Trigger
FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET ARITHABORT ON
SET NOCOUNT ON
DECLARE @xmlEventData XML
-- Capture the event data that is created
SET @xmlEventData = eventdata()
-- Insert information to a EventLog table
INSERT INTO DB_AUDIT.dbo.tblDDLEventLog
(
EventTime,
EventType,
ServerName,
DatabaseName,
ObjectType,
ObjectName,
LoginName,
UserName,
CommandText
)
SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),
'T', ' '),
CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),
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(50), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/LoginName)')),
CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/UserName)')),
CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [ddltrg_CREATE_LOG] ON DATABASE
BTW, I can recover the database following the error during restore simply by bringing it back online, which puts it into single user mode, and then going to database properties and changing the Restrict Access value from Single_User to Multi_User. However, this is less than ideal.
- Mike
November 20, 2007 at 7:00 am
The simplest solution I can think of is to disable the DDL trigger on the Production DB, backup the production DB with the trigger disabled, re-enable the trigger on your Production DB, then restore the backup where the trigger is disabled.
This should allow you to restore without the trigger going off.
November 20, 2007 at 8:27 am
Hello again.
I took your suggestion and made it work. I added two new steps to the daily backup job for the databases having the trigger. The first new step disables the DDL trigger, the next step backs up the db and the second new step enables the DDL trigger.
This way, I can restore the database to my test environment without any problems. It does not have the trigger enabled but that's okay.
It is not as elegant as I'd like but it will do for now.
Thanks.
November 20, 2007 at 8:40 am
I'm glad it worked.
One thing I would do, if I were you, is to head out to MS's bugs & suggestions forum and see if anyone has reported this as a problem before. If not, you might want to report it as either a bug or a suggestion and see what the Microsoft crew has to say about it.
URL is: http://connect.microsoft.com/SQLServer and click on "Submit Product Feedback". You need a live account to log into the site.
January 14, 2008 at 8:24 am
You might be able to rewrite your trigger to avoid throwing errors. Instead of using "CONVERT(VARCHAR(25),..." you might try "LEFT(CONVERT(VARCHAR(max),...), 25)". Or use BEGIN TRY ... BEGIN CATCH to handle the error.
January 15, 2008 at 3:22 am
problems probably got to do someting with xml u r inserting rather than the trigger. I did not get this error. This i what i did .
Created a new db and wrote a DDL trigger with database scope for DROP_TABLE.
backed up this db (which has DDL Trigger) and restored it to another blank db (new db). It restored the database succesfully.
"Keep Trying"
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply