Rollback is taking long time-Nav 4 & SQL Server2005

rmpatel22

We have started Adjust Cost - Item Entries (ACIE) report 2 days back, but it took long time then we expected, and it was blocking other users so we stopped it (End Task) after 48 hours. Now its still rolling back, is there any way to approx time it will take to roll back?

If we restart SQL server, it will affect our data?

We are using Navision 4 SP3 and SQL server 2005 DB.

rmpatel22

Now rollback finished after 4 hrs.

stryk

Hi!

The worst case would be that the ROLLBACK takes as long as the previous transaction - from last COMMIT to CANCELLATION.
During a ROLLBACK the SQL Server compares the Database-File with the Transaction Log data, undoing all changes from the cancelled transaction. This might cause heavy pressure on the Memory and the Disk-Subsystem.

Just shutting down the server will not really help, because SQL Server performs a RECOVERY process when taking a database only, which includes also a ROLBBACK - taking as long - and a ROLLFORWARD ...

You should consider optimizing the system's performance in general (configuration & setup, SIFT!!!, Indexes, etc.) and specifically look into this report, maybe you could split up the processing into smaller chunks ...

Best regards,
Jörg

rmpatel22

Thanks stryk for your replay.

I found another thread on this website how to run this report Itemwise, I did changes accordingly. Now we will run this report in chunks.

We will also try to optimize database for Keys and SIFTs, because we recently migrated from Native to SQL and untill now we have not done any optimization.

Regards,
Rakesh Patel