Sql-server – Is it normal for tempdb to not give up disk space when it releases things

disk-spacesql-server-2012tempdb

So my tempdb is getting huge. Like 200+ GB.

USE tempdb  GO  EXEC sp_spaceused

database_name   database_size   unallocated space 
tempdb          211668.88 MB    206803.45 MB

It's using a SIMPLE recovery model, which according to Microsoft:

Set the recovery model of tempdb to SIMPLE. This model automatically
reclaims log space to keep space requirements small.

But does 'reclaim log space' mean that it still maintains the disk allocation?

Kind of like if I had an influx of patrons at my business so I borrowed a lot across the street to put the extra cars, but then once I no longer need all that space I still reserved that extra parking lot for later (didn't allow anyone else to use it) despite it remaining empty?

If this is normal behavior then I might expect that it's hording space that it thinks it might use again one day, and thus if it had 'infinite' space to expand it would eventually stop growing based on the determined usage patterns?

Or, is it suppose to be giving back disk space to the OS when it releases?

UPDATE

Also found this: http://technet.microsoft.com/en-us/library/ms176037(v=sql.105).aspx

By default, the tempdb database automatically grows as space is
required, because the MAXSIZE of the files is set to UNLIMITED.
Therefore, tempdb can continue growing until space on the disk that
contains tempdb is exhausted. You can prevent tempdb from growing
without limits by setting a MAXSIZE for tempdb, however this is not
recommended. Limiting the size of tempdb may cause the database to run
out of disk space. This can cause significant disruptions in your
production environment and can prohibit applications that are running
from completing operations.

So maybe I've answered my own question here but hopefully someone can clarify: Does it then follow that I should have it on a drive/partition by itself? Then it can grow 'as big as it can' while at the same time not taking up disk space that I want to use for other things? But in that case, I wonder what the difference is between MAXSIZE and actually not having any more space to physically grow.

Best Answer

Yes, SQL Server files do not shrink automatically. They remain the same size unless you explicitly shrink them, either through the SQL Server Management Studio or by using the DBCC SHRINKFILE command. While you can set files to autoshrink, this is widely advised against because shrinking/growing files is very resource intensive, especially when you're dealing with log files which don't have instant file initialization.

EDIT: In response to your edit: there is a difference between MAXSIZE and the size a file can get before running out of disk. You can set that in the Files section of the database properties, or with an ALTER DATABASE command.

Related Solutions

Sql-server – What are the most effective techniques to reduce SQL Server disk space use when updating all rows in a very large table

I've just gone through a similar process just couple of weeks ago. After several tries, with couple of the bigger tables (one of them more than 100million rows, near 80Gb) I came up with these steps to speed up things and keep transaction log small:

ALTER DATABASE [my_db] SET RECOVERY SIMPLE WITH NO_WAIT (check here for more info)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED (check here for more info)
drop nonclustered indexes: this step will avoid the hassle with the need of updating them on each operation (check Brent Ozar blog for more details)
use batches: as suggested by mrdenny, do whatever you need to do, update, delete, inserts, in smaller chunks.

Here is a sample of batch processing for an update, 1000 rows at a time:

 UPDATE TOP(1000) your_table
 SET    col1 = new_value
 WHERE  <your_condition>
 WHILE  @@rowcount > 0
 BEGIN
     UPDATE TOP(1000) your_table
     SET    col1 = new_value
     WHERE  <your_condition>;
 END;
 GO

restore nonclustered indexes
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
ALTER DATABASE [my_db] SET RECOVERY FULL;

Sql-server – TempDB Log Space and ACTIVE_TRANSACTION

As Max mentioned the alert probably fired just prior to the log growing/needing to grow. SCOM collects transaction log free space % although I am not sure at what threshold the alert will fire.

here is a quick example to show you what state tempdb is probably in when you get these alerts but no log file growth.

first create a database, set recovery to full, and back it up

    create database tlogspace
    on(name=tlogspace_dat,
        filename='c:\temp\tlogspace.mdf',
        size=4MB)
    log on (name=tlogspace_log,
        filename='c:\temp\tlogspace.ldf',
        size=1MB);
    go

    alter database tlogspace set recovery full;
    go
    backup database tlogspace to disk='nul';
    go

now switch to that database, create a table, and run DBCC sqlperf(logspace) to check the size oand free space in your log file.

use tlogspace
go

create table data(id int identity(1,1), col varchar(8000))
dbcc sqlperf(logspace)

On my system I have logfile size of 0.9921875 and log space used (%) of 48.4245. Now insert some data into the table and run DBCC sqlperf(logspace) again. On my system 45 rows inserted gave desired results(number of rows inserted may need to be adjusted).

insert into data(col)
select replicate('a',8000)
go 45 --may need to adjust number
dbcc sqlperf(logspace)

This time, the DBCC sqlperf output should show that log size is the same but log space used is just under 100%. In this case, SCOM would probably fire an alert that log space is low. There is no more activity going on to cause the log file to grow and (in this example) no tlog backup to free up used space. tempdb is in simple recovery, so your active transaction probably used up most of the available space and did not release it but there was not enough activity in tempdb to trigger log file growth thus causing the alert fire.

cleanup database when finished

use master
drop database tlogspace

Best Answer

Related Solutions

Sql-server – What are the most effective techniques to reduce SQL Server disk space use when updating all rows in a very large table

Sql-server – TempDB Log Space and ACTIVE_TRANSACTION

Related Question