December 6, 2004 at 6:33 am
Hi all.
I have a job that run on weekend and make a purge to delete old record on my database. Because many row are deleted, I don't want that any delete will be written to the log, so I activate Truncate Log on Checkpoint.
This weekend, my job fail with the following error message.
Checkpointing database that was changed. [SQLSTATE 01000] (Message 15443) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) Checkpointing database that was changed. [SQLSTATE 01000] (Message 15443) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) Checkpointing database that was changed. [SQLSTATE 01000] (Message 15443) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528). The step succeeded.
The job never failed before. I searched on the web to see something like this but nothing very interresting.
Somebody have an idea ??
Sorry for my english.
Thank
Jonathan
December 6, 2004 at 10:15 am
Try running DBCC CHECKDB and verify that everything is fine on the database. Regarding writing to the log file, Truncate log on checkpoint will only truncate completed transactions on a checkpoint. The instructions will still be written to the log file. But only after a transaction is completed and a checkpoint is issued, the corresponding log entry will be deleted. SQL does write to the log file for rolling back transactions. e.g. if you run delete * from table_name. and the table is 1,000,000 records big, SQL will write everything to the log file and if your transactions does not complete will roll back everything. You can break down your transactions as below to delete in small chunks so that if for some reason, your delete fails, SQL server does not have to roll back everything.SQL will consider each delete to be a transaction and now on setting truncate log on checkpoint will be helpful.
set rowcount 100000 --(or you can mention any value.)
while @@rowcount > 0
begin
delete * from table_name
end
December 6, 2004 at 4:57 pm
My purge run on Sunday night and I have a DBCC CheckDB every day at 04h30 am.
So, the DBCC CheckDB run correctly.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply