| Author |
Topic |
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
 Posted - 2005-10-20 : 18:16:54
|
| SQL DBA Guru: (at least I think its that complicated).A database on our server is used for storing blob images of reports. Its on a specific filegroup separate from our application data (which is in a different database. This blob database has grown to over 16 gig. No biggy. However, this morning, suddenly it wouldn't allow for any report to be saved to it that was larger than 1k or so. This exact code has been working for nearly two years as is. We decided to cleanse some of the records in this database to shrink the db, this actually helped!?The thing is we ran DBCC CheckDB on it, there is 25 gig availabled on the server, and small inserts to the db went fine.Anybody have trouble ever?________________________________________________SQL = Serious Quaffing of Liquor |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-20 : 19:40:31
|
| What is the error that they are getting?Tara |
 |
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2005-10-20 : 19:57:04
|
quote:
Originally posted by tduggan
What is the error that they are getting?Tara
No error, just a timeout by the proc that does the writing.No error in the logs, the server os event didn't show anything, and our traces didn't show anything either(ouside of the timeout).Is there some undocumented proc to work on/maintain BLOB tables? Once we cleaned out some of the old data, we were back in business.Hopefully this will help get us closer to having a file server for the data, and get it out of our db, but that is a long ways off. Any thoughts, pointers?________________________________________________SQL = Serious Quaffing of Liquor |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-20 : 20:03:54
|
| There is no undocumented proc to maintain the data. Was the table heavily fragmented? Can you post the code?Tara |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2005-10-21 : 09:10:02
|
quote:
Originally posted by tduggan
There is no undocumented proc to maintain the data. Was the table heavily fragmented? Can you post the code?Tara
It wasn't heavily fragmented. You might have to throw me a bone on "the code" you want. The .net app simply has a byte array that they save to the table ( in chunks) with the BLOBs in it. We could do a manual insert of small data, but when the application tried to save a 6kb file to it, it would die. We then simply deleted several thousand records from the table and things were back in business. Again, the strangest part is that this has been running for nearly two years. And nothing has changed on the production server for several weeks.________________________________________________SQL = Serious Quaffing of Liquor |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-10-21 : 12:44:48
|
| You say there were 25 Gig available on the Server, but what about within the database? Is it a fixed size and wouldn't take any more? Is it set to Auto-Grow and was just taking so dang long to complete the growth?---------------------------EmeraldCityDomains.com |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2005-10-21 : 13:04:14
|
quote:
Originally posted by AjarnMark
You say there were 25 Gig available on the Server, but what about within the database? Is it a fixed size and wouldn't take any more? Is it set to Auto-Grow and was just taking so dang long to complete the growth?---------------------------EmeraldCityDomains.com
Yeah, it was set to autogrow (i think 10%).Another DBA found an article that said when dealing with dbs like this, you should do an initial allocation of much more than anticipated so that fragmentation couldn't occur on the OS (cause defragging a production database isn't a good option), with an large initial allocation, the os would give the db the space together and then fragmentation would be much more manageable.its a fun one, scares me because its working, but the problem nor the solution seem real clear.________________________________________________SQL = Serious Quaffing of Liquor |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-10-21 : 14:01:08
|
| That article the DBA found applies pretty much across the board for databases. For best performance, size the database as large as you think you'll need for a while so that it is pre-allocated and doesn't fragment files. Regardless of whether the database contains images or regular varchar fields.I probably wouldn't rest too well either until I was able to repeat it. Got a separate test server you can use to explore the sizing issue and see what you get?---------------------------EmeraldCityDomains.com |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2005-11-03 : 12:17:50
|
| Continuation on the topic:We have server side traces running and I setup a utility to use textcopy to insert jpg images into the db. After a few thousand inserts, we started gettting connection timeouts. Not sure why. I then did a size increase of the filegroup where the blob data is stored (a couple of gig and turned auto grow off), it appears to be working for the time being.Question: I know Blobs aren't stored in the database but on the file system (A SAN in our case) but the filegroup holds the blobs, would auto grow have issues with the blob fields, how does auto grow operate when blobs are in the table?I am doing additional testing, but just hoping for that one person that has gone through this headache and can give some pointers.________________________________________________SQL = Serious Quaffing of Liquor |
|
|
|
|
BLOB Database Problems