.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Asad Ali
Fauzul Azmi
Post New Web Links

RECOVERY_PENDING - ALTER DATABASE emergencydemo SET EMERGENCY; Fails

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server
 

I have SQL Express 2008 and SQL 2008 both installed on a server. There are a variety of dbs installed on SQL Standard all with many backups. SQL Express has only 4 small dbs and it turns out these are only backed up using DPM 2010.

One of these is stuck on recovery_pending, apparently since August 31. It is a blog with irregular entries and absence was not noted.

I tried all the steps at http://sqlhera.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-EMERGENCY-mode-repair-the-very-very-last-resort.aspx. The SET Emergency and also the SET Single_User command reports success, but then the DBCC part just says:

Msg 922, Level 14, State 1, Line 1
Database 'emergencydemo' is being recovered. Waiting until recovery is finished.

At this point there is only one backup in DPM dated August 30. I restored this to a different location and tried to attach it ot SQL Standard, but it fails:

Database 'emergencydemo' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.
Could not open new database 'emergencydemo'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 3415)

It is looking pretty grim for both the database and the backup.


View Complete Post


More Related Resource Links

Database is looking as (Suspect/readonly/offline/emergency/)

  
Could not open FCB for invalid file ID 0 in database 'mssc_admin' and database is looking as (Suspect/readonly/offline/emergency/) Actually log file is not visibiling and I was trying to restore it.   PLease PLease help"SQLSERVER DBA" "INDIA"

SQL Server 2008 Windows Authentication Mode fails for Database Engine, error 18456

  
I installed SQL Server 2008 Developer Edition (10.0.1600.22.080709-1414) on a Windows Vista 32-bit development machine with Windows Authentication Mode only (no SQL Server Authentication).   Since this is a development machine, I saw no need for Mixed Mode when I did the install.  The SQL Server Management Studio allows me to login to Analysis Services, Integration Services, Reporting Services, BUT NOT the Database Engine!Windows Authentication for the Database Engine gives the following error: EventID: 18456Login failed for user MYDOMAIN\MYLOGIN'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]The 'fix' I have found online is to login as SA in SQL Server Authentication mode & add MYDOMAIN\MYLOGIN as a administrator for the Database Engine.  Unfortunately, I can't since I didn't install SQL Server Authentication mode (only Windows Authentication Mode).  It appears my only recourse is to uninstall, then reinstall in Mixed mode, then to login as SA in SQL Server Authentication mode & add MYDOMAIN\MYLOGIN as a administrator for the Database Engine. Before I do so, does anyone know of a better approach?Scott D Duncan

Full Farm Backup Fails on Windows Sharepoint Services Search Database

  
I am just now starting to us central administration to backup the entire sharepoint farm.  All content backups fine except for the Search Database.  In looking through the backup log, it appears that the search database may not exist.  How can I determine if the search database actually does exist and if it does not can it be recreated?

SQL Server 2008 R2 rebuild master database fails with error -2068643838

  
I want to change the server collation setting for SQL 2008 R2.  I'm using the command: Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MyServerName /SQLSYSADMINACCOUNTS="MyDomain\Special Account Group" "NT AUTHORITY\SYSTEM" "NT SERVICE\MSSQLSERVER" "NT SERVICE\SQLSERVICEAGENT" sa /SQLCOLLATION=Latin1_General_CI_AI The setup program starts, runs a while, then exits with a return code: Error result: -2068643838 Result facility code: 1203 Result error code: 2 The final lines of the Detail.txt file in the ...\Setup Bookstrap\Log\<specific timestamp>\ directory are: 2010-07-12 14:54:58 Slp: Running Action: ProduceStatusLogsBeforeFinishPage 2010-07-12 14:54:59 Slp: Completed Action: ProduceStatusLogsBeforeFinishPage, returned True 2010-07-12 14:55:00 Slp: Received request to add the following file to Watson reporting: C:\Users\<MyLoginName>\AppData\Local\Temp\2\tmpDAAF.tmp 2010-07-12 14:55:00 Slp: The following is an exception stack listing the exceptions in outermost to innermost order 2010-07-12 14:55:00 Slp: Inner exceptions are being indented 2010-07-12 14:55:00 Slp: 2010-07-12 14:55:00 Slp: Exception type: Microsoft.SqlServer.Setup.Chainer.Workflow.NoopWorkflowException 2010-07-12 14:55:00 Slp:     Message: 2010-07-12 14:55:00 Slp:         The state of your

SQLServer2008 job created by Copy DataBase Wizard fails - cannot determine if job owner has server a

  

Trying to copy a SQL2000 database on server TUNA to destination server MOJITO which runs default instance of SQL2008 (at ServicePack1) via the CD Wizard. Resulting job fails on MOJITO with this in application log:

SQL Server Scheduled Job 'CopyDatabaseWizard_TUNA_MOJITO_1' (0x64AB69F2880A7E4DA3708546C33DFF40) - Status: Failed - Invoked on: 2010-09-23 17:05:04 - Message: The job failed. Unable to determine if the owner (CBMIWEB\johna) of job CopyDatabaseWizard_TUNA_MOJITO_1 has server access (reason: Could not obtain information about Windows NT group/user 'CBMIWEB\johna', error code 0x5. [SQLSTATE 42000] (Error 15404)).

There is a credential on MOJITO defined for CBMIWEB\johna. There is a proxy on MOJITO that uses that credential. The job has one step and in the properties I have set the RUN AS value for the job to be the name of the Proxy. The proxy is established for SSIS jobs.

The "owner" of the job is also CBMIWEB\johna which is a domain userid in the local Administrators group of each machine (both TUNA and MOJITO). This userid has been granted the permission to Logon as a Batch Job on both servers.

TUNA is a Windows 2000 standalone server; MOJITO is Windows 2003. I can logon to each server as CBMIWEB\johna.

I don't know what else to do.


AS Database Restore fails: OLAPinfo element .... (namespace) cannot appear under BackupLog. (Micros

  

We encounter this problem when restoring a backup from the SAME sql server version (2008R2):

TITLE: Microsoft SQL Server Management Studio
------------------------------

The OlapInfo element at line 1, column 445 (namespace ) cannot appear under BackupLog.
 (Microsoft.AnalysisServices)

 

 

 


ALTER DATABASE AdventureWorks SET ENABLE_BROKER takes too long to finish, how long does it run

  
try to run ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
but still running after 8mins, is this normal, what is approximate time? can it be cancelled?

Need to execute ALTER DATABASE inside stored procedure

  

SQL 2005 Standard.

I wrote a sp that uses ALTER DATABASE instruction. When I execute sp with elevated user it works fine; when I try to execute that sp with a normal user (I alerady gave execute permission for that user) it fails with "alter database failed".

I tried to modify create proc with execute as owner but nothing changed.

How can I do ?

Thanks.


SQL Server 2008 - Alter Table Switch Statement Fails Where Partition Key Defined as a Persisted Comp

  

Here's a very annoying and poorly (or undocumented) issue in SQL Server 2008 Ent. Ed. post-SP1, CU8:

When creating a table with a persisted computed column as the clustered primary and partitioning key (see below), where the computation includes a call to CONVERT, watch out for the representation of this calculation on sys.computed_columns.  The query parser will modify the definition at table creation to insert a default style parameter of "(0)", if no other style is specified.  If the original definition did not include any style parameter and you later attempt to programmatically recreate the table using the stored definition contained on sys.computed_columns, the query optimizer will throw an error when you then attempt to switch partitions from the original table into the new programmatically created table, as follows:

Msg 4966, Level 16, State 1, Line 72

ALTER TABLE SWITCH statement failed. Computed column '%' defined as '%' in table %' is different from the same column in table %' defined as '%'.

This is so, eventhough the two definitions are completely functionally equivalent, considering the default value of the style parameter.  This can cause a lot of headaches, because correcting the problem requires dropping and rebuilding the computed column with a definition that include

SQL 2008 SP2 succefull but instance start fails - Script level upgrade for database 'master' faile

  

after installing SP2 instance starts and stops with error:

Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 5831, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Already tried without success:
- enabled SA account (was disabled) http://support.microsoft.com/kb/960781/en-us
- uninstalled SP2 -> other error, but same problems with running the script

 

 


SQL Server Express R2 Database Engine fails to install on XP Home (SP3)

  

I have had multiple failed attempts at installing SQL Server 2008 R2 Express on an elderly but reliable machine running XP Home with SP3. 
The support files and management tools loaded OK when I de-selected the database engine.  I was installing as a user with administrator rights. 
I wanted to start the DB as a service following boot without a user logged on.

The failure appeared as a message a 'Library not registered' at stage Slp_Config_SQLEngine_ADHlpr_Cpu32.  I cancelled at this point (although in other attempts I let it run on with similar, unsuccessful results).

Other posts covering failed installation do not appear to cover the same problem.  If they do, I am afraid it is my limited understanding!

The summary log file is below and what I think are the relevant lines from the detail are given after. 

Can anyone help identify the cause and suggest what I need to do to rectify it?

Many thanks.

******************************************************************
Summary Log File
******************************************************************
Overall summary:
  Final result:                  User cancelled
  Exit code (Decimal):    

Backup of ShareService Provider database using Central Admin fails

  
Anybody have an idea of what causes this?  It's very consistent on one of my farms.

Membership Provider - Login fails after change in database name

  

Hi Experts,

I am in the process of deploying an website developed in ASPNet 2.0 and using the login control and membership provider features. The site was deployed fine and was working perfectly till I changed the database name and user name to access the database. I changed the accordingly in the web.config to point to the new database. Everyting is working file except when the user tries to login into the website.

The error log says that login failed because it is trying to open the old database.

Any idea how I can solve the issue? Thanks for your help.

Regards,

Sudipta


Does ALTER DATABASE SET RESTRICTED_USER block until connections are killed?

  

Hi, we have a database restore script that runs an ALTER DATABASE [Database] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE command.  The next statement is a WAITFOR DELAY with 30 seconds.  This is to ensure any non compliant connections are killed before continuing.

However, I'm wondering if the WAITFOR is actually necessary?  Would the ALTER DATABASE command not block until all connections have been killed?

Thanks,
Tom


Change database owner using Alter Database 2008

  

Hello,

I need to change the owner of a bunch of databases, and I do not want to use sp_changedbowner as it is deprecated for the next sql releases. I there a way to do this in an Alter Database statement?

Any help would be appreciated

Dave


Dave SQL Developer

2008 R2- Restoring MASTER database from v10.00.1600 to v10.50.1600 fails with error..

  

Hey Guys,

I backed up the master database on a machine with SQL Server 2008 version 10.00.1600. Then I turned on SQL Server in single-user mode on the new machine with SQL Server 2008 R2 version 10.50.1600 with the following command (using sqlcmd)

RESTORE DATABASE master FROM DISK = 'D:\master.bak' WITH REPLACE;

Running this command gets me the following error.....

Msg 3168, Level 16, State 1, Server U15417546, Line 1
The backup of the system database on the device D:\master.bak cannot be restored because it was created by a different version of the server (10.00.1600) than this server (10.50.1600). Msg 3013, Level 16, State 1, Server U15417546, Line 1
RESTORE DATABASE is terminating abnormally.

Since i am restoring from a lower version, to a higher version, i assume the restore should be able to complete? I have not been able to find much help regarding this, so any information would be much appreciated.

 


Restoring MASTER database from version 10.00.1600 to version 10.50.1600 fails with error...

  

Hey Guys,

I backed up the master database on a machine with SQL Server 2008 version 10.00.1600. Then I turned on SQL Server in single-user mode on the new machine with SQL Server 2008 R2 version 10.50.1600 with the following command (using sqlcmd)

RESTORE DATABASE master FROM DISK = 'D:\master.bak' WITH REPLACE;

Running this command gets me the following error.....

Msg 3168, Level 16, State 1, Server U15417546, Line 1
The backup of the system database on the device D:\master.bak cannot be restored because it was created by a different version of the server (10.00.1600) than this server (10.50.1600). Msg 3013, Level 16, State 1, Server U15417546, Line 1
RESTORE DATABASE is terminating abnormally.

Since i am restoring from a lower version, to a higher version, i assume the restore should be able to complete? I have not been able to find much help regarding this, so any information would be much appreciated.

 


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend