Last month (April 24, 2018), the Service Pack 2 for SQL Server 2016 was released and distributed.
This Service Pack has new DMVs, already available in SQL Server 2017 RTM.
In this article, I will just write few words about 2 DMVs (sys.dm_db_log_stats & sys.dm_db_log_info) and a new column (modified_extent_page_count) in the DMV sys.dm_db_file_space_usage that I presented during our last event about SQL Server 2017. I think they are really helpful for DBA.
It’s also the opportunity to present you the demo that I create for our Event.
First, I create the database smart_backup_2016 and a table Herge_Heros
CREATE DATABASE [smart_backup_2016] CONTAINMENT = NONE ON PRIMARY ( NAME = N'smart_backup_2016', FILENAME = N'G:\MSSQL\Data\smart_backup_2016.mdf' ) LOG ON ( NAME = N'smart_backup_2016_log', FILENAME = N'G:\MSSQL\Log\smart_backup_2016_log.ldf' ) GO USE smart_backup_2016 GO CREATE TABLE [dbo].[Herge_Heros] ( [ID] [int] NULL, [Name] [nchar](10) NULL ) ON [PRIMARY] GO
I do a little insert and run a first Full and a first TLog Backup
INSERT INTO [Herge_Heros] VALUES(1,'Tintin') -- Tim INSERT INTO [Herge_Heros] VALUES(2,'Milou') -- Struppi BACKUP DATABASE [smart_backup_2016] TO DISK = N'C:\Temp\smart_backup.bak' WITH NOFORMAT, NOINIT, NAME = N'smart_backup-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO BACKUP Log [smart_backup_2016] TO DISK = N'C:\Temp\smart_backup.log' WITH NOFORMAT, NOINIT, NAME = N'smart_backup-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
After, I insert a lot of line to have more than 50% modified pages
INSERT INTO [Herge_Heros] VALUES(3,'Quick') --Strups INSERT INTO [Herge_Heros] VALUES(4,'Flupke') --Stepppke GO 100000
Now, the demo is ready!
new column modified_extent_page_count in sys.dm_db_file_space_usage
As you can see in this screenshot, the column is really existing in SQL Server 2016 SP2 (13.0.5026.0).
After, you can, like us in our DMK maintenance, create an adapted Backup Strategy depending from changes and no more depending from the time.
In this stored procedure, if the modified pages are greater than 50% of the total pages, it will do a Full Backup and if the modified pages are less than 50%, it will do a Differential Backup.
USE [dbi_tools] GO CREATE or ALTER PROCEDURE [maintenance].[dbi_smart_backup] @database_name sysname as DECLARE @pages_changes Numeric(10,0) DECLARE @full_backup_threshold INT DECLARE @diff_backup_threshold INT DECLARE @sql_query nvarchar(max) DECLARE @page_change_text nvarchar(20) DECLARE @param nvarchar(50) DECLARE @backupfile nvarchar(2000) SET @full_backup_threshold=50 SET @diff_backup_threshold=0 SET @param = N'@pages_changesOUT nvarchar(20) OUTPUT' SET @sql_query =N'SELECT @pages_changesOUT=( 100 * Sum(modified_extent_page_count) / Sum(total_page_count) ) FROM ['+@database_name+'].sys.dm_db_file_space_usage' EXECUTE sp_executesql @sql_query,@param ,@pages_changesOUT=@page_change_text OUTPUT; SET @pages_changes = CAST(@page_change_text AS Numeric(10,0)) IF @pages_changes > @full_backup_threshold BEGIN --Full Backup threshold exceeded, take a full backup Print 'Full Backup Threshold exceeded, take a full backup' SET @backupfile = N'C:\Temp\'+@database_name+N'_' + replace(convert(nvarchar(50), GETDATE(), 120), ':','_') + N'.bak' BACKUP DATABASE @database_name TO DISK=@backupfile END ELSE BEGIN IF @pages_changes >= @diff_backup_threshold BEGIN -- Diff Backup threshold exceeded, take a differential backup Print 'Diff Backup threshold exceeded, take a differential backup' SET @backupfile = N'C:\Temp\'+@database_name+N'_' + replace(convert(nvarchar(50), GETDATE(), 120), ':','_') + N'.dif' BACKUP DATABASE @database_name TO DISK=@backupfile WITH differential END ELSE BEGIN -- No threshold exceeded, No backup PRINT 'No threshold exceeded, No backup' END END GO
Now, I run the stored procedure [maintenance].[dbi_smart_backup] in the dbi_tool
USE smart_backup_2016; GO EXEC [dbi_tools].[maintenance].[dbi_smart_backup] @database_name = N'smart_backup_2016'
The dbi backup Stored Procedure in this case do a Full Backup because the modified pages are 64%.
I check the status of the modified pages and the modified pages are at 5%.
If I restart the stored procedure, I do a differential backup.
My backup strategy is really adapted to the change of pages in the database and no more based on the time (RTO vs RPO).
Let’s go to the new DMV sys.dm_db_log_stats do to the same with the TLog backup.
This DMV gives really good information about the transaction log files and can help to adapt the backup strategy and also control the growth of the file.
The DMV is very easy to use and for example, if you want to have the growth of the size since the last TLog backup, use the column log_since_last_log_backup_mb
SELECT log_since_last_log_backup_mb from sys.dm_db_log_stats(DB_ID('smart_backup_2016')) GO
Like below, I create in our DMK maintenance an adapted TLOG Backup [dbi_smart_tlog_backup]
If the TLOG is growing more that 5 MB from the last TLOG backup, It will do a TLOG Backup and if not, no TLOG Backup.
In my example, the growth is 548 MB, then a TLOG Backup is necessary.
After, I control the size and as you can see the size since last TLOG Backup is 0.07MB
As you can see, no TLOG backup… My backup strategy is adapted to the load!
This DMV will help us to have all VLF(Virtual Log File) information and no more using the DBCC Loginfo.
You can use this DMV very easily like this:
SELECT [name] AS 'Database Name', COUNT(l.database_id) AS 'VLF Count' FROM sys.databases s CROSS APPLY sys.dm_db_log_info(s.database_id) l GROUP BY [name]
These DMVs are very helpful and it is a good thing to have it also in SQL Server 2016 now.