Error: 1101, Severity: 17, State: 2.
Could not allocate a new page for database ‘tempdb’ because of insufficient disk space in filegroup ‘PRIMARY’. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
We have configured the tempdb on J:\ which has only 10GB space. TempDB has occupied the 10GB space. We have tried to shrink the tempdb but no luck, after sometime we are not able to even right click the TempDB and neither execute the some system stored procedures (sp_who2)
If you don’t want to restart the SQL Server you can add the another data file to different drive in tempdb
ALTER DATABASE tempdb
NAME = tempdev1,
FILENAME = ‘E:\tempdb1.mdf’,
SIZE = 5MB,
MAXSIZE = 5000MB,
FILEGROWTH = 10MB
) TO FILEGROUP [PRIMARY];
If the disk space is available on the drive where tempdb residing you can change file MAX SIZE option to allocate more space.
Restart the SQL Services, it will create the new instance of tempdb
For user databases with restricted growth you can use the AUTO GROWTH option true or increase the file max size
Note: You can’t move the tempdb files, if you execute the ALTER command to move the tempdb files, it will just mark the file to diffrent drive and will create the file the target drive on SQL Server restart only