Restored DB is 'in recovery' after OS upgrade - fi

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Restored DB is 'in recovery' after OS upgrade - fi

Author

Topic

Robert London
Starting Member

26 Posts

Posted - 2010-07-29 : 15:43:04

Hi,

We were running on OS server 2003 x32 Standard with SQL 2008 and backed up a large DB. When we went to recover the DB it kept failing due to the following error 'There is insufficient system memory in resource pool 'internal' to run this query'. After attempting /pae, /3gb, -g parameter fixes the DB still failed.

?We then decided to upgrade the OS to server 2003 x64 Enterprise as processes were running slow prior anyway, so it made economic sense. Then re-installed SQL 2008.

We have tried restoring the DB but it has gone into recovery mode.....

Any suggestions on how to rectify/ fix this would be appreciated.

Thanks

Robert

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-29 : 15:49:17

Post your restore script.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-29 : 15:51:10

I locked your duplicate topic.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-29 : 15:53:21

Oh and also you need to add the "lock pages in memory" privilege to the SQL Server service account. That should be what would have fixed the issue. Do it on the new OS too.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Robert London
Starting Member

26 Posts

Posted - 2010-07-29 : 18:38:18

Ok, sorry, I did not know which one would give quicker results.

I thought 'locked in memory' pages made no difference in x64?

The database has been 'in recovery' for 5 hrs, is it likely to fail?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-29 : 20:13:39

Locked pages in memory is definitely needed on x64.

Your restore script is likely wrong. It probably specified the option of NORECOVERY, so it won't actually ever complete. Run WITH RECOVERY on the backup file. Or post your script, and we'll help out.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-30 : 04:51:48

What's the exact state of the database (check the column state_desc in sys.databases)

Check the SQL error log. Are there any errors relating to this database? (along the lines of Recovering database x Phase 2 of 3. Estimated time remaining...) or any messages complaining about missing files, full files, inaccessible files, etc.

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-30 : 04:54:57

quote:
Originally posted by Robert London

I thought 'locked in memory' pages made no difference in x64?

It's AWE that is meaningless in x64. Locked pages can still be necessary unless you like the OS paging SQL's memory out to disk.

--
Gail Shaw
SQL Server MVP

Robert London
Starting Member

26 Posts

Posted - 2010-07-30 : 06:34:22

I did not run a restore script, just right clicked Database and then selected restore.

The database completed the restore and recovery. We then shutdown and added the extra 8gb of memory we ordered and restarted. When we tried to run a simple query (SELECT TOP 1000) the DB went back in recovery.

Here is the latest error log:

2010-07-30 10:40:45.48 Server Microsoft SQL Server 2008 (SP2) - 10.0.3798.0 (X64)
Jun 18 2010 16:37:39
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)

2010-07-30 10:40:45.53 Server (c) 2005 Microsoft Corporation.
2010-07-30 10:40:45.53 Server All rights reserved.
2010-07-30 10:40:45.53 Server Server process ID is 1160.
2010-07-30 10:40:45.53 Server Authentication mode is WINDOWS-ONLY.
2010-07-30 10:40:45.56 Server Logging SQL Server messages in file 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2010-07-30 10:40:45.60 Server This instance of SQL Server last reported using a process ID of 1212 at 30/07/2010 10:30:27 (local) 30/07/2010 09:30:27 (UTC). This is an informational message only; no user action is required.
2010-07-30 10:40:45.60 Server Registry startup parameters:
-d E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
-e E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2010-07-30 10:40:45.73 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2010-07-30 10:40:45.73 Server Detected 2 CPUs. This is an informational message; no user action is required.
2010-07-30 10:40:46.18 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2010-07-30 10:40:46.46 Server Node configuration: node 0: CPU mask: 0x0000000000000003 Active CPU mask: 0x0000000000000003. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2010-07-30 10:40:46.81 spid7s Starting up database 'master'.
2010-07-30 10:40:47.10 spid7s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2010-07-30 10:40:47.46 spid7s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2010-07-30 10:40:47.84 spid7s SQL Trace ID 1 was started by login "sa".
2010-07-30 10:40:47.91 spid7s Starting up database 'mssqlsystemresource'.
2010-07-30 10:40:47.92 spid7s The resource database build version is 10.00.3798. This is an informational message only. No user action is required.
2010-07-30 10:40:48.40 spid10s Starting up database 'model'.
2010-07-30 10:40:48.42 spid7s Server name is '**********'. This is an informational message only. No user action is required.
2010-07-30 10:40:48.51 spid7s Informational: No full-text supported languages found.
2010-07-30 10:40:48.51 spid13s Starting up database 'msdb'.
2010-07-30 10:40:48.51 spid14s Starting up database 'D_Livev1'.
2010-07-30 10:40:48.81 Server A self-generated certificate was successfully loaded for encryption.
2010-07-30 10:40:48.88 Server Server is listening on [ 'any' <ipv4> 1433].
2010-07-30 10:40:48.92 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2010-07-30 10:40:48.92 Server Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2010-07-30 10:40:48.93 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2010-07-30 10:40:48.93 Server Dedicated admin connection support was established for listening locally on port 1434.
2010-07-30 10:40:48.99 spid10s Clearing tempdb database.
2010-07-30 10:40:49.01 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2010-07-30 10:40:49.01 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2010-07-30 10:40:49.90 spid10s Starting up database 'tempdb'.
2010-07-30 10:40:50.46 spid13s The Service Broker protocol transport is disabled or not configured.
2010-07-30 10:40:50.48 spid13s The Database Mirroring protocol transport is disabled or not configured.
2010-07-30 10:40:50.76 spid13s Service Broker manager has started.
2010-07-30 10:40:52.61 spid51 Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
2010-07-30 10:40:52.64 spid51 Using 'xpsqlbot.dll' version '2007.100.1600' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2010-07-30 10:40:52.98 spid51 Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2010-07-30 10:40:53.04 spid51 Using 'xpstar.dll' version '2007.100.3798' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2010-07-30 10:40:53.34 spid51 Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
2010-07-30 10:40:53.37 spid51 Using 'xplog70.dll' version '2007.100.1600' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2010-07-30 10:42:26.70 Logon Error: 18456, Severity: 14, State: 38.
2010-07-30 10:42:26.70 Logon Login failed for user **********\Administrator'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
2010-07-30 10:55:14.70 Logon Error: 18456, Severity: 14, State: 38.
2010-07-30 10:55:14.70 Logon Login failed for user '********\Administrator'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

I have not yet done 'locked pages in memory' as I would need to restart for it to take effect.

Any thoughts on how this will turn out?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-30 : 07:02:20

quote:
Originally posted by GilaMonster

What's the exact state of the database (check the column state_desc in sys.databases)

--
Gail Shaw
SQL Server MVP

Robert London
Starting Member

26 Posts

Posted - 2010-07-30 : 09:21:26

Heres the results from:
SELECT name, state_desc FROM sys.databases

master ONLINE
tempdb ONLINE
model ONLINE
msdb ONLINE
D_Livev1 RECOVERING

Really appreciate your help on this

Robert London
Starting Member

26 Posts

Posted - 2010-07-30 : 09:42:16

Ok, more information:

We ran:
DBCC CHECKDB

And got back:

DBCC results for 'master'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
DBCC results for 'sys.sysrscols'.
There are 822 rows in 11 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 116 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysallocunits'.
There are 128 rows in 3 pages for object "sys.sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syspriorities'.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for 'sys.sysdbfrag'.
There are 6 rows in 1 pages for object "sys.sysdbfrag".
DBCC results for 'sys.sysfgfrag'.
There are 2 rows in 1 pages for object "sys.sysfgfrag".
DBCC results for 'sys.syspru'.
There are 6 rows in 1 pages for object "sys.syspru".
DBCC results for 'sys.sysbrickfiles'.
There are 13 rows in 1 pages for object "sys.sysbrickfiles".
DBCC results for 'sys.sysphfg'.
There are 1 rows in 1 pages for object "sys.sysphfg".
DBCC results for 'sys.sysprufiles'.
There are 2 rows in 1 pages for object "sys.sysprufiles".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysowners'.
There are 16 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysdbreg'.
There are 6 rows in 1 pages for object "sys.sysdbreg".
DBCC results for 'sys.sysprivs'.
There are 1937 rows in 15 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 74 rows in 3 pages for object "sys.sysschobjs".
DBCC results for 'sys.syslogshippers'.
There are 0 rows in 0 pages for object "sys.syslogshippers".
DBCC results for 'sys.syscolpars'.
There are 659 rows in 14 pages for object "sys.syscolpars".
DBCC results for 'sys.sysxlgns'.
There are 28 rows in 1 pages for object "sys.sysxlgns".
DBCC results for 'sys.sysxsrvs'.
There are 1 rows in 1 pages for object "sys.sysxsrvs".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.sysusermsgs'.
There are 0 rows in 0 pages for object "sys.sysusermsgs".
DBCC results for 'sys.syscerts'.
There are 6 rows in 1 pages for object "sys.syscerts".
DBCC results for 'sys.sysrmtlgns'.
There are 0 rows in 0 pages for object "sys.sysrmtlgns".
DBCC results for 'sys.syslnklgns'.
There are 1 rows in 1 pages for object "sys.syslnklgns".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 34 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 172 rows in 2 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 327 rows in 3 pages for object "sys.sysiscols".
DBCC results for 'sys.sysendpts'.
There are 5 rows in 1 pages for object "sys.sysendpts".
DBCC results for 'sys.syswebmethods'.
There are 0 rows in 0 pages for object "sys.syswebmethods".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysaudacts'.
There are 0 rows in 0 pages for object "sys.sysaudacts".
DBCC results for 'sys.sysobjvalues'.
There are 287 rows in 35 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 22 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 163 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 110 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysguidrefs'.
There are 1 rows in 1 pages for object "sys.sysguidrefs".
DBCC results for 'sys.syschildinsts'.
There are 0 rows in 0 pages for object "sys.syschildinsts".
DBCC results for 'sys.syscompfragments'.
There are 0 rows in 0 pages for object "sys.syscompfragments".
DBCC results for 'sys.sysftstops'.
There are 0 rows in 0 pages for object "sys.sysftstops".
DBCC results for 'sys.sysqnames'.
There are 101 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 99 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 112 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 18 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 8 rows in 1 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 3 rows in 1 pages for object "sys.sysbinsubobjs".
DBCC results for 'sys.syssoftobjrefs'.
There are 0 rows in 0 pages for object "sys.syssoftobjrefs".
DBCC results for 'spt_fallback_db'.
There are 0 rows in 0 pages for object "spt_fallback_db".
DBCC results for 'spt_fallback_dev'.
There are 0 rows in 0 pages for object "spt_fallback_dev".
DBCC results for 'spt_fallback_usg'.
There are 0 rows in 0 pages for object "spt_fallback_usg".
DBCC results for 'sys.queue_messages_1003150619'.
There are 0 rows in 0 pages for object "sys.queue_messages_1003150619".
DBCC results for 'sys.queue_messages_1035150733'.
There are 0 rows in 0 pages for object "sys.queue_messages_1035150733".
DBCC results for 'sys.queue_messages_1067150847'.
There are 0 rows in 0 pages for object "sys.queue_messages_1067150847".
DBCC results for 'sys.syscommittab'.
There are 0 rows in 0 pages for object "sys.syscommittab".
DBCC results for 'spt_monitor'.
There are 1 rows in 1 pages for object "spt_monitor".
DBCC results for 'spt_values'.
There are 2506 rows in 12 pages for object "spt_values".
DBCC results for 'MSreplication_options'.
There are 3 rows in 1 pages for object "MSreplication_options".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.
DBCC results for 'mssqlsystemresource'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
DBCC results for 'sys.sysrscols'.
There are 1124 rows in 11 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 240 rows in 2 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysallocunits'.
There are 252 rows in 3 pages for object "sys.sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syspriorities'.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for 'sys.sysfgfrag'.
There are 2 rows in 1 pages for object "sys.sysfgfrag".
DBCC results for 'sys.sysphfg'.
There are 1 rows in 1 pages for object "sys.sysphfg".
DBCC results for 'sys.sysprufiles'.
There are 2 rows in 1 pages for object "sys.sysprufiles".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 20 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 3069 rows in 54 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 17993 rows in 336 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 2 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 4 rows in 1 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 34 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 540 rows in 10 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 808 rows in 5 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysaudacts'.
There are 0 rows in 0 pages for object "sys.sysaudacts".
DBCC results for 'sys.sysobjvalues'.
There are 3736 rows in 1277 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 20 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 895 rows in 6 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 486 rows in 2 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysguidrefs'.
There are 3 rows in 1 pages for object "sys.sysguidrefs".
DBCC results for 'sys.syscompfragments'.
There are 0 rows in 0 pages for object "sys.syscompfragments".
DBCC results for 'sys.sysftstops'.
There are 15829 rows in 119 pages for object "sys.sysftstops".
DBCC results for 'sys.sysqnames'.
There are 483 rows in 4 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 736 rows in 4 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 322 rows in 2 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 923 rows in 7 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 848 rows in 26 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 3 rows in 1 pages for object "sys.sysbinsubobjs".
DBCC results for 'sys.syssoftobjrefs'.
There are 0 rows in 0 pages for object "sys.syssoftobjrefs".
DBCC results for 'sys.syspalvalues'.
There are 598 rows in 5 pages for object "sys.syspalvalues".
DBCC results for 'sys.spt_server_info'.
There are 27 rows in 1 pages for object "sys.spt_server_info".
DBCC results for 'sys.spt_datatype_info'.
There are 76 rows in 1 pages for object "sys.spt_datatype_info".
DBCC results for 'sys.role_permissions'.
There are 165 rows in 2 pages for object "sys.role_permissions".
DBCC results for 'sys.sysbinpals'.
There are 45 rows in 1 pages for object "sys.sysbinpals".
DBCC results for 'sys.syscolrdb'.
There are 11908 rows in 183 pages for object "sys.syscolrdb".
DBCC results for 'sys.spt_provider_types'.
There are 32 rows in 1 pages for object "sys.spt_provider_types".
DBCC results for 'sys.syspalnames'.
There are 127 rows in 1 pages for object "sys.syspalnames".
DBCC results for 'sys.spt_permission_names'.
There are 5 rows in 1 pages for object "sys.spt_permission_names".
DBCC results for 'sys.sysobjrdb'.
There are 1926 rows in 27 pages for object "sys.sysobjrdb".
DBCC results for 'sys.spt_datatype_info_ext'.
There are 13 rows in 1 pages for object "sys.spt_datatype_info_ext".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'sys.filestream_tombstone_2073058421'.
There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421".
DBCC results for 'sys.syscommittab'.
There are 0 rows in 0 pages for object "sys.syscommittab".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'mssqlsystemresource'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Then we ran:
ALTER DATABASE DreamMedia_Livev1 SET ONLINE

And got the following error:
Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database 'DreamMedia_Livev1', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-30 : 12:56:39

You can't change the recovering state to online.

Usually that means that SQL's performing restart-recovery on the database and you must be patient. However the lack of messages is puzzling. Was that the entire SQL error log, or just the beginning? If the latter, I'd like to see the whole thing.

Have you tried restarting the server? It may help in this situation.

--
Gail Shaw
SQL Server MVP

Robert London
Starting Member

26 Posts

Posted - 2010-07-30 : 14:21:29

That's is the error log from after we added the new memory and restarted. Then ran a basic select top 100 query and the DB went into recovery again.

I'll post the previous log file later today when I'm back with the server.

Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-30 : 16:52:44

No, not looking for older errors, looking for any newer.

Restart the server please, if the DB comes online run this before you run anything else.
DBCC CHECKDB('DreamMedia_Livev1') WITH NO_INFOMSGS, ALL_ERRORMSGS

If it doesn't come online, check the state of the DB again (in sys.databases) and check the error log

--
Gail Shaw
SQL Server MVP

Robert London
Starting Member

26 Posts

Posted - 2010-08-01 : 04:46:52

Our DB is back up post recovery and is working ok.

Thanks for all of your help.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-01 : 06:52:10

Did you restart? Did it just come out of recovery by itself?

--
Gail Shaw
SQL Server MVP

Robert London
Starting Member

26 Posts

Posted - 2010-08-02 : 05:29:37

It recovered on it's own. I ran a few basic queries which worked, so I have now run a process heavy one which I'll monitor today and then stop so I can check the speed of process to check the additional memory is making the difference required.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources