Hello All,
I am currently working on a process of restoring backup and upgrading it to a specific version in a project.
To upgrade the backup to a specific version, I use batch sql files ordered in predefined format. While executing these batch files, I must set databases in single user mode. And, I use Smo(SQL Server Management Objects) to execute these bacth files.
Everthing works fine in virtual PCs, but in one of the PCs, which is used for system test in the project, execution always stops in one of the files and waits for some time (nearly 3 mins). I do not know why this occurs because there is no apparent reason for waiting (Connection is already open and no other application is connecting to sql server.)
After waiting, execution continues, but an exception is thrown. It says that database is already open and user is currently connecting to database. I looked for trace and log files and stopped all applications to make sure that nobody is connecting to database.
Another interesting point is that execution always stops in the same sql file (whose size is about 2500 kb) and the exception is thrown in the same line in this file. Sql statement is just changing stored procedure and functions. There is no problem related with these statements.
I make this process run over web site and whenever IIS is reset, everything works fine.
I am dealing with fixing this problem without reseting IIS, but no solution is successful yet unfortunately.
Any suggestions to fix this problem and why this occurs?