Delete / Replace Blobs

Question

Delete / Replace Blobs

jtetler

SSC Enthusiast

Points: 132
More actions
March 29, 2010 at 10:49 am

#220468

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

Viewing 15 posts - 1 through 15 (of 21 total)

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

can you show us the table definition that contains the blobs? ie CREATE TABLE....

if there is a column in the table that has the date in it, you should be able to create a DELETE statement fairly easily...

the only caveat is you want to delete duplicates of the scans that are older than 6 months....

it might be someone on maternity leave, for example, last signed in 7 months ago, and that's the only scan, so you don't want to remove that one.

with the table structure , and maybe a few rows of sample data, we can help you.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jtetler SSC Enthusiast Points: 132 More actions · Answer 2

The table in question has the following columns:

ImageID

CustomerID

IDimage - blob

FaceImage - blob

I can figure out from the transactin DB the ImageID that I want as my starting point.

There are no contingencies that would create a problem by deleting old rows

Thanks

Lowell SSC Guru Points: 323546 More actions · Answer 3

no...i really need you to actually script out the table; pseudocode doesn't help...for example there is no "blob" data type in SQL ...it could be TEXT/IMAGE/varbinary(max), for example;

Also I didn't see any date in the little bit of data you posted;

i'm looking for something like this:

CREATE TABLE SOMETABLENAME(

ImageID int identity(1,1) NOT NULL PRIMARY KEY,

CustomerID int REFERENCES SOMEOTHERTABLE(CustomerID ),

IDimage image,

FaceImage image,

otherColumns varchar(50) )

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jtetler SSC Enthusiast Points: 132 More actions · Answer 4

Lowell, 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]

There is no date to query on - I've searched the Transaction DB for ImageID or something similar and couldn't find it - in fact there doesn't seem to be any dependency between the two - the program runs fine with an empty Image DB - like I said, its historical images. I can determine the ImageID for 6 months ago from an old backup so that would be my starting point.

Thanks,

Jim

Lowell SSC Guru Points: 323546 More actions · Answer 5

well too bad about no date so we can't keep the last x months of data; but assuming we can delete all but one(the highest ImageID) for each customer, here's a trio of diagnostic sqls to see what might be eligible or not.

--customers with exactly one backup image

SELECT [CustomerID],COUNT([ImageID])

FROM [tblCustomerImages]

GROUP BY [CustomerID]

HAVING COUNT([ImageID]) = 1

--customers with more than one backup image

--i presume we can delete all but one

SELECT [CustomerID],COUNT([ImageID])

FROM [tblCustomerImages]

GROUP BY [CustomerID]

HAVING COUNT([ImageID]) > 1

--specific id's we could delete?

SELECT *

SELECT ROW_NUMBER() OVER (PARTITION BY [CustomerID]) ORDER BY [ImageID] DESC) AS RW,

[CustomerID],

[ImageID]

)MySubQuery

WHERE RW > 1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 6

jtetler (3/29/2010)
I can determine the ImageID for 6 months ago from an old backup so that would be my starting point.
Thanks,
Jim

So find out what that ID is and delete everything with an ID less than that. Once you've done that, add a date column to the table so you don't run into this problem anymore.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 7

jtetler (3/29/2010)
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.

P.s. I hate thieves like that. THAT functionality should have been built into the product to begin with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jtetler SSC Enthusiast Points: 132 More actions · Answer 8

What I actually need is sample code on how to either delete the selected rows or preferably how to delete the images in those rows.

Thanks,

Jim

Lowell SSC Guru Points: 323546 More actions · Answer 9

jtetler (3/31/2010)
What I actually need is sample code on how to either delete the selected rows or preferably how to delete the images in those rows.
Thanks,
Jim

here's some code; i would delete the rows, but i provided both an update to null and a delete statement:

--to set the images to null, but leave the rows

UPDATE [tblCustomerImages]

SET [IDImage] = NULL,

[FaceImage]= NULL

FROM (SELECT ROW_NUMBER() OVER (PARTITION BY [CustomerID] ORDER BY [ImageID] DESC) AS RW,

[CustomerID],

[ImageID]

FROM [tblCustomerImages]

)MySubQuery

WHERE [tblCustomerImages].[CustomerID] = MySubQuery.[CustomerID]

AND MySubQuery.RW > 1

--to delete the rows out right:

DELETE FROM [tblCustomerImages]

WHERE [ImageID] IN(

SELECT [ImageID] FROM (

SELECT ROW_NUMBER() OVER (PARTITION BY [CustomerID] ORDER BY [ImageID] DESC) AS RW,

[CustomerID],

[ImageID]

FROM [tblCustomerImages]

)MySubQuery

WHERE RW > 1 )

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 10

jtetler (3/31/2010)
What I actually need is sample code on how to either delete the selected rows or preferably how to delete the images in those rows.
Thanks,
Jim

Ummm... I'm thinking the hint about deleting the rows less than the imageID should be pretty simple. And if you decide to just "delete the images in those rows" (using an UPDATE like Lowell did), prepare to be disappointed. LOB's aren't self shrinking just because you delete them. You'll need to at least reorganize the indexes (lord, I hope you have a clustered index on the table) using ALTER INDEX/REINDEX with the ALL option.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jtetler SSC Enthusiast Points: 132 More actions · Answer 11

Lowell,

Thanks for the code. I probably won't get to try it 'til next week or so - I'll be travelling a bit, but I'll let you know how it went. I was just informed that we'll be adding cameras to record transactions & that data will end up in the same DB - I estimate it will grow 2-300MB a week! It looks like I'll be doing quarterly maintenace for sure!

Thanks,

Jim

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 12

jtetler (3/31/2010)
Lowell,
Thanks for the code. I probably won't get to try it 'til next week or so - I'll be travelling a bit, but I'll let you know how it went. I was just informed that we'll be adding cameras to record transactions & that data will end up in the same DB - I estimate it will grow 2-300MB a week! It looks like I'll be doing quarterly maintenace for sure!
Thanks,
Jim

Heh... schedule Lowell's good code to run once a week and you won't have to worry about it at all. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jtetler SSC Enthusiast Points: 132 More actions · Answer 13

Jeff - the index is clustered. I'll probably try both methods & see what kind of space savings I get. We have to keep 30 days of ID images available so that will likely be my maintenance schedule. Right now I'll use old backups to determine the latest index used but I'm sure I'll be able to find a date based correlation in the transaction DB that will allow me to automate.

Thanks,

Jim

Lowell SSC Guru Points: 323546 More actions · Answer 14

you definitely should follow Jeff's advice and add a datetime column to the table; it'll save you a lot of toruble, and is harmless:

ALTER TABLE [tblCustomerImages] ADD [CreatedDate] DATETIME DEFAULT getdate()

[/code

with my code, all it leaves is the latest ID; without that date, there's no guarantee of it having been in the last 30 days.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!