SQL Server Performance issue - With DB Compatibility upgrade from 120 to 150

Worked Solution:

We turned on the Legacy Cardinality Estimation on the database properties. 

If anyone has other solutions please comment.

SQL Server 2022 Services Failed To Come Online.

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:

https://blog.sqlauthority.com/2019/04/04/sql-server-script-level-upgrade-for-database-master-failed-because-upgrade-step-msdb110_upgrade-sql-encountered-error-8649-state-1-severity-17/

Failed to initialize sqlcmd library with error number -2147467259.

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. 

SSRS not starting - unable to connect to the report server

 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 =================

SQL Server DB Object, Job and DB Owner Details

 -- 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

 

-- Below will provide DB Owners

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'

 

Restore encrypted databases

 Msg 33111, Level 16, State 3, Line 1

Cannot find server certificate with thumbprint '0xE11A199C1059C6F1E0223B56581CDCF3F043DFE8'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Solution

Script to List the Version and Edition

 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

Query to list the SPID time and completion time

 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;

=====================================================================

SSIS- The version number in the package is not valid.

Package migration from version 8 to version 6 failed with error 0xC001700A "The version number in the package is not valid. The version number cannot be greater than current version number.". 

Solution:

1. Go to properties 

2. Change the Version and redeploy the package

3. Copy the DTSX or move it to source server and execute.

Alter failed for Database 'DBNAME '. (Microsoft.SqlServer.Smo), It is in the middle of a restore. (Microsoft SQL Server, Error: 927)

 First Solution 

This error will appear when we configure DB Mirroring. 

Solution 1:

Please ensure you restored the latest log backup on the mirrored server and then click on start mirroring again on principal.

 

Solution 2:

 

Set the endpoints on both mirror and principal servers by executing the below commands on each server.

On Mirror Server:

 

 ALTER DATABASE DBNAME SET PARTNER = ‘TCP://PrincipalServername.com:5022’

On Principal Server:

 

 ALTER DATABASE DBNAEM SET PARTNER = ‘TCP://MirrorServername.com:5022’

The publisher of this content does not allow it to be displayed in a frame

This will issue will come when SQL Server has been upgraded to any versions after 2016, SSRS reports that are used to display in iFrame will fail to display.

Solution:

Go to URL and change the URL to reportviwer, if the URL is using reports then it will not dispay in the iframe.

Example:

Change From:

https://Server/Reports/browse

Change To:

https://Server/ReportServer/Pages/ReportViewer.aspx?&rs:embed=true

or

 http://Servername/ReportServer/Pages/ReportViewer.aspx?%kjhkjs%khjkhkjhy%retre+uytts%2popiopoi+iuiuyiuy&rs:Command=Render&Rs:embed=true

Note:  this will be your report name -> %kjhkjs%khjkhkjhy%retre+uytts%2popiopoi+iuiuyiuy

SSRS Failed to Start - an error occurred when attempting to connect to the report server remote procedure call (RPC) end point.

ERROR: 

report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the operation.

 ---> System.Runtime.InteropServices.COMException: The RPC server is not listening. (Exception from HRESULT: 0x800706B3)

   --- End of inner exception stack trace ---

   at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ThrowOnError(ManagementBaseObject mo)

   at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.GenerateDatabaseScript(String databaseName, Int32 lcid, Boolean isSharePointIntegrated, String& script)

   at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.GenerateDatabaseScript(String databaseName, Int32 lcid, Boolean isSharePointIntegrated, String& script)

Solution:

1. Switch the account to local service/local system

2. Start the reporting services and configure.

3. Now change the service account to domain account.

 

SQL Server Job - Failing - Error: 4014, Severity: 20, State: 8.

 There are many reasons to fail the SQL Server job with this error code like -

1. Permission issue for the Job owner
2. Permissions for the SQL Server Agent account
3. Permissions on MSDB and User Datbases
4. Issues with the sp_send_dbmail parameters 

If above are proper please follow the below steps to identify the issue -

Open profiler and choose the below events and then run the job.

In our case this was the culprit.

Now fix the code and run the job, which will succeed. 

Job Output file location

-- Script to find the JobOutPut Location.

select j.name,js.output_file_name,

  js.step_name,js.database_name "Executing On which DB?",

  last_run_outcome = case when js.last_run_outcome = 0 then 'Failed'

        when js.last_run_outcome = 1 then 'Succeeded'

        when js.last_run_outcome = 2 then 'Retry'

        when js.last_run_outcome = 3 then 'Canceled'

        else 'Unknown'

       end,

  last_run_datetime = msdb.dbo.agent_datetime(

       case when js.last_run_date = 0 then NULL else js.last_run_date end,

       case when js.last_run_time = 0 then NULL else js.last_run_time end)

from msdb.dbo.sysjobs j

  inner join msdb.dbo.sysjobsteps js

   on j.job_id = js.job_id

   WHERE js.output_file_name <> '[NULL]' 

  order by js.output_file_name

Thanks to : https://www.sqlservercentral.com/scripts/query-to-find-the-job-output-file-location-at-each-job-step

ORACLE- Contol File Missing – Recovery (ORA-00205: error in identifying control file)

 Good Recovery Tips, Thank you:

https://fatdba.com/2012/09/24/contol-file-missing-recovery-ora-00205-error-in-identifying-control-file/

Change Mode Of SSAS

Below are the details how we can change this mode, please note take a backup of existing CUBES before changing this option.

DeploymentModes :

DeploymentMode=2 for Tabular Mode

DeploymentMode=0 for Multidimensional Mode

DeploymentMode=1 is for PowerPivot for SharePoint instances.

Steps:

1. Backup any multidimensional databases on the server and either detach them or delete them.  Once mode is changed this may not work.
2. Copy the msmdsrv.ini file to safe location this can be found here - C:\Program Files\Microsoft SQL Server\MSAS15.MSSQLSERVER\OLAP\Config.
3. Open the config file in Notepad. Change the DeploymentMode property as required - 0 (multidimensional) to 2 (tabular) Save and close the file.
4. Copy the msmdsrv.ini file back to the OLAP\Config directory.
5. Restart the SQL Server Analysis Services instance.

SSRS 2019 Could Not Load Folder Contents - You are not allowed to view this folder

1. Add the user group / service account to the local admin
2. Refresh the URL