![]() |
![]() | ![]() | ![]() | ![]() | ![]() | ![]() | ![]() |
| Welcome to Windows Vista Forums. Our forum is dedicated to helping you find solutions with any problems, errors or issues you are experiencing with Windows Vista. The Vista forum also covers news and updates and has an extensive Windows Vista tutorial section that covers a wide range of tips and tricks. |
| |||||||
![]() |
| |
| | #1 (permalink) |
| | SQL CommandTimeOut problem Hi, We recently Virtualised our servers. Basically we had two physical servers and now we have two vertual servers. We run a couple of Web applications on the VM server. We had everything running fine for a while - until we got a timeout problem with a search function. Basically a page doing a search call to SQL server and that took more than the default SQL Command Execution timeout which is set to 30 secs my MS. To resolve that problem, we set CommandTimeout property of the SQLCommand object as well as the SQLConnection object - to 0 - this makes the timeout to be unlimited. The code snippet below shows what we did: Note: that the CommandTimeout is set at both the SQLConnection and SQLCommand levels !! Quote: Quote: Quote: >>> code snippet follows ... ("Database=myDB;Trusted_Connection=True;Connection Timeout = 0;") mCommand = New SqlCommand(strStoredProcedureName, aConnection) mCommand.CommandType = CommandType.StoredProcedure mCommand.CommandTimeout = 0 <<<... The question is: this stuffed up the Virtual Server memory. The connection seems to consume the memory really big time. From 4GB RAM down to 1GB RAM left in no time whenever we run our application making use of the above SQL Connection Statements. We are not sure what caused the problem - but we decided to roll-back to our old code and remove the above SQL CommandTimeout Statements and left it to the default 30secs. This seemed to run the application and RAM consumption as normal and happy.degradation which is of course We suspected the above CommandTimeout code snippet to have the caused the problem. Any changes to any other code did not cause any memory problems. ==>> Also - the problem was apparent only in the Virtual Servers network setup. ===> That is the application could run fine when hoseted on a local IIS (personal web server) ===> making calls to the same SQL server ... Will someone please tell me what exactly happened. Can the CommandTimeout setting affect the RAM so much? Please also CC me your reply to => billion.mohammed@newsgroup Thanks. Billion. |
My System Specs![]() |
| | #2 (permalink) |
| | Re: SQL CommandTimeOut problem Sure. I can tell you what happened. Each "connection" to your web server creates a session. Sessions consume memory. Before, sessions timed out and ended. Now, Sessions just hang. User reconnects and gets a new session. My guess is you are running about three times the concurrent sessions you did before, with many of the sessions hung waiting on SQL. Solution: Find and fix the timeout, don't just mask it by setting the timeout to unlimited. -- Geoff N. Hiten Principal SQL Infrastructure Consultant Microsoft SQL Server MVP "microsoft.public.virtualisation" <billion.mohammed@newsgroup> wrote in message news:e7571df6-58a7-4c37-a3f7-716f1fd11f51@newsgroup Quote: > Hi, > > We recently Virtualised our servers. Basically we had two physical > servers and now we have two vertual servers. > > We run a couple of Web applications on the VM server. We had > everything running fine for a while - until we got a timeout problem > with a search function. Basically a page doing a search call to SQL > server and that took more than the default SQL Command Execution > timeout which is set to 30 secs my MS. > > > To resolve that problem, we set CommandTimeout property of the > SQLCommand object as well as the > SQLConnection object - to 0 - this makes the timeout to be unlimited. > > > The code snippet below shows what we did: > Note: that the CommandTimeout is set at both the SQLConnection and > SQLCommand levels !! > Quote: Quote: >>>> code snippet follows ... > aConnection = New SqlConnection > ("Database=myDB;Trusted_Connection=True;Connection Timeout = 0;") > > mCommand = New SqlCommand(strStoredProcedureName, aConnection) > mCommand.CommandType = CommandType.StoredProcedure > mCommand.CommandTimeout = 0 > > <<<... > > The question is: this stuffed up the Virtual Server memory. The > connection seems to consume the memory > really big time. From 4GB RAM down to 1GB RAM left in no time whenever > we run our application making > use of the above SQL Connection Statements. > > We are not sure what caused the problem - but we decided to roll-back > to our old code and > remove the above SQL CommandTimeout Statements and left it to the > default 30secs. > > This seemed to run the application and RAM consumption as normal and > happy.degradation which is of course > > We suspected the above CommandTimeout code snippet to have the caused > the problem. > > Any changes to any other code did not cause any memory problems. > > ==>> Also - the problem was apparent only in the Virtual Servers > network setup. > ===> That is the application could run fine when hoseted on a local > IIS (personal web server) > ===> making calls to the same SQL server ... > > > Will someone please tell me what exactly happened. > > Can the CommandTimeout setting affect the RAM so much? > > Please also CC me your reply to => billion.mohammed@newsgroup > > Thanks. > > Billion. |
My System Specs![]() |
![]() |
| Thread Tools | |
| |
Similar Threads | ||||
| Thread | Forum | |||
| the problem with ndis.sys Blue Screen of Death problem solved? | Vista General | |||
| Re: Windows Mail Attachement Problem and Adobe Player Problem with IE8 | Vista mail | |||
| Generic McAfee Problem Message in Vista Problem Reports | Vista performance & maintenance | |||
| fixed unable to delete email problem, now I have new problem | Vista mail | |||
| Vista Upgrade Problem - Windows Explorer Loop problem | Vista installation & setup | |||