How to shrink the database in SQL Server

Steps to follow for shrink the database log files:

1. Check the file size and status:

DBCC ShowFileStats

Note: Show the files status of the Total Extents and used Extent.

2. Change the database full recovery mode to simple mode

Note: If you want to reduce the log file size which is taking huge space then you can convert the full database recovery model to simple in non production enviornment like test or pre-prod otherwise first go for full backup then simple use shrink command skip step 2 for production environment

-- Check the recovery model first
select name, recovery_model_Desc from sys.databases

USE <db_name>
Alter database SET Recovery simple;

3. Shrink the files by providing the file path in following command otherwise use 4 step.

DBCC SHRINKFILE('', 1) -- use logical file name

4. For Shrink the completer database , it cover all files of the database:

dbcc shrinkdatabase('ADVENTUREWORKS')

5. Change recovery mode of the database to full recovery mode:

Note: if you use step 2 to make your database simple recovery mode then convert back to full recovery mode

dbcc shrinkdatabase('ADVENTUREWORKS')

6. Check the size again for verify:

DBCC ShowFileStats

Syntax of Shrink the database 

DBCC SHRINKDATABASE
( 'database_name' | database_id | 0
[ ,target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)
[ WITH NO_INFOMSGS ]


Parameters:
'database_name' | database_id | 0 
Is the name or ID of the database to be shrunk. If 0 is specified, the current database is used. Database names must comply with the rules for identifiers.
target_percent
Is the percentage of free space that you want left in the database file after the database has been shrunk.
NOTRUNCATE 
Causes the freed file space to be retained in the database files. If not specified, the freed file space is released to the operating system.
TRUNCATEONLY 
Causes any unused space in the data files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_percent is ignored when TRUNCATEONLY is used.
WITH NO_INFOMSGS
Suppresses all informational messages that have severity levels from 0 through 10