February 3, 2012 at 9:40 am
Hi friends,
I got a problem. I`ve a procedure that got deletes. One of these deletes, generate extense log.
Is there a function or command that I use that block (or exclude) this procedure or delete to record at the log file?
February 3, 2012 at 9:48 am
nope, EVERYTHING is logged, whether simple mode or FULL; it's just simple mode commits the changes right away and issues a check point.
you could consider having the procedure do it's work in batches, and committing smaller batches, instead of say, a 1 Million row insert/update as a single operation;
you need to be able to determine what has not been processed yet, which depedns on the operation the procedure is doing.
a basic example:
SET ROWCOUNT 50000
WHILE 1=1
BEGIN
DELETE FROM dbo.myTable
WHERE MyField = My Condition
IF @@ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Lowell
February 3, 2012 at 9:58 am
What Lowell said, except a small point on the delete sample code. Whilst perfectly functional, you should get in the habit of using:
DELETE TOP(n)
rather than setting ROWCOUNT as it will be ignored in future versions of SQL Server for DELETE, INSERT and UPDATE
February 3, 2012 at 10:11 am
Thanks all,
I had used that function before, but the problem is that I want to execute the procedure (delete) and delete that info at the log (about xxxGB).
There's no away? Like a "set no_log" before the procedure?
February 3, 2012 at 10:14 am
No, or the database wouldn't be recoverable if it crashed during your transaction.
You can minimally log a TRUNCATE, but you need to have no FK's on the table and clear the whole table (without a WHERE clause)
February 3, 2012 at 10:19 am
Sorry.
Can you detail what said, with an example?
February 3, 2012 at 10:26 am
Have a look at the BOL article on TRUNCATE:
http://msdn.microsoft.com/en-us/library/ms177570.aspx
It works very differently to DELETE in that it is not selective at all, it literally wipes the table clean by deallocating the pages and as such it's very fast and has no significant impact on the log size. If you need to keep any data in the table, or you have Foreign Keys, you cannot use it.
An example is:
TRUNCATE TABLE myTable
Note that there's no WHERE clause.
If you need to, for example, delete 99% of the rows in the table and only keep a few, it may be faster to select the rows you want to keep into a temporary table, truncate the table, then insert the 1% of rows back again.
February 3, 2012 at 10:28 am
dbapleno (2/3/2012)
Thanks all,I had used that function before, but the problem is that I want to execute the procedure (delete) and delete that info at the log (about xxxGB).
There's no away? Like a "set no_log" before the procedure?
No.
Would you like it if your database went suspect and had to be restored from backup if the delete failed (like if it found a row that was referenced by a foreign key)? That's what you're asking for when you ask for a non-logged operation and that's why there is no way to have unlogged data modifications
Do the deletes in batches and run checkpoint or log backup between them, or use truncate if you're clearing the table, or if you're deleting a range of data, consider table partitioning
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply