The REAL problem

[Buddy Ackerman]

I was running a server with TempDB in RAM (please don't comment on why that's not a good idea). Well, everything was fine until one of our administrator install teh backup exec agent and restarted the server. Since then the server won't start giving an error the Tempdb coulld not be moved into ram. Well, I started the server using the -f parameter and reset the Tempdb in RAM setting to zero. Howeever, it still won't start and gives the same error about not being able to move Tempdb into RAM. Is my only option to rebuild the master database? This is just a backup server but I'd like to avoid the hassle of rebuilding master and reinitializing the databases.


--Buddy

[Craig]

Buddy,

Have you tried this procedure?:
PSS ID Number: Q166350
Article last modified on 04-09-1999

WINNT:6.0,6.5

winnt


================================================== ====================
-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft SQL Server versions 6.0, 6.5
-------------------------------------------------------------------------------

SYMPTOMS
========

If the SQL Server tempdb in RAM value is configured to a value higher than the
available RAM remaining on the computer, SQL Server fails on the next startup
attempt. Because the configuration value does not take affect until the server
is stopped and restarted, the server functions as normal until that time. Upon
attempting to restart the server, the following message appears in the SQL
Server error log:

kernel udactivate(IN_RAM): Operating system error 8(Not enough storage
is available to process this command.) encountered
spid1 Device activation error. The physical filename 'IN_RAM' may be
incorrect
spid1 crdb_tempdb: Unable to move tempdb into RAM; RAM device doesn't
exist, cannot be created, or doesn't have enough space for tempdb

CAUSE
=====

On startup, if tempdb is configured to exist in RAM, SQL Server must create a
temp_db device within the available system RAM in which to store tempdb. If the
size of tempdb exceeds the available amount of system RAM, the device cannot be
created. Without the device, SQL Server is unable to create tempdb in RAM. It is
important to note that the tempdb in RAM value uses RAM in addition to memory
currently allocated to SQL Server, and is specified in MB, not 2-KB pages.

WORKAROUND
==========

To resolve this problem, do the following:

1. Open a command prompt session and start SQL Server in minimal configuration
mode with the following command:

sqlservr -c -f

You will get a screen dump of the error log. When the startup is complete, you
will have a flashing cursor after the following line:

97/02/27 15:34:46.14 kernel Warning: override, autoexec procedures skipped.

2. After the server is started, open a second command prompt session and connect
to the server as the system administrator (SA) using ISQL:

isql -Usa -Ppassword (if you are not local to the computer, add -Sservername)

3. Reconfigure the tempdb in RAM parameter to an acceptable value. It is
important to note that the tempdb in RAM value uses RAM in addition to memory
currently allocated to SQL Server, and is specified in MB, not in 2-KB pages.
To adjust the current value, use the following commands (where X is the value
of tempdb in RAM, in MB):

sp_configure 'tempdb in ram', X
go

Configuration option changed. Run the RECONFIGURE command to install.

reconfigure
go

4. Shut down SQL Server:

shutdown
go

Server SHUTDOWN by request.

5. Start SQL Server as normal.




------------
Buddy Ackerman at 10/3/00 2:54:08 PM

I was running a server with TempDB in RAM (please don't comment on why that's not a good idea). Well, everything was fine until one of our administrator install teh backup exec agent and restarted the server. Since then the server won't start giving an error the Tempdb coulld not be moved into ram. Well, I started the server using the -f parameter and reset the Tempdb in RAM setting to zero. Howeever, it still won't start and gives the same error about not being able to move Tempdb into RAM. Is my only option to rebuild the master database? This is just a backup server but I'd like to avoid the hassle of rebuilding master and reinitializing the databases.


--Buddy

[Buddy Ackerman]

I tried to set the TempDB in RAM to 10 MB but that didn't work either. I have 2.3GB of RAM in this server and the server had been running fine with a 200 MB TempDB in RAM and 1.8GB of RAM dedicated to SQL Server. The thing that bothers me is that even when I set TempDB in RAM to zero it still says that it can't move TempDB to RAM because there is not enough system memory.


--Buddy


------------
Craig at 10/3/00 3:40:27 PM

Buddy,

Have you tried this procedure?:
PSS ID Number: Q166350
Article last modified on 04-09-1999

WINNT:6.0,6.5

winnt


================================================== ====================
-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft SQL Server versions 6.0, 6.5
-------------------------------------------------------------------------------

SYMPTOMS
========

If the SQL Server tempdb in RAM value is configured to a value higher than the
available RAM remaining on the computer, SQL Server fails on the next startup
attempt. Because the configuration value does not take affect until the server
is stopped and restarted, the server functions as normal until that time. Upon
attempting to restart the server, the following message appears in the SQL
Server error log:

kernel udactivate(IN_RAM): Operating system error 8(Not enough storage
is available to process this command.) encountered
spid1 Device activation error. The physical filename 'IN_RAM' may be
incorrect
spid1 crdb_tempdb: Unable to move tempdb into RAM; RAM device doesn't
exist, cannot be created, or doesn't have enough space for tempdb

CAUSE
=====

On startup, if tempdb is configured to exist in RAM, SQL Server must create a
temp_db device within the available system RAM in which to store tempdb. If the
size of tempdb exceeds the available amount of system RAM, the device cannot be
created. Without the device, SQL Server is unable to create tempdb in RAM. It is
important to note that the tempdb in RAM value uses RAM in addition to memory
currently allocated to SQL Server, and is specified in MB, not 2-KB pages.

WORKAROUND
==========

To resolve this problem, do the following:

1. Open a command prompt session and start SQL Server in minimal configuration
mode with the following command:

sqlservr -c -f

You will get a screen dump of the error log. When the startup is complete, you
will have a flashing cursor after the following line:

97/02/27 15:34:46.14 kernel Warning: override, autoexec procedures skipped.

2. After the server is started, open a second command prompt session and connect
to the server as the system administrator (SA) using ISQL:

isql -Usa -Ppassword (if you are not local to the computer, add -Sservername)

3. Reconfigure the tempdb in RAM parameter to an acceptable value. It is
important to note that the tempdb in RAM value uses RAM in addition to memory
currently allocated to SQL Server, and is specified in MB, not in 2-KB pages.
To adjust the current value, use the following commands (where X is the value
of tempdb in RAM, in MB):

sp_configure 'tempdb in ram', X
go

Configuration option changed. Run the RECONFIGURE command to install.

reconfigure
go

4. Shut down SQL Server:

shutdown
go

Server SHUTDOWN by request.

5. Start SQL Server as normal.




------------
Buddy Ackerman at 10/3/00 2:54:08 PM

I was running a server with TempDB in RAM (please don't comment on why that's not a good idea). Well, everything was fine until one of our administrator install teh backup exec agent and restarted the server. Since then the server won't start giving an error the Tempdb coulld not be moved into ram. Well, I started the server using the -f parameter and reset the Tempdb in RAM setting to zero. Howeever, it still won't start and gives the same error about not being able to move Tempdb into RAM. Is my only option to rebuild the master database? This is just a backup server but I'd like to avoid the hassle of rebuilding master and reinitializing the databases.


--Buddy

[Craig]

Did you try this procedure? If not, try it by the numbers and get tempdb out of ram.

As a last resort, have your friend uninstall the last piece of software that caused you to lose some RAM.

Craig


------------
Buddy Ackerman at 10/3/00 4:57:45 PM

I tried to set the TempDB in RAM to 10 MB but that didn't work either. I have 2.3GB of RAM in this server and the server had been running fine with a 200 MB TempDB in RAM and 1.8GB of RAM dedicated to SQL Server. The thing that bothers me is that even when I set TempDB in RAM to zero it still says that it can't move TempDB to RAM because there is not enough system memory.


--Buddy


------------
Craig at 10/3/00 3:40:27 PM

Buddy,

Have you tried this procedure?:
PSS ID Number: Q166350
Article last modified on 04-09-1999

WINNT:6.0,6.5

winnt


================================================== ====================
-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft SQL Server versions 6.0, 6.5
-------------------------------------------------------------------------------

SYMPTOMS
========

If the SQL Server tempdb in RAM value is configured to a value higher than the
available RAM remaining on the computer, SQL Server fails on the next startup
attempt. Because the configuration value does not take affect until the server
is stopped and restarted, the server functions as normal until that time. Upon
attempting to restart the server, the following message appears in the SQL
Server error log:

kernel udactivate(IN_RAM): Operating system error 8(Not enough storage
is available to process this command.) encountered
spid1 Device activation error. The physical filename 'IN_RAM' may be
incorrect
spid1 crdb_tempdb: Unable to move tempdb into RAM; RAM device doesn't
exist, cannot be created, or doesn't have enough space for tempdb

CAUSE
=====

On startup, if tempdb is configured to exist in RAM, SQL Server must create a
temp_db device within the available system RAM in which to store tempdb. If the
size of tempdb exceeds the available amount of system RAM, the device cannot be
created. Without the device, SQL Server is unable to create tempdb in RAM. It is
important to note that the tempdb in RAM value uses RAM in addition to memory
currently allocated to SQL Server, and is specified in MB, not 2-KB pages.

WORKAROUND
==========

To resolve this problem, do the following:

1. Open a command prompt session and start SQL Server in minimal configuration
mode with the following command:

sqlservr -c -f

You will get a screen dump of the error log. When the startup is complete, you
will have a flashing cursor after the following line:

97/02/27 15:34:46.14 kernel Warning: override, autoexec procedures skipped.

2. After the server is started, open a second command prompt session and connect
to the server as the system administrator (SA) using ISQL:

isql -Usa -Ppassword (if you are not local to the computer, add -Sservername)

3. Reconfigure the tempdb in RAM parameter to an acceptable value. It is
important to note that the tempdb in RAM value uses RAM in addition to memory
currently allocated to SQL Server, and is specified in MB, not in 2-KB pages.
To adjust the current value, use the following commands (where X is the value
of tempdb in RAM, in MB):

sp_configure 'tempdb in ram', X
go

Configuration option changed. Run the RECONFIGURE command to install.

reconfigure
go

4. Shut down SQL Server:

shutdown
go

Server SHUTDOWN by request.

5. Start SQL Server as normal.




------------
Buddy Ackerman at 10/3/00 2:54:08 PM

I was running a server with TempDB in RAM (please don't comment on why that's not a good idea). Well, everything was fine until one of our administrator install teh backup exec agent and restarted the server. Since then the server won't start giving an error the Tempdb coulld not be moved into ram. Well, I started the server using the -f parameter and reset the Tempdb in RAM setting to zero. Howeever, it still won't start and gives the same error about not being able to move Tempdb into RAM. Is my only option to rebuild the master database? This is just a backup server but I'd like to avoid the hassle of rebuilding master and reinitializing the databases.


--Buddy

[Buddy Ackerman]

I did it by the numbers and it did work. Interesting that starting it from the command line makes it behave differently that starting it as a service. Thanks.


--Buddy


------------
Craig at 10/4/00 7:40:50 AM

Did you try this procedure? If not, try it by the numbers and get tempdb out of ram.

As a last resort, have your friend uninstall the last piece of software that caused you to lose some RAM.

Craig


------------
Buddy Ackerman at 10/3/00 4:57:45 PM

I tried to set the TempDB in RAM to 10 MB but that didn't work either. I have 2.3GB of RAM in this server and the server had been running fine with a 200 MB TempDB in RAM and 1.8GB of RAM dedicated to SQL Server. The thing that bothers me is that even when I set TempDB in RAM to zero it still says that it can't move TempDB to RAM because there is not enough system memory.


--Buddy


------------
Craig at 10/3/00 3:40:27 PM

Buddy,

Have you tried this procedure?:
PSS ID Number: Q166350
Article last modified on 04-09-1999

WINNT:6.0,6.5

winnt


================================================== ====================
-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft SQL Server versions 6.0, 6.5
-------------------------------------------------------------------------------

SYMPTOMS
========

If the SQL Server tempdb in RAM value is configured to a value higher than the
available RAM remaining on the computer, SQL Server fails on the next startup
attempt. Because the configuration value does not take affect until the server
is stopped and restarted, the server functions as normal until that time. Upon
attempting to restart the server, the following message appears in the SQL
Server error log:

kernel udactivate(IN_RAM): Operating system error 8(Not enough storage
is available to process this command.) encountered
spid1 Device activation error. The physical filename 'IN_RAM' may be
incorrect
spid1 crdb_tempdb: Unable to move tempdb into RAM; RAM device doesn't
exist, cannot be created, or doesn't have enough space for tempdb

CAUSE
=====

On startup, if tempdb is configured to exist in RAM, SQL Server must create a
temp_db device within the available system RAM in which to store tempdb. If the
size of tempdb exceeds the available amount of system RAM, the device cannot be
created. Without the device, SQL Server is unable to create tempdb in RAM. It is
important to note that the tempdb in RAM value uses RAM in addition to memory
currently allocated to SQL Server, and is specified in MB, not 2-KB pages.

WORKAROUND
==========

To resolve this problem, do the following:

1. Open a command prompt session and start SQL Server in minimal configuration
mode with the following command:

sqlservr -c -f

You will get a screen dump of the error log. When the startup is complete, you
will have a flashing cursor after the following line:

97/02/27 15:34:46.14 kernel Warning: override, autoexec procedures skipped.

2. After the server is started, open a second command prompt session and connect
to the server as the system administrator (SA) using ISQL:

isql -Usa -Ppassword (if you are not local to the computer, add -Sservername)

3. Reconfigure the tempdb in RAM parameter to an acceptable value. It is
important to note that the tempdb in RAM value uses RAM in addition to memory
currently allocated to SQL Server, and is specified in MB, not in 2-KB pages.
To adjust the current value, use the following commands (where X is the value
of tempdb in RAM, in MB):

sp_configure 'tempdb in ram', X
go

Configuration option changed. Run the RECONFIGURE command to install.

reconfigure
go

4. Shut down SQL Server:

shutdown
go

Server SHUTDOWN by request.

5. Start SQL Server as normal.




------------
Buddy Ackerman at 10/3/00 2:54:08 PM

I was running a server with TempDB in RAM (please don't comment on why that's not a good idea). Well, everything was fine until one of our administrator install teh backup exec agent and restarted the server. Since then the server won't start giving an error the Tempdb coulld not be moved into ram. Well, I started the server using the -f parameter and reset the Tempdb in RAM setting to zero. Howeever, it still won't start and gives the same error about not being able to move Tempdb into RAM. Is my only option to rebuild the master database? This is just a backup server but I'd like to avoid the hassle of rebuilding master and reinitializing the databases.


--Buddy