Bank Services Data Integrity Freezes 1

[WalterHeisenberg]

Hello,

We running the financial suite for 5.6 Product Update 2 on SQL Server 08/Windows Server 08. When I run the data integrity check on bank services it constantly freezes after running for 10 minutes or so. I know this module can be slow so I let run for 24 hours but nothing happened and I had a dialogue box letting me know it was not responding.

Now, I'm thinking of running it for each application option 1 by 1 to find the source of the error but aside from loading the data into another database and trying the check there I'm not sure what else can be done.

Has anyone come across this before? Thanks for your time and help.

Regards.

 

[Jay Converse]

1. You shouldn't have to run DIC that often.
2. Do you ever clear checks and post bank recs?
3. Run Dbspy to make sure it's not still running.
4. Dbdump/Dbload may speed it up..

 

[ramam1]

Are you able to open the banking reconcile statements module?

 

[WalterHeisenberg]

We had an unexpected power outage on the accounting server which is why we're running DIC. All users have reported they were not entering anything into any module nor were they posting anything.

To answer the other questions, we clear checks and post banks recs. every month. I still can currently get into the reconcile statements and am able to view the transactions for every bank.

I'll have dbspy loaded so I can see if is still running or not. I plan on doing the DB dump/load as a last resort. Any other feedback is greatly appreciated. Thanks.

 

[Jay Converse]

I was just making sure you didn't have 100,000 uncleared checks.

Dbspy will make it run slowly, but you might see the bad data when it quits.

If you don't like dump/load, an index rebuild is quick.

 

[WalterHeisenberg]

Thanks for the suggestion. We have a large database so I'm still in the process of loading the data into our test company. I'll report back how it goes.

 

[WalterHeisenberg]

So after leaving this running for 15 hours with the DB spy it is still actually working and running through records. This is after reloading the data via dump/load.

How would I go about refreshing the indexes? Does Accpac have something to do this out of the box or should I search for that? Thanks!

 

[ettienne]

Dbload would have rebuilt the indexes - along with the database.
You can run DBCC DBREINDEX in SQL.

http://msdn.microsoft.com/en-us/library/aa258828(v=SQL.80).aspx

You can create a stored procedure to rebuild all tables in a db, google should spit out some examples.

 

[WalterHeisenberg]

Great. Thanks for your help. I'll report back when I can actually get the integrity check to complete.

 

[Jay Converse]

Are you familiar with SQL Profiler? If you turned it on and monitored the test database, you would get similar answers to Dbspy, and you could speed up the process 10-fold. Just start the profiler and turn off Dbspy.

 

[WalterHeisenberg]

Good idea. Going to switch to profiler hoping that this process finishes sometime today. Thanks.

 

[WalterHeisenberg]

Just to wrap this up, DIC did complete but it took 30 hours to do so just for Bank Services. I will be doing some maintenance to try to improve performance. Thanks for everyone's help.

 

[Jay Converse]

Check the database's recovery model, it should be Simple.

 

[ettienne]

Good grief, 30 hours must be a record!

 

[ramam1]

Wow. Do you regularly maintain your indexes?

This is a reliable quality script from a well regarded MS SS guru:

http://sqlfool.com/tag/defrag/