‘PRIMARY’ filegroup is full. How do I increase temdb size in SQL Server Express 2017?

Question

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

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.

Answer 1

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

Answer 2

You do not need to increase the size of temp db.

in database ‘mydatabase’ because the ‘PRIMARY’ filegroup is full.

Is telling you it can't write to your primary filegroup is full (c:\path to database\mydatabase.mdf). Either there is no disk space where the MDF is stored, or the more likely cause is that your Primary Filegroup has no more available room to write into.

You can address this a few different ways

1. Enable autogrowth if not enabled
2. If autogrowth is enabled increase the maximum size by MB's, percentage or unlimited

In the GUI

In SSMS:

• right click your database
• select properties
• select files in the database properties window
• On the Autogrowth Max Size select your auto growth options.

SQL Alter statement

use master
go
-- Use one of the statements below

-- this statement will allow for unlimited file growth 
alter database [mydatabase] modify file ( name ='mydatabase', maxsize = UNLIMITED)

-- this statement will allow the database size to 100MB's.
alter database [mydatabase] modify file ( name ='mydatabase', maxsize = 102400KB)