June 2, 2011 at 4:04 am
[font="Tahoma"]Am Getting Connection Time out's at Fully loaded Time of my Database (500GB Space).
Even though we have lots of space available in Database.
Am not Sure it is depends on only one factor.
can anybody have ideas/suggestions/solutions please reply.. !!!
Regards,
Subramanyam V[/font]
June 2, 2011 at 6:22 am
think you're going to have to be more specific here. If you're talking about app/web tier connections timing out for clients, these usually have deafult timeouts, about 30 secs ( in IIS etc. ) , generally sql server does not time out for client connections.
If you mean your server is too busy to accept connections from ssms or similar then you need to work out what has all the resource - cpu/disk/memory or just badly configured.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 2, 2011 at 1:30 pm
advanced tab, on property window for SQL Server, verify the property: Remote Login Timeout, it's specify the number of seconds to wait before retuning from a failed remote login attempt;
June 2, 2011 at 1:32 pm
the default value is 20! if value is less than 20 then try increase!!!
June 2, 2011 at 5:08 pm
I've found that most people are doing the wrong thing by simply increasing the timeout. Do you really want the users to wait for more than 20 seconds? My recommendation would be to fix the code and, possibly, the database design problem behind the scenes. That means find the offending code and fix its performance... and that will sometimes (usually, in my experience), require a rewrite of the offending code. Don't be afraid to spend that time.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2011 at 5:01 am
Jeff Moden (6/2/2011)
I've found that most people are doing the wrong thing by simply increasing the timeout. Do you really want the users to wait for more than 20 seconds? My recommendation would be to fix the code and, possibly, the database design problem behind the scenes. That means find the offending code and fix its performance... and that will sometimes (usually, in my experience), require a rewrite of the offending code. Don't be afraid to spend that time.
I not want that timeout greater than 20, is only to verify if it's less than 20 and if true then change to 20!!!
June 6, 2011 at 10:06 am
check the locks on the database,if resources or locks are using by queries and taking time to login then enable the ROW Versioning in SQL Server
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 6, 2011 at 10:41 am
Syed Jahanzaib Bin hassan (6/6/2011)
check the locks on the database,if resources or locks are using by queries and taking time to login then enable the ROW Versioning in SQL Server
What? I'd disagree totally, unless you mean to say "After What Jeff Moden said....then check the locks..."
As a DBA, if I encounter application time outs, I'd look at the typical performance indicators first: number of rows being returned to the application(poorly designed / is it doing SELECT * From MillionRowTable?), then look towards the DMV's to see the slow running queries and start the tuning process. checking execution plans and SARG-ability of the query parameters typically are the first line of performance tuning, along with proper indexing.
Lowell
June 7, 2011 at 3:03 am
If you're getting a *lot* of timeouts there's another reason to be wary of using read committed snapshot: your tempdb could get rather large (a lot of timeouts might mean a lot of bad code holding long locks or using a lot of temp tables... ask me how I know). I'd go with Jeff's idea first if you're allowed to access the code (ie, if it's not a vendor system - sigheth).
June 7, 2011 at 7:18 am
rfr.ferrari (6/3/2011)
Jeff Moden (6/2/2011)
I've found that most people are doing the wrong thing by simply increasing the timeout. Do you really want the users to wait for more than 20 seconds? My recommendation would be to fix the code and, possibly, the database design problem behind the scenes. That means find the offending code and fix its performance... and that will sometimes (usually, in my experience), require a rewrite of the offending code. Don't be afraid to spend that time.I not want that timeout greater than 20, is only to verify if it's less than 20 and if true then change to 20!!!
I have to admit, that's a bit confusing. If it's less than 20, then it would work without changing from 20, wouldn't it?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 7, 2011 at 8:19 am
I have to admit, that's a bit confusing. If it's less than 20, then it would work without changing from 20, wouldn't it?
yes, really is confusing!!! and i tell more, i'm very confusing!!!
to confuse: timout for remote Login in SERVER with timout in Database!!!!
i'm sorry!!!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply