2

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.

migrated from serverfault.com Oct 6 at 20:23

This question came from our site for system and network administrators.

  • What does this select type_desc, name, size, max_size, growth from tempdb.sys.database_files output? – David Browne - Microsoft Oct 6 at 21:18
  • Thans @DavidBrowne-Microsoft I updated the question. – kirsten g Oct 7 at 6:39
  • >>>The file size is already 10236 Mb<<< The tempdb data size in your picture is 71Mb only, it's reported in pages and not in Mb, 10Gb is what you see under max_size (1310720 pages) – sepupic Oct 7 at 9:55
3

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

  • 1
    Also rebuilding the indexes one-by-one, and disabling non-clustered indexes if rebuilding a heap can reduce the space required. – David Browne - Microsoft Oct 7 at 15:17
  • I don't get the error if I rebuild one index at a time whether I use SORT_IN_TEMPDB or not. – kirsten g Oct 7 at 16:57
4

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.

enter image description here enter image description here

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)
  • I updated the question to show that the file is already near maximum size but that it has unallocated space. – kirsten g Oct 7 at 6:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.