{"id":522,"date":"2011-10-18T08:17:00","date_gmt":"2011-10-18T08:17:00","guid":{"rendered":"\/blogs\/joe\/post\/Adjusting-target-recovery-time-by-database-in-SQL-Server-2012.aspx"},"modified":"2013-01-20T10:53:54","modified_gmt":"2013-01-20T18:53:54","slug":"setting-indirect-checkpoints-by-database-in-sql-server-2012","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/setting-indirect-checkpoints-by-database-in-sql-server-2012\/","title":{"rendered":"Setting indirect checkpoints by database in SQL Server 2012"},"content":{"rendered":"

This blog post applies to\u00a0SQL Server 2012 CTP3 (11.0.1440).<\/span><\/span><\/em><\/p>\n

SQL Server 2012 introduces the \u201cindirect checkpoints\u201d feature.\u00a0 <\/span>At a high level, it allows you to adjust the target recovery time of a specific database, rather than relying entirely on the SQL Server instance-level \u2018recovery interval (min)\u2019 setting. <\/span><\/span>To demonstrate the impact, I\u2019ll walk through a scenario where we\u2019re performing large batch inserts into a data warehousing\u00a0Fact table.\u00a0 <\/span>In this\u00a0demo I\u2019m using the Codeplex \u201cAdventureWorksDWDenali\u201d database.<\/span><\/span><\/p>\n

As a first step, I\u2019ll back up the database so that I can reset the baseline in order to compare the default checkpoint behavior with the new indirect checkpoint setting:<\/span><\/span><\/p>\n

USE master<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

\n

\n

BACKUP DATABASE [AdventureWorksDWDenali] <\/span><\/span><\/p>\n

TO\u00a0 <\/span>DISK = N’C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\Backup\\IndirectCheckpoint_AWDW.bak’<\/span><\/span><\/p>\n

\u00a0<\/span><\/span>WITH NOFORMAT, NOINIT,\u00a0 <\/span>NAME = N’AdventureWorksDWDenali-Full Database Backup’, CHECKSUM<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

\n

Now with that out of the way I\u2019ll check the recovery interval of the SQL Server instance:<\/span><\/span><\/p>\n

SELECT value_in_use\u00a0<\/span><\/span><\/p>\n

FROM sys.configurations<\/span><\/span><\/p>\n

WHERE name = ‘recovery interval (min)’<\/span><\/span><\/p>\n

\n

\n

The value in use is \u201c0\u201d \u2013 the default.\u00a0 <\/span>So basically the estimated checkpoint will be every minute for an active database (although I\u2019m over-simplifying things here \u2013 since automatic checkpoint frequency varies based on other factors as well – but for this demo you’ll still see a clear difference in the before-and-after).<\/span><\/span><\/p>\n

\n

\n

Now I\u2019ll check the indirect checkpoint time for the AdventureWorksDWDenali database:<\/span><\/span><\/p>\n

\n

\n

SELECT target_recovery_time_in_seconds<\/span><\/span><\/p>\n

FROM sys.databases<\/span><\/span><\/p>\n

WHERE name = ‘AdventureWorksDWDenali’<\/span><\/span><\/p>\n

\n

\n

This returns \u201c0\u201d \u2013 meaning we haven\u2019t configured this new SQL Server 2012 database option (yet) and so the SQL Server instance level setting is in effect.<\/span><\/span><\/p>\n

\n

\n

Now I\u2019ll create an extended events session to track the individual checkpoint events for the database (database_id “5” happens to be the AdventureWorksDWDenali database on my instance):<\/span><\/span><\/p>\n

\n

\n

CREATE EVENT SESSION [track_checkpoints] ON SERVER <\/span><\/span><\/p>\n

ADD EVENT sqlserver.checkpoint_end(<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0 <\/span><\/span>ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.is_system)<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0 <\/span><\/span>WHERE ([sqlserver].[database_id]=(5))) <\/span><\/span><\/p>\n

ADD TARGET package0.ring_buffer(SET max_memory=(2048))<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

\n

\n

\n

(By the way – I updated max_memory down to 2MB from the 100MB per Jonathan Kehayias’ heads up on potential\u00a0limits\u00a0– not to mention that this was more memory than needed for this example<\/em>).<\/span><\/span><\/p>\n

\n

\n

\u00a0Next I\u2019ll turn it on:<\/span><\/span><\/p>\n

\n

\n

ALTER EVENT SESSION [track_checkpoints] ON SERVER STATE=START<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

\n

\n

And now I\u2019m going to push some I\/O by creating a new table and populating it multiple times from the FactInternetSales table:<\/span><\/span><\/p>\n

\n

\n

USE AdventureWorksDWDenali<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

\n

\n

SELECT *<\/span><\/span><\/p>\n

INTO dbo.CheckPoint_Test_FactInternetSales<\/span><\/span><\/p>\n

FROM dbo.FactInternetSales<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

\n

\n

INSERT dbo.CheckPoint_Test_FactInternetSales<\/span><\/span><\/p>\n

SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, LEFT(CAST(NEWID() AS NVARCHAR(36)),20), SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, <\/span><\/span><\/p>\n

TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate<\/span><\/span><\/p>\n

FROM dbo.FactInternetSales<\/span><\/span><\/p>\n

GO 10<\/span><\/span><\/p>\n

\n

\n

This shows the following rows affected:<\/span><\/span><\/p>\n

\n

\n

(60398 row(s) affected)<\/span><\/span><\/p>\n

Beginning execution loop<\/span><\/span><\/p>\n

(60398 row(s) affected)<\/span><\/span><\/p>\n

(60398 row(s) affected)<\/span><\/span><\/p>\n

(60398 row(s) affected)<\/span><\/span><\/p>\n

(60398 row(s) affected)<\/span><\/span><\/p>\n

(60398 row(s) affected)<\/span><\/span><\/p>\n

(60398 row(s) affected)<\/span><\/span><\/p>\n

(60398 row(s) affected)<\/span><\/span><\/p>\n

(60398 row(s) affected)<\/span><\/span><\/p>\n

(60398 row(s) affected)<\/span><\/span><\/p>\n

(60398 row(s) affected)<\/span><\/span><\/p>\n

Batch execution completed 10 times.<\/span><\/span><\/p>\n

\n

\n

Now I\u2019ll query the track_checkpoints Extended Event session (** and thanks to my colleague\u00a0Jonathan Kehayias for the following query which I just modified to query against track_checkpoints and then change a few column names **):<\/span><\/span><\/p>\n

\n

\n

SELECT <\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0 <\/span><\/span>n.value(‘(event\/@name)[1]’, ‘varchar(50)’) AS event_name,<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>n.value(‘(event\/@timestamp)[1]’, ‘datetime’) as event_datetime<\/span><\/span><\/p>\n

FROM<\/span><\/span><\/p>\n

(\u00a0\u00a0\u00a0 <\/span>SELECT td.query(‘.’) as n<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0 <\/span><\/span>FROM <\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0 <\/span><\/span>(<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>SELECT CAST(target_data AS XML) as target_data<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>FROM sys.dm_xe_sessions AS s<\/span>\u00a0\u00a0\u00a0 <\/span><\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>JOIN sys.dm_xe_session_targets AS t<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>ON s.address = t.event_session_address<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>WHERE s.name = ‘track_checkpoints’<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>AND t.target_name = ‘ring_buffer’<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0 <\/span><\/span>) AS sub<\/span><\/span><\/p>\n

\u00a0\u00a0\u00a0 <\/span><\/span>CROSS APPLY target_data.nodes(‘RingBufferTarget\/event’) AS q(td)<\/span><\/span><\/p>\n

) as tab<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

\n

\n

This returns the following checkpoint event data:<\/span><\/span><\/p>\n

\n

\n

event_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>event_datetime<\/span><\/span><\/p>\n

checkpoint_end2011-10-18 20:54:46.200<\/span><\/span><\/p>\n

checkpoint_end2011-10-18 20:54:46.650<\/span><\/span><\/p>\n

checkpoint_end2011-10-18 20:54:47.063<\/span><\/span><\/p>\n

checkpoint_end2011-10-18 20:54:47.433<\/span><\/span><\/p>\n

checkpoint_end2011-10-18 20:54:47.647<\/span><\/span><\/p>\n

checkpoint_end2011-10-18 20:54:48.200<\/span><\/span><\/p>\n

checkpoint_end2011-10-18 20:54:48.597<\/span><\/span><\/p>\n

checkpoint_end2011-10-18 20:54:49.157<\/span><\/span><\/p>\n

checkpoint_end2011-10-18 20:54:49.620<\/span><\/span><\/p>\n

checkpoint_end2011-10-18 20:54:50.127<\/span><\/span><\/p>\n

\n

\n

So we see 10 checkpoints for the default behavior.<\/span><\/span><\/p>\n

\n

\n

And now I\u2019m going to stop the event session and restore the database back to its original state to show you the comparison to the SQL Server 2012 indirect checkpoint option:<\/span><\/span><\/p>\n

\n

\n

ALTER EVENT SESSION [track_checkpoints] ON SERVER STATE=STOP<\/span><\/span><\/p>\n

\n

\n

— Restoring the database back to the original format<\/span><\/span><\/p>\n

USE master<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

\n

\n

RESTORE DATABASE [AdventureWorksDWDenali] <\/span><\/span><\/p>\n

FROM\u00a0 <\/span>DISK = N’C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\Backup\\IndirectCheckpoint_AWDW.bak’ <\/span><\/span><\/p>\n

WITH\u00a0 <\/span>FILE = 1<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

\n

\n

Next I\u2019m going to change the database\u2019s TARGET_RECOVERY_TIME to 5 minutes (this is the SQL Server 2012 indirect checkpoints feature I was talking about):<\/span><\/span><\/p>\n

\n

\n

ALTER DATABASE AdventureWorksDWDenali <\/span><\/span><\/p>\n

SET TARGET_RECOVERY_TIME = 5 MINUTES<\/span><\/span><\/p>\n

\n

\n

Just to confirm that it \u201ctook\u201d \u2013 I\u2019ll validate sys.databases:<\/span><\/span><\/p>\n

\n

\n

SELECT target_recovery_time_in_seconds<\/span><\/span><\/p>\n

FROM sys.databases<\/span><\/span><\/p>\n

WHERE name = ‘AdventureWorksDWDenali’<\/span><\/span><\/p>\n

\n

\n

This returns 300 seconds instead of the 0 seconds we saw before \u2013 300 representing the new target recovery time I just set in minutes.<\/span><\/span><\/p>\n

\n

And now I\u2019m going to enable the event session again and re-populate the table:<\/span><\/span><\/p>\n

\n

\n

ALTER EVENT SESSION [track_checkpoints] ON SERVER STATE=START<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

\n

\n

USE AdventureWorksDWDenali<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

\n

\n

SELECT *<\/span><\/span><\/p>\n

INTO dbo.CheckPoint_Test_FactInternetSales<\/span><\/span><\/p>\n

FROM dbo.FactInternetSales<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

\n

\n

INSERT dbo.CheckPoint_Test_FactInternetSales<\/span><\/span><\/p>\n

SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, LEFT(CAST(NEWID() AS NVARCHAR(36)),20), SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, <\/span><\/span><\/p>\n

TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate<\/span><\/span><\/p>\n

FROM dbo.FactInternetSales<\/span><\/span><\/p>\n

GO 10<\/span><\/span><\/p>\n

\n

\n

How many checkpoints will we see now?\u00a0 With all things equal between tests, we now only see four checkpoints:<\/span><\/span><\/span><\/p>\n

\n

\n

event_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>event_datetime<\/span><\/span><\/p>\n

checkpoint_end2011-10-18 21:06:52.697<\/span><\/span><\/p>\n

checkpoint_end2011-10-18 21:06:53.520<\/span><\/span><\/p>\n

checkpoint_end2011-10-18 21:06:54.717<\/span><\/span><\/p>\n

checkpoint_end2011-10-18 21:06:56.277<\/span><\/span><\/p>\n

\n

\n

I tested this before-and-after scenario three times, and each time I saw the identical change in the number of checkpoints.\u00a0 <\/span>So I saw a consistent result given an identical test setup.<\/span><\/span><\/p>\n

\n

\n

That\u2019s all for now \u2013 but two closing thoughts:<\/span><\/span><\/p>\n

\n

\n

\u00b7<\/span><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span><\/span><\/span><\/span>I like how we\u2019re able to adjust this target recovery time at the database level<\/span><\/em>.\u00a0 <\/span>This allows us to take into account the recovery time requirements per application if we need to\u00a0\u2013 adjusting upward or downward based on different requirements or issues. <\/span><\/span><\/p>\n

\u00b7<\/span><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span><\/span><\/span><\/span>I didn\u2019t measure the actual performance impact against the load, but if I had I imagine we would have seen\u00a0some performance improvement with the second scenario of less frequent checkpoints (but with a trade-off of longer recovery time – a nontrivial consideration).<\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

This blog post applies to\u00a0SQL Server 2012 CTP3 (11.0.1440). SQL Server 2012 introduces the \u201cindirect checkpoints\u201d feature.\u00a0 At a high level, it allows you to adjust the target recovery time of a specific database, rather than relying entirely on the SQL Server instance-level \u2018recovery interval (min)\u2019 setting. To demonstrate the impact, I\u2019ll walk through a […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[],"class_list":["post-522","post","type-post","status-publish","format-standard","hentry","category-performance"],"yoast_head":"\nSQL Server Indirect Checkpoints - Joe Sack<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/setting-indirect-checkpoints-by-database-in-sql-server-2012\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Indirect Checkpoints - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"This blog post applies to\u00a0SQL Server 2012 CTP3 (11.0.1440). SQL Server 2012 introduces the \u201cindirect checkpoints\u201d feature.\u00a0 At a high level, it allows you to adjust the target recovery time of a specific database, rather than relying entirely on the SQL Server instance-level \u2018recovery interval (min)\u2019 setting. To demonstrate the impact, I\u2019ll walk through a […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/setting-indirect-checkpoints-by-database-in-sql-server-2012\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2011-10-18T08:17:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-20T18:53:54+00:00\" \/>\n<meta name=\"author\" content=\"Joseph Sack\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph Sack\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/setting-indirect-checkpoints-by-database-in-sql-server-2012\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/setting-indirect-checkpoints-by-database-in-sql-server-2012\/\",\"name\":\"SQL Server Indirect Checkpoints - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2011-10-18T08:17:00+00:00\",\"dateModified\":\"2013-01-20T18:53:54+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/setting-indirect-checkpoints-by-database-in-sql-server-2012\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/setting-indirect-checkpoints-by-database-in-sql-server-2012\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/setting-indirect-checkpoints-by-database-in-sql-server-2012\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Performance\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/performance\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Setting indirect checkpoints by database in SQL Server 2012\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\",\"name\":\"Joe Sack\",\"description\":\"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\",\"name\":\"Joseph Sack\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"caption\":\"Joseph Sack\"},\"description\":\"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.\",\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/joe\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Indirect Checkpoints - Joe Sack","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/joe\/setting-indirect-checkpoints-by-database-in-sql-server-2012\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Indirect Checkpoints - Joe Sack","og_description":"This blog post applies to\u00a0SQL Server 2012 CTP3 (11.0.1440). SQL Server 2012 introduces the \u201cindirect checkpoints\u201d feature.\u00a0 At a high level, it allows you to adjust the target recovery time of a specific database, rather than relying entirely on the SQL Server instance-level \u2018recovery interval (min)\u2019 setting. To demonstrate the impact, I\u2019ll walk through a […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/setting-indirect-checkpoints-by-database-in-sql-server-2012\/","og_site_name":"Joe Sack","article_published_time":"2011-10-18T08:17:00+00:00","article_modified_time":"2013-01-20T18:53:54+00:00","author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/setting-indirect-checkpoints-by-database-in-sql-server-2012\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/setting-indirect-checkpoints-by-database-in-sql-server-2012\/","name":"SQL Server Indirect Checkpoints - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2011-10-18T08:17:00+00:00","dateModified":"2013-01-20T18:53:54+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/setting-indirect-checkpoints-by-database-in-sql-server-2012\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/setting-indirect-checkpoints-by-database-in-sql-server-2012\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/setting-indirect-checkpoints-by-database-in-sql-server-2012\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Performance","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/performance\/"},{"@type":"ListItem","position":3,"name":"Setting indirect checkpoints by database in SQL Server 2012"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/","name":"Joe Sack","description":"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648","name":"Joseph Sack","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","caption":"Joseph Sack"},"description":"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.","sameAs":["http:\/\/3.209.169.194\/blogs\/joe","https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack"],"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/522","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/comments?post=522"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/522\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=522"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=522"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=522"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}