Can we delete whole table data without filling log

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

SQL Server 2008 Forums

Transact-SQL (2008)

Can we delete whole table data without filling log

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-04-26 : 02:57:07

Due to some user level permission i can't let truncate allow in my stored procedure.I have to delete whole data from some tables without expanding the log file.
Is it some how possible with any option in sql server 2008?

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-26 : 03:11:27

drop table and recreate?

No, you're never too old to Yak'n'Roll if you're too young to die.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-26 : 03:44:37

Truncate in another stored procedure with EXECUTE AS?

--
Gail Shaw
SQL Server MVP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-26 : 03:45:44

No. Delete in smaller batches?

DELETE top(1) from yourtable
while @@rowcount > 0 DELETE top(100000) from yourtable

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-26 : 04:37:39

quote:
Originally posted by Peso

No. Delete in smaller batches?

DELETE top(1) from yourtable
while @@rowcount > 0 DELETE top(100000) from yourtable

N 56°04'39.26"
E 12°55'05.63"

additional:
This means to have the database in recovery model SIMPLE.

No, you're never too old to Yak'n'Roll if you're too young to die.

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-04-26 : 05:31:32

mean't last solution is recovery model dependent?

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-26 : 05:48:55