no log - SQL Server Forums

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

no log

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-12-13 : 00:12:38

hi team,

i want to delete 10 million records from a large table but required that my log size will not increased
is there any session specific configuration that disable logging i that particular delete

challenge everything

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-13 : 00:52:18

CASE1:
If you want to delete ALL records from a table, go ahead with TRUNCATE.(It will give single log record)
http://www.sqlserver-training.com/list-10-difference-between-truncate-delete-statement-in-sql-server/-

CASE2: If you want to delete only specific rows...
If you want to hold onto the past day's data (or past month or year or whatever), then save that off, do the TRUNCATE TABLE, then insert it back into the original table:

SELECT
*
INTO
tmp_My_Table
FROM
My_Table
WHERE
<Some_Criteria>

TRUNCATE TABLE My_Table

INSERT INTO My_Table SELECT * FROM tmp_My_Table

Check this link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80349

--
Chandu

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-13 : 08:10:12

You can use Truncate but it won't work if table has relationship. What recovery model are you using ? If you have simple it won't log much but you need to delete in batch though.

prett
Posting Yak Master

212 Posts

Posted - 2012-12-17 : 01:32:49

I have found an informative article on "How to delete multiple records from a large table without Growing the Transaction Log"? I request you to visit this link to read more: http://datacentricity.net/2012/04/how-to-delete-from-large-tables-in-batches-without-growing-the-transaction-log/

EsmondNorton
Starting Member

5 Posts

Posted - 2014-11-07 : 05:17:04

Problem with database help you repair tools Recovery Toolbox for SQL Server on this site : http://www.sql.recoverytoolbox.com/ [url][/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources