DB Is Marked Restore Pending Which May Indicate Disk Corruption

Apr 27, 2015

i have a SQL 2008 R2 RTM production instance, in which we run dBCC CheckDB every weekend to check on the DB. This weekend this sql job returned the error:

DBCC RESULTS
--------------------
<DbccResults>
<Dbcc ID="0" Error="8928" Severity="16" State="1">Object ID 866531312, index ID 1, partition ID 72057602979266560
, alloc unit ID 72057603064397824 (type In-row data): Page (1:7650240) could not be processed. See other errors
for details.</Dbcc>
<Dbcc ID="1" Error="8939" Severity="16" State="98">Table error: Object ID 866531312, index ID 1, partition ID 720
57602979266560, alloc unit ID 72057603064397824 (type In-row data), page (1:7650240). Test (IS_OFF (BUF_IOERR, pB
UF->bstat)) failed. Values are 12716041 and -6.</Dbcc>
<Dbcc ID="2" Error="8990" Severity="10" State="1">CHECKDB found 0 allocation errors and 2 consistency errors in t
able 'tblDistpatch' (object ID 866531312).</Dbcc>

We tried to rebuild the indexes in the table: tblDistpatch...the non clustered indexes ran fine however the cluster index rebuilt returned an error:

Error: The statement has been terminated.
Msg 829, Level 21, State 1, Line 1

Database ID 3, Page (1:7650240) is marked RestorePending, which may indicate disk corruption. To recover from this state, perform a restore.in TEST environment we were able to reproduce this error by restoring latest backup.

we ran :

dbcc checktable (tblDistpatch ,REPAIR_ALLOW_DATA_LOSS )

then we ran dbcc checkdb and no errors where found.

my question comes with in your experience is this the best possible way to fix this table?only one table, but running this in production environment will required to put db in single user mode first.

View 7 Replies


ADVERTISEMENT

Restore Log To Marked Transaction

Jan 29, 2007

Simon writes "Hi

using the following syntax in SQL 2005 (win 2003) to restore a log to a marked transaction. Code runs ok but I'm never getting back to the point in time where id 13 still exists , help !

kind regards

Simon

/*backup and overwrite */
BACKUP DATABASE AdventureWorks
TO DISK = N'C:SQL Backupsadw.bak'
WITH FORMAT, INIT, NAME = N'AdventureWorks-Full Database Backup'
GO

/* verify data */
restore verifyonly
from disk = 'C:SQL Backupsadw.bak'
with file = 1
go

-- with MARK to place a marker in the tranny log
BEGIN TRANSACTION CandidateDelete13a
WITH MARK N'Deleting a Job Candidate';
GO
USE AdventureWorks;
GO
DELETE FROM AdventureWorks.HumanResources.JobCandidate
WHERE JobCandidateID = 13;
GO
COMMIT TRANSACTION CandidateDelete13a;
GO

BACKUP LOG AdventureWorks
TO DISK = N'C:SQL Backupsadw_log.bak'
with format, NAME = N'AdventureWorks-Trans Log Backup'
GO


/*
RESTORE DATABASE AdventureWorks FROM DISK = N'C:SQL Backupsadw.bak' WITH FILE = 1, RECOVERY, NOUNLOAD, REPLACE,STATS = 10
GO
*/

use master
RESTORE DATABASE AdventureWorks FROM DISK = N'C:SQL Backupsadw.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE,STATS = 10
GO

use master
RESTORE LOG AdventureWorks FROM DISK = N'C:SQL Backupsadw_log.bak'
WITH recovery, STOPatMARK = 'CandidateDelete13a'
GO"

View 1 Replies View Related

Please Help!!! After SQL Database Restore It Is Marked As In Load?

Jan 18, 2004

Hello Friends

I had a really big Problem.
Sorry about my Englisch, came from Austria.

After a SQL Database Restore, who ends with no faults, i cannot connect to it.

In the Enterprise Manager it is marked as "in load" and in the Errorlogfile stand following.

"Bypassing Recovery für Database "WINLINE" because it is marked "in load"?

What does it mean! Please help!!
Thanks for all answers

Mike

View 2 Replies View Related

Database Is Still Marked As Restoring After Full Restore

Dec 2, 2000

After I restore a database with SQL the database I cannot access it. However, after restoring using Enterprise Manager I do not have the same problem. The message I get is:

Database 'les_test' cannot be opened. It is in the middle of a restore.

The code I am trying to use for the restore is:

RESTORE DATABASE les_test
FROM DISK = 'c:MSSQL7BACKUPiztrobeta_db_200012011201.BAK'
WITH NORECOVERY,
REPLACE,
MOVE 'biztrobeta_PRI' TO 'c:mssql7DATAiztrobeta_PRI.ndf',
MOVE 'biztrobeta_FGE_Dat1' TO 'c:mssql7DATAiztrobeta_FGE_Dat1.ndf',
MOVE 'biztrobeta_FGX_Idx1 ' TO 'c:mssql7DATAiztrobeta_FGX_Idx1.ndf',
MOVE 'biztrobeta_LOG' TO 'c:mssql7Dataiztrobeta_LOG.ldf'
GO

I tried changing the status in the master..sysdatabases tables, but the restored database was still not accessible. I tried to do this using the following statment:

UPDATE sysdatabases
SET STATUS = 4194316
WHERE name = 'les_test'
GO

Any help is appreciated.

Thanks,

Les

View 1 Replies View Related

DB-Corruption: Backup, Restore And Recover

Oct 2, 2001

Hi

I have a question concerning backup / restore / recover of a MS-SQL-Server-Database.

Following configuration exists:

MS-SQL-Server running on NT

OS, Server located on a mirrored Raid 0 Disk -> C:

Transactionlog-Files (.ldf) on the same physical mirrored Disk -> E:

Database-Files (.mdf) located on a separate pyhisical Disk (Disk-Set, Raid 5) -> D:

I take a full backup every night of all databases, no Transaction-Log backup is made during a day (never)

Assuming following situation:

During a day one Database became corrupt. The physical-file (.ldf) of corrupted database is still accessible
on Drive E:

1. Step: I make a copy of the .ldf-File and put it on a secure place or in an other directory

2. Step: I restore the DB from the last full backup

3. Step: ???? How is it now possible to recover from the physical available .ldf-File? I know it should be
possible but I don't now how.

Has anybody tips or suggestion or has someone ever had a similar situation? Feedback would be appreciated.

Kind regards

Gabi

View 3 Replies View Related

Restore DB From File On Disk

Sep 28, 1999

Hi,

I am trying to create a scheduled task taht will restore a database from a backup file. I do not store my backups on a backup device, but on a local disk. To restore a DB from a backup device, the following statement will work:
"Load DBName from BackupDeviceName". Does any know what statement to use to restore from a file, if the file is "E:DBName_db_dump_199909272220".

Thanks in a advanced.

View 1 Replies View Related

MDF Disk Fragmentation - Backup And Restore

Jul 23, 2005

If databases on a physical drive [G:] are fragmented, and the drive isextended by adding more hard drives to the array, does it make sense tobackup and restore the fragmented databases?The Windows Server should be able to find contiguous space for eachdatabase, since it shows 75% free space on the SQL Data drive withoutany file fragments on it.Or will it restore to the original location, in which case does it makesense to delete the databases and restore them from the backups?Thank you very much!

View 1 Replies View Related

Restore From Phisical Copy Of Disk (datafiles?)

Oct 19, 2004

Hi,
I have formatted my server because of serious problem and i did not backup my database. I have only a phisical copy of the disk containing data on another disk. :( How I can recover my db? Thank you in advance.

View 6 Replies View Related

SQL 2012 :: Restore Labelonly From Disk Running Nonstop

Jun 22, 2015

We're having some issues with where our backups write to, so I've been watching and monitoring the performance, when I noticed today that restore labelonly from disk has been running almost non stop for the past few hours.

The account running the query is the SQL Server's service account, and the program is "Microsoft SQL Server".

Every minute or so the SPID changes which made me think it was related to the transaction logs, the "restore labelonly" runs for as long as each database in the transaction log backup.

Example: Database A transaction log backup takes 1 minute and the SPID XX for restore labelonly runs 1 minute
Database B transaction log backup starts and there is a new SPID for restore labelonly.

I hope this makes sense because I normally don't see this restore labelonly running.

View 2 Replies View Related

SQL Enterprise Manager Hangs When Choosing A Location On Disk For A Backup Or Restore

Sep 15, 2004

When I select All Actions>Backup Database and then click the ... button to choose a location on the hard drive, SQL Enterprise Manager Hangs and I have to kill it from Task Manager.

All other areas of EM browse the disk drive without a problem. (DTS, File Groups, etc.)

Anyone ever run into a similair problem?

Thanks

View 2 Replies View Related

Sql Server Express 2005 9.0.2047 - Problem RESTORE VERIFYONLY FROM DISK

Mar 5, 2007

Hi all

every time I try to verify the backup file I get the error:

backup set on file '1' is not valid (translated from italian).

this is my backup script:

BACKUP DATABASE [ahr_sistema] TO DISK = N'C:ProgrammiMicrosoft SQL ServerMSSQL.1MSSQLBackupahr_sistemaahr_sistema_backup_1.bak' WITH FORMAT, INIT, NAME = N'ahr_sistema-Completo Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

declare @backupSetId as int

select @backupSetId = position from msdb..backupset where database_name=N'ahr_sistema' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'ahr_sistema' )

if @backupSetId is null begin raiserror(N'Verifica non riuscita. Impossibile trovare le informazioni di backup per il database ''ahr_sistema''.', 16, 1) end

RESTORE VERIFYONLY FROM DISK = N'C:ProgrammiMicrosoft SQL ServerMSSQL.1MSSQLBackupahr_sistemaahr_sistema_backup_1.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

GO

View 5 Replies View Related

Is It Possible To Move My Sql 2000 Database (in C Disk) To Another Disk (Disk) ?

Dec 28, 2006

hello,all
          I am new to Sql 2000,I installed sql 2000 database in C disk,but Now I found my C disk space is smaller than before,So I want to move my databse(include data and structure)   from C Disk to D Disk(its space is very large) .
         is it possible to do it ? 
         if its can be done ,do I need to change my asp.net program source code (exp: chaneg my crystal  report connectstring ) ?
        thanks in advanced!
 
 
 
      

View 1 Replies View Related

Calculating COUNTER Physical Disk: AVG. DISK QUEUE LENGTH

Sep 10, 2007

If I return the Average, Minimum, and Maximum values for the counter Physical Disk: Avg. Disk Queue Length, and those values are 10, 0, 87 respectively, which value do I use to compute the Avg. Disk Queue Length for a 4 disk array(RAID 10): Average, Minimum, or Maximum? The disk(lun) is on a SAN.

View 1 Replies View Related

Get Total Disk Size And Free Disk Space

Nov 13, 2007

-- Initialize Control Mechanism
DECLARE@Drive TINYINT,
@SQL VARCHAR(100)

SET@Drive = 97

-- Setup Staging Area
DECLARE@Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)

WHILE @Drive <= 122
BEGIN
SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''

INSERT@Drives
(
Info
)
EXEC(@SQL)

UPDATE@Drives
SETDrive = CHAR(@Drive)
WHEREDrive IS NULL

SET@Drive = @Drive + 1
END

-- Show the expected output
SELECTDrive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes,
SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes,
SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes
FROM(
SELECTDrive,
Info
FROM@Drives
WHEREInfo LIKE 'Total # of %'
) AS d
GROUP BYDrive
ORDER BYDrive

E 12°55'05.25"
N 56°04'39.16"

View 16 Replies View Related

Should The Quorum Disk Be A Physical Disk Or Majority Node Set?

Nov 15, 2006

Hello,

I am trying to setup a test cluster and am having an issue. When I try to create the resource of a physical disk it takes both the drive e: and drive q: and doesn't seperate them into two physical disks as resources. This means when I try to associate the quorum disk it links the to physcial disk resource of drive e and q. Then when I try to install SQL2k5 I get the warning about installing SQL on the quorum disk. Am I missing something? Is there a way to seperate e and q onto two physical disk resources so I can specifically associate the quorum to q and the sql to e or should I be setting the quorum disk to a majority node set? Thanks in advance.

John

View 4 Replies View Related

Disk Crash Of Disk That Contains The Paging File.

Feb 20, 2001

Hello,

this is my configuration :

1) 3 disks in RAID5 that hold the SQL data
2) 1 disk in RAID0 that holds the only paging file.

What will happen to the SQL data (DB) when the disk that holds the paging file crashes?

Kindest regards,
Luc.

View 1 Replies View Related

SQL Server, Disk Arrays And Disk IO

May 7, 2004

Hi all,

Ok here goes,

I have a three tier system using SQL server 2000, we are currently experiencing IO bottle necks on our SCSI Raid 10 array, which holds the Data and the logs in separate partitions.

So my options as I understand it are:

Get Enterprise edition

or

Get another physical raid 10 array and separate the logs and data i.e. data on one array and logs on the other array.


I would like to try the latter but I am totally unsure how much difference this will make or whether it will make any difference at all.

Does anyone know how much performance increase I will get from using two arrays as opposed to one?


Any other advice on this scenario would be greatly appreciated.


Thanks

View 4 Replies View Related

Pending Transactions

May 15, 2008

i have a small doubt
where to see the pending transactions in transactional replication

For a database can we have multiple publications

View 2 Replies View Related

Pending Disaster

Nov 9, 2007


I am using SQL 2000 and have a major issue! I am new to SQL and not sure what to do about the license key being lost due to an update.

Background:
The server was originally loaded with a temp verison of SQL 2000 by the installer of the hardware until the Enterprise Edition arrived on site to insure that all the hardware worked fine. The Enterprise software was loaded onto the system and the license keys were installed. Everything has been working fine for several months. The server has been rebooted a few times after installing the license keys.

Trouble:
I loaded SP2 for Server 2003 and rebooted the server and now SQL thinks it is running the temp version and states that the license key has expired. I tried the suggested solution listed on the site of "upgrading with no changes" and the installation completes but never asks for my license keys. I rebooted the server and it states the samething thing that the temp has expired. It appears that one section can see the keys and another section does not. I have an active database that I do not want to loose but I am stumped on what to do to in order to re-install the license keys.

Any suggestions? Can copy the databases that I have created, uninstall SQL, re-install SQL from CD and then paste back my database? Will the databases restore correctly or is this to simple of a solution?

I have run out of ideas and options in order not to loose the database.

View 1 Replies View Related

Pending Transaction

Mar 28, 2006

Is there any command to check out any outstanding transactions not yet
replicated to subscriber DB? Thanks in advance

View 2 Replies View Related

Develope Pending &> Activate

Apr 2, 2007

Hi,I have an application (Asp.net + c#).There is a SQL table called 'Status' it is used to store pending and activated events.There is a field called 'Complete'.So when user register I want to add recored to 'Complete' field as 'Pending'But I cant understand how to do this. Plese help me  

View 1 Replies View Related

SQL 2012 :: AlwaysOn Pending Changes

Aug 18, 2014

How to find the pending transactions to be applied on secondary in Always On? Can i get those details from dashboard?

View 2 Replies View Related

Error Pending File

May 31, 2007

I've uninstall my previous sql2000 developer edition with leave some dll file undeleted.

once I tried to reinstall the sqlserver, the installation setup stop the process with message: A previous installation created pending file operations on the installation machine.
you must restart the computer before running setup.

I fail in continueing setup although i've restart the pc.

What should i do to handle this?



Regards,

BHT



Hhh.... please someone help me...

I don't want to reinstall the O/S as previously i've done. headache and waisting my days.



Regards,

BHT

View 3 Replies View Related

Pending Reboot Requirement

Feb 20, 2007

I just installed SP2. I wanted to install the AdventureWorksDW database as part of the Database Engine Tuning Advisor tutorial. It said my install was not finished and I had a "pending reboot requirement." I shut down, and 8 different updates ran. When I tried to install the AdventureWorksDW database again, I got the same message. I'll see if there are still more updates to run.

View 10 Replies View Related

DB Marked Loading

Jul 16, 2001

Hi! maybe this question is very common, but i can resolve that. I have a DB marked 'loading', and nothing seems to change this state. Is any way to revert to a normal state, or the only way is destroy and re-create?
Thanks in advance

View 1 Replies View Related

Is XmlTextReader Marked With HPA

Mar 31, 2006

Hi all,

I want to know if the System.Xml.XmlTextReader is marked with Host Protection Attributes(HPA) in the SQL Server 2005 CLR. I ask this because when I add a custom class that inherits from System.Xml.XmlTextReader, I get a code access security error at runtime. I am able to register the assembly just fine. I have tested it with all the cases and pretty much sure my code breaks in this class.

My code snippet is shown below:

public class CustomXmlTextReader : XmlTextReader
{
......
......
}

Doesn't really matter what is in this class. It gives an expection even when there is no code inside.

Any help is greatly appreciated.

Thanks,
Jai

View 1 Replies View Related

DB Marked Suspect

Mar 13, 2007

I have SQL Express SP1 installed on serveral machines, each has one instance and each instance has more than one database.

Recently, I found out some dbs are marked Suspect. MOM tool reported something like:

The database "your-DB" in the instance "Your-Instance" is in a critical state. The state is: Suspect.

I tried to run "DBCC CHECKDB", but it failed.

Can someone tell me what could cause the problem, and how to fix it?

Thanks,

Peter

View 2 Replies View Related

Repl.Pending Xacts Question

Aug 2, 2006

We have VLDBs running on a SQL 2005 server. Some users have questioned speed and response times. When I investigated the server (W2K3) using the perfomrance monitor, I noticed an interesting questionabel result. The Repl. Pending Xacts showed 44,000,000 transactions waiting to be delived to the distribution databse. Problem is - we do not have replication tunred on on any of the db's on the box. In my research, I have not noticed anyone with a similiar issue. Does this amount of transaction take up space, or is it simply a counter that I don't have to be worried about?

View 4 Replies View Related

DB Engine :: Database In Recovery Pending

Jun 30, 2015

i've change the database log file from E drive to Drive due to insufficient space.without attaching the log file i've shutdown the sytem. becz it's gone in hand mode.post resuming its showing recovery pending.i'm unable to get online db.

View 15 Replies View Related

Database Marked (Loading)

Dec 7, 2001

I canceled a restore job, and now the database is marked (Loading). When I go to propertied it tells me "database can't be opened, it's in a middle of a restore" Yet from a Query window I can connect and run queries against that database. Should I just wait until it finishes "Loading" or should I re-start the server?
Thanks

View 1 Replies View Related

Database Marked As Suspect

Mar 14, 2002

Hello Gurus,

After the power failure my database was marked suspect. According to my associate we will execute SQL command sp_resetstatus. My question is what are the things we have to do first before we execute the command? We are using NT 4.

thanks
Claudio

View 1 Replies View Related

Msdb Marked Suspect!!!!

Jul 15, 2002

Hi all,
I have a big problem with a Database on SQL-Server7.0 running on windows 2000 advanced server. The msdb database is marked suspect. I have tried a few suggestions in the SQL BOL but no luck.

Can anyone help please!

View 4 Replies View Related

Msdb Marked Suspect

May 29, 2001

We have a new installation on Friday which was running fine. Unfortunately today, the msdb is marked suspect. Do I uninstall and reload SQL server or change the status. We only have a training data on the server presently. Does anybody has any other suggestions? Please help. Thanks for your assistance.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved