SQL Server Tempdb Full Error

SQL Server "Tempdb" Full Error

Sometimes you have noticed following errors, while you are working in SQL Server database platform,

Well… initially I want to mention, simply you can change “tempdb” file location or shrink “tempdb” files (if available space). But best practice is, you need to monitor your database and identify the low disk space issues before this happens. And initial SQL server installation you need to locate your “tempdb” files in separate disk (apart from default “C” drive)

Following example will explain you how to move “tempdb” files to different location,

USE tempdb
GO
SELECT * FROM sys.database_files;
GO
EXEC sp_helpfile
GO

USE master
GO
DECLARE @ssql                    nvarchar(max);
DECLARE @tempdbnewfilelocation    nvarchar(200);

SET @ssql='';
SET @tempdbnewfilelocation='E:\MSSQL\2005\tempdb';

SELECT @ssql=@ssql+'ALTER DATABASE tempdb MODIFY FILE(NAME = '+name+', FILENAME = '''+@tempdbnewfilelocation+''+REVERSE(LEFT(REVERSE (physical_name),CHARINDEX('\',REVERSE (physical_name))))+''');'+char(13)+char(10) FROM tempdb.sys.database_files;
PRINT @ssql;

EXEC (@ssql);
      

If you need to place "tempdb" files in different location, then you need to change above script according to the drive name.  

Cheers...
and Most welcome your comments and ideas...