February 13, 2008 at 11:34 am
I have a .NET based windows application (SQL Express 2005 as a database) and have couple of hundreds users have the application installed. Everyone user has there own SQL Express 2005 installed locally and all the data reside on user's local machine/database.
The application was running using SQL default (MSSQLServer) instance previously but now we created a named instance (SQLHES) and moved all the databases to named instance.
Few users are complaining that they got this error "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding" every now and then. Can someone please help me what could be wrong. Is is something to do with the migration of data from default to named instance. We never encountered this issue when we were using default instace.
Please help!!!!
February 13, 2008 at 11:36 am
how is the application connecting to the SQL Server? Maybe your connection string needs modified.
February 13, 2008 at 12:19 pm
The connection string is below
connectionString="Database= ;Server=.\SQLHES;Integrated Security=SSPI;"
providerName="System.Data.SqlClient
We never have any issue when we were using default instance with the same connection string (Server = .)
Any other thoughts??
February 13, 2008 at 2:54 pm
Is your new named instance running on the same box as your old default instance? And is your old default instance still running?
We created a named instance on our production server to run parallel to our default instance. We ran into some resource issues that caused our queries to run longer. If your queries are already pushing the limits the extra strain on the resources may be an issue.
If you are running both instances at once you could take a look and see if you have enough memory for both of them to pull their "max memory".
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 14, 2008 at 8:52 am
Thanks for your reply Kenneth.
Yes, named instance is running on the same local machine and both named and default instance are running.
How can we set up to run the both instance running on the max memory? If we stopped or disabled the default instance will that work or be helpful? Please advise.
Thanks,
February 14, 2008 at 8:56 am
The default instance is probably eating up too many resources. I'd disable it unless some other application needs it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 14, 2008 at 8:57 am
If you arn't using your default instance anymore I would definatly stop it. Although be warned that SSIS is instance un-aware and anything that you store on the "server" is stored on the default instance unless you specify otherwise. If you need me to I can find the article that tells you how to point it somewhere else.
That being said if you ARE using your default instance then you may need to add more memory to your server, or you could try lowering the max memory on the default instance if it isn't being used as often.
If its an option you could at the very least try disabling the default instance for a couple of days and see if your queries start running better.
In the mean time you may want to look at your queries that are timing out .. maybe do a trace looking for long running queries and start tuning them. Maybe add some new indexes etc.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 14, 2008 at 8:59 am
You might even consider uninstalling the default instance altogether -
http://support.microsoft.com/kb/909967
Tommy
Follow @sqlscribeFebruary 14, 2008 at 12:06 pm
Thanks all for the reply. I will mostly likely end up un-installing the default instance since no other application is using it at the moment.
I have one more question though related it. Because of the Timeout, I noticed that the log file (.LDF) file become so huge three times the size of the database. How can I shrink the log file to the minimum. Can I just truncate all the data in the log file. Please let me know.
Thanks,
Syed
February 14, 2008 at 12:08 pm
dbcc shrinkfile
February 15, 2008 at 11:08 am
Syed Anis (2/13/2008)
The connection string is belowconnectionString="Database= ;Server=.\SQLHES;Integrated Security=SSPI;"
providerName="System.Data.SqlClient
We never have any issue when we were using default instance with the same connection string (Server = .)
Any other thoughts??
Timeouts frequently mean that the request is not being heard. Check some of these.
Different port? Is TCP/IP allowed? Could the local Firewall be a problem?
March 7, 2008 at 8:09 pm
I have seen similar issues on our webserver when it was trying to hit the database network. I tried the shrinking the transaction log on the database server that fixed it. But I also noticed a lag in the network to the database server. You might want to inspect your network connection via ping and traceroute. Good Luck
March 9, 2008 at 11:25 am
The process you are going to need to following to shrink the log file is:
1) Change recovery model to simple
ALTER DATABASE database SET RECOVERY SIMPLE;
2) Shrink the transaction log
DBCC SHRINKFILE ('log file', size);
3) Determine whether or not you need full recovery model
If not, then you can stop here - if yes, then reset using:
ALTER DATABASE database SET RECOVERY FULL;
If you need the database to be in full recovery model, then make sure you also setup frequent transaction log backups to keep the log file at a reasonable size. If you don't do this, the next issue you are going to be dealing with is the users calling saying they no longer have any space available.
A couple of other things to look at:
Do you have auto shrink turned on? If so, you need to determine whether or not this is needed. On server installations this is something you never want to do, but on local installs - it might be. Just note that there will be a performance penalty when the system determines that it needs to shrink the database.
Do you have auto close turned on? Again, if so - determine whether or not this is required or needed. With auto close - the database will be 'closed' every time the users exits your application and started everytime they start the application.
HTH,
Jeff
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 12, 2008 at 5:32 pm
We have similar issues with our software, which is written in VB.NET
If you are the programmer of the .NET code, take a look at the commandtimeout property on the troublesome commands in your program.
.NET will issue a command to SQL server, and if the command has not completed during the timeout period (the default timeout is 30 seconds), .NET aborts the command and throws the command timeout exception. This prevents your software from waiting forever on a runaway SQL command.
If you're catching the exception and trying again, you can easily get caught in an infinite loop.
Bump that default time to 60 or 90 seconds and see if it doesn't fix the problem.
It also may be an indication you need to optimize the SQL code you are sending, but thats a whole different discussion.
Take a look at http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(VS.71).aspx
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply