Deleting Blobs -SQL 2005 Express

[Cyber-guy via SQLMonster.com]

I'm on the verge of exceeding the 4GB limit on a 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 relace the ID 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.

I'm hoping there are tools or scripts out there that may help resolve this.

Thanks in advance,

Cyber-Guy

--
Message posted via http://www.sqlmonster.com

[Andrea Montanari]

hi,

Cyber-guy via SQLMonster.com wrote:
> I'm on the verge of exceeding the 4GB limit on a 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 relace the ID 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.
>

you can probably "find" the referenced hystorical rows to be deleted as they
should be someway referenced in the "current" row.. a DDL of the scenario
could help a lot :)
regards
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz
DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools
http://www.hotelsole.com - http://www.hotelsolericcione.de
--------- remove DMO to reply

[Cyber-guy via SQLMonster.com]

Andrea Montanari wrote:
>hi,

>> I'm on the verge of exceeding the 4GB limit on a database. The
>> program we use has 2 databases, the first is customer transactions,

>[quoted text clipped - 11 lines]

>> 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.
>
>you can probably "find" the referenced hystorical rows to be deleted as they
>should be someway referenced in the "current" row.. a DDL of the scenario
>could help a lot :)
>regards

Finding a starting point is easy - there is no date field but I can figure a
starting index point from older backups. I'm new to SQL scripting & the few
scripts I've found are cryptic at best. What I really need is sample code on
how to either delete rows or set images to null (preferable) for all
ImageID's less than a certain #.

I pulled this from SQL 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]

Thanks in advance,
Jim

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-msde/201003/1

[Andrea Montanari]

hi Jim,

ok, [ImageID], the primary key of your table, has been set to be an identity
column, so it's value always increases monotonically.. that means you can
assume [ImageID] always increases in value and in time of insertion.. the
oldest entity in the table will be [ImageID] = 1 (or the minimum value in
the table) and the last on will be the max value in the table..
if you can know the oldest [ImageID] you have/want to keep, you can proceed
like
UPDATE [dbo].[tblCustomerImages]
SET [IDImage] = NULL,
[FaceImage] = NULL
WHERE [ImageID] < IdYouWantToKeep;

where IdYouWantToKeep is the minimun value of the oldest entity in the table
you want to preserve..
this leaves all the entities older than that id with NULL values for the
images attributes..
but you probably can actually delete those entities as they probably are
obsolete and the current value of the CustomerID reference is set to a new
set of images...
if this is the case, you can drop them with
DELETE [dbo].[tblCustomerImages]
WHERE [ImageID] < IdYouWantToKeep;

BTW, I see you probably missed a referential integrity foreign key
constraint in your design, as the [CustomerID] column should probably
reference a row in the Customer table...