How can we shrink the IdentifySessionstate database?
The current Identify UI doesn’t support this feature. However, you can do it manually:
- 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-SQL123456Use IdentifysessionstateGOTRUNCATE TABLE [dbo].[ASPStateTempSessions]GOTRUNCATE TABLE [dbo].[ASPStateTempApplications]GO
- 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-SQL1234567891011USE IdentifySessionStateGOALTER DATABASE IdentifySessionState SET RECOVERY simpleGODBCC 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 needDBCC SHRINKDATABASE (IdentifySessionState, 90);GOALTER DATABASE IdentifySessionState SET RECOVERY FULLGO
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:
dbcc opentran (IdetifySessionState)
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.