SQL Server Performance

How to find the image size stored in table .. datalength did not work !

Discussion in 'SQL Server 2005 General DBA Questions' started by raagi2000, Jun 29, 2009.

  1. raagi2000 New Member

    Hi,
    I am at SQL 2005 . we have a table that stores pdf files along with file id. it is of data type of image. we are in the process of archieving the old images and we need to find the size of individual file size in the row and then sum up and come with total size that we can possibly save.
    Select fileID , Datalength (Image_field) /1024 AS "SIZE_IN_KB" from table_name
    FileID Uniqueidentifier
    Image_Field image
    does not report the right size . NOT EVEN CLOSE.
    is there any work around... we are running out of san space....
    thanks for your help.
  2. moh_hassan20 New Member

    you can work around and use sp_spaceused to get the size of table data
    exclude the computed datalength of other fields from that size.
  3. raagi2000 New Member

    Hi ,
    We don't want table size. let me explain a bit more , we are in the process of archiving based on createdate . the table also has fileid and data fields. we are trying to get how much space we save month by month based on year. so we know how much data we are archiving.
  4. moh_hassan20 New Member

    i used datareader in c# to get size of the blob of type varchar(max) and give me same result as datalength in T-SQL
    to avoid truncation, try to run
    Select fileID , Datalength (Image_field) /1024.0 AS "SIZE_IN_KB" from table_name
    Select fileID , Datalength (Image_field) AS "SIZE_IN_Bytes" from table_name
  5. FrankKalis Moderator

    Just out of curiosity: This seems to be an updated database from SQL Server 2000. The use of the image datatype suggest this. If I remeber correctly, there used to be a bug in SQL Server 2000 where space once occupied by blob data was never released, even though you have deleted the corresponding data.
    Did you and if so how did you upgrade the database to SQL Server 2005?

Share This Page