Post reply

TAKES AGES TO DELETE A TABLE!

  • February 3, 2009 at 3:41 am

    #218135

    Hi,

    I have a database that contains 3 inventory tables and 3 inventory backup tables.

    Each morning the system runs a DTS packages to delete the backup tables, copies the inventory data into the backup tables and then imports the new daily data. Each table holds approx 150K items.

    For the last couple of days it appears to have corrupted the table! You can go into design view and see the structure, however, if you use Enterprise Manager to view the table contents it is blank and eventually comes back with an ODBC timeout error!

    Running a DBCC on the database comes back clean, I have to delete the table and re-instate! When you delete the table it TAKES AGES to drop the table! I'm still waiting for it now!!!!!!

    Any ideas?

    Thanks.

    Mike

  • February 3, 2009 at 3:46 am

    #936998

    What do you get if you run a SELECT query from Query Analyzer? Just because EM doesn't return any data does not necessary mean that the table is empty.

    [font="Verdana"]Markus Bohse[/font]

  • February 3, 2009 at 5:25 am

    #937031

    Unfortunately the table has now been dropped!! So unable to test, hopefully this will work ok tomorrow!! If not, will indeed try this.

    Table now re-created, just importing the data via DTS package now!

    Many thanks for you prompt response.

    Mike

    🙂

  • February 3, 2009 at 3:18 pm

    #937608

    Deleting is a logged process with some overhead involved. However, 150K rows is not a lot of data in my opinion. Are you doing a total refresh of the table? If so, why not truncate table instead? It's quicker and logging is minimal.

    -- You can't be late until you show up.

  • February 4, 2009 at 2:25 am

    #937764

    Hi,

    As these are non - critical tables I can afford to delete the table and re-script it and then run my DTS packages to populate the table.

    My concern was why did Enterprise Manager not want to open the table and display the data! Hence my assumption that the table was corrupt!

    I will do a SELECT query on the table before I delete if it happens again.

    Thanks.

    Mike

Viewing 5 posts - 1 through 4 (of 4 total)

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