January 24, 2020 at 3:04 pm
Have a fun one that is surprisingly not causing any issues - my master database has the flag "is_cleanly_shutdown" set to 1. Has anyone seen that before?
I ran the following:
SELECT name, state_desc, [is_cleanly_shutdown]
FROM [sys].databases
DBCC CHECKDB(master) WITH NO_INFOMSGS, ALL_ERRORMSGS
and for all of the databases, state_desc comes back as "ONLINE" and for everything except master is_cleanly_shutdown comes back with 0. On master, it comes back with 1.
My first thought was that something must be corrupted in master, so was hopeful that checkdb would see that odd flag and flip it back for me. But alas, checkdb came back saying everything was good.
My understanding is that if master is "cleanly shutdown", I should not be able to log into the instance or view other databases or really do anything. With master cleanly shutdown, the SQL instance should be off. That is not the case on my system and I don't understand why.
I have already tried rebooting the instance and the entire server, yet is_cleanly_shutdown is still set to 1.
Everything on this SQL instance is working as expected, so I am not overly concerned about this, but my worry is that it could lead to future problems such as CU's failing to install as it thinks the master database is offline.
This SQL instance is used solely for SSRS and SSRS is functioning as good as it ever does. It was originally a SQL Server 2008 R2 instance that we did an in-place upgraded to 2012 and then finally to 2016. During this process, we bumped the compatibility level of the user databases (the report databases) to 2012 when we did that upgrade but forgot to bump them to 2016. All of the system databases automatically bumped up their compatibility level as we went except for master which was at 2008 when I looked. My thought was maybe it was a compatibility level issue, so bumped master to 2016. Did the user databases too, but we were not noticing any issues with them. Unfortunately this made no difference.
My 2 questions are:
1 - Has anyone seen and corrected this behavior before and if so how?
2 - Am I right to be concerned with this or is it safe to ignore?
I am uncertain how long it has been running this way, but we have been running SQL 2016 on that instance for roughly 1.5 years. The 2012 was short lived (roughly 3 months) and the 2008 R2 before that was running since 2010.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 25, 2020 at 3:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
January 25, 2020 at 4:49 pm
I've never seen such a thing. I have to admit, though, I've never thought to look for such a thing simply because I'd never have thought such a thing was possible.
If it were my system, I'd get on the horn with Microsoft and ask them what's up. It very well could be an upgrade bug.
Of course, the first thing they're likely to do is to ask you to reboot the system. With that, I have to ask, when is the last time you've rebooted the system? I ask because, although I've never looked for this specific problem, we did follow an upgrade/migration path similar to what you said you folks did (we started at 2005 and went to 2012 and then 2016) and, having checked just now, we don't have such an issue and am wondering if the monthly reboots we do for Windows and SQL Server updates might be the reason.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 27, 2020 at 2:03 pm
Hello Jeff,
Thanks for the reply. As for the reboot, I had to do some maintenance on our servers on the 18th and at that time we rebooted them. Unfortunately, rebooting them did not cause the flag to swap.
I am wondering if restoring the database from master would resolve the issue or not... I expect that it would, but I also would prefer to not take the system offline if I can avoid it. Plus, I would prefer not to have to do overtime.
I think you are correct that opening a case with Microsoft is likely a good next course of action. Was just hoping there was some easy command I could run that would cause SQL to do a sanity check and automatically flip that flag back. I expect I could do it with a hex editor, but without Microsoft explicitly telling me to do that, I'd prefer to avoid that. Especially since this is a production instance. If it was happening on dev/test and not live, I'd monkey around with a few different things such as a hex editor. But on a production system, I am a lot more hesitant.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 27, 2020 at 9:50 pm
Was working with a different forum member (Sue_H) and she pointed me in the right direction and pointed me to a process I did that is likely the culprit - restored master from backup.
First she had me run:
DBCC TRACEON (3604)
DBCC DBINFO('master')
DBCC TRACEOFF (3604);
and I sent her a copy of that output. She saw the following line:
dbi_crdate = 2014-07-27 15:38:59.617dbi_dbname = master_LIVE dbi_dbid = 7
And pointed that out to me. She pointed out that the dbname is master_LIVE and dbid is 7, even though in sys.databases that is not the case. And I knew exactly what had caused this - I restored master from backup as "master_LIVE", took the SQL instance offline, and copy-pasted the mlf/ldf from master_LIVE over top of master then restarted the instance.
Fun part about the above - that also explains why master was in 2008 compatibility mode after an in-place upgrade: restored from an old backup. What I don't remember (as it was a LONG while back) is why I restored master from live. I will be ignoring this problem for now, but when I do my next upgrade, it'll be a fresh SQL Instance so I can make sure not to screw up master worse. Mildly concerned that my master db may have some glitches due to it being a restore from the 2008 R2, but things seem good for now.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply