Although this is a very basic topic but I wanted to write about it since long for all the new DBAs and developers out there but for some reason or other I did not write about it and after sometime it was out of mind. But today in the office while I was working on a Production issue similar to this one I thought this is the perfect time to write a Blog about this topic–so here I am.
Actually what happened today, I got a mail from some developer that while executing a stored procedure they are getting the following error message:
“Could not allocate a new page for database ‘TEMPDB’ because of insufficient disk space in filegroup ‘DEFAULT’. 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.”
Ok..this error message clearly states that we have some space issue in the TempDB database. So I checked the file properties of the TempDB database and found the following thing: ( I did not create the secondary files 😀 )
So we had all the files create and the auto-growth property was also set then the only reason we should receive the error was because of low disk space. I immediately RDP the server and found that sufficient amount of disk space was available. That’s strange !! Then I checked the size of the TempDB database- it was showing the size as 3865 MB–Bang!! So that was the problem. I checked the SQL Server error log files to confirm my doubt and there it was..
So there was some problem with the physical path..I checked the server and found that some of the folders were deleted from the server. So that was the problem.
Next solution was to add all the secondary files to the TempDB database. One point to note is that we do not have to restart the SQL Server after adding files to the TempDB database. The basic syntax to add secondary files to TempDB:
ALTER DATABASE [tempdb] ADD FILE ( NAME = N‘tempdev2′,
FILENAME = N‘D:\Data\tempdev2.ndf’ , SIZE = 1oooooMB , FILEGROWTH = 0)