How to capture SQL query time out error and run loop till query not completed?

My requirement to capture SQL Query time out in SQL statement and run continues loop till query not successful.

Step.1 Run loop.

Step.2 Run loop Query (DBCC Shrink).

Step.3 If shrink command through timeout error run again till not successful.

As I know shrink command not rollback so whatever is complete that will be remain available so next time when we again shrink it will take less time to shrink database that’s why I want to loop this process.

I cannot increase remote query time out due to some reasons.

Please help me to find timeout exception in SQL QUERY 2000 and run loop.

more ▼

asked Sep 12, 2011 at 12:38 AM in Default

avatar image

1.4k 90 93 97

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

You're running a loop of DBCC shrinks from a client app? Is there a particular reason you dislike your database server and your data consumers?

If you're running out of space, it's time to go and buy a hard drive. Unless we're talking SSD's in some type of RAID array, disks are dirt cheap. There's no reason to not add one to deal with space issues. I've got a 2TB external drive sitting on my desktop that cost me $90. You can get more space.

If you are dealing with out of control log files, look to your Recovery Model and the frequency of your log backups. If tempdb is growing out of control, you need to look to the processes that are causing it. Shrinking your databases over & over is extremely problematic.

more ▼

answered Sep 12, 2011 at 05:12 AM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

  • @Grant, but been there, explained that! OP is adamant that a shrink has to be performed on a regular basis.

@sqlchamp - you have proven (in other questions) that you can write loops using cursors, so that part is solved. Unfortunately, error handling in SQL 2000 is poor (at best), so you will have to do something using DTS, where logic for error handling is a little better.

I still think that @Grant's suggestion is the best to go with - buy more diskspace, it is cheaper than messing around with shrinking and will also not negatively affect your database (unlike shrinking).

Sep 12, 2011 at 06:07 AM WilliamD

Ah well.

If it's timing out, then change the settings where it's timing out. It's that simple. If it's the client connection, change the settings in the connection string. If it's DTC, change it there. If you have a timeout on connections on the SQL Server instance, change those.

This shouldn't be a big issue.

Sep 12, 2011 at 07:11 AM Grant Fritchey ♦♦

Thank you so much all of you. I understand that It is not good practice to do shrinking but I have some database size limitation due to this reason I choose monthly shrink option instead of auto shrink.

Sep 12, 2011 at 09:59 PM Sharma

@sqlchamp, I hope you rebuild the indexes after the shrink, otherwise you'll end up with performance issues. If you rebuild the indexes, make sure you use rebuild in tempdb (I hope it exists in SQL server 2000, can't remember, it's a long time since I've worked with databases that have passed extended support) to avoid increasing the datafiles again.

Sep 12, 2011 at 11:52 PM Håkan Winther

MS SQL 2005 onward only we can use sort_in_tempdb option. Is reorg will not fix performance issue after shrink database?

Sep 13, 2011 at 05:51 AM Sharma
(comments are locked)
10|1200 characters needed characters left

Can you explain what you are trying to achieve and for what purpose please?

There are very few reasons to shrink a database and to apply automation to this process, which your question seems to allude to, is a rare situation to be in.

more ▼

answered Sep 12, 2011 at 01:56 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

I have good knowledge about disadvantage of shrinking the database and I do not want to know what are the disadvantages of that's. I have few SQL SERVER 2005 Express Edition on which I want to stop unnecessary flood of increasing database size and control them by monthly shrink process, I also do not want to go with Auto Shrink option.

Sep 12, 2011 at 09:51 PM Sharma
(comments are locked)
10|1200 characters needed characters left

Shrinking usually is a bad idea....

more ▼

answered Sep 14, 2011 at 08:20 AM

avatar image

11 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 12, 2011 at 12:38 AM

Seen: 2539 times

Last Updated: Sep 12, 2011 at 12:38 AM

Copyright 2018 Redgate Software. Privacy Policy