Tuesday, March 1, 2011

When Tempdb is full and occupied disk space?

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.

No comments:

Post a Comment