Confluence Session to the SQL server has an open transaction and eats up disk space from Tempdb

[infra_gddiergezondheid]

HI Support,

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

[Stephen Sifers]

Hello,

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:

• Do you have nightly SQL maintenance plans in place to back up the DB and shrink logs?
• What is your recovery plan for your SQL DBs? Simple, Full, Bulk?
• https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-2017
• Are there any maintenance plans in place for the tempdb itself?

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.

Regards,
Stephen Sifers

[infra_gddiergezondheid]

Hi,

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.

Greetings Reitze

[Stephen Sifers]

Hello,

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).

Regards,
Stephen Sifers

[infra_gddiergezondheid]

Hi Stephen,

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)

Any idea?

Regards Reitze 

[Stephen Sifers]

Hello Reitze,

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

Regards,
Stephen Sifers

[infra_gddiergezondheid]

Hi Stephen,

We have Confluence installed on a SQL 2008 R2 Server (CU#2 for SQL Server 2008 R2 SP1) Patch level is 10.50.2772

Regards

Reitze

[Stephen Sifers]

Hello Reitze,

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:

• https://support.microsoft.com/en-us/lifecycle/search?alpha=SQL%20Server%202008%20R2%20Service%20Pack%203
• https://blogs.msdn.microsoft.com/sqlreleaseservices/end-of-mainstream-support-for-sql-server-2008-and-sql-server-2008-r2/

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.

Regards,
Stephen Sifers

[infra_gddiergezondheid]

Hi Stephan,

Thanks for the info and I will setup steps to migrate the database.

Thanks

Gr Reitze

[Nikki Amie-Fong]

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...

[Nikki Amie-Fong]

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...

[Nikki Amie-Fong]

BTW this appears to be started by some kind of scheduled process in Confluence. These transactions are started daily at 3AM precisely.