Growing up as a young boy, my elementary teachers always warned me about the perils of putting stuff in master. But what’s the real deal? Why is this such a bad idea?
System databases are often located on space-limited drives. The default installation parameters for SQL Server throw master, model, and msdb all on the C drive. I’d rather not create tables that might grow when I’m not looking, and then possibly run my server’s limited boot drive out of space. Starting Windows with a full C drive is not a lot of fun.
In the event of a disaster, we don’t usually restore master. While you can indeed restore one server’s master database over to another, it’s just not a good idea. You’re failing over to another server because things are already going wrong – why make things worse by doing something you rarely practice? Plus, if you’re failing over due to corruption, you probably don’t want to bring over a possibly corrupt master database.
Even if you could restore it, you’ll lose data. You can’t do transaction log backups for the master database, so you’ll lose whatever changes were made since the last full backup.
Now, having said all this, I’m completely okay with putting utility stored procedures in the master database – things like sp_WhoIsActive, sp_Blitz®, and the like. After all, those objects take hardly any space, and we don’t need to restore them over to our DR environment.
Kendra says: You may have more in your master database than you think! It just takes one ‘oops’ on a deployment script, and there you go.
Want to learn more?
We just launched our new Fundamentals of Database Administration class – an online video course that teaches you a lot of things you should have been taught when you first took this job. (No, none of us learned it the smart way.)
17 Comments. Leave new
Would you say that user write access to the master database is a design mistake in SQL Server? In the same spirit one could ask whether it’s a mistake that you can create non-temporary objects in tempdb.
Tobi – no, I wouldn’t say that.
How do you feel about the location of the Ola Hallengren scripts?
I have placed them in master just like the sp_blitz scripts. I feel that they are very close in purpose to the sp_blitz scripts (DBA Utility, non-critical, easily recreated). However, the main difference appears to be that they store data by default in the commandlog table. There is a job that I use on a regular basis to archive that data. The data is very minimal. I do not have space issues and I do not store the system databases on system drives.
Do you recommend moving them elsewhere? If so, where would you recommend?
-Thanks!
Joel – I create a database called DBAtools for this kind of thing.
I call mine DBAUtility
The First Responder Kit is having this code in dbo.sp_AllNightLog:
EXEC master.dbo.sp_DatabaseRestore @Database = @database……..
So when I ran it in DBATools database it got this warning
The module ‘sp_AllNightLog’ depends on the missing object ‘master.dbo.sp_DatabaseRestore’. The module will still be created; however, it cannot run successfully until the object exists.
So I thought, mmm is Brent telling me here that I should install sp_Blitz in the master database?
I googled it, and came here to you stating not to do it.
So I am confused.
Thanks
Same here!! What gives. It’s hard coded master.dbo.
If that’s a problem for you, feel free to contribute code to the open source project to get it to do what you want. We welcome contributions. Thanks!
I had created a database called Administration on all our database servers, just to hold all the little, and not so little, DBA procedures and functions out there. However we still do have a suite of stored procedures that we run on each server, and then mark them as system objects.
This. I call mine “SysAdmin”.
I have a set of stored procedures I use regularly that are stored in each master database in every instance I support. All of this is scripted and stored on a file share. In the event of a disaster, I simply run the scripts on the new master and all of my toys are back in the box.
i put whoisactive in there because i’m too lazy to remove the use statement.
As many of you, I have a DBADB in each instance, but I have the central DBADB in my personal, administrative UTILITY instance in which I keep the main copies of my stored procedures. I push them (and only them) out by simply re-initializing a tiny publication I created of just my core stored procedures. With those couple clicks, all my instances are current. That instance is also my MSX so I can push out my standard jobs (system backups, DBCC checks, sp_BlitzCache data collection, etc.). Pretty slick.
This is the basic approach:
http://allen-mcguire.blogspot.com/2014/11/work-smarter-not-harder-utility-instance.html
When managing enterprise wide and you have over 500 instances, creating the extra DB for admin scripts adds a lot of overhead. Especially if there are acquisitions and a bunch of new servers\instances being added -_- So, i believe this depends on your environment. A small-medium size infrastructure, it can be ideal; however, large enterprise wide, it can be not so much fun & DBAs are all about having fun. Am I right?!?!
I know this article is 4 years old, but maybe I’ll still get a reply…
I have a sort of hybrid situation. I have a utility function, that is used by a production application. It’s just a simple function that takes in a csv string and spits out a table.
The problem is, we use this function in probably 5 different databases, so there’s now 5 instances of the same function. Every time we need to use it in a new database, we have to deploy it to that database….(sometimes other devs just reference one that’s in a different database…but that’s a different issue lol).
So I’m assuming because of what you said earlier about not restoring ‘master’, that it shouldn’t go there, because there’s a dependency on it in other DB’s. So is the best solution here to just do what we’re doing and deploy to each DB as needed…..or create a new DB for things like this to go in (we have probably another 10 other functions similar to this use case that could go in there).
Chad – if you’re on 2016, I would use the new STRING_SPLIT function. It’s built into the system, and performs really well. If not, I’d deploy it to each database as needed.
Awesome, thanks for the response!
PS, unfortunately we’re not 🙁 but hopefully soon. In some cases we need to retain the original order of the list, so even then, it will have to stick around. Really wish STRING_SPLIT did 🙁