Error: 1101, Severity: 17, State: 2.


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)

Solution 1
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
ADD FILE
(
NAME = tempdev1,
FILENAME = ‘E:\tempdb1.mdf’,
SIZE = 5MB,
MAXSIZE = 5000MB,
FILEGROWTH = 10MB
) TO FILEGROUP [PRIMARY];
GO

Solution 2
If the disk space is available on the drive where tempdb residing you can change file MAX SIZE option to allocate more space.

Solution 3
Restart the SQL Services, it will create the new instance of tempdb

Solution 4
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

Advertisements

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s