After applying the performance optimization script from the "Optimize SQL Server performance" section of this article, users might experience issues with session database read/write operations after restarting the SQL server. SQL users configured to have access to the tempDB created by the script might no longer have such access, and Sitecore can throw session-related exceptions. This behavior is related to the fact that tempDB is recreated along with its access rights each time the SQL server is restarted.
CREATE PROCEDURE [dbo].[Sitecore_InitializeSessionState] AS BEGIN EXECUTE [Sitecore.Sessions].[dbo].[CreateTables]; EXEC('USE tempDb; CREATE USER [YOUR_USER] FOR LOGIN [YOUR_USER] WITH DEFAULT_SCHEMA=[dbo]; ALTER ROLE [db_datareader] ADD MEMBER [YOUR_USER]; ALTER ROLE [db_datawriter] ADD MEMBER [YOUR_USER]') END; GO
We would like to thank Rob Ahnemann from the Rock, Paper, Sitecore blog and his reader Shaun for the provided solution to this problem. The original article with this approach can be found at the following link: Sitecore Shared Session: Moving from the tempDB.