April 21, 2014

Tempdb database continues to grow after upgrading vCenter

After upgrading a small customer from 5.1 (Simple Install) to 5.5 we discovered that the system disk was filling up. This system was installed using the MSSQL Express version that ships with vCenter that by default will put databases on the system disk. We soon figured out that tempdb was growing quickly and we were worried that we would soon run out of disk space. When the SQL Server service (+vcenter) is restarted tempdb is cleared, but it would start growing quickly again if the services were started.

In this process we saw the file C:\Program Files\Microsoft SQL Server\MSSQL10_50.VIM_SQLEXP\MSSQL\DATA\tempdb.mdf  growing to 40 Gigabytes before we had to restart services to avoid running out of disk space.

We first suspected that the read_committed_snapshot option was enabled, but it turned out it wasn't:

We also changed the tempdb file location so it wouldn't fill up the data disk, but we were still worried about it's growth rate.

In the end we set a growth limit on the database from SQL Server Configuration.

We defined a fixed growth limit of 2000MB.

After doing this the server was running happily ever after and the disks wouldn't fill up. Setting a growth limit on tempdb may affect database performance negatively, but we found it more important that we wouldn't run out of disk space.

I later also discussed this issues with a database admin and he had also seen tempdb temporarily growing quickly after upgrades to different systems, and he said it would normally stop growing at some point and it's size would eventually go back to normal again.

No comments:

Post a Comment