[quoted text, click to view]

TRUNCATE TABLE xxx will delete the contents of an entire table (much faster
than a DELETE statement)

If you are desperately low on resources, you may want to try shrinking the
database , and setting to simple recovery-model.

Steven

Hi,

I have SQL Server 2000 and 50 GB databas, on server is 1 GB free hard disk
space. I try to delete data from tables but MS tempdb is growing tu much.
Does anyone know a SQL command which delete for example 1000 entries witch
special clause ?

Thanks,
Fibre Optic

[quoted text, click to view]


You can try:

SET ROWCOUNT 100
DELETE FROM TABLE FOO

Lather, rinse, repeat.

[quoted text, click to view]

[quoted text, click to view]

Hello Steven,

I want to configure autoshrink on tempdb, when I conected to DB via SQL
Query Analizer and execute such a command:
EXEC sp_dboption 'tempdb' , 'autoshrink'
and message is:
autoshrink off

I execute such a command from SQL Query Analizer:
EXEC sp_dboption 'tempdb' , 'autoshrink', 'on'
and message is:
Server: Msg 5058, Level 16, State 1, Line 1
Option 'AUTO_SHRINK' cannot be set in database 'TEMPDB'.
sp_dboption command failed.

It is complicated to anderstand becouse shrink on tempdb executed from MS
SQL Enterprise Managera works WELL (!)

Do you know why EXEC command shows this fatal message ?

Thanks,
Fibre Optic

Fibre Optic (fibre_optic@go2.pl) writes:
[quoted text, click to view]

sp_dboption is obsolete. Use ALTER DATABASE instead:

ALTER DATABASE tempdb SET AUTO_SHRINK ON

You still get an error message, but at least you use relevant syntax.

As for why you get an error message, I guess this is because SQL Server
does not support autoshrink on tempdb. That is probably a good thing.
Overall, autoshrink is a feature you should use with care.

[quoted text, click to view]

But than that is not an autoshrink, but a shrink on demand.

The best solution for your case would be to see your local hardware
vendor, and then move over the database to the new disk.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at