SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cannot delete records in high cardinality table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

merrittr
Starting Member

10 Posts

Posted - 07/22/2013 :  10:35:35  Show Profile  Reply with Quote
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?

Edited by - merrittr on 07/22/2013 10:37:20

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 07/22/2013 :  10:37:33  Show Profile  Reply with Quote
did you try truncate?
truncate table [SCS_NAV2009R2_PROD].[dbo].[Payroll Ledger Entry]

Database Development MCTS, MCTIP
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 07/22/2013 :  10:39:32  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 07/22/2013 :  10:41:20  Show Profile  Reply with Quote
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.
Go to Top of Page

merrittr
Starting Member

10 Posts

Posted - 07/22/2013 :  10:42:53  Show Profile  Reply with Quote
I will try truncate command

there is currently 79gb of 200gb on that disk so that isnt the problem
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/22/2013 :  10:59:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 07/22/2013 :  11:17:07  Show Profile  Reply with Quote
>>
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/22/2013 :  11:25:04  Show Profile  Reply with Quote
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
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 07/22/2013 :  15:49:17  Show Profile  Reply with Quote
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



Haha, you are a good translator

Database Development MCTS, MCTIP
Go to Top of Page

merrittr
Starting Member

10 Posts

Posted - 11/25/2013 :  09:22:16  Show Profile  Reply with Quote
I ended up doing it small slices , kind of a pain but it worked.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/30/2013 :  15:46:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You are welcome.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000