Author |
Topic |
merrittr
Starting Member
10 Posts |
Posted - 2013-07-22 : 10:35:35
|
HelloDELETE FROM [SCS_NAV2009R2_PROD].[dbo].[Payroll Ledger Entry]gives this errorMsg 9002, Level 17, State 4, Line 1The 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 helpsnow i have set the log to autogrow and set the max size to 2tbwhat 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 HelloDELETE FROM [SCS_NAV2009R2_PROD].[dbo].[Payroll Ledger Entry]gives this errorMsg 9002, Level 17, State 4, Line 1The 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.databasesnow i have set the log to autogrow and set the max size to 2tbwhat 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 commandthere 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 Tablecreate table parent (id int primary key);--Child Table has FK constraintcreate 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 Tablecreate table parent (id int primary key);--Child Table has FK constraintcreate 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 MVPhttp://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 Tablecreate table parent (id int primary key);--Child Table has FK constraintcreate 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Haha, you are a good translatorDatabase Development MCTS, MCTIP |
|
|
merrittr
Starting Member
10 Posts |
Posted - 2013-11-25 : 09:22:16
|
I ended up doing it small slices , kind of a pain but it worked. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-11-30 : 15:46:55
|
You are welcome. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|