Transaction rolled back

[former_SQLA_member1694872]

How to fix error log file without owning.

I/O error fatal error: No such file or directory --- transaction rolled back

Thanks!

[justin_willey]

Hi - you'll have to explain a bit more - when does this happen, what reports the error message? Also it would be useful to know which version you are using.

[former_SQLA_member1694872]

I am using the version of Adaptive Server Anywhere 9 (9.0.2.2542) Is being presented this error message when trying to start the database.

I. 10/17 13:58:57. Adaptive Server Anywhere Database Engine Version 9.0.2.2542
I. 10/17 13:58:57. 
I. 10/17 13:58:57. Copyright © 1989-2004 Sybase, Inc.
I. 10/17 13:58:57. Portions Copyright © 2002-2004, iAnywhere Solutions, Inc.
I. 10/17 13:58:57. All rights reserved. All unpublished rights reserved.
I. 10/17 13:58:57.  
I. 10/17 13:58:57. This software contains confidential and trade secret information of 
I. 10/17 13:58:57. iAnywhere Solutions, Inc.
I. 10/17 13:58:57. Use, duplication or disclosure of the software and documentation
I. 10/17 13:58:57. by the U.S. Government is subject to restrictions set forth in a license
I. 10/17 13:58:57. agreement between the Government and iAnywhere Solutions, Inc. or 
I. 10/17 13:58:57. other written agreement specifying the Government's rights to use the 
I. 10/17 13:58:57. software and any applicable FAR provisions, for example, FAR 52.227-19.
I. 10/17 13:58:57.  
I. 10/17 13:58:57. iAnywhere Solutions, Inc., One Sybase Drive, Dublin, CA 94568, USA
I. 10/17 13:58:57. 
I. 10/17 13:58:57. Running on Windows NT 6.1 Build 7601 Service Pack 1
I. 10/17 13:58:57. 2048K of memory used for caching
I. 10/17 13:58:57. Minimum cache size: 2048K, maximum cache size: 262140K
I. 10/17 13:58:57. Using a maximum page size of 1024 bytes
I. 10/17 13:58:57. Starting database "DATABASE" (C:\\DATABASE\\DATABASE.DB) at Wed Oct 17 2012 13:58
I. 10/17 13:58:57. Database recovery in progress
I. 10/17 13:58:57.     Last checkpoint at Wed Oct 17 2012 07:54
I. 10/17 13:58:57.     Checkpoint log...
I. 10/17 13:58:58.     Transaction log: DATABASE.log...
I. 10/17 13:58:58.     Rollback log...
I. 10/17 13:58:58. A read failed with error code: (38), End of file reached
I. 10/17 13:58:58. Fatal error: Unknown device error
I. 10/17 13:58:58. 
E. 10/17 13:58:58. I/O error Fatal error: Unknown device error -- transaction rolled back

[justin_willey]

DOS error 38 is a bit vague - "Unable to complete the operation". It is going to be something to do with the ability of the database engine to access the db and log files properly. Two possibilities to check: 1 is the log file in the location the database is expecting it to be (you can check with dblog.exe) 2 could another process have locks on either of the files?

[former_SQLA_member1694872]

dblog.exe the run and gave the following details:

C:\\>dblog.exe c:\\database\\database.log
Adaptive Server Anywhere Transaction Log Utility Version 9.0.2.2542
File "c:\\database\\database.log" is a transaction log file
File version is 41
Log starts at offset 0442411716
Log ends at offset 0907814876
Log contains 454528 pages with a page size of 1024 bytes
465403160 bytes in use (99.99%), 33512 bytes free (0.01%)

but what does that mean? using the command line (-f or-gr) to start the database displays error has the same message.

[justin_willey]

Can you try checking what file the database is looking for? The command line would be:

dbinfo.exe -c "dbf=c:\\database\\database.db;uid=dba;pwd=sql"

you may need to change the password (pwd) of course.

[Former Member]

Correct is: dbinfo.exe -c "dbf=c:databasedatabase.db;uid=dba;pwd=sql", only for the connected database.

[justin_willey]

Thanks for spotting that! I've corrected usn to uid in mine.

[former_SQLA_member1694872]

I tried to run the command line but it did not work, I had to run as follows:

dbinfo.exe -c "dbf=c:databasedatabase.db;uid=dba;pwd=sql" instead of dbinfo.exe -c "dbf=c:databasedatabase.db;usn=dba;pwd=sql" And does not work because the database must be started

[justin_willey]

What do you get if you run

C:\\>dblog.exe c:\\database\\database.db

ie against the database not the log

[former_SQLA_member1694872]

dblog.exe the run and gave the following details:

C:>dblog.exe c:databasedatabase.db

Adaptive Server Anywhere Transaction Log Utility Version 9.0.2.2542 "c:databasedatabase.db" is using log file "database.log" "c:databasedatabase.db" is using no log mirror file Transaction log starting offset is 0442411716 Transaction log current relative offset is 0465403160

[justin_willey]

That confirms that the database is looking for a log file with the right name in the same folder as itself - which is what you have. Also the starting offsets match which is a good sign. However this:

A read failed with error code: (38), End of file reached

Does suggest a problem with the log file. Does it translate to SQL successfully:

dbtran c:\\database\\database.log c:\\database\\database.sql

One other thing - when you tried to start with the -f swtich, did you still have the log file in the same folder as the db, if so that could have failed because a conflict when the engine tried to create a new log file.

[former_SQLA_member1694872]

Translated SQL successfully.

Running dbtran shows:

C: > dbtran c: database database.log c: database database.sql Adaptive Server Anywhere Log Translation Utility Version 9.0.2.2542 Transaction log "c: database database.log" starts at offset 0442411716 100% complete Transaction log ends at offset 0907814876

Sql file generated from 2.6 GBytes (how to use this file, what is?)

Yes, Running-f (without the log file).

[justin_willey]

Hi - the sql file is a translation of the log file into SQL statements - ie everything done to the database since the log file was last truncated. The fact that it translated without error suggests that there is nothing wrong with the log file.

I'm afraid I'm running out of suggestions here. Do you have another machine you can try this db and log file on? Do you have another db that you know is OK you can try on this machine? Otherwise you may have to open a support case with SAP, unless anyone else has any ideas?

[former_SQLA_member1694872]

I followed this link

http://www.sybase.com/detail?id=1010805

and still do not know how to solve. I requested a backup. I'll end up starting the db backup.

[jeff_albion · Accepted solution]

Running on Windows NT 6.1 Build 7601 Service Pack 1

That's either Windows 7 SP1, or Windows 2008 SP1, correct?

Adaptive Server Anywhere Database Engine Version 9.0.2.2542

This is not a supported version of SQL Anywhere on Windows 7/2008. EBF 9.0.2.3924 or higher must be installed - see: http://www.sybase.com/detail?id=1023009#footnote_4

You can download the Windows x86 9.0.2.3951 EBF here.

Database recovery in progress

What happened to this database the last time it was shut down such that the database needs to undergo recovery? Did the computer lose power?

A read failed with error code: (38), End of file reached

The error code 38 is the Microsoft Windows system error. The command: net helpmsg 38 says just that: Reached the end of the file.

It sounds like the database file (database.db) is actually corrupt and the file system metadata on disk that provides the length of the file does not match the actual length of the database file on disk. This can happen in some particular power-loss scenarios or with some disk drivers that do not properly flush data to disk (See: http://dcx.sybase.com/index.html#1201/en/dbprogramming/server-deploy.html*pg-deploy-windows-registry... ).

If it is available and valid, you should go to your backup, and apply the current transaction log to bring it up to date.

There are also possible ways to salvage the corrupted database, but it's likely that you will lose data. If you wish to attempt this, you should contact Technical Support. (Note: for this specific scenario, it may not require a full salvage engagement).

---

Aside: Microsoft is also aware of a general metadata disk flushing problem / 'torn writes' and is attempting to address some of these issues in their enhanced file system, ReFS. Read more about this file system here: http://blogs.msdn.com/b/b8/archive/2012/01/16/building-the-next-generation-file-system-for-windows-r...

[former_SQLA_member1694872 · Accepted solution]

To conclude, I applied as directed current log file in the copy of the database and it worked perfectly. Thank you.

Below result:

Starting database "DATABASE" (C: DATABASE DATABASE.DB) Fri Oct 19 2012 at 14:56 Database recovery in progress Last checkpoint at Mon Oct 15 2012 11:00 Checkpoint log ... Performance warning: Database file "C: DATABASE DATABASE.DB" Consists of five disk fragments Transaction log: database.log ... Rollback log ... Checkpointing ... Starting checkpoint of "DATABASE" (DATABASE.DB) Fri Oct 19 2012 at 15:02 Finished checkpoint of "DATABASE" (DATABASE.DB) Fri Oct 19 2012 at 15:02 Recovery complete Database "DATABASE" (DATABASE.DB) started at Fri Oct 19 2012 15:02 Performance warning: Page size too small for database "DATABASE" Database server started at Fri Oct 19 2012 15:02 Trying to start SharedMemory link ...

 SharedMemory link started successfully

Trying to start NamedPipes link ...

 NamedPipes link started successfully

Trying to start TDS (TCPIP) link ...

TDS (TCPIP) communication link not started

Now accepting requests