Cannot delete records in high cardinality table

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

Cannot delete records in high cardinality table

Author

Topic

merrittr
Starting Member

10 Posts

Posted - 2013-07-22 : 10:35:35

Hello

DELETE FROM [SCS_NAV2009R2_PROD].[dbo].[Payroll Ledger Entry]

gives this error

Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'SCS_NAV2009R2_PROD' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

~ 7,000,000 records if that helps
now i have set the log to autogrow
and set the max size to 2tb

what else can i do to get this going?

sigmas
Posting Yak Master

172 Posts

Posted - 2013-07-22 : 10:37:33

did you try truncate?
truncate table [SCS_NAV2009R2_PROD].[dbo].[Payroll Ledger Entry]

Database Development MCTS, MCTIP

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-22 : 10:39:32

quote:
Originally posted by merrittr

Hello

DELETE FROM [SCS_NAV2009R2_PROD].[dbo].[Payroll Ledger Entry]

gives this error

Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'SCS_NAV2009R2_PROD' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

now i have set the log to autogrow
and set the max size to 2tb

what else can i do to get this going?

If log file is full, the database is practically non-functional. So the first thing you need to do is to add some space to it (either adding disk space, or increasing the max size if you have one set or both perhaps).

Second, you want to see why your log file is growing and becoming full - whether it is just this query that caused it or whether it has been building up for some time. If you are in FULL or BULK-LOGGED recovery models, and if you have not been taking log backups your log file will keep on growing. You need to address that.

For the deleting a large number of rows, delete in small chunks. And if you are in full or bulk logged recovery models, take frequent log backups.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-22 : 10:41:20

quote:
Originally posted by sigmas

did you try truncate?
truncate table [SCS_NAV2009R2_PROD].[dbo].[Payroll Ledger Entry]

Database Development MCTS, MCTIP

Yeah, or what sigma's suggested :)

If you are trying to delete all the rows in the table, that would be the best option. In some cases you may not be able to do that because of foreign keys etc. In some cases you may not want to (if you have identity columns and you want to preserve the next identity value). But if you are allowed to, truncating would be painless.

merrittr
Starting Member

10 Posts

Posted - 2013-07-22 : 10:42:53

I will try truncate command

there is currently 79gb of 200gb on that disk so that isnt the problem

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 10:59:56

TRUNCATE cannot be used if the tables has FK-constraints.

Try to delete in smaller batches.

DELETE	TOP(10000)
FROM	[SCS_NAV2009R2_PROD].[dbo].[Payroll Ledger Entry];

WHILE @@ROWCOUNT = 10000
	DELETE	TOP(10000)
	FROM	[SCS_NAV2009R2_PROD].[dbo].[Payroll Ledger Entry];

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

sigmas
Posting Yak Master

172 Posts

Posted - 2013-07-22 : 11:17:07

>>
TRUNCATE cannot be used if the tables has FK-constraints.
<<

No, the Child table has a FK constraint. but we can use truncate for Child table.

--Parent Table
create table parent (id int primary key);

--Child Table has FK constraint
create table child
(id int primary key,
parent_id int
constraint FK
references parent(id)
);

truncate table child
--Command(s) completed successfully.

Database Development MCTS, MCTIP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 11:25:04

quote:
Originally posted by sigmas

>>
TRUNCATE cannot be used if the tables has FK-constraints.
<<

No, the Child table has a FK constraint. but we can use truncate for Child table.

--Parent Table
create table parent (id int primary key);

--Child Table has FK constraint
create table child
(id int primary key,
parent_id int
constraint FK
references parent(id)
);

truncate table child
--Command(s) completed successfully.

Database Development MCTS, MCTIP

What he suggested was about the table which is referenced by a FK constraint. Try your truncate on parent and you'll understand what he meant!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sigmas
Posting Yak Master

172 Posts

Posted - 2013-07-22 : 15:49:17

quote:
Originally posted by visakh16

quote:
Originally posted by sigmas

>>
TRUNCATE cannot be used if the tables has FK-constraints.
<<

No, the Child table has a FK constraint. but we can use truncate for Child table.

--Parent Table
create table parent (id int primary key);

--Child Table has FK constraint
create table child
(id int primary key,
parent_id int
constraint FK
references parent(id)
);

truncate table child
--Command(s) completed successfully.

Database Development MCTS, MCTIP

What he suggested was about the table which is referenced by a FK constraint. Try your truncate on parent and you'll understand what he meant!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs