1

Topic: Corruptings in a DB

Hello, respected.
At once I will tell, I 1, with mssql am familiar superficially.
I ask the help in DB recovery.
Prehistory. There Lived-was a DB on 10 sas raid. The controler cheap, cache Hdd has not been ungeared. On the adjacent server it is adjusted logshipping. One fine day from the server-receiver logshipping we receive the message
1. Could not redo log record (1718035:250:44), for transaction ID (0:807870501), on page (1:4299870), database ' tis103 ' (database ID 6). Page: LSN = (1717708:15918:51), type = 2. Log: OpCode = 11, context 3, PrevPageLSN: (1716153:4472:2). Restore from a backup of the database, or repair the database.
Got into broad gulls lohshipping, there found the message that it is impossible to recover the next log file since (in a free translation and I I do not remember word-for-word) the file is not correct continuation of a chain.
That for an error I could not understand, therefore simply took down logshipping and started to adjust . In the course of recovery we receive the information that one departed hdd on the working server and all tightly , we do  servers. BASIS in suspect, therefore we recover a copy on the beginning of day, we work in it.
Recovery DBCC CheckDB (' bitaya ', REPAIR_ALLOW_DATA_LOSS) produces the following:
Failed to restart the current database. The current database is switched to master.
Msg 5123, Level 16, State 1, Line 3
CREATE FILE encountered operating system error 3 (the System does not manage to find the specified way.) while attempting to open or create the physical file ' L:\tis103_log.ldf '.
Msg 5024, Level 16, State 2, Line 3
No entry found for the primary log file in sysfiles1. Could not rebuild the log.
Msg 5028, Level 16, State 2, Line 3
The system could not activate enough of the database to rebuild the log.
DBCC results for ' bitaya '.
CHECKDB found 0 allocation errors and 0 consistency errors in database ' bitaya '.
Msg 7909, Level 20, State 1, Line 3
The emergency-mode repair failed. You must restore from backup.

From the message I see that mssql tries to find the transaction log on an old way (L: \tis103_log.ldf) where it certainly is not present as I picked up basis on other server. Picked up so: created a DB with a name "bitaya", stopped service of the server and to the place of empty files "bitaya.mdf" and "bitaya_log.ldf" supposed basis files then launched server service. It was necessary to do so because attach did not work, and a copy of basis after falling did not do, and simply copied files mdf and ldf

2

Re: Corruptings in a DB

I am sorry, the cache hdd has been included. The cache on the controler is ungeared

3

Re: Corruptings in a DB

mustdwindows98aie wrote:

It was necessary to do so because attach did not work, and a copy of basis after falling did not do, and simply copied files mdf and ldf

With what error fell ?

mustdwindows98aie wrote:

From the message I see that mssql tries to find the transaction log on an old way (L: \tis103_log.ldf) where it certainly is not present as I picked up basis on other server.

Ways to files of bases are stored in basis master, instead of in the basis. Besides they can be replaced a command alter database... modify file.

4

Re: Corruptings in a DB

attach falls so:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server ' localhost '. (Microsoft. SqlServer. Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.5000.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach database Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft. SqlServer. ConnectionInfo)
------------------------------
Could not redo log record (1718136:9499:70), for transaction ID (0:807904397), on page (1:4383854), database ' tis103 ' (database ID 5). Page: LSN = (1717709:6880:12), type = 2. Log: OpCode = 11, context 3, PrevPageLSN: (1716153:12447:3). Restore from a backup of the database, or repair the database.
During redoing of a logged operation in database ' tis103 ', an error occurred at log record ID (1718136:9499:70). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Could not open new database ' tis103 '. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 3456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&EvtSrc=MSSQLServer&EvtID=3456&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

5

Re: Corruptings in a DB

While created  a way for the transaction log, as on the fighting server.
Launched
DBCC checkdb (' tis103 ')
ALTER DATABASE tis103 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (' tis103 ', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE tis103 SET MULTI_USER

I wait

6

Re: Corruptings in a DB

Basis 50

7

Re: Corruptings in a DB

Result of performance specified above command set in one request.
https://docs.google.com/document/d/1ID_ … sp=sharing
That I did not understand that, why that specifies that a minimum level of recovery REPAIR_ALLOW_DATA_LOSS. The first command DBCC checkdb (' tis103 ') turns out only whether was fulfilled that?...
Launched once again following 3.
ALTER DATABASE tis103 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (' tis103 ', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE tis103 SET MULTI_USER
I wait

8

Re: Corruptings in a DB

It is too much errors, here it is better to recover from  or from basis on which there was a broad gull .

9

Re: Corruptings in a DB

There transited check REPAIR_ALLOW_DATA_LOSS
https://docs.google.com/document/d/1LIM … sp=sharing
Following the results of I can tell that in basis a broad gull-shipping it appeared the data more (in a broad gull  the last records on 15-00, and in recovered on 14-53).
It turns out at us the data literally for 1-5 mines took off, is exact for I know, on Monday broad gulls of online cash registers I will look. But it too is critical enough, in this time could transit on cash registers the considerable totals is a database of shopping center with passableness on cash registers ~20 people for 5 minutes + in the same basis 4 branches in the terminal work.
It is very strange, why the last data was not saved, after all the transaction log file was allocated absolutely on other hard disk (raid1) with which problems were not.
Unless can be such this data is not present in the transaction log file, after all they at first are written there, and then only to basis, correctly I understand? After all in a broad gull to basis the broad gull-shippinga of the data is more for the whole 7 minutes.

10

Re: Corruptings in a DB

These are all trifles about 5 minutes, after REPAIR_ALLOW_DATA_LOSS  will beg to recover basis at least for last year.

11

Re: Corruptings in a DB

Well to whom a trifle and to whom is not present. On departments and a warehouse there are no goods which have been sold in these 5 minutes. The totals can be very considerable. And now begins that all under one comb start to row. I on their place at once  a couple , and got rid of all on failure .:)
That is not the question. The question in that is why some data is not present in  to basis if they are in a broad gull-shippinge

12

Re: Corruptings in a DB

All last operations should be reflected in the transaction log file, time they got to a broad gull-shipping.

13

Re: Corruptings in a DB

REPAIR_ALLOW_DATA_LOSS Does not recover the data, it on holes in a pattern glues a plaster

14

Re: Corruptings in a DB

, we forget about REPAIR_ALLOW_DATA_LOSS.
There are methods besides to pull out the data? After all there should be they in the transaction log

15

Re: Corruptings in a DB

Should be, but confuses that at you  a broad gull at deployment through a broad gull  produced an error.
To make  a broad gull at the damaged basis (is called  a tail)
Fulfill here it:
backup log db_name to disk = ' C:\log_tail.trn'
WITH NO_TRUNCATE, NORECOVERY
I so understood you basis where went  translated in an active mode? Then on it already broad gulls not to roll from the damaged basis. Therefore to you it is necessary to tear full  (without damages) in NORECOVERY then to tear a chain  a broad gull (too in NORECOVERY) and right at the end  a tail (already with RECOVERY)

16

Re: Corruptings in a DB

And here still, time you after the basis was screwed I did checkdb with LOSS_DATA that I think and  a tail at you not in the best possible way, therefore if recover  a tail, it is necessary to recover it till the moment when all was screwed:
RESTORE LOG [db_name] from disk ' C:\log.trn ' with RECOVERY, STOPAT = ' 2017-01-01 12:00:00.000'
And generally you need to write out to begin with that generally is, what  .., in what state basis where the broad gull  went

17

Re: Corruptings in a DB

MKDT, I explain a situation on available copies.
In approximately at 15-00 I made the full archive for a broad gull-shipping (just tried to recover log-shipping, yet without knowing about all gravity of a situation as raid 10 failure of one HDD without problems should withstand). It is one of the most important and saving copies, therefrom I already recovered all the day long, except the last 5 minutes (from 15-00 till).
At 15-05 the system wedged completely, the server . When the server booted we clarified that basis suspect and on a standby server tore a copy on the beginning of day in which continued operation, in hope that then the data for first half of day we tighten. Thus: there is here this beaten basis and full a copy on 15-00, that is 5 minutes prior to the full wedge of basis.
Also there is still now a beaten basis after REPAIR_ALLOW_DATA_LOSS, in which there is an information only till 14-45 (!!!). That is strange, as on idea from a broad gull should the state at least will be recovered on 15-00, after all this data is in a copy for a broad gull-shipping. The log file lay on separate spot-check with which was not problems, and the data got to a backup copy. But thus were not recovered from the transaction log at dbcc on beaten basis. I do not understand, how so could it turns out, after all the data should be written at once to a broad gull of transactions, and then to be preempted in basis and the more so in a backup copy.
Apropos  a tail. Whether there is a sense if I already see, what in regenerated basis it is less than data, than in  which is made 5 minutes prior to a wedge?

18

Re: Corruptings in a DB

The hard disk cache can played a malicious joke and had not time to preempt the transaction log data on a screw pancake

19

Re: Corruptings in a DB

mustdwindows98aie wrote:

the hard disk Cache can played a malicious joke and had not time to preempt the transaction log data on a screw pancake

By the way in I creep this version says that on the same physical screw (though and on other section) the directory a broad gull-shippinga lies, that is there during this moment the full backup copy was preempted, queue could be formed

20

Re: Corruptings in a DB

I do not understand, how so could it turns out, after all the data should be written at once to a broad gull of transactions, and then to be preempted in basis and the more so in a backup copy.

I think so: the data was written on  when it launched check checkdb with with LOSS_DATA saw that all new data in basis bad ( do not converge) and nullified them, therefore you and do not see these> =10 minutes since them LOSS_DATA nullified, but in a broad gull they most likely still are. This my assumption.

21

Re: Corruptings in a DB

I will try  for review ldf to play and look at a tail of basis to rapair data loss... But it on Monday since it is necessary to have a rest, the week was hot:)

22

Re: Corruptings in a DB

More shortly following the results of. All data found in not tested killed basis by means of utility Toad for a SQL Server 6.8 (tools-administer-log reader). Very convenient thing, it is possible to generate sql request for the subsequent analysis or loading in basis. Allows to open immediately file of basis without  to the SKL-SERVER.
Before tried SysTools SQL Log Analyzer, ApexSQL Log - result a zero.