SQL Server – Reporting Services – Error – “Invalid or Expired Session”

Background

Reviewing collected “Extended Events” and noticed a recurring error.

Error

Image

Tabulated

  1. Message :- Invalid or Expired Session: [session id]
  2. Event Name :- error_reported
  3. Error Number :- 50000
  4. Severity :- 16
  5. session_server_principal_name :- Web Site’s Application Pool Identity
  6. server_principal_name :-
  7. is_system :- false
  8. database name :- ReportServer
  9. client_app_name :- Report Server

TroubleShooting

Database

ReportServerTempDB

Table

ReportServerTempDB.dbo.SessionData
Reviewing date of entries
Reviewing date of entries – SQL

SELECT

        [ExpirationMin] 
		  = min 
				(
					[Expiration]
				)
      
      , [ExpirationMax] 
		= max 
			(
				[Expiration]
			)
      
      , [durationBetweenMinAndMax]
			= datediff
				(
					  minute
					, min ([Expiration])
					, max ([Expiration]) 
				)

      , [durationBetweenMinAndCurrent] 
		  = datediff
				(
					  minute
					, min ([Expiration])
					, getdate() 
				)

      , [durationBetweenMaxAndCurrent] 
		= datediff
			(
				  minute
				, max ([Expiration])
				, getdate() 
			)

FROM [dbo].[SessionData] tblSD


Explanation
  1. The entries returned will have duration reflecting the settings set for session recycling.

Remediate

Configuration

Configuration Files

rsreportserver.config

Item :- Configuration \ CleanupCycleMinutes
Default

The default setting for CleanupCycleMinutes is 10.

This translates to entries in the ReportServerTempDB been pruned every 10 minutes.

Revised

Please increase timeout.

Doing so will mean that records are recycled less frequently.

Image – Default

 

Image – Revised

Explanation

Change CleanupCycleMinutes from 10 minutes to 180 minutes, 3 hours.

Transact SQL – Drop Temp Table if it it exists

Background

Reviewing some Transact SQL Code and saw a code block that works well in exception handling, but can have a bit of side effect in Transact SQL.

Code

Original Code

SQL


BEGIN TRY

    DROP TABLE #pollingData;
     
END TRY
BEGIN CATCH
END CATCH;

 

Explanation

When the temp table does not exist, an error is raised.

Because the drop table is enclosed in a try/catch block the error is gracefully handled by the system.

Noise

But, yet there is a bit of silent noises.

SQL Server Profiler

Image

Tabulated
  1. Event
    • Exception
      • Error :- 3701
      • Severity :- 11
      • State :- 5
    • User Error Message
      • Error :- 3701
      • Severity :- 11
      • State :- 5

Trace Events

Image

Explanation
  1. Events
    • objectName = error_reported
      • eventData
      • error Number :- 3701
      • severity :- 11
      • message :- Cannot drop the table ‘#pollingData’, because it does not exist or you do not have permission.
      • sqlStatement :- empty

 

Revised Code

Check If Table Exists, before attempt to drop

SQL

BEGIN TRY

	if object_id('tempdb..#pollingData') is not null
	begin
		DROP TABLE #pollingData;
    end 

END TRY

BEGIN CATCH

END CATCH;

Drop Table, If Exists

In MS SQL Server v2016 and later versions, we can use the new “drop object if exists” conditional statement…

SQL


drop table if exists #pollingData;

 

Other Errors

There are other errors that can be avoided with dropping an object only upon validation that it exists.

SET XACT_ABORT ON;

If you use the set xact_abort on directive, your code will abort upon running into the error mentioned above.

Error Message

Msg 3930, Level 16, State 1 ..
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

 

Windows Internal Database & “Windows Server Update Services” ( WSUS ) DB

 

Background

As part of a SQL Server Inventory exercise we have been working on since Summer 2017, noticed 3 machines running “Windows Internal Database” version 2005.

 

Patch Report

Here is a snapshot of GM’s report…

inventory_20171110_0630AM

 

Examination

Let us remote desktop to the SCCM machine and see whether GM’s fingerprinting bears true.

 

Windows Version

We ran winver to deduce the version of Windows

Command

 
winver
 

Output

winver_20171110_0508AM

 

Explanation

We are running Windows Server 2008/R2 with Service Pack 1 ( SP1 )

 

File System

Windows Internal Database are packaged as part of the main application’s installer and very little customization is allowed to the WID sub-component.

Therefore we can learn a lot based on the OS Folder where it is installed.

 

Control Panel – File Options

Let us access Control Panel \ File Options and make sure that it is configured for Administrative needs.

 

Images

Images – Before

controlPanel_folderOptions_20171110_0458PM

 

Images – After

controlPanel_folderOptions_20171110_0459PM

 

Explanation

  1. Uncheck
    • Hide extensions for known file types
    • Hide protected operating system files
  2. Checked
    • Show hidden files, folders, and drives

File System

Application Targeted Folder

Folder Win OS Application
C:\Windows\sysmsi\ssee\ Windows Server 2008 – x64 bit Windows SharePoint Services 3.0

Windows Server Update Services 3.0

 C:\Windows\WID  Windows Server 2012 – x64 bit Windows Server Update Services v 2012

Folders

Folder – C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL

SQL Server

SQL Server – Error Log

SQL Server – Error Log – File Structure

SQL Server – Error Log – File Structure – Files

SQL Server – Error Log – File Structure – File – ErrorLog

SQL Server – Error Log – File Structure – File – ErrorLog – Contents

Reviewing SQL Server’s error log gets us quite a bit of information and so let take the opportunity to take a quick cursory look.

Content

2017-10-29 21:35:33.28 Server Microsoft SQL Server 2005 - 9.00.5000.00 (X64)
Dec 10 2010 10:38:40
Copyright (c) 1988-2005 Microsoft Corporation
Windows Internal Database (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

2017-10-29 21:35:33.28 Server (c) 2005 Microsoft Corporation.
2017-10-29 21:35:33.28 Server Authentication mode is WINDOWS-ONLY.
2017-10-29 21:35:33.28 Server Logging SQL Server messages in file 'C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\LOG\ERRORLOG'.
2017-10-29 21:35:33.28 Server Registry startup parameters:
2017-10-29 21:35:33.28 Server -d C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\DATA\master.mdf
2017-10-29 21:35:33.28 Server -e C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\LOG\ERRORLOG
2017-10-29 21:35:33.28 Server -l C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\DATA\mastlog.ldf
2017-10-29 21:35:34.76 Server Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query ].
2017-10-29 21:35:34.78 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2017-10-29 21:35:34.76 spid5s Server name is 'SCCM\MICROSOFT##SSEE'. This is an informational message only. No user action is required.
2017-10-29 21:35:34.79 spid5s Starting up database 'msdb'.
2017-10-29 21:35:34.79 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2017-10-29 21:35:42.03 spid51 Starting up database 'SUSDB'.
2017-10-29 21:35:42.28 spid51 Recovery is writing a checkpoint in database 'SUSDB' (5). This is an informational message only. No user action is required.
Interpretation
  1. Microsoft SQL Server 2005 – 9.00.5000.00 (X64)
    • Microsoft SQL Server 2005
      • Version is 2005
    • 9.00.5000.00
      • Service Pack 4 ( SP4 )
    • X64
      • 64-bit
  2. Windows Internal Database (64-bit) on Windows NT 6.1 ( Build 7601: Service Pack 1)
    • Version is Windows Internal Database (64-bit)
    • Windows NT 6.1
      • OS  ( Windows Version – Wikipedia – Link )
        • Windows 7
        • Windows Server 2008 R2
        • Windows Home Server 2011
    • Build 7601 : Service Pack 1
      • SP1

SQL Server – SQL Server Configuration Manager

Launch sql server configuration manager to review “SQL Server Services

Image

SQL Server Configuration Manager

SQL Server Configuration Manager – Properties – Service
Image

Explanation

  1. Binary Path
    • C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\Binn\sqlservr.exe -sMICROSOFT##SSEE
      • Instance’s name is MICROSOFT##SSEE
  2. Name
    • Windows Internal Database (MICROSOFT##SSEE)

 

Installed Programs

Control Panel – Add or Remove Programs

Image

Explanation

  1. Applications Installed
    • Name :- Windows Server Update Services 3.0 SP2
    • Date :- 7/29/2015
    • Version :- 3.2.7600.226

 

SQL Server – SQLCMD

Let us quickly use sqlcmd to get metadata

Connect to SQL Server Instance using sqlcmd

Command

sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -E

Output

sqlcmd – Version using @@version

SQL

select [@@version] = @@version
go

Output

Explanation
  1. Version is MS SQL Server v2005 – 9.00.5000 ( x64 )
    • Version :- MS SQL Server v2005
    • Version # :- 9.00.5000
      • SP4
    • Bitness
      • x64

sqlcmd – list databases using sys.databases

SQL

select name, create_date from sys.databases
go

Output

Explanation
  1. Databases
    • System
      • master, tempdb, model, msdb
    • User / Application
      • susdb
        • susdb created on 2015-July-29th

Summary

Confirmed that GM’s patch report is indeed accurate.

We have a few Windows Internal databases on our network.

Will complete the steps of aligning each of them to specific Products, Vendors, and in-house support engineers.

 

References

  1. Microsoft
    • Microsoft Developer
      • VedMS
        • Following is the steps to connect to these Windows internal database on Windows 2012 machine. This to access the WAP configuration from the ADFS configuration database.
          Published On :- 2014-August-19th
          Link
  2. Edgewood Solutions LLC ( mssqltips.com )
    • Edwin Sarmiento
      • Administering your Windows Internal Database MICROSOFT##SSEE instance
        Link
  3. System Specialist .NET
    • Move or Delete a WSUS 4 Windows Internal Database (WID) on Windows Server 2012
      Link

Visual Studio Community 2017 – Upgrading

Background

The lead developer on one of our projects experienced problems deploying a report yesterday evening.

It is time to see if we can duplicate and thus research the error.

 

SQL Server Data Tools

SQL Server Data Tools – v2015

Downloaded SQL Server Data Tools (SSDT) v2015 from here.

Was able to successfully install and deploy the report.

 

SQL Server Data Tools – v2017

Download

From same link, downloaded SSDT v2017.

Install

Prerequisite

Visual Studio 2017

Blocking Issue
Textual

The current installation requires Visual Studio 2017 version number 15.3.0 or higher.  Please upgrade your Visual Studio instance before continuing the setup.

Image

 

Validate v2017

Launched our installed v2017 and validated the Version # is 15.2 (26430.15 )

Image

Textual

Microsoft Visual Studio Community 2017
Version 15.2 (26430.15) Release
VisualStudio.15.Release/15.2.0+26430.15
Microsoft .NET Framework
Version 4.7.02053

Review Visual Studio 2017 Packages

Let us quickly review Visual Studio ( VS) 2017 Release History

Image

Explanation
  1. v15.2
    • We have v15.2, released on May 10th, 2017
  2. v15.3
    • We need at least 15.3, released on August 14th, 2017
  3. v15.4.3
    • The latest as of today, 2017-Nov-9th, is v15.4.3
Download Visual Studio 2017 – Bootstrapper

We will to “Install Visual Studio 2017 on low bandwidth or unreliable network environments” ( here  )and reach for the Bootstrapper community edition.

The direct link to Community edition is here.

 

Create an offline installer

Let us create an offline installer


set "_target=C:\VS2017offline"
set "_desktop=Microsoft.VisualStudio.Workload.ManagedDesktop"
set "_netWeb=Microsoft.VisualStudio.Workload.NetWeb"
set "_github=Component.GitHub.VisualStudio"

vs_community.exe --layout %_target% --add %_desktop% --add %_netWeb%  --add %_github% --includeOptional --lang en-US

 

Build offline installer

Run the batch file created earlier to build the offline installer.

Image – Invoke batch file

Image – download…

Image – Successful
Patch Visual Studio 2017

Let us patch our installed v2017

Run the setup.exe availed through the offline installer.

Image – 1

Update available

Visual Studio 2017 is already installed.

Click update to update to version 15.4.3

 

Image – 2

Image – 3

 

Image – 4

 

Image – 5

 

Validate Visual Studio 2017 Version

Launch Visual and validated that v2017 is installed.

 

Install

Steps

Let us go back and try to install SQL Server Data Tools – Release 15.4.0 Preview

Install Tools to this Visual Studio 2017 instance

 

References

  1. Microsoft
    • Docs / SQL / SSDT ( SQL Server Data Tools )
      • Download SQL Server Data Tools ( SSDT )
        Link
    • Docs / Visual Studio / Documentation / Installation
      • Install Visual Studio 2017 on low bandwidth or unreliable network environments
        Link
      • Create an offline installation of Visual Studio 2017
        Link
    • VisualStudio.com
      • Visual Studio 2017 version 15.4 Release Notes
        Link

SQL Server – Database Scoped Configurations

Background

Earlier touched on reviewing database compatibility level via sys.databases’ compatibility_level column.

That post is here.

Database Scoped Configuration

In v2016, there is also a new component known as “Database Scoped Configuration” that can be investigated and tuned to review and compare database performance.

Areas

Areas currently exposed includes:

  1. Clearing procedure cache
    • Legacy
      • DBCC FREEPROCCACHE
        • Works against entire SQL Instance or specific plans
  2. MAXDOP parameter for specific databases ( OLTP )
    • Legacy
      • SQL Instance
        • sp_configure ‘max degree of parallelism’
      • Query Hint
        • OPTION ( MAXDOP 1)
  3. Set the query optimizer cardinality estimation model independent of the database compatibility level
    • Legacy :- v2008 thru 2012
    • Current :- v2014
  4. Enable or disable parameter sniffing at the database level
    • Legacy
      • Trace Flag 4136
        • SQL Server 2008 R2 CU2, SQL Server 2008 SP1 CU7 and SQL Server 2005 SP3 CU9 introduce trace flag 4136 to disable the “parameter sniffing” process
          Link
  5. Enable or disable query optimization hotfixes at the database level
    • Legacy
      • Trace Flag
        • SQL Server query optimizer hotfix trace flag 4199 servicing model
          Link
  6. Enable or disable the identity cache at the database level.
    • Legacy
      • Trace Flag
        • Trace Flag 272 disables batching of identity values

 

Read

To read Database Scoped configuration for the current database, please issue


use [dbname]
go

select 
        [database] = db_name()
      , tblSDSC.*

from   sys.database_scoped_configurations tblSDSC

Set LEGACY_CARDINALITY_ESTIMATION

Syntax

Syntax


use [database]
go

ALTER DATABASE
    SCOPED CONFIGURATION  
        SET LEGACY_CARDINALITY_ESTIMATION = OFF;  
go  


Sample


use [hrdb]
go

ALTER DATABASE
    SCOPED CONFIGURATION  
        SET LEGACY_CARDINALITY_ESTIMATION = OFF;  
go  


References

  1. Docs / SQL / T-SQL / Statements
    • ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
      Link
  2. Docs / SQL / Relational databases / System catalog views /
    • sys.database_scoped_configurations (Transact-SQL)
      Link

SQL Server – Database Compatibility Level

Background

Upgrading quite a bit of SQL Server Instances.

One of the areas to keep an eye on is the compatibility level of individual databases within each Instance.

Code

Read Compatibility Level

Credit

Crediting Nick Kavadias ( Link ) for his response on Stack Overflow:

How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
Link

SQL



/*
    How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
    https://stackoverflow.com/questions/1501596/how-to-check-sql-server-database-compatibility-after-sp-dbcmptlevel-is-deprecate

*/
select 

              [database] 
                = tblSD.[name]

            , [compatibilityLevel]
                = tblSD.[compatibility_level]

            , [version] = 
                    CASE [compatibility_level]
                        WHEN 65  THEN 'SQL Server 6.5'
                        WHEN 70  THEN 'SQL Server 7.0'
                        WHEN 80  THEN 'SQL Server 2000'
                        WHEN 90  THEN 'SQL Server 2005'
                        WHEN 100 THEN 'SQL Server 2008/R2'
                        WHEN 110 THEN 'SQL Server 2012'
                        WHEN 120 THEN 'SQL Server 2014'
                        WHEN 130 THEN 'SQL Server 2016'
                        WHEN 140 THEN 'SQL Server 2017'
                    END

from sys.databases tblSD

order by 
        tblSD.[name]

Set Compatibility Level

Legacy

sp_dbcmptlevel

Syntax


exec master..sp_dbcmptlevel 
         @dbname = @name 
       , @new_cmptlevel = @version 

Sample


exec master..sp_dbcmptlevel 
         @dbname = 'hrdb' 
       , @new_cmptlevel = 130

Modern

Alter Database / Set Compatibility Level

Syntax


alter database [db-name]
	set COMPATIBILITY_LEVEL = [compatibilityLevel]

Sample


alter database [hrdb]
	set COMPATIBILITY_LEVEL = 130

References

  1. Alter Database
    • ALTER DATABASE (Transact-SQL) Compatibility Level
      • Docs / SQL / T-SQL / Statements
        Link
    • sp_dbcmptlevel (Transact-SQL)
      • Docs / SQL / Relational databases / System stored procedures
        Link