How to shrink MS SQL database and log file by using Transact-SQL commands

Steps

How to work on the Transact-SQL commands to shrink the Database

Prerequisites:

1. Roles/ privileges recommended:
   • Membership in the sysadmin fixed server role. This role grants the highest level of permission on the SQL Server instance, including the ability to perform any database operation. Applies to the entire SQL Server instance, including all databases and server settings
   • Membership in the db_owner fixed database role. This role grants full control over a specific database, allowing the user to perform all configuration and maintenance activities within that database, including shrinking its files. Limitations: This role is only applicable on the database is set up.
2. Run queries from SSMS (SQL Server Management Studio):
   • From every SQL Server version, launch SSMS (Windows start menu, then select SQL Server Management Studio), then use your credentials to select and log on to one of the involved SQL Server instances; the SQL server account or domain account used must have at least one of the roles privileges mentioned in 1.
   • Click the "New Query" button on the toolbar, or press Ctrl + N. This opens a new query editor window.
   • After entering the query, script or even single command. Run the query by pressing the F5 on your keyboard or by clicking the Play icon (Execute) on the top of the console.
3. Example of Transact-SQL command,

SELECT * FROM Customers

This query selects all columns and all rows from the Customers table.

Previous steps:

1. Checking the logs size:

Use the below query that is useful for troubleshooting issues related to transaction log growth, as it helps identify the blocking factor preventing the log from being truncated and space from being reclaimed:

select name, log_reuse_wait_desc from sys.databases where name='your_database_name'.

Where the log_reuse_wait_desc column provides information for managing transaction log space. When the value is NOTHING, it means the transaction log can be truncated and reused efficiently. However, if it displays other values, it indicates a factor preventing the log reuse.

• Use the query below to get the transaction log space usage statistics for all your databases:

dbcc sqlperf(logspace)

In SQL Server, it can also be used to reset wait and latch statistics. See if looking back at the file sizes, we could check that the size of the .mdf file has not changed.

Conversely, you can check if the .ldf file has grown tremendously, but it depends on the DB activity.

Example:

dbcc sqlperf (logspace);
go

How to shrink the database:

1. Please follow the steps below in a Database Replica (used for Disaster Recovery & High Availability) scenario:
   1. Remove the server from the AG (Availability Group):
   2. Using SQL Server Management Studio (SSMS):
   3. Connect to the Primary Replica:
   4. In Object Explorer, connect to the SQL Server instance hosting the primary replica of the availability group. 
   5. Navigate to the Availability Group.
   6. Expand "Always On High Availability" -> "Availability Groups" and then expand the specific availability group.
   7. Remove the Secondary Replica.
   8. Expand "Availability Replicas." Right-click the secondary replica you wish to remove and select "Remove from Availability Group." 
      Confirm Removal:
      In the "Remove Secondary Replicas from Availability Group" dialog box, confirm the removal by clicking "OK.".
2. Data-base backup.

Use the example shown below to take backup from DB, SQL Server transaction log backup file (.trn file).

BACKUP LOG [your_transact_log_name] TO DISK = N'Disk_drive:\backup_path\backupname.trn' WITH NOFORMAT, INIT, NAME = N'Trn Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

DBCC SHRINKFILE (N'backupname_log’ ,  Filesize, NOTRUNCATE)

Where, Filesize, is the desired size in MB.

DBCC SHRINKFILE (N'backupname_log’ , Filesize)

END

5. Add the DB again to the Availability Group, Using SQL Server Management Studio (SSMS):

5. Connect to the server instance hosting the replica.
6. Expand the "Always On High Availability" node, then "Availability Groups," and then the specific availability group.
7. Expand the "Availability Databases" node.
8. Right-click the database and select "Resume Data Movement."
9. Confirm in the dialog box.

The expected result is that the DB size shrink should successfully happen. You can use the above steps 1 and 2 to check the final statistics.