Doing re-indexing on transaction table again application works fine

1

I have an OLTP database using platform Sql Server 2012, This application issues policy when transaction completed. Last week I have faced an issue that Issuing of policies has stopped suddenly while there is no lock on any tables but when doing re-indexing on transaction table again application works fine OR after re-starting of SQL SERVER services application works fine. Kindly suggest what can be possible cause.

1 Like
2

it could be one of many things
lets start from bottom up

  1. what kind of server is it as far as disk space, partitioning of data and log files and ram etc, processor, virtual server? other competing servers on the virtual host
  2. any jobs running in background during working hours that could lock up table(s)
  3. could there be some long running query?
  4. Do you maybe have some store procedure or other data manipulation code that is failing and there is a long rollback happening? Do you have batch processes with BEGIN TRANSACTION; COMMIT TRANSACTION;

to name a few