Results 1 to 9 of 9

Thread: Deleting Blobs

  1. #1
    Join Date
    Mar 2010
    Posts
    5

    Red face Unanswered: Deleting Blobs

    I'm on the verge of exceeding the 4GB limit on a SQL Express 2005 database. The program we use has 2 databases, the first is customer transactions, the second is a historical database containing images of customer ID's. Each time a customer shows his ID it is updated in the transaction DB and the old ID image is stored in the image DB. I would like to either delete the older blobs or delete the older rows, for example everything prior to 6 months ago. Another option would be to replace the older blobs with a smaller 'image on file' blob. For all practical purposes the older images have no purpose and if I did need them I can restore an older backup.

    I would greatly appreciate any ideas on how to solve this problem. The company that wrote the software wants $$$$7500 to write a one time script to delete rows. Our other option with them is to go to full SQL Server. Given the economy, neither is a valid option.

    Thanks in advance,

    Cyber-Guy

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You will need to run an update on the table that contains historical records with blobs, if you don't want to delete them. Similarly, you can run a delete against the same table. In either case you will need to either have a date field to reference any record that is older than 6 months. After the operation completes (either update or delete), you will need to either drop and recreate a clustered index, or run ALTER INDEX...WITH (LOB_COMPACTION = ON).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Mar 2010
    Posts
    5
    I have no problem selecting the records in question. What I need is a sample script to either delete the image fields or to delete the rows. My preference would be to delete the blobs (or replace them with a much smaller 'on file' blob). I'm new to SQL scripting and the few scripts I've found are cryptic at best. I figure I have 2-3 weeks before I hit the 4GB limit. If I don't come up with a solution by then I'll replace that DB with an empty one. The app runs fine with an empty image DB. That will create issues if I have to access historical data but in 2 1/2 years I haven't had to (knock on wood). Looking forward, the DB will grow much faster as we are adding cameras to capture transactions and they too will end up in the historical DB - I estimate it will grow 2-300MB a week - that will definitely require quarterly housekeeping. Full SQL Server would be so much nicer but licensing for 2 servers is way too much for an app that only has a couple of users for each server.

    Any help would be appreciated.

    Thanks,
    Cyber-guy

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is just a rehash of the response from rdjabarov. Without knowing what your table structures are, it is really hard to give you a simple answer.

    Capture an "on file image" and store it in your database in a record or table that you can easily identify. Figure out how to identify the offending rows (that need their blobs replaced), and replace their image blob with the "on file image" blob. You might have to do this to small groups of rows and rebuild the clustered index or ALTER INDEX in between the groups.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Mar 2010
    Posts
    5
    Pat, here goes (from MS SQL Server Management Studio Express)

    USE [Images]
    GO
    /****** Object: Table [dbo].[tblCustomerImages] Script Date: 03/29/2010 17:26:07 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tblCustomerImages](
    [ImageID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerID] [int] NOT NULL,
    [IDImage] [image] NULL,
    [FaceImage] [image] NULL,
    CONSTRAINT [PK_tblCustomerImage] PRIMARY KEY CLUSTERED
    (
    [ImageID] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    Any help on sample code would be greatly appreciated. I'm a newbie at SQL scripting and the few scripts I found are totally cryptic to me.

    Thanks,
    Cyber-guy

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Cyber-Guy View Post
    I would like to either delete the older blobs or delete the older rows, for example everything prior to 6 months ago.
    That's a bit tricky without a timestamp. All you can identify here is the chronological order of images, not the actual chronology. As such you can delete all non-current images, but you don't know if these are 10 years or 10 seconds old.

  7. #7
    Join Date
    Mar 2010
    Posts
    5
    The DB looks like it adds an image each time an ID is updated. If a customer comes in every day for a month, the image DB has 29 copies of his ID, the transaction DB has 1. The images are added sequentially so using the max value of the index from 6 months ago would only involve rows prior to that point in time. If I add a check box column for 'last run' and check the max id I can use that id as the basis for the next run 6 months later (I'll probably end up with a 30 day cycle) or I could call it 'next run' and check them all & next time delete any row with a checked box.

    Thanks,
    Cyber-guy

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Or just stick in a date column and default to CURRENT_TIMESTAMP()

  9. #9
    Join Date
    Mar 2010
    Posts
    5
    WAY too easy!

    hehehe

    Thanks,
    Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •