Using dbtran in a directory of log files

[former_SQLA_member1694871]

I know I have to be missing something here. We are running into an issue where we believe we have a couple users in a company here that are overwriting data they shouldn't be.

We are running our production database in a replication environment, so when we backup our database every hour, we use the -r switch to start a new transaction log.

Anyway, we have about 8,000 log files currently sitting in our database directory.

I need to be able to do a dbtran against all the transaction logs (except the active one), and come up with one large sql file, or multiple sql files that would correspond to each transaction file. That would at least let me do a blanket search to see if this data is being entered, and then overwritten.

I was attempting to use the -m switch to specify the directory that contains the transaction logs, but it kept throwing up the help information from the command line.

I've attached an image of my command line.

Any thoughts on this would be greatly appreciated!!

TIA

Jeff Gibson
Intercept Solutions - Sybase SQL Anywhere OEM Partner
Nashville, TN

[Breck_Carter · Accepted solution]

According to the V12 Help, "If -c is used, dbtran attempts to translate the online transaction log file, and all the offline transaction log files in the same directory as the online transaction log file." so if your 8,000 offline logs are in the same folder as the active log, you should be able simply do this:

"%SQLANY12%\\bin32\\dbtran.exe"^
  -c "ENG=inventory; DBN=inventory; UID=dba; PWD=sql;"^
  dbtran_inventory_sql_1.sql

PAUSE

This works OK when you run it on the computer where the log files are located. In this test, there were two offline logs AA and BB, plus the active log, all located in the same directory.

The displayed output is hugely confusing...

C:\\projects\\foxhound_benchmark_inventory_db>"C:\\Program Files\\SQL Anywhere 12\\bi
n32\\dbtran.exe"  -c "ENG=inventory; DBN=inventory; UID=dba; PWD=sql;"  dbtran_in
ventory_sql_1.sql
SQL Anywhere Log Translation Utility Version 12.0.1.3797
Processing transaction logs from directory "C:\\projects\\foxhound_benchmark_inven
tory_db\\"
Transaction log "C:\\projects\\foxhound_benchmark_inventory_db\\140221AA.LOG" start
s at offset 045301888397
Processing transactions from transaction log "C:\\projects\\foxhound_benchmark_inv
entory_db\\140221AA.LOG"
 46% complete
Transaction log ends at offset 045301892717
Transaction log "C:\\projects\\foxhound_benchmark_inventory_db\\140221AB.LOG" start
s at offset 045301892717
Processing transactions from transaction log "C:\\projects\\foxhound_benchmark_inv
entory_db\\140221AB.LOG"
 96% complete
Transaction log ends at offset 045301896991
Processing transactions from active transaction log
 100% complete
C:\\projects\\foxhound_benchmark_inventory_db>PAUSE
Press any key to continue . . .

But the output *.SQL file contains the correct transactions; in this test, there were two offline logs, each with a single UPDATE SET item_count = 111 and 222, plus the active log with a single UPDATE SET item_count = 333:

--CONNECT-1006-045301892518-DBA-2014-02-21 08:04
--BEGIN TRANSACTION-1006-045301892529
BEGIN TRANSACTION
go
--UPDATE-1006-045301892617
UPDATE DBA.inventory
   SET item_count=111
 WHERE item_id=1
go
--COMMIT-1006-045301892634
COMMIT WORK
go
--CHECKPOINT-0000-045301892648-2014-02-21 08:04
--CHECKPOINT-0000-045301892694-2014-02-21 08:05
--CONNECT-1006-045301896838-DBA-2014-02-21 08:06
--BEGIN TRANSACTION-1006-045301896849
BEGIN TRANSACTION
go
--UPDATE-1006-045301896937
UPDATE DBA.inventory
   SET item_count=222
 WHERE item_id=1
go
--COMMIT-1006-045301896954
COMMIT WORK
go
--CHECKPOINT-0000-045301896968-2014-02-21 08:06
--CONNECT-1006-045301901112-DBA-2014-02-21 08:08
--BEGIN TRANSACTION-1006-045301901123
BEGIN TRANSACTION
go
--UPDATE-1006-045301901211
UPDATE DBA.inventory
   SET item_count=333
 WHERE item_id=1
go
--COMMIT-1006-045301901228
COMMIT WORK
go
--START CHECKPOINT-0000-045301901231-2014-02-21 08:26
--FINISH CHECKPOINT-0000-045301901252-2014-02-21 08:26

(it would be nice if the output named each log file, wouldn't it?)

Alas, that technique does not seem to work for a folder located on a different computer:

"%SQLANY12%\\bin32\\dbtran.exe"^
  -c "ENG=inventory; DBN=inventory; UID=dba; PWD=sql; LINKS=TCPIP(HOST=dgrk3x31;DOBROADCAST=NO)"^
  dbtran_inventory_sql_1.sql

PAUSE

The displayed output doesn't explicitly name the offline logs, and it appears that dbtran is looking for "C:\\projects\\foxhound_benchmark_inventory_db\\" on the local computer where it does not exist, rather than the computer where the logs are located (that's not a user error, nowhere in the dbtran command is the folder specified):

C:\\projects\\$SA_templates\\run\\dbtran>"C:\\Program Files\\SQL Anywhere 12\\bin32\\dbt
ran.exe"  -c "ENG=inventory; DBN=inventory; UID=dba; PWD=sql; LINKS=TCPIP(HOST=d
grk3x31;DOBROADCAST=NO)"  dbtran_inventory_sql_1.sql
SQL Anywhere Log Translation Utility Version 12.0.1.3298
Processing transaction logs from directory "C:\\projects\\foxhound_benchmark_inven
tory_db\\"
Processing transactions from active transaction log
 100% complete
C:\\projects\\$SA_templates\\run\\dbtran>PAUSE
Press any key to continue . . .

The output *.SQL file only contains the UPDATE from the active log...

--CONNECT-1006-045301901112-DBA-2014-02-21 08:08
--BEGIN TRANSACTION-1006-045301901123
BEGIN TRANSACTION
go
--UPDATE-1006-045301901211
UPDATE DBA.inventory
   SET item_count=333
 WHERE item_id=1
go
--COMMIT-1006-045301901228
COMMIT WORK
go

In your case, it may be a moot point. I suspect your 8,000 offline logs are located in a different folder from the active log, so you will need to use dbtran -m.

Here is a test where the offline logs were moved to the "bkup" subfolder under the folder where the active database and transaction logs are located. Like the second example above, dbtran is run on a workstation computer different from the server computer.

With this syntax, the -c must be removed and -n must be appended in front of the output *.sql file name:

"%SQLANY12%\\bin32\\dbtran.exe"^
  -m "\\\\dgrk3x31\\c\\\\projects\\foxhound_benchmark_inventory_db\\bkup"^
  -n dbtran_inventory_sql_2.sql

PAUSE

C:\\projects\\$SA_templates\\run\\dbtran>"C:\\Program Files\\SQL Anywhere 12\\bin32\\dbt
ran.exe"  -m "\\\\dgrk3x31\\c\\\\projects\\foxhound_benchmark_inventory_db\\bkup"  -n d
btran_inventory_sql_2.sql
SQL Anywhere Log Translation Utility Version 12.0.1.3298
Processing transaction logs from directory "\\\\dgrk3x31\\c\\\\projects\\foxhound_benc
hmark_inventory_db\\bkup"
Transaction log "\\\\dgrk3x31\\c\\\\projects\\foxhound_benchmark_inventory_db\\bkup\\140
221AB.LOG" starts at offset 045301892717
Transaction log "\\\\dgrk3x31\\c\\\\projects\\foxhound_benchmark_inventory_db\\bkup\\140
221AA.LOG" starts at offset 045301888397
Transaction log "\\\\dgrk3x31\\c\\\\projects\\foxhound_benchmark_inventory_db\\bkup\\140
221AA.LOG" starts at offset 045301888397
Processing transactions from transaction log "\\\\dgrk3x31\\c\\\\projects\\foxhound_be
nchmark_inventory_db\\bkup\\140221AA.LOG"
 91% complete
Transaction log ends at offset 045301892717
Transaction log "\\\\dgrk3x31\\c\\\\projects\\foxhound_benchmark_inventory_db\\bkup\\140
221AB.LOG" starts at offset 045301892717
Processing transactions from transaction log "\\\\dgrk3x31\\c\\\\projects\\foxhound_be
nchmark_inventory_db\\bkup\\140221AB.LOG"
 99% complete
Transaction log ends at offset 045301896991

C:\\projects\\$SA_templates\\run\\dbtran>PAUSE
Press any key to continue . . .

Now the output *.sql file contains the transactions from both offline logs (but not, of course, the active log)...

--CONNECT-1006-045301892518-DBA-2014-02-21 08:04
--BEGIN TRANSACTION-1006-045301892529
BEGIN TRANSACTION
go
--UPDATE-1006-045301892617
UPDATE DBA.inventory
   SET item_count=111
 WHERE item_id=1
go
--COMMIT-1006-045301892634
COMMIT WORK
go
--CHECKPOINT-0000-045301892648-2014-02-21 08:04
--CHECKPOINT-0000-045301892694-2014-02-21 08:05
--CONNECT-1006-045301896838-DBA-2014-02-21 08:06
--BEGIN TRANSACTION-1006-045301896849
BEGIN TRANSACTION
go
--UPDATE-1006-045301896937
UPDATE DBA.inventory
   SET item_count=222
 WHERE item_id=1
go
--COMMIT-1006-045301896954
COMMIT WORK
go
--CHECKPOINT-0000-045301896968-2014-02-21 08:06

---

Thank you for asking this question! In my efforts to reproduce your symptom I discovered that a SQL Anywhere 12 database running on an older, rather feeble performance test computer had a 32 GB active log file... no big deal, except when the drive capacity is only 74.4 GB... now it has 32.2 GB free space, which tells you all you need to know about what life was like on that computer... before your question, that is, now life is good:

All Clear - Alert #7: The free disk space on the drive holding the transaction log file has fallen below 1G.
All Clear - Alert #6: The free disk space on the drive holding the temporary file has fallen below 1G. 
All Clear - Alert #5: The free disk space on the drive holding the main database file has fallen below 1G.

Again, thank you!

[former_SQLA_member1694871 · Accepted solution]

Breck,

No "Thank You's" Needed!! 🙂

I appreciate your response. Actually the offline logs are in the same directory as the active log. I assume that its safe to say that I should shut the database server down and copy everything to another directory that I can run this dbtran from. I get concerned that running this against an active database could cause issues if it were to possibly crash during the process or anything along the lines of that.

I don't remember what it was, but I do remember now that you needed to be careful of the server side path vs the client side path in some of the utilities.

I will keep you advised on my findings!!

Thanks again!!

Jeff Gibson
Intercept Solutions - SAP Sybase SQL Anywhere OEM Partner
Nashville, TN

[Breck_Carter · Accepted solution]

No need to shut the server down, just copy or move the offline logs... they are inactive, after all 🙂

[reimer_pods]

To translate multiple logfiles to SQL, I've used a DOS loop like the following:

FOR %i IN (*.log) DO "%SQLANY12%\\Bin32\\Dbtran.exe" -t %i %i.sql

This walks through alle the logfiles in the current directory and creates a SQL file adding .sql to the logfile name.

[Former Member]

I believe you'll need to drop the -c connection parameter. Since you are translating old transaction logs, and not the active log, connecting to a server would be erroneous.

If -c is used, dbtran attempts to translate the online transaction log file, and all the offline transaction log files in the same directory as the online transaction log file. Log Translation Utility

If the renamed log files are also in the same directory as the active log, you may need to move/copy them out as well.

[VolkerBarth]

In my understanding, that is a contradiction to the docs: They tell -m can only be used with -n, and -n requires to run DBTRAN against a database server and therefore requires -c to specify the connestion string...

[Breck_Carter]

@Tyson: It's not the -c causing the problem, it's the fact -m requires -n as the screenshot explicitly states.

[Breck_Carter]

@Volker: The Help needs a rewrite. The Help says "-m ... This option must be used in conjunction with the -n option." which is an extremely awkward way of saying "-m requires -n"... most folks read that as "-n requires -m" which is not the case.

[Breck_Carter]

Of course, in this case, he may HAVE to drop the -c, but for an entirely different reason 🙂