December 1, 2011 at 5:50 am
Hello,
I tried a quick search about this subject but cannot find any relevant topic. I don't even know if i'm posting in the right section. Apologize if i'm posting in the wrong section.
Anyway, i've a small issue. We're facing some problems with the default connection timeout for mirroring and I was trying to create a trigger that will automatically increase this timeout for any newly database configured for DB Mirroring.
Here's how I wrote it:
CREATE TRIGGER Change_Mirroring_Timeout
ON [master].[sys].[database_mirroring]
WITH EXECUTE AS 'sa'
AFTER INSERT
AS
DECLARE @dbname NVARCHAR(150)
DECLARE @query varchar(max)
SET @dbname = (SELECT name
FROM master.sys.databases
WHERE database_id = (SELECT MAX(database_id)
FROM master.sys.database_mirroring
WHERE mirroring_guid IS NOT NULL));
SET @query = 'ALTER DATABASE "'+@dbname+'" SET PARTNER TIMEOUT 30'
EXEC (@query)
It was just a quick test so, be kind please 🙂
I get this error when trying to execute the code:
Msg 8197, Level 16, State 4, Procedure Change_Mirroring_Timeout, Line 1
The object 'msdb.sys.database_mirroring' does not exist or is invalid for this operation.
Why doesn't this work ?
How could I make it running well ?
Thanks in advance.
Regards.
Franck.
December 1, 2011 at 6:06 am
You can't create triggers on system objects. I've had the same issue some time ago with replication tables.
IIRC, it should work from a DAC connection, but I don't think it's a good idea anyway.
-- Gianluca Sartori
December 5, 2011 at 5:07 am
Okay thanks for fast reply. I'm gonna try to find a workaround. I'll let you know...
Regards.
Franck
April 14, 2015 at 7:41 am
Hi Franc, did you manage to get a work-around this? I have a smiliar need ...
April 14, 2015 at 7:50 am
Why not just set all databases to have the increased timeout? Is there any reason for any database not to have it? If you can only set it when mirroring is enabled, then I suppose you could create a job that runs hourly and sets the timeout to the required value for all mirrored databases.
John
April 14, 2015 at 8:14 am
I was actually trying to detect IF [mirroring_witness_state] on [master].[sys].[database_mirroring] has been changed by placing a trigger on it....but realising it's not possible- getting the same error '' The object 'master.sys.database_mirroring' does not exist or is invalid for this operation. ''
Any other ways to detect/log IF a Witness has been CONNECTED or UNKNOWN on a mirrored database?
April 14, 2015 at 8:17 am
Have you looked at extended events? I don't know if it's possible to do what you're trying to do, but it's worth investigating.
John
April 15, 2015 at 3:29 am
Hey !
It's still forbidden to create triggers on system databases, unfortunately. So, I've nothing for you, vlatkad 🙁
@john-2 Mitchell-245523: Why not ? Because, to me, the 10 seconds timeout should be enough. Of course, a job that would increase the timeout by scanning all the new databases on the instance every x hours would work, but the trigger thing was a clean way to do the job, I thought.
I ended up leaving everything with the default parameters. Because, again, 10secs should be enough :-).
Regards.
Franck.
April 15, 2015 at 1:17 pm
John Mitchell's approach (a job that periodically checks for new database mirrors) would be the way I would go if automatically changing that setting really were necessary, perhaps because of consistent network blips or some such thing.
As an aside, I think your posted error message is from a previous try at creating the trigger, since that error mentions msdb.sys.database_mirroring, while the script only correctly refers to master.sys.database_mirroring. 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply