SQL db shrink code

--Select Count(name) from sysdatabases
declare @db as varchar(100)
set @db = 'db to be shrunk goes here'

BACKUP LOG @db
With Truncate_only

-- Set truncate truncate on checkpoint to true
EXECUTE sp_dboption @db, 'trunc. log on chkpt.', TRUE

-- Checkpoint to truncate the inactive part of the transaction log
CHECKPOINT

-- Shrink Database with the specified % Free Space
DBCC SHRINKDATABASE (@db, 10)

-- Shrink file with the specified ID
DBCC SHRINKFILE(1)

-- Set truncate truncate on checkpoint to true
EXECUTE sp_dboption @db, 'trunc. log on chkpt.', FALSE

  Print

Comments

Please login to comment