Deleting Records Keeps TIMING OUT ?!?!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Deleting Records Keeps TIMING OUT ?!?!

Author

Topic

kev100
Starting Member

4 Posts

Posted - 2009-02-24 : 22:53:09

Have a table with 6 million plus records.

I really need to delete most of the records....but it keeps timing out.

I am not too familiar with Sql commands....but here's what I've tried:

In the past, this has worked.....I simply would keep submitting it until no other records met the criteria. As long as the TOP number was not too high...it would not time out:

DELETE TOP (10000)
FROM MyTable
WHERE (date < '12/20/2008')

But now.....using almost any TOP number (unless it is just a few hundred) will time out.

-----------------

After referencing this forum...I tried:

set rowcount 10000
select 1
while @@rowcount = 10000
DELETE Table WHERE Date < '12/20/08'

...but the follow error pops up in a window...

Query Definitions Differ
The following errors were encountered while paring the contents of the SQL pane:
The Set SQL construct or statement is not supported.
The query cannot be represented graphically in the Diagram and Criteria Pane.

----------------

I think the database may be beginning to have some performance issues and am really needing to trim this table down.

Am I using correct syntax, etc?

Any help appreciated.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 00:48:45

Use a normal query window for this and NOT a view definition pane...

E 12°55'05.63"
N 56°04'39.26"

kev100
Starting Member

4 Posts

Posted - 2009-02-25 : 13:45:37

I'm using SQL Server 2005 and am not very familiar with the various options.

I'm pretty new to this and am doing well to just open the table up...

Does the normal query window have a particular menu item name or location?

Thanks very much.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-25 : 14:30:57

>>Does the normal query window have a particular menu item name or location?
A lot of ways - one way is the tool bar has a button "New Query". That opens a query window.

Be One with the Optimizer
TG

kev100
Starting Member

4 Posts

Posted - 2009-02-25 : 20:40:07

Thanks very much...I do see that "New Query" button when opening the SQL Server Management Studio.

Sorry to ask such basic questions...but...this is all pretty new....

----------------------

Entering and running a query from the definition pane would (I assume) automatically apply the query to the table in the pane.

However.....when selecting the "New Query" button from that higher level.....I'm guessing the code needs to be a little more specific.

Submitting the previously used...

DELETE TOP (10000)
FROM MyTable
WHERE (date < '12/20/2008')

....does not seem to cut it....it results in the error:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'MyTable'.

And just to be safe...I did not try:

set rowcount 500
select 1
while @@rowcount = 500
DELETE Table WHERE Date < '12/20/08'

...but....using this in the New Query window......does it need to specify which table....something like...

set rowcount 500
select 1
while @@rowcount = 500
DELETE MyTable WHERE Date < '12/20/08'

(...the above resulting in...hopefully....500 records with a value earlier that 12/20/08 in the Date field being deleted from the table "MyTable" ??? )

Any advice greatly appreciated.
THANKS

kev100
Starting Member

4 Posts

Posted - 2009-02-25 : 21:36:09

Just added the following....it seems to be working much better in this area (using the "New Query" button)....have run a "DELETE TOP (200000)" without it timing out.

DELETE TOP (500)
FROM dbo.MyTable
WHERE (Date < '12/31/2008')

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-26 : 03:19:09

There is a combobox containing all the databases for the current servers.
Most often this is defaulted to MASTER database.

Change the database name to your database.

E 12°55'05.63"
N 56°04'39.26"

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-02-26 : 11:31:39

It's also quite possible that as you're deleting, the log file for the database is trying to auto-grow at the same time, but not quite fast enough to keep up with the delete. Had this very problem yesterday trying to delete 3.3 million rows via an off-the-shelf application. Manually increased the log file and the delete processed just fine.

Terry

-- Procrastinate now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources