I've read a lot about this over the last couple of days, but none specifically on the Master db.
If I switch from full to simple, will it flush out any plans, statistics, etc? Any performance hits?
I know how to proceed, just never seen Master on full recovery before.
TIA
It does not really make sense to have the master database in FULL recovory. The installation process leaves the database in SIMPLE recovery, so this means that somebody changed it afterwards because they could.
I do not think that you perform operations on the master database that require point-in-time recovery and if you do I strongly suggest that you rethink this strategy. Also, take into account that a database in FULL recovery needs to back up its log regularly in order to avoid filling the log files. Is this happening? How are you planning to restore these log backups in case?
That said, I would change it back to SIMPLE without worrying too much about performance hits.
I remember looking into this during a beta phase of SQL Server (I think it was 2005). I was surprised that you even could change recovery model for master.
And, to make things more interesting, log backup isn't allowed for master.
Back in the days, SQL Server behaved like if master was in simple mode, in the sense that it would auto-truncate the log.
But below test show that this auto-truncate doesn't happen nowadays (SQL Server 2019).
Note: below will fill log for master database and ldf will autogrow. Don't run if you feel uncomfortable with that.
ALTER DATABASE MASTER SET RECOVERY FULL
BACKUP DATABASE MASTER TO DISK = 'nul'
DROP TABLE IF EXISTS t
CREATE TABLE t(c1 int identity, c2 char(80))
INSERT INTO t (c2) VALUES ('a')
DECLARE @i int = 1
WHILE @i < 100000
BEGIN
IF @i % 20000 = 0
BEGIN
SELECT counter_name, instance_name, cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Log File(s) Used Size (KB)' AND instance_name = DB_NAME()
CHECKPOINT
END
SET @i += 1
UPDATE t SET c2 = REPLICATE(SUBSTRING(CAST(@i AS varchar(20)), 1, 1), 70)
END
--Below result in error
--BACKUP LOG master TO DISK = 'nul'
