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
Step2
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.
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 nameD:\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
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