We help IT Professionals succeed at work.

SQL Server 2005 - deleting images from a database

694 Views
Last Modified: 2008-01-09
Database:  SQL Server 2005

We are currently trying to build a SQL statement to delete image records in a database, related to other tables related via foreign keys.  We are successful in deleting regular data from tables, but when it comes to deleting from tables with images stored inside of them, the statement is timing out on us.  I have tried many different solutions (running the SQL code in the code-behind of the windows service, increasing/decreasing the rowcount, but still have the problem of timing out.)

This query is built into a stored procedure, and being called by a windows service that we have created as well.

Here is the error we are receiving:
System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Here is the query being ran:
SET ROWCOUNT 50

WHILE EXISTS
(SELECT DISTINCT ID FROM Images_Table WHERE ID NOT IN (SELECT num FROM CurrentIDs))
    BEGIN
        DELETE FROM Images_Table WHERE ID NOT IN (SELECT num FROM CurrentIDs)
    END
Comment
Watch Question

Top Expert 2007
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi,

Any chance that the database is on autoshrink? That wont be helping performance any if it is.

Regards
  David

Author

Commented:
1)  I don't think that our database is on autoshrink, where would I check that at?  (none of us here are database experts, but are decent at database work.  i know i personally haven't messed with autoshrink before)

2)  I will try this query out shortly, and come back afterwards.

Author

Commented:
hello,

I have an update here, I may be mis-understanding things.  I first left the batch size set as 3, and it only deleted the 3 images.  what we are wanting basically is for this to continue to loop through, until there are no more images that need to be deleted.  so, I tried to increase the batch size to 100, and got the timeout error again.
Top Expert 2007

Commented:
That is exactly the problem !
Don't try to remove everything at once, that will take too long for the batch to come with a result and your connection is timing out on that. So either remove everything in small batches, or increase the timeout on your connection.

Hope this helps ...
Top Expert 2007

Commented:
Glad I could be of any help and thanks for the grade !

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.