Sql-server – ‘PRIMARY’ filegroup is full. How to increase temdb size in SQL Server Express 2017

sql serversql-server-2017sql-server-express

When I reindex a particular table 

DBCC DBREINDEX(@TableName, '',90)

I get an error

Level 17, State 2, Procedure aareindex, Line 21 [Batch Start Line 0]
Could not allocate space for object ‘dbo.SORT temporary run storage:
422738479742976’ in database ‘mydatabase’ because the ‘PRIMARY’
filegroup is full. Create disk space by deleting unneeded files,
dropping objects in the filegroup, adding additional files to the
filegroup, or setting autogrowth on for existing files in the
filegroup.

So I want to try increasing the size of TempDb

I can see the size using SSMS by looking at the database properties but I don't see how to set it.

[Update]

select type_desc, name, size, max_size, growth from tempdb.sys.database_files

returns

these results

The file size is already 10236 Mb and the Express limit is 10 Gig
However the Disk Usage report shows that 26% of the diskspace is unallocated, 40% is data and 32% is index.

Best Answer

The file size is already 10236 Mb and the Express limit is 10 Gig

If your user database data file size reached it's limit (10Gb in Express Edition) but 26% of the diskspace is unallocated, you can try to rebuild your indexes with sort in tempdb.

DBCC DBREINDEX is deprecated and there is no reason to use in SQL Server 2017, and with alter index..rebuild you can indicate that you want to perform sort in tempdb:

alter index your_index on your_table rebuild
with (sort_in_tempdb = on, fillfactor = 90);

When you rebuild an index, you first need the space to build the copy of your index (and this space will be allocated in your db) and to build it you also need space to do sort, this space can be allocated in tempd. In your case the error clearly said that there was no space to do sort in your db, so maybe you'll resolve your problem if the sort will be performed in tempdb.

You can read more on that here: SORT_IN_TEMPDB Option For Indexes

Related Question