Sometimes, we come across a situation where the transaction log file grows unexpectedly, and it needs to be managed properly. One of the options is to reduce the size of database transaction log by shrinking the database transaction log file. Please be careful while implementing this solution on production databases.
Let me create a sample and then expalin it step by step as shown below.
Sample:
CREATE DATABASE SampleDB;
GO
Use SampleDB
GO
CREATE TABLE [Customers]
(
[CustomerID] INT IDENTITY(1,1),
[CustomerName] VARCHAR(250),
[RegistrationDate] DATE,
[EmailAddress] VARCHAR(100),
[Address] VARCHAR(500),
[PhoneNumber] VARCHAR(100)
);
GO
--Lets's insert 3,000,000 records in customer table
INSERT INTO [Customers]
(CustomerName,RegistrationDate,EmailAddress,Address,PhoneNumber)
VALUES('John','01-13-2023','John@raresql.com','AB Street','0134018201');
GO 3000000
Step 1:
Let’s check the size of the database files as shown below.
Use SampleDB
GO
SELECT name AS [DB File Name]
, size AS [Size in KBs]
, size * 8/1024 AS [Size in MBs]
, Max_Size AS [Max Size]
FROM sys.master_files
WHERE DB_NAME(database_id) = 'SampleDB';
GO
Step 2:
Now, let’s take the full backup of the database by using the following query.
--Backup database
BACKUP DATABASE [SampleDB] TO DISK = N'D:\SampleDB_Backup.bak';
GO
Step 3:
Let’s shrink database log file by using the following script.
Use SampleDB
GO
--Let's change recovery model to simple
ALTER DATABASE SampleDB
SET RECOVERY SIMPLE;
GO
--Let's shrink the database log file
DBCC SHRINKFILE (SampleDB_log, 1, TRUNCATEONLY);
GO
--Let's change recovery model to full
ALTER DATABASE SampleDB
SET RECOVERY FULL;
GO
Step 4:
Let’s check the database log file size again. As we can see below the database_log file size has been reduced.
Use SampleDB
GO
SELECT name AS [DB File Name]
, size AS [Size in KBs]
, size * 8/1024 AS [Size in MBs]
, Max_Size AS [Max Size]
FROM sys.master_files
WHERE DB_NAME(database_id) = 'SampleDB';
GO
Conclusion:
This is one of easiest ways to shrink the database transation log. Do let me know, which method you use and how effective it is.
In SQL Server 2022, a new column namely last_valid_restore_time has been added to the table backupset as a part of the enhancements. It contains the information regarding the last valid restore time, and it became very handy when something happens to your database and you need to restore data from the backup and you want to know the exact last valid restore time. Actually, it is the time when you post your last transaction in your database before taking backup. By the way, last transaction can be way before you take your backup.
One important point is that it shows the last_valid_restore_time in Transaction Log Backup ONLY. In Full Backup & differential back, it returns NULL value and which makes sense.
Let me create a sample database, a table and insert a record to demonstrate the behaviour of last_valid_restore_time as shown below.
Sample:
USE master
GO
CREATE DATABASE TESTDB;
GO
USE TESTDB
GO
CREATE TABLE Customers
(
CustomerID INT IDENTITY,
CustomerName VARCHAR(100),
CreatedAt DATETIME,
ModifiedAt DATETIME
);
GO
INSERT INTO Customers(CustomerName, CreatedAt)
VALUES('Customer-1', GETDATE());
GO
SELECT * FROM Customers;
GO
--OUTPUT
Step 1: Full Backup
In this step, we will take full backup of the database.
--Taking full backup
BACKUP DATABASE TESTDB
TO DISK ='D:\Backup\TESTDBfullbackup.BAK';
GO
--OUTPUT
Step 2: Differential Backup
In this step, first we will insert a new record then will take differential backup.
USE TESTDB
GO
INSERT INTO Customers(CustomerName, CreatedAt)
VALUES('Customer-2', GETDATE());
GO
--Taking differential backup
BACKUP DATABASE TESTDB
TO DISK ='D:\Backup\TESTDBdifferentialbackup.BAK'
WITH DIFFERENTIAL;
GO
SELECT * FROM Customers;
GO
--OUTPUT
Step 3: Log backup
In this step, we will insert and update few records and then take log backup.
USE TESTDB
GO
INSERT INTO Customers(CustomerName, CreatedAt)
VALUES('Customer-3', GETDATE());
GO
UPDATE Customers
SET CustomerName = 'Customer22'
, ModifiedAt = GETDATE()
FROM Customers
WHERE CustomerID = 2;
GO
--Taking log backup
BACKUP LOG TESTDB
TO DISK ='D:\Backup\TESTDBlogbackup1.TRN' ;
GO
SELECT * FROM Customers;
GO
--OUTPUT
Step 4: Last Valid Restore Time
Now, we have successfully created the sample along with backups. Let me run the query and add this new column namely last_valid_restore_time as shown below. As we can see, the last_valid_restore_time & the last record modified date & time is same which is 2022-12-03 18:37:18.
SELECT bs.database_name, backuptype = CASE
WHEN bs.type = 'D' AND bs.is_copy_only = 0 THEN 'Full Database'
WHEN bs.type = 'D' AND bs.is_copy_only = 1 THEN 'Full Copy-Only Database'
WHEN bs.type = 'I' THEN 'Differential database backup'
WHEN bs.type = 'L' THEN 'Transaction Log'
WHEN bs.type = 'F' THEN 'File or filegroup'
WHEN bs.type = 'G' THEN 'Differential file'
WHEN bs.type = 'P' THEN 'Partial'
WHEN bs.type = 'Q' THEN 'Differential partial'
when bs.type = 'i' THEN 'last valid restore value' END + ' Backup',
CASE bf.device_type
WHEN 2 THEN 'Disk' WHEN 5 THEN 'Tape' WHEN 7 THEN 'Virtual device'
WHEN 9 THEN 'Azure Storage' WHEN 105 THEN 'A permanent backup device'
ELSE 'Other Device' END AS DeviceType,
bms.software_name AS backup_software,
/**New column addition in SQL Server 2022**/
bs.last_valid_restore_time,
/**************************************/
BackupStartDate = bs.Backup_Start_Date,
BackupFinishDate = bs.Backup_Finish_Date,
LatestBackupLocation = bf.physical_device_name,
backup_size_mb = CONVERT(DECIMAL(10, 2), bs.backup_size / 1024. / 1024.),
compressed_backup_size_mb = CONVERT(DECIMAL(10, 2), bs.compressed_backup_size / 1024. / 1024.),
database_backup_lsn, -- For tlog and differential backups, this is the checkpoint_lsn of the FULL backup it is based on.
checkpoint_lsn,
begins_log_chain,
bms.is_password_protected
FROM msdb.dbo.backupset bs
LEFT JOIN msdb.dbo.backupmediafamily bf
ON bs.[media_set_id] = bf.[media_set_id]
INNER JOIN msdb.dbo.backupmediaset bms
ON bs.[media_set_id] = bms.[media_set_id]
WHERE bs.backup_start_date > DATEADD(MONTH, - 2, sysdatetime()) --only look at last two months
ORDER BY bs.database_name ASC,
bs.Backup_Start_Date DESC;
--OUTPUT
Step 5: Restore
Now, in order to get data before the execution of update statement, we are going to check the last_valid_restore_time and restore the database before the last_valid_restore_time which will return the data before update of the table as shown below.
USE [master]
RESTORE DATABASE [TESTDB]
FROM DISK = N'D:\Backup\TESTDBfullbackup.BAK'
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
RESTORE DATABASE [TESTDB]
FROM DISK = N'D:\Backup\TESTDBdifferentialbackup.BAK'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [TESTDB]
FROM DISK = N'D:\Backup\TESTDBlogbackup1.TRN'
WITH FILE = 1, NOUNLOAD, STATS = 5, STOPAT = N'2022-12-03T18:37:15'
GO
--OUTPUT
Conclusion:
Last_valid_restore_time is very handy information when we want to know the last transaction date in the specified backup & would like to restore based on specific time. Do let me know, how were you tracking this time in earlier version of SQL Server 2022.
This blog/website is a personal blog/website and all articles, postings and opinions contained herein are my own. The contents of this blog/website are not intended to defame, purge or humiliate anyone should they decide to act upon or reuse any information provided by me. Comments left by any independent reader are the sole responsibility of that person. Should you identify any content that is harmful, malicious, sensitive or unnecessary, please contact me via email (imran@raresql.com) so I may rectify the problem.
SQL Server Portal 