truncate table is very very slow

[ehx6]

Hi,

I am importing and exporting extensive data daily to the database. Today, I am experiencing a very slow sql server performance, I wait for ages if I want to truncate a table, I finally cancel the process. I have restarted the server box, and it was fine. but after afew imports and exports, the database become slow, and I am not able to truncate a table. Any idea how to solve it
thanks
ehx

 

[SQLSister]

Are you actually issuing a truncate table command or are you really just deleting all the records? Truncate table does not log the record deletions so it should be virtually instantaneous.

One possibility is that there is a lock on one of records in the table and it is waiting for it to clear.

The other thing to do is run profiler while this is happening and see what exactly is going on with your server, maybe it isn't growing the transaction log fast enough during an import or maybe someone else is doing something that affects the same tables as your imports or which is taking up all your processing power.

Questions about posting. See faq183-874

 

[mrdenny]

Make sure that no one has the table open in Enterprise Manager while you are trying to truncate it. Having the table open in Enterprise Manager can block the truncate from running.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLSister]

Since this just started today, I would also look at whatever has recently changed on the server. Does it havea new service pack, is there a new scheduled job, is there a new user (esepcially one with dbo rights) who might be doing something from Enterprise Manager or QA at the same time you are running this process. Did someone load a new version of the GUI or database to production? If so what changed in that?

Questions about posting. See faq183-874

 

[yksvaan]

Just one point of view. If it is SQL2000, what's the Recovery Model? You find it in Database's properties, Options tab. If your database is not a database for business-transaction software(like ERP) but rather dw/staging/import/export database, the Recovery model should be Simple, not Full. Simple will mean that you don't do transaction-log backups, instead you will do full-database backup everytime. (I think I remember that equivalent option to Simple in SQL7 was to check Truncate log on checkpoints). Which recovery model to use though depends on the nature of data's refreshs, but basicly if it's a dw/etl-staging kind of database, I noticed that Full can sometimes cause difficulties and Simple is anyway normally recommended for these kind of databases.

If it is that the transaction log has become too vast, it takes some step to reduce the size, but I won't go into that, I think there's topics covering this.

One of the most important point was from SQLSister, to use TRUNCATE TABLE to empty a table. One major effect compared to DELETE is that TRUNCATE resets the identity counter("auto-number"), normally this is okay, but I mentioned it just in case..

Cheers