MS SQL Server - truncating tables

Author

truncating tables

Firestarter

2005-04-11, 9:23 am

hi,
I need to delete all the load of data in some tables, so I issued
truncate table PAGES
Error msg came back saying log file is full.
I thought truncate was unlogged. I truncated the log anyway and tried again
but with same result.
Why am I seeing this?
DB is set to simple recovery so I thought checkpoint runs every minute.
Should I maybe make this more frequent?
If truncate table is non-logged, it should make do difference, should it?

Tibor Karaszi

2005-04-11, 9:23 am

TRUNCATE TABLE is not a non-logged operation. It is a minimally logged operation, where deallocation
of pages (extents?) are logged so that they can be undone if in the same transaction.

What you need to do is to investigate size of transaction log file(s), and how full the log is.
DBCC SQLPERF(logspace)

Also, check for old open transactions:
DBCC OPENTRAN
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Firestarter" < Firestarter@discussi
ons.microsoft.com> wrote in message
news:E1A8BBEF-F2B9-4EA7-8638- 0543FB625308@microso
ft.com...
> hi,
> I need to delete all the load of data in some tables, so I issued
> truncate table PAGES
> Error msg came back saying log file is full.
> I thought truncate was unlogged. I truncated the log anyway and tried again
> but with same result.
> Why am I seeing this?
> DB is set to simple recovery so I thought checkpoint runs every minute.
> Should I maybe make this more frequent?
> If truncate table is non-logged, it should make do difference, should it?

Firestarter

2005-04-11, 8:23 pm

Thanks Tibor,

While waiting for a reply I had done as you suggested, (increased size of
log) and itruncate worked.
I read in BOL that it was non-logged - "The TRUNCATE TABLE statement is a
fast, nonlogged method of deleting all rows in a table" - but I can see from
your post and the behaviour I witnessed that isn't true.

regards,

"Tibor Karaszi" wrote:

> TRUNCATE TABLE is not a non-logged operation. It is a minimally logged operation, where deallocation
> of pages (extents?) are logged so that they can be undone if in the same transaction.
>
> What you need to do is to investigate size of transaction log file(s), and how full the log is.
> DBCC SQLPERF(logspace)
>
> Also, check for old open transactions:
> DBCC OPENTRAN
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Firestarter" < Firestarter@discussi
ons.microsoft.com> wrote in message
> news:E1A8BBEF-F2B9-4EA7-8638- 0543FB625308@microso
ft.com...
>
>
>

Tibor Karaszi

2005-04-11, 8:23 pm

Seems BOL is incorrect... Perhaps the more recent BOL update has fixed this documentation error...?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Firestarter" < Firestarter@discussi
ons.microsoft.com> wrote in message
news:364D1119-E69E-4F86-98EF- 827D2DFCD05D@microso
ft.com...[color=darkred]
> Thanks Tibor,
>
> While waiting for a reply I had done as you suggested, (increased size of
> log) and itruncate worked.
> I read in BOL that it was non-logged - "The TRUNCATE TABLE statement is a
> fast, nonlogged method of deleting all rows in a table" - but I can see from
> your post and the behaviour I witnessed that isn't true.
>
> regards,
>
> "Tibor Karaszi" wrote:
>

David Gugick

2005-04-11, 8:23 pm

Tibor Karaszi wrote:
> Seems BOL is incorrect... Perhaps the more recent BOL update has
> fixed this documentation error...?

My BOL (updated) says:

TRUNCATE TABLE
Removes all rows from a table without logging the individual row
deletes.

Remarks
TRUNCATE TABLE is functionally identical to DELETE statement with no
WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is
faster and uses fewer system and transaction log resources than DELETE.

--
David Gugick
Imceda Software
www.imceda.com

Tibor Karaszi

2005-04-12, 3:23 am

Seems they got it right in the update. :-)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"David Gugick" <davidg-nospam@imceda.com> wrote in message
news:uVzfVttPFHA.2736@TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>
> My BOL (updated) says:
>
> TRUNCATE TABLE
> Removes all rows from a table without logging the individual row deletes.
>
> Remarks
> TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all
> rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log
> resources than DELETE.
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com

Sponsored Links