Skip to main content

Shrink all SQL Server databases transaction log files

PROBLEM:
You want to shrink all SQL Server databases transaction log files to clean up some space

SOLUTION:
Use the following script to shrink all databases transaction log files. You can use it also as a maintenance job using SQL Server Agent

DECLARE @Log_name VARCHAR(1000)
DECLARE @Db_name VARCHAR(1000)
DECLARE @Recovery_model_desc VARCHAR(1000)
DECLARE @SQL nvarchar(2000)
DECLARE @ParmDefinition nvarchar(1000)
DECLARE @SizeAfter int

DECLARE db_cursor CURSOR FOR
SELECT
       F.NAME AS [LOG_NAME],
       DB.NAME AS [DB_NAME],
       DB.RECOVERY_MODEL_DESC AS [RECOVERY_MODEL_DESC]
FROM
       MASTER.SYS.MASTER_FILES F INNER JOIN MASTER.SYS.DATABASES DB
       ON DB.DATABASE_ID = F.DATABASE_ID
WHERE F.FILE_ID=AND DB.NAME <> 'tempdb'

OPEN db_cursor 
       FETCH NEXT FROM db_cursor INTO @Log_name, @Db_name,@Recovery_model_desc
       WHILE @@FETCH_STATUS = 0 
       BEGIN 
              SET @SQL = N'
              ALTER DATABASE '+ @Db_name + N' SET RECOVERY SIMPLE WITH NO_WAIT
              SELECT F.SIZE
              FROM MASTER.SYS.MASTER_FILES F INNER JOIN MASTER.SYS.DATABASES DB
              ON DB.DATABASE_ID = F.DATABASE_ID
              WHERE F.NAME = ''' + @Log_name +''' AND DB.NAME =  ''' +@Db_name+'''
              '

              SET @SQL = @sql +'
              USE [' + @Db_name + ']
              DBCC SHRINKFILE (' + @Log_name + ', 10)';
             
              SET @sql = @sql + N'
              SELECT F.SIZE
              FROM MASTER.SYS.MASTER_FILES F INNER JOIN MASTER.SYS.DATABASES DB
              ON DB.DATABASE_ID = F.DATABASE_ID
              WHERE F.NAME = ''' + @Log_name +''' AND DB.NAME =  '''+@Db_name+'''
              '
             
              SET @SQL = @SQL + N'
              ALTER DATABASE ' + @Db_name + N' SET RECOVERY ' +@Recovery_model_desc + ' WITH NO_WAIT
              '

              SET @ParmDefinition =N'@Size int OUTPUT';
              EXECUTE sp_executesql @SQL ,@ParmDefinition,@Size = @SizeAfterOUTPUT;
              FETCH NEXT FROM db_cursor INTO @Log_name, @Db_name,@Recovery_model_desc
       END 

CLOSE db_cursor
DEALLOCATE db_cursor

Comments

Popular posts from this blog

Connecting SQL Server on Azure VM

Steps for configuring SQL Server connectivity in an Azure VM The connection path is summarized by the following diagram: Create a TCP endpoint for the virtual machine In order to access SQL Server from the internet, the virtual machine must have an endpoint to listen for incoming TCP communication. This Azure configuration step, directs incoming TCP port traffic to a TCP port that is accessible to the virtual machine. Note If you are connecting within the same cloud service or virtual network, you do not have to create a publically accessible endpoint. In that case, you could continue to the next step. For more information, see   Connection Scenarios . 1.       On the Azure Portal, select   Virtual machines (classic) . 2.       Then select you SQL Server virtual machine. 3.      Select   Endpoints , and then click the   Add   button at the top of the Endpoints blade. ...