Archive

Archive for the ‘TempDB Issue (DAC)’ Category

Temp Database Issues Resolution (Move Tempdb)

August 25, 2010 Leave a comment

Issue

SQL Server Services is not starting because temp database can’t grow further as it reached to its maximum size (All Disk Space is utilized). We can’t delete the temp database file from the disk because of disk issues.

What can be done to resolve the issue?

Resolution

To resolve the issue DAC should be enabled.

What basically we will do is moving the temp database on to some other drive.

Steps to do
Step1

Start SQL Server Service with minimal configuration

 

Start the command prompt with Administrative Priviliges

D:\Program Files\Microsoft SQL Server\MSSQL10.DELL\MSSQL\Binn>sqlservr.exe -sInstancename -f

In case instancename is default  then u don’t provide the instance name

D:\Program Files\Microsoft SQL Server\MSSQL10.DELL\MSSQL\Binn>sqlservr.exe -f

Start another instance of command prompt with administrative priviliges.

Step2

sqlcmd -A -dmaster -E -SNL03-DF271\instance name

In case instancename is default  then u don’t provide the instance name

 sqlcmd -A -dmaster -E -SNL03-DF271\instance name

Move the temp database file to some other location

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘E:\SQLData\tempdb.mdf’);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘F:\SQLLog\templog.ldf’);
GO

Once file get moved;Restart the SQL Server Services

Press Ctrl+C on the command Prompt in the first step

Restart the SQL Server Service.