Saturday, February 25, 2012

Could not create tempdb - SQL Server 2005 with SP1

I am receiving the following message within the Application Event Log on one
of my servers.
Could not create tempdb. You may not have enough disk space available. Free
additional disk space by deleting other files on the tempdb drive and then
restart SQL Server. Check for additional errors in the event log that may
indicate why the tempdb files could not be initialized.
I am also receiving this error:
The database 'model' is marked RESTORING and is in a state that does not
allow recovery to be run.
The strange thing about this message is that the server was working
correctly yesterday. The only thing that changed: The server was shut down
and moved to a new data center. Connectivity is in place because I can
connect to the box via Remote Desktop from work and from home.
This error is ONLYhappening on the default instance. There are two other
instances on the server (all three SQL Server installs are running under the
same network account).
There is enough free space on the volumes.
OS: 24GB free
internal array: 650 GB free
external array (MSA 500 G2): 900GB free
I even tried starting that instance in single user mode from the command
prompt via:
sqlservr -smssqlserver -c -f -m -T3608
This worked and I tried moving tempdb to the different volumes that are
available on the box. When I try to start SQL Server I receive the same
errors about model and tempdb and this instance fails to start.
I appreciate any assistance you can provide!
Keith Kratochvil
Keith Kratochvil wrote:
> I am receiving the following message within the Application Event Log on one
> of my servers.
> Could not create tempdb. You may not have enough disk space available. Free
> additional disk space by deleting other files on the tempdb drive and then
> restart SQL Server. Check for additional errors in the event log that may
> indicate why the tempdb files could not be initialized.
> I am also receiving this error:
> The database 'model' is marked RESTORING and is in a state that does not
> allow recovery to be run.
> The strange thing about this message is that the server was working
> correctly yesterday. The only thing that changed: The server was shut down
> and moved to a new data center. Connectivity is in place because I can
> connect to the box via Remote Desktop from work and from home.
>
> This error is ONLYhappening on the default instance. There are two other
> instances on the server (all three SQL Server installs are running under the
> same network account).
>
> There is enough free space on the volumes.
> OS: 24GB free
> internal array: 650 GB free
> external array (MSA 500 G2): 900GB free
> I even tried starting that instance in single user mode from the command
> prompt via:
> sqlservr -smssqlserver -c -f -m -T3608
> This worked and I tried moving tempdb to the different volumes that are
> available on the box. When I try to start SQL Server I receive the same
> errors about model and tempdb and this instance fails to start.
>
> I appreciate any assistance you can provide!
>
Hi
Check your logs(ldf) file size
regards
SW
|||In addition to the errors that I mentioned (recorded within the Application
log), this error is within the System log:
The SQL Server (MSSQLSERVER) service terminated with service-specific error
1814 (0x716).
The error is written right after the entry that states:
The SQL Server (MSSQLSERVER) service entered the stopped state.
I tried starting SQL Server normally after telling it where to create tempdb
(via ALTER DATABASE ... MODIFY FILE...).
It would not start -- the same errors on model and tempdb are written to the
log.
Then I started SQL Server via the command line again
(sqlservr -smssqlserver -c -f -m -T3608).
It started (as before).
SQL crashes if I execute these commands:
sp_helpdb tempdb
select * From tempdb..sysfiles
I can execute these commands, however:
select * From master..sysdatabases where name = 'tempdb'
select * From master..sysaltfiles where name like 'temp%'
Here are the results:
name dbid sid mode status status2 crdate reserved category cmptlevel
filename version
tempdb 2 0x01 0 8 1090520064 2006-10-26 09:26:35.027 1900-01-01 00:00:00.000
0 90 E:\mssql\data\tempdb.mdf NULL
fileid groupid size maxsize growth status perf dbid name filename
1 1 1024 -1 10 1048578 0 2 tempdev E:\mssql\data\tempdb.mdf
2 0 64 -1 10 1048642 0 2 templog E:\mssql\data\templog.ldf
However tempdb's data and log files are are not on the disk. I searched for
them using Agent Ransack (as well as Windows Explorer).
Keith Kratochvil
"Sebastian" <sebastian@.waksmundzcy.com.pl> wrote in message
news:%23Bnb$FJGHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Keith Kratochvil wrote:
> Hi
> Check your logs(ldf) file size
> regards
> SW
|||Does that directory structure exist on the E Drive? Does the E: Drive
even exist?
What other databases were supposed to be on your E Drive?
Keith Kratochvil wrote:[vbcol=seagreen]
> In addition to the errors that I mentioned (recorded within the Application
> log), this error is within the System log:
> The SQL Server (MSSQLSERVER) service terminated with service-specific error
> 1814 (0x716).
> The error is written right after the entry that states:
> The SQL Server (MSSQLSERVER) service entered the stopped state.
>
> I tried starting SQL Server normally after telling it where to create tempdb
> (via ALTER DATABASE ... MODIFY FILE...).
> It would not start -- the same errors on model and tempdb are written to the
> log.
> Then I started SQL Server via the command line again
> (sqlservr -smssqlserver -c -f -m -T3608).
> It started (as before).
> SQL crashes if I execute these commands:
> sp_helpdb tempdb
> select * From tempdb..sysfiles
>
> I can execute these commands, however:
> select * From master..sysdatabases where name = 'tempdb'
> select * From master..sysaltfiles where name like 'temp%'
> Here are the results:
> name dbid sid mode status status2 crdate reserved category cmptlevel
> filename version
> tempdb 2 0x01 0 8 1090520064 2006-10-26 09:26:35.027 1900-01-01 00:00:00.000
> 0 90 E:\mssql\data\tempdb.mdf NULL
>
> fileid groupid size maxsize growth status perf dbid name filename
> 1 1 1024 -1 10 1048578 0 2 tempdev E:\mssql\data\tempdb.mdf
> 2 0 64 -1 10 1048642 0 2 templog E:\mssql\data\templog.ldf
>
> However tempdb's data and log files are are not on the disk. I searched for
> them using Agent Ransack (as well as Windows Explorer).
> --
> Keith Kratochvil
>
> "Sebastian" <sebastian@.waksmundzcy.com.pl> wrote in message
> news:%23Bnb$FJGHHA.2456@.TK2MSFTNGP06.phx.gbl...

No comments:

Post a Comment