Script - Generate DBCC ShrinkFile for each datbase files

You can use below query to generate DBCC Shrikfile command for data and log file.

Query

select 'use ' + db_name(dbid) + char(13) + 'dbcc shrinkfile (' + quotename(sf.name,'''') + ' ,truncateonly)' from sysaltfiles sf
inner join sys.databases sd on sf.dbid = sd.database_id
where state_desc = 'online'


 

Shrink the log to as small a size as possible (truncateonly)

You can use the below command to shrink the database as small as possible.

DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)

NOTE: if you don't know the logical filename of your transaction log use sp_helpfile to list all of your database files.