Thursday, March 20, 2014

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...

1 comment:

  1. Nice Article !
    This is my pleasure to read your article.
    Really this will help to people of SQL Server Community.

    I have also prepared one article about, SQL Server Full tempdb: How to shrink it and move it?
    You can also visit my article, your comments and reviews are most welcome.
    http://www.dbrnd.com/2016/02/sql-server-the-tempdb-is-full-shrink-it-or-move-it/

    ReplyDelete