Error: Script level upgrade for database ‘master’ failed because upgrade step msdb110_upgrade.sql encountered error 926, state 1, severity 25
The above error will be present in the Error log and the solution will be to start the SQL Server either from /T902 or add this in strtup parameter to permently resolve this issue.
This will bypass the upgrade script and starts the SQL Server.
Reference:
This error has too many options to fix, one of them is below resolution -
In our case we were using a dynamic query which will send some results over the email with help of dynamic execution. when the query was executed using sp_send_mail it used to fail.
Failed to initialize sqlcmd library with error number -2147467259.
After couple of hours identified that query results using linked server was causing the issue.
An error occurred during Service Master Key decryption There is no remote user ' ' mapped to local user '(null)' from the remote server ' '. (Microsoft SQL Server, Error: 33094)
To fix this we need to run
ALTER SERVICE MASTER KEY FORCE REGENERATE
Once the above command executed linked server started working properly. The dynamic query started to work smoothly, which inturn resolved the issue.
After the windows upgrade few cases WMI RS integration will be corrupted, during that time we get the error "Unable to connect to the Report Server "
Solution :
Based on the SQL Server Version the MOF file will be located in different locations, in our case it was on D drive.
1. Open CMD prompt with elevated privileges
2. Run below command
mofcomp "D:\SQL Server Files\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin\\reportingservices.mof"
=============== This will fix the issue =================
-- Below will provide the Job Owner Details
SELECT s.name AS JobName, l.name AS JobOwner, s.enabled
FROM msdb..sysjobs s
LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid
WHERE l.name IS NOT NULL and l.name in ('Domain\User' )
ORDER by l.name
SELECT name,
suser_sname( owner_sid ) AS DBOwnerName
FROM master.sys.databases
WHERE suser_sname( owner_sid )in ('Domain\User' )
--- Below will provide Each Object Owners
;with objects_cte as
(
select
o.name,
o.type_desc,
case
when o.principal_id is null then s.principal_id
else o.principal_id
end as principal_id
from sys.objects o
inner join sys.schemas s
on o.schema_id = s.schema_id
where o.is_ms_shipped = 0
and o.type in ('U', 'FN', 'FS', 'FT', 'IF', 'P', 'PC', 'TA', 'TF', 'TR', 'V')
)
select
cte.name,
cte.type_desc,
dp.name
from objects_cte cte
inner join sys.database_principals dp
on cte.principal_id = dp.principal_id
where dp.name in ('Domain\User' ) or dp.name <>'dbo'
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xE11A199C1059C6F1E0223B56581CDCF3F043DFE8'.SELECT
SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
SERVERPROPERTY ('ServerName') AS SQLServerName,
SERVERPROPERTY ('Edition') AS ServerEdition,
CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL2019'
ELSE 'unknown'
END AS MajorVersion,
CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN SERVERPROPERTY ('productlevel')
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN SERVERPROPERTY ('productlevel')
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN SERVERPROPERTY ('productlevel')
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN SERVERPROPERTY ('productlevel')
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN SERVERPROPERTY ('productlevel')
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN SERVERPROPERTY ('productlevel')
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN SERVERPROPERTY ('productlevel')
else
SERVERPROPERTY('ProductUpdateLevel')
END as 'ProductUpdate Level',
SERVERPROPERTY ('ProductVersion') AS ProductVersion,
--RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
SERVERPROPERTY ('Collation') AS Collation
Here is the query i found on one of the blogs, we can use this to know the SPID duration along with wait type. Thanks to the blogger.
===================================================================
SELECT R.session_id
, R.percent_complete
, R.total_elapsed_time/1000 AS elapsed_seconds
, R.wait_type
, R.wait_time
, R.last_wait_type
, DATEADD(s,100/((R.percent_complete)/ (R.total_elapsed_time/1000)), R.start_time) AS est_complete_time
, ST.text AS batch_text
, CAST(SUBSTRING(ST.text, R.statement_start_offset / 2,
(
CASE WHEN R.statement_end_offset = -1 THEN DATALENGTH(ST.text)
ELSE R.statement_end_offset
END - R.statement_start_offset
) / 2
) AS varchar(1024)) AS statement_executing
FROM sys.dm_exec_requests AS R
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) AS ST
WHERE R.percent_complete > 0
AND R.session_id <> @@spid;
=====================================================================