Tag: DBCC Shrinkfile

  • Script – Generate DBCC ShrinkFile for each datbase files

    You can use below query to generate DBCC Shrikfile command for data and log file. Next step you have to copy the output from the result windows & execute it.

    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.