We noticed that on our SQL Server the Temp DB is not cleaning up and grows exponentionaly. Some inspection pointed us to the Confluence SQL session. We used the method descripted here (https://blogs.msdn.microsoft.com/sqlserverfaq/2010/10/13/troubleshooting-tempdb-growth-due-to-version-store-usage/) and can see that the version_store_kb is very large. When we query the sys.dm_tran_active_snapshot_database_transactions its returns the Confluence application that has a lot of sleeping statussen. We now have to reboot the sql server to clean up the space in the tempdb. Do you know haw we can fix this?
Hope to h
ear from you.
Gr Reitze Draaisma
Gezondheidsdienst voor dieren
The tempdb stored size is rather large. There are a few items that may depend on what is causing this. Your screenshots and insight help to let us know that there is a problem with the tempdb size itself.
A few SQL questions I would like to review before we move any further would be as follows:
These questions will help to understand if SQL is maintaining itself to reduce the size of the databases and logs it generates.
We look forward to hearing back so we can address this tempdb size.
we restarted the sql server and the tempdb's were back to normal (been really busy sinds then). Sinds April, the tempdb's started to grow again and the disc is full. Tempdb are 21 GB (have 4 tempdb's).
We have maintenance plans in place for the db's and system db's. We do not schrink the logfiles part of this plan.
We have Full recovery plan
The tempdb's are backupped as part of the system db maintenance plan.
If you’re doing regular backups jobs you should be able to free up space within the SQL databases to allow for shrinking the logs (If they have large growth). Also, Full recovery plan will only be appropriate for when you need a point in time recovery of a database and you have constant backups to allow for this time of recovery. If you’re doing daily backups then full recovery may be creating additional logging which is filling up your disk.
Further information on this may be found at Recovery Models (SQL Server).
Thanks for your anwer. The backup of the Confluence database is not a problem. The files are not big (1gb db en 1 mb Log). The problem is that the tempdb's of SQL server are growing, because of sleeping processes (multiple) from the Confluence application. It seems like something is holding thats process open (like a user session as a example) and the tempdb cant clean up because of that. The only way is to restart the sql service)
I have seen similar issues with Microsoft SQL having issues like this which may be resolved with an update. Could you please let us know the MS SQL version you're currently running along with the patch level?
Here is an article on how to get this information: How to determine the version, edition, and update level of SQL Server and its components
SQL 2008 R2 has SP3 available. The service pack of SQL you're currently running is end of life support from Microsoft as of October 8th, 2013. Even SQL 2008 R2 with SP3 is showing end of life was July 4th, 2014, the extended support ended July 9th, 2019. Further information on this at the following:
Confluence currently supports the following MS SQL databases: 2012, 2014, 2016, 2017 and Azure SQL. Here is the list of supported platforms: Confluence Supported Platforms.
At this point, I would suggest migrating to a newer instance of Microsoft SQL or switching to a PostgreSQL Database if a new MS SQL license is not within your budget.
I have the exact same problem as described, but we are running SQL Server 2016 SP2.
We have 3 confluence instances, and all 3 start a transaction daily that is never closed - so every day we get 3 sleeping processes. This causes the version store in tempdb to blow out as SQL Server is keeping versions of things while these transactions hang around. This has nothign to do with transaction log backups (which we have) or size of tempdb (128GB, this is a large SQL Server instance).
Is this a known bug in Confluence perhaps? It started happening after an upgrade was performend in Confluence about 6 months ago...
I can tell you the SQL command that was last run in these transactions:
SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'PAGE' AND CONTENT_STATUS = 'current' and PREVVER IS NULL AND PARENTID IS NULL AND SPACEID = @P0
Btw, for anyone else that has this issue, killing these SPIDs immediately starts the clean up in the versionstore in tempdb. However, this means manually going in there and doing this, or writing something to clean up old confluence SPIDs - neither of which is really palatable...
- Create your own custom emoji 🔥 - "Shake for Feedback" on mobile 📱 - An endless supply of GIFs via GIPHY 🤩 Is there anything quite as nice as a pleasant surprise? Comment below with what...
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event
You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events