SQL Server Portal

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.

Read Full Post »