Menu
header photo

SQL-Talk

Talks all about SQL Server and Azure

SQL Server Checkpoints Demo

--Step1: Creating table
create table customers(cid int,cname varchar(40),city varchar(30))
--Step2: Inserting data
insert customers values(1,'Rakesh','Hyd')
--Get clean and dirty pages count in a database
SELECT Page_Status = CASE WHEN is_modified = 1 THEN 'Dirty' 
ELSE 'Clean' END,
DBName = CASE WHEN database_id = 32767 THEN 'RESOURCEDB' 
ELSE DB_NAME(database_id) END,
Pages = COUNT(1)
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID()
GROUP BY database_id, is_modified
ORDER BY 2
--Run Checkpoint
CHECKPOINT
--To clear clean pages
DBCC DROPCLEANBUFFERS()
GO