Question:

How can we shrink the IdentifySessionstate database?

Answer:

The current Identify UI doesn't support this feature. However, you can do it manually:

1. Truncate IdentifySessionState's tables to clear all data. Please note that doing this will invalidate all active user sessions, so we recommend doing it when your site is idle:
   
   Transact-SQL

   Use Identifysessionstate GO TRUNCATE TABLE [dbo].[ASPStateTempSessions] GO TRUNCATE TABLE [dbo].[ASPStateTempApplications] GO

   1 2 3 4 5 6

   Use Identifysessionstate GO TRUNCATE TABLE [dbo].[ASPStateTempSessions] GO TRUNCATE TABLE [dbo].[ASPStateTempApplications] GO

2. Shrink the transaction log of the IdentifySessionState database: When the Identify instance is in use, the transaction log keeps increasing overtime. You need to have the solution for the transaction log management (refer: https://www.sqlskills.com/blogs/paul/importance-of-proper-transaction-log-size-management/ ). If you just want to clear it, you can consider executing the script below:
   
   Transact-SQL

   USE IdentifySessionState GO ALTER DATABASE IdentifySessionState SET RECOVERY simple GO DBCC SHRINKFILE (IdentifySessionState_log , 1) GO -- I set it to shrink the database with 90% but you can set it with another approx. value if you need DBCC SHRINKDATABASE (IdentifySessionState, 90); GO ALTER DATABASE IdentifySessionState SET RECOVERY FULL GO

   1 2 3 4 5 6 7 8 9 10 11

   USE IdentifySessionState GO ALTER DATABASE IdentifySessionState SET RECOVERY simple GO DBCC SHRINKFILE (IdentifySessionState_log , 1) GO -- I set it to shrink the database with 90% but you can set it with another approx. value if you need DBCC SHRINKDATABASE (IdentifySessionState, 90); GO ALTER DATABASE IdentifySessionState SET RECOVERY FULL GO

Troubleshoot:

Q: when I run the shrinking script to clean the log, I get the error: Cannot shrink log file because all logical log files are in use. As result, the log can't be shrinked. 

A: You can run: DBCC OPENTRAN to identify SPID of the process that is accessing log files, kill it and try again. 

here is the sample:  

Q: What should I do after I clean up the db?  

A: You need to access the web servers and have the IIS restart on these servers.