Software & Applications
  • 43 Views
  • 22/08/2024

Script shink log database SQL Server

Below is a simple script that will shrink the transaction log file of a specific database in Microsoft SQL Server

USE [YourDatabaseName];
GO

-- Check current size of the log file
DBCC SQLPERF(LOGSPACE);
GO

-- Shrink the transaction log file
-- Replace 'LogicalLogFileName' with your actual log file logical name
DBCC SHRINKFILE (N'LogicalLogFileName', 1);
GO

-- Optionally, you can set the recovery model to SIMPLE before shrinking and then set it back to FULL
-- Set recovery model to SIMPLE
ALTER DATABASE [YourDatabaseName] SET RECOVERY SIMPLE;
GO

-- Shrink the log file
DBCC SHRINKFILE (N'LogicalLogFileName', 1);
GO

-- Set recovery model back to FULL
ALTER DATABASE [YourDatabaseName] SET RECOVERY FULL;
GO

-- Check size after shrinking
DBCC SQLPERF(LOGSPACE);
GO

 

Explanation:

    DBCC SQLPERF(LOGSPACE): This command helps to check the current size of the transaction log files.
    DBCC SHRINKFILE: This shrinks the transaction log file. Replace LogicalLogFileName with the actual logical name of the transaction log file you want to shrink.
    ALTER DATABASE SET RECOVERY SIMPLE: This step is optional. By changing the recovery model to SIMPLE, the transaction log will truncate automatically. After shrinking, you can revert the recovery model to FULL.

Important Notes:

    Shrinking the transaction log frequently is not recommended unless absolutely necessary (e.g., after a large operation that temporarily expanded the log file).
    Regular log backups should be used to manage the size of the log in databases using the FULL recovery model.

Be sure to replace YourDatabaseName with the name of your actual database and LogicalLogFileName with the name of the logical log file you want to shrink.

Thanks for visit my website