How do I shrink my SQL Server Database by using SQL query?

 Admin   
175 View    Mar 4 2020 1:38PM

Below are steps to shrink the database log file

1. Take the backup of TestDB database

2. Run below query to get logical log file name 

 

EXEC sp_helpdb TestDB;

 

 

3. Run below query (Change database name if required)

 

USE TestDB;

GO

--EXEC sp_helpdb TestDB;

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE TestDB

SET RECOVERY SIMPLE;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (TestDB_080912_Log, 1);

 

 

GO

-- Reset the database recovery model.

--ALTER DATABASE TestDB

--SET RECOVERY FULL;

GO