OK it's a long story. We have task which run permanently on a customer site.
This task has SQL server as a back end.
sometimes users complain that database become too big (more that 2 g) and crashed with error 1190 (sometimes it runs for months).
I inserted code which close all open views and cursors after each loop and run pack database.
So far so good. But recently i noticed that when i close all tables in data base (in all data sessions) SQL server react very strange.
It changed SPID of this process and changed record in sys.sysprocesses view for this process.And this cause problem for me because when this process start i record some fields from corresponded sys.sysprocesses in our sql table and later use those fields to show information to user.
After record in sys.sysprocesses is changed my join with this table is not working (i use spid and login_time fields).
It means to keep record in sys.sysprocesses intact i cannot close all open tables but with some tables are open i cannot issue
PACK DATABASE command.
Can somebody help with this issue.
Thank you. Alex.