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.
it could be one of many things
lets start from bottom up
- 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
- any jobs running in background during working hours that could lock up table(s)
- could there be some long running query?
- 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