January 31, 2005 at 6:21 am
Hello,
We have a microsoft cluster set up with several different databases, we have 5 different webservers that connect to the databases nad a very frequently visited site. a couple of days back we starrted experiencing a problem when posting data into one of the databases, the specific database is the biggest one we have, just below 8 gb of data ( all of the data is basically in 2 different tables).
we run w2k advanced serve service pack 4 , ms clustering , sqlserver sp3.
we started getting messages of timeouts from the webservers, so the timeout was because data couldnt be posted in the database.
after trying to figure out what the problem was , running dbcc checkdb etc i found no errors, but decided to restore a backup. , i did so and things worked perfect again. but only for a days time.. this of course made me consider database /table size being a problem, i now deleted the tables that contained the data and scripted new ones, that "solved" the problem of being able to post data , BUT, we need to have the other data in there as well. i am a bit at a loss now , aný clues out there?
Friendly Regards
David
January 31, 2005 at 6:29 am
Well 8Gb for a database isn't very large, maybe the number of rows has an impact. I'd make sure your tables are definite heaps if the issue is with inserts?
Timeouts will almost certainly come from the web app not sql server, I get this often with web apps " The sql server's timing out!!!" - no, your timeout setting of 10 seconds is too short for the query!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 31, 2005 at 7:00 am
Hi Colin,
Thank you for your reply, I would agree with you if it wasnt for this issue occurring now and not before, it seems as if we should have gotten this problem a long time ago , at least once in a while , but it has never occurred before. we use mom and this application is used by many people every day , so it should have happende earlier , sometines bperhaps because of latency due to backups being taken etc , but no, never , but when it reached the magical numbers around 8 gb , it happened, and when i created a new database ( empty ) it didnt happen any more.
thanks for your help so ar , though.
Friendly Regards
David
January 31, 2005 at 2:58 pm
How big can your database grow. Is autogrow on. Is there freespace in the datafile. It sounds like your database is autogrowing the datafile and the application is timing out before the space is allocated. Try making your datafile 10 gig and then see if you continue to have the problem with the timeouts.
Tom
February 1, 2005 at 11:31 am
Right now we're looking at an entire forrest. We need to get to a grove of trees in the forrest. What indexes are on the tables ? Are there primary keys ? How many rows in each table ? Answers to these questions and those on the database size/growth/options set may give us a bit more of a clue as to where to look next.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
February 2, 2005 at 3:27 am
hi guys, thanks for your replies so far.
yes , autogrow is turned on ( to increase by 10% )
interesting idea that it timesout because the time it takes to grow is taking too long , but if that is the case , then the timeout should have solved itself after a while shouldnt it? , i cant say that i think it actually started generating new free space , since i think there was still some 50-100 MB of free space left, but the idea got me thinking though.
the problem is that the 2 tables arent indexed , the table that contains all the data has a blob field with xml data in it, so indexing isnt supported.
the table containing all the data should have about 1500000 rows ( cant count ít though, due to the database swap to a new empty db ) but the new one has about 12000 rows in it after a few days , so in about a years time , tha should be about 1500000 rows again.
we will restore the old one and transfer the data once we have solutions to the problem, thanks again for your time and answers.
Friendly Regards
David
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply