Creating index fail in varbinary column

• amns

  SSCrazy

  Points: 2628

  More actions

  August 16, 2011 at 12:26 pm

  #241782

  Hi!

  I'm creating a non clustered index on a table with images and i want to include an 'image' column to avoid a lookup.

  I updated the 'image' type column to 'varbinary(max)' column, so i can inlcude that column in index.

  Now, I'm getting the following error:

  ------------------------------

  Create failed for Index 'IX_[index_name]'

  Could not allocate space for object 'dbo.SORT temporary run storage: 140738001108992' in database 'db_name' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

  ------------------------------

  I've recently updated that column from 'image' type to 'varbinary(max)' and the table size changed from more than the double: 1.078,828 to 2.882,273 MB. Is this normal?

  I'm using Sql Express.

  Should i use 'image' type or 'varbinary'??

  Hope someone could comment on this..

• R.P.Rozema

  SSChampion

  Points: 12317

  More actions

  August 17, 2011 at 1:16 am

  #1369437

  It is not good practice to include binary columns in an index. Indexes are meant to be re-arranged easily, having large objects in them makes it hard to re-arrange them.

  On the out-of-disk-space message, are you sure you've either set the database options to 'simple' mode or otherwise have a backup plan in place? If not, your log will keep on growing with every change made in the database, eventually making it run out of disk space. Especially these ddl changes, which affect all of the large objects in this table, will make the log grow very fast.

  ---

  
  Posting Data Etiquette - Jeff Moden[/url]
  Posting Performance Based Questions - Gail Shaw[/url]
  Hidden RBAR - Jeff Moden[/url]
  Cross Tabs and Pivots - Jeff Moden[/url]
  Catch-all queries - Gail Shaw[/url]
  

  ---

  
  If you don't have time to do it right, when will you have time to do it over?

• Jayanth_Kurup

  SSC-Insane

  Points: 22967

  More actions

  August 17, 2011 at 1:26 am

  #1369446

  Since your using sql 2008 , you could consider using Filestream

  http://msdn.microsoft.com/en-us/library/bb933993.aspx

  Jayanth Kurup[/url]

• Johan Bijnens

  SSC Guru

  Points: 135369

  More actions

  August 17, 2011 at 1:45 am

  #1369453

  could also be your disk subsystem couldn't handle the file extend within the timeout time sqlserver is waiting for it to complete.

  The extension will have been completed ( if it has the needed space ) afterward, but sqlserver will have generated the message and stopped its current operation.

  Keep in mind these operations are also logged, so I would guess the db enlargement could be considered normal.

  If your server cannot afford that size, you should have a look at file level within that database and shrink the appropriate file. If that is a data file, you should rebuild the objects it contains after the shrink operation.

  Keep in mind you should avoid shrinking ! ( so only do it after planned and considered large operations )

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me