dbi file growing very large

Posted by Admin on 06-Feb-2009 08:49

Hi all,

We have a problem with a dbi file growing out of control. The dbi file has a PID associated with a sqlsvr process, any idea on how we can trace that back to an individual user.

I must say we are also running AppServer, but have determined that the process isn't the Appserver process.

We have done all of the proper setup with secondary brokers etc. the dbi file is growing on the DB server not local to the user even though we have the -T setup to write local to the user.

p.s. we have implemented the -tmpbsize, and this is on a Windoze system, db block size 4096

My question is why would a SQL process create a dbi file if it doesn't use temp-tables?

All Replies

Posted by ChUIMonster on 06-Feb-2009 11:46

What version of progress is this?

Posted by jmls on 09-Feb-2009 01:30

We have the same issue with 10.2A. Most recently, we had a .dbi file top 18GB ...

Posted by rmarshal on 10-Feb-2009 13:11

To trace this back, you can use promon or the _Connect VST. This will show the sonnection device (PC name or IP) that performed the database connection; from there you can further track down the user process (or machine running a automated job)

SQL can make dbi files as depending on ODBC transaction isolation rules, they may be putting a lock on every record read.

Posted by Admin on 10-Feb-2009 18:05

You mention that "SQL process create a dbi file if it doesn't use temp-tables" - the SQL92 uses temp-tables in all types of unexpected ways, other that unions and sub-selects. For example ORDER BY DESC requires a temp-table .

The query plan may help, cant remember if it shows all temp-table use though.

Try using "SET PRO_SERVER LOG ON WITH (STATEMENT, QUERY_PLAN)" (see kcentre P119161 for info). You may want to be selective as to how/when you enable this as it will take a performance hit while it is on, but it should be the quickest way to your answer.

Posted by Admin on 10-Feb-2009 18:10

You mention that "SQL process create a dbi file if it doesn't use temp-tables" - the SQL92 uses temp-tables in all types of unexpected ways, other that unions and sub-selects. For example ORDER BY DESC requires a temp-table .

The query plan may help, cant remember if it shows all temp-table use though.

Try using "SET PRO_SERVER LOG ON WITH (STATEMENT, QUERY_PLAN)" (see kcentre P119161 for info). You may want to be selective as to how/when you enable this as it will take a performance hit while it is on, but it should be the quickest way to your answer.

Posted by Admin on 10-Feb-2009 18:21

You mention that "SQL process create a dbi file if it doesn't use temp-tables" - the SQL92 uses temp-tables in all types of unexpected ways, other that unions and sub-selects. For example ORDER BY DESC requires a temp-table .

The query plan may help, cant remember if it shows all temp-table use though.

Try using "SET PRO_SERVER LOG ON WITH (STATEMENT, QUERY_PLAN)" (see kcentre P119161 for info). You may want to be selective as to how/when you enable this as it will take a performance hit while it is on, but it should be the quickest way to your answer.

This thread is closed