May 10, 2005 at 12:28 pm
Hello,
This is a thought, in order to capture user stored procedure and user defined functions exceptions and errors, is it appropriate to create an exception log table in master database and create a sp_ABC procedure and to call this procedure (sp_ABC) in stored procedures and functions (in user databases)?
Is there a better place (non-user created database like msdb, model) to create the exception log table?
Or, create a new user database to hold this table and still have sp_ABC in master database?
Thank you all.
R
May 10, 2005 at 1:47 pm
Personally, I prefer to create a user database (called something like Admin) to hold administrative tables and stored procedures, etc. that I create as a DBA. I don't like creating objects in Master or msdb. I like the ability to back up and restore this stuff seperately from Master. There is always a chance (granted extremely remote) that Microsoft could add a new object to Master with the same name as the object that you previously created.
On the other hand, I've heard speakers at PASS (or TechEd, I can't remember which) that said they put these types of objects in master.
May 10, 2005 at 3:36 pm
I would not put things in Master for the following reasons:
1. You don't want accidentally corrupt it - things happen.
2. Suppose you want a copy of you admin environment on the same server to test something or get yesterday's data - you would not want to restore Master even as a database of another name: to much space for user error
3. Suppose you want to give access to other people to your logs or admin tables, but would not want them to have access to Master
4. If Microsoft would like you to put your own objects in Master they would not call it System database.
5. Upgrades and Service Packs consideration: system databases may be getting different fixes then user databases.
6. Size and Performance problems: the last thing you will want is to have your exception log be generated in Master and for some reason locking it when the system is not functioning properly.
Yelena
Regards,Yelena Varsha
May 10, 2005 at 6:35 pm
Thank you Yelena,
What's your opinion on adding sp_ABC stored procedure, so that it can be called in other databases where necessary?
R
May 11, 2005 at 9:05 am
Yelena is on target ... At my site we have a dba_stats database on the MSX server and all other servers write to it avoiding all of the mentioned issues. As for the stored procedure part, yes we put some DBA specific procedures in the master database on each SQL Server (presently about 10 of them). Maintenance and upgrades are not an issue since we have documented procedures for building and upgrading a server. The DBA specific procedures are a part of the configuration and enlistment process for our MSX environment. I've personally been using this type of configuration since SQL v7.0 with no adverse affects in many different environments. Hope the additional information helps.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
May 11, 2005 at 10:59 am
I agree with Rudy. I think SP is OK. Just remember to backup your Master database before you modify it in any way to a file that will not be overwritten by later backups.
Yelena
Regards,Yelena Varsha
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply