Home > SQL Server Internals, TempDB Issue (DAC) > Temp Database Issues Resolution (Move Tempdb)

Temp Database Issues Resolution (Move Tempdb)

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.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

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

%d bloggers like this: