do checkpoint on a DB every 5 seconds

anushahanna
anushahanna used Ask the Experts™
on
how would to implement a way to do checkpoints every 5 seconds in a database while a bulk insert is happening?

thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
You can set the default checkpoint interval by using the sp_configure stored procedure setting named recovery interval.

However, recovery interval option can be set in terms of minutes and not possible to set it in terms of seconds.
Setting checkpoint to happen every 5 seconds is not good and having Recovery mode to Full would suffice as SQL Server would automatically do Checkpoints as required.
More info below:
http://technet.microsoft.com/en-us/library/ms191154.aspx
ill
Commented:
Why to implement a way to do checkpoints every 5 seconds in a database while a bulk insert is happening?
Choose either 5 sec. checkpoint or bulk insert. Combining them means loosing both a)bulk insert performance advantage and b)checkpoint fast recovery advantage.
Are you trying to:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/03/10/how-to-create-a-corrupt-database-using-bulk-insert-update-and-bcp-sql-server-as-a-hex-editor.aspx ?

Author

Commented:
rrjegan17,
was hoping to flush data to disk once in a few seconds, so that the log does not grow too big... i think you have another solution for that?

i was hoping not to touch recovery interval option.

this db is in bulk logged mode now.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Author

Commented:
ill, no i am not trying anything close to that link.. mainly have lot of data inserts, and want to keep the log chain and also not blow the log..

>>Choose either 5 sec. checkpoint or bulk insert. Combining them means loosing both a)bulk insert performance advantage and b)checkpoint fast recovery advantage.

could you recommend how you would handle a scenario like this and there is space issue concern and you want to do something to help the log from exponential growth?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> this db is in bulk logged mode now.

Change recovery model to Full to capture all events.
And not sure why you are trying to flush out data to disk as it would be efficiently handled by SQL Server automatically.
More info on how Checkpoints work efficiently here:
http://msdn.microsoft.com/en-us/library/ms188748.aspx

And its recommended not to change it to lower intervals like 1 minute(Minimum) or so as it would impact your Server performance drastically.

Author

Commented:
thanks for the important info.

there was a one time operation that was going to increase the log file. hence, only for that 2 hour period, i was hoping to keep the log contained without putting it in simple mode with checkpoints.

but per your point, that may cause drastic performance impact, as i will be loading lot of writes into the db.
ill
Commented:
for one time operation, i would go with batches. just tell the server ( bcp -b10000 ? ) the number of rows to process in single batch and it will do it automatically

Author

Commented:
>>i would go with batches. just tell the server ( bcp -b10000 ? )

do you mean to say use BCP command, instead of insert statements?

does the system do a checkpoint at the end of the batch automatically?
ill
Commented:
it did and I hope it still does. I remember doing upgrade of some business systems to SQL2005 and we did not change the bcp scripts batch scripts.
http://support.microsoft.com/kb/81339

Author

Commented:
Thanks very much for the helpful info.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial