Changing an error message in the sys.messages system table

Question

Changing an error message in the sys.messages system table

Steve-443559

SSCrazy

Points: 2309
More actions
August 16, 2010 at 2:20 pm

#141534

I was looking to change a message (an existing, not custom) in the sys.messages table of a SQL 2005 server. Basically, I just want to add to the existing text - "This alert can be ignored between the hours of ....." However, I'm being blocked at every turn and probably because I'm not supposed to alter system tables/views. Just wondering if there was a way around this. I tried starting in Single user mode, using the DAC connection but got the error - failed because it contains a derived or constant field.
Any ideas? should I even attempt to make this change or am I playing with fire?
Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Don't change the system tables. They are not there for you to mess around with. Leave them alone unless you're happy with possibly ending up with a corrupt and unusable database (in this case master)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

LutzM SSC Guru Points: 107049 More actions · Answer 2

If you're "unhappy" with the system messages, create your own message and deal with the system generated messages by using a TRY/CATCH block.

I'd be curious to know the business case you're dealing with...

I'm sure there are several ways to get rid of the error message without messing around with system tables.

To me it sounds like you're trying to cheat your navigation system not to warn you when entering a one way street from the wrong direction just during night time (since you assume there won't be too much traffic...). Scary....

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Steve-443559 SSCrazy Points: 2309 More actions · Answer 3

It will still warn me. The message I'm trying to modify slightly is one of the log shipping alerts. Our weekly index job creates a large transaction log backup which takes time to copy to our DR Site and thus sometimes a restore doesn't happen for an hour or two, triggering the alert. I discussed this with the business and they said they are OK with it during this time of night; however the over night crew can't remember this (they are not the sharpest knife in the drawer) and the only way I can think of for now is to add this small note to the message stating that the alert can be ignored during this one time of the week it occurs.

However, if you have other ideas, I am all ears. If it's not possible, I'll just live with it.

LutzM SSC Guru Points: 107049 More actions · Answer 4

There are several options I can think of:

1) Analyze the index job if it can be optimized (e.g. only roerganize/rebuild indexes that need to be touched basd on fragmentation level)

2) build a customized reindexing procedure wrapped into a TRY/CATCH block, evaluate the error messages in the CATCH block relative to the time the erros occurs and raise a customized warning or even no warning.

3) I'm sure there are more options... 😉

@Gail: Do you have any related links at hand?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 5

Any ideas? should I even attempt to make this change or am I playing with fire?

Yes u r !

Update to system tables should not be done in any way.

Gail and Lutz are correct and follow their recommendations.

BTW, sys.messages is a view which appears in master but is actually in mssqlsystemresource database which can be accessed when sql server is started in single user mode .

Any update is not allowed !

Cheers !

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Adiga One Orange Chip Points: 27284 More actions · Answer 6

To add to the conversation, the messages in sys.messages are dependent on the RLL files. Please refer this

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

LutzM (8/16/2010)
There are several options I can think of:
1) Analyze the index job if it can be optimized (e.g. only roerganize/rebuild indexes that need to be touched basd on fragmentation level)
2) build a customized reindexing procedure wrapped into a TRY/CATCH block, evaluate the error messages in the CATCH block relative to the time the erros occurs and raise a customized warning or even no warning.
3) I'm sure there are more options... 😉

4) Increase the frequency of log backups during that interval

5) Write a custom reindex job that fires off a waitfor delay if the log is of a certain size

6) Create a document for the night shift people, one that has a list of 'if you see this error do this' in it, which tells them to ignore the error.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve-443559 SSCrazy Points: 2309 More actions · Answer 8

Thanks Everybody, I'll probably write the custom indexing procedure, defragging only whats needs it and implementing a WAITFOR as necessary. much appreciated!