Shrink database log files)

1. You only need to change the DBname to the database you want to shrink.
2. We recommend that you back up the database first.
3
4 declare @ dbname sysname
5 set @ dbname = 'dbname'
6 -- 1. Clear logs
7 exec ('dump TRANSACTION ['+ @ dbname +'] WITH NO_LOG ')
8
9 -- 2. truncate transaction logs:
10 exec ('backup LOG ['+ @ dbname +'] WITH NO_LOG ')
11
12 -- 3. compress the database file (if it is not compressed, the database file will not be reduced
13 exec ('dbcc SHRINKDATABASE (['+ @ dbname +']) ')
14
15 -- 4. Set automatic contraction
16 exec ('exec sp_dboption ''' + @ dbname + ''', ''autoshrink '', ''true ''')

To shrink a particularly large database log file, you only need to replace DBname with the database you want to shrink.
We recommend that you back up the database first.

Declare @ dbname sysname
Set @ dbname = 'dbname'
-- 1. Clear logs
Exec ('dump TRANSACTION ['+ @ dbname +'] WITH NO_LOG ')

-- 2. truncate transaction logs:
Exec ('backup LOG ['+ @ dbname +'] WITH NO_LOG ')

-- 3. compress the database file (if it is not compressed, the database file will not be reduced
Exec ('dbcc SHRINKDATABASE (['+ @ dbname +']) ')

-- 4. Set automatic contraction
Exec ('exec sp_dboption ''' + @ dbname + ''', ''autoshrink '', ''true ''')