May 25, 2011 at 9:07 am
This is about the wierdest problem I've had in a long time.
I create a function or stored proc - something simple that doesn't take parameters and just returns "Hello World" as follows:
============================
create function fn_HelloWorld()
returns varchar(50)
as
Begin
Declare @varReturn varchar(50)
set @varReturn = 'Hello World'
Return @varReturn
end
============================
Simple right? I checked that this creates a function in the master database or any other database - no problem.
So I go over to my producion DB & run it and what do I get?
An errror - that's what. In fact the following is printed into the immediate windows
********************** Immediate Window Contents *****************************
USE [BASys]
create function fn_HelloWorld()
returns varchar(50)
as
Begin
Declare @varReturn varchar(50)
set @varReturn = ''Hello World''
Return @varReturn
end
Msg 8501, Level 16, State 3, Procedure SyncFunctions, Line 15
MSDTC on server 'SERVER' is unavailable.
************************ end *************************************************
That's it. Nothing else - and obviously no newly created function either.
Any internet research points to a bundle of stuff that makes no sense, starting with SQL 2000 related issues and ending up telling me that my MSDTC is problematic - but the point is that this is the only database on which this is happening.
Please tell me that someone else has had a similar issue at sometime and that I'm not going insane.
May 25, 2011 at 9:41 am
You talk about data corruption. Have your run checkdb? If so what are the errors (all of them >>> WITH NO_INFOMSGS, ALL_ERRORMSGS)
May 25, 2011 at 9:48 am
I posted in the data corruption section 'cause I thought that it seems like some kind of corrution issue - but I have run DBCC CHECKDB - and found no errors.
If anyone has found a similar error - maybe they can say whether or not they had any corruption associated with their DB.
The suspicion about data corruption came about because I noticed that the DB was in recovery mode for a few minutes earlier today and then went back to normal all on its own - so I'm not sure if there are aliens abound either 😉
May 25, 2011 at 9:51 am
I take it this error occurs when you create the function, not when you access it? Since, according to the error message, the error occurred in SyncFunction, I think you must have a DDL trigger on your production server that connects to a remote server or does something else that requires MSDTC to be working.
John
May 25, 2011 at 9:58 am
Nope - no DDL triggers.
In fact, I have a duplicate DB that is a restore of a backup of the DB in question. The backup is maybe 3 days old.
There are no problems with creating functions in the duplicate DB.
In fact, I created the function in the duplicate db, then scripted the function to a new query windows, changed the target DB to the problem Db, pressed F5 - and then I get the error.
Stupifying !!!!
Also - yes the problem occurs when i create it. I can use all of the existing stored procs & functions - but can obviously not use the function I'm trying to write - cause it won't let me create it.
May 25, 2011 at 10:10 am
In that case, I don't know what's happening. But I would look for that procedure SyncFunctions and see what it's trying to do.
John
May 25, 2011 at 10:13 am
John Mitchell-245523 (5/25/2011)
In that case, I don't know what's happening. But I would look for that procedure SyncFunctions and see what it's trying to do.John
The only hint that makes sense from google is that this function can be linked to replication... which could explain almost all the symptoms (or lack thereof).
Good luck!
May 25, 2011 at 11:18 am
Thanks John - it looks like I'm going to need a lot of luck on this one.
We are not using any replication on this DB at all - so I'm stuck trying to figure it out slowly.
Maybe what I will do is just sidestep the issue. Script out the entire DB, including data. Push the lot into a new DB, drop the old DB, rename the new DB to the old and then keep working.
The problem is that I will not have learnt what the problem was and will not be able to replicate it.
May 25, 2011 at 11:25 am
SeanAchim (5/25/2011)
Thanks John - it looks like I'm going to need a lot of luck on this one.We are not using any replication on this DB at all - so I'm stuck trying to figure it out slowly.
Maybe what I will do is just sidestep the issue. Script out the entire DB, including data. Push the lot into a new DB, drop the old DB, rename the new DB to the old and then keep working.
The problem is that I will not have learnt what the problem was and will not be able to replicate it.
Is it impossible that someone setup replication (even for 1 sec on that db) and now it's screwing with you?
That's the only lead I have that makes any sort of sense...
May 25, 2011 at 11:30 am
Yep, definitely not possible that anyone setup replication.
I actually had a similar problem when we deployed the DB to a client's site. We then scripted out the DB, imported the data and went on as normal - so i know that this is a way forward. But I have no clue as to what caused the problem so i can't take preventative measures. I just have to hope that we don't get the problem again when we have a very large DB and a high risk associated to the primary key values changing when scripting out the DB again in the future.
May 25, 2011 at 1:40 pm
is clustered server? because i found this link for SQL 2K0 in environment W2K3 with problem on MSDTC!
this link: http://support.microsoft.com/kb/889706/en-us
May 30, 2011 at 8:56 am
LATEST UPDATE:
Having scripted out all of the database objects, moved the data over, recreated all permissions and rebuilt the solution, everything is fine...
I can now recreate stored procs, create functions and work normally.
I could not even do a normal database restore... Wierd!!!
So, I now have resolved the problem the LOOONNNNGGGG way around and am no closer to knowing what the heck happened.
Soooooo, just as I think that everything is in the clear.......
I noticed that another of my databases are frequently "in recovery" mode. Hmmm.
These databases that go into and out of recovery mode are allowing me to work normally once they have stabilised, but still.... It's left me wondering...
Humph.....
May 30, 2011 at 8:58 am
Have you checked the sans to make sure there's nothing wrong there?
1 db "might" be a fluke, but not 2-3!
May 30, 2011 at 9:06 am
Well, the db in recovery isn't causing a problem .... YET...
It's something I'm starting to track as part of the problem solving process - and it's come up now on more than 1 DB.
I can access the DB normally after recovery mode has ended properly, but it's left me wondering & investigating.
I still have this nasty suspicion that something is happening somewhere - and that 8501 message was the only alert I received. I'm convinced in fact that the 8501 message was just a general failure type of message and that I reckon I still have some problems ahead, but for the moment I can sa - with some awkardness - that everything seems okay.
The SANs and all other physical structures are scheduled for testing later tonight, if there is a problem there I'll mention it here too.
May 30, 2011 at 9:17 am
What is the EXACT status that the databases are in when they're not available? (Query sys.databases, check the status column)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply