Post reply

row size

  • December 7, 2011 at 9:00 am

    #264418

    I have a database that I inherited (don't we all), and it contains a VARBINARY(MAX) field that allows users to "Attach" files to a specific record inside of the database. However, we have had some [Insert choice word here] who have decided that it would be an AWESOME idea to store GB moves in the record, which it wasn't intended to do.

    Is there a way to go through and find the space that is being used so I can find each of these "attachments" and remove them?

    Thanks,

    Fraggle

  • December 7, 2011 at 9:09 am

    #1418197

    SELECT DATALENGTH(BigAssColumn) AS BytesUsed FROM dbo.PoorTable

    Of course, this will scan the whole table. So however many GBs you have in there it'll be read from disk (assuming not already in cache).

    This one you might want to run off hours.

  • December 7, 2011 at 9:15 am

    #1418200

    Thanks Ninja. And thanks for mentioning the performance hit. I am sure it will be an awesome one over 120m some odd records. 🙁

    Tenagra

  • December 7, 2011 at 9:19 am

    #1418205

    Fraggle-805517 (12/7/2011)

    Thanks Ninja. And thanks for mentioning the performance hit. I am sure it will be an awesome one over 120m some odd records. 🙁

    Tenagra

    In that case I'd do a select into to save the output. Save the ID and Lenght.

    I'm sure you'll have a lot of playing with this data and excel to save the output is not really an option :-D.

  • December 7, 2011 at 9:37 am

    #1418220

    Ninja's_RGR'us (12/7/2011)

    Fraggle-805517 (12/7/2011)

    Thanks Ninja. And thanks for mentioning the performance hit. I am sure it will be an awesome one over 120m some odd records. 🙁

    Tenagra

    In that case I'd do a select into to save the output. Save the ID and Lenght.

    I'm sure you'll have a lot of playing with this data and excel to save the output is not really an option :-D.

    Actually, I was going to have it setup in the new tabular model of SSRS, on the same server, so as I read the GB of data from the table, I then of course put all of the data into the tabular model, which puts everything in memory. :w00t:

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply