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=2 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

Post a Comment

Popular posts from this blog

SQL Code Review-Tips

General Standards: (Code Format, Naming Conventions, Datatype and Data Length, Syntax): §   Always follow a template in designing stored procedure so that it can easier developer job while designing and integrating. For example each stored procedure should be defined as various blocks such as “Comments Section”, “Variable Declaration”, “Actual Body”, “Audit”, ”Exception Handling”, “Temp_Obj_Removel” and define environment sections if any required. §   Check proper comments are used or not. Always describe procedure, inputs and expected output in comments section. §   Check naming conventions are used properly for procedure name, variables and other internal objects. §   Check all objects used inside the procedure are prefixed with the schema name and column names are referencing with table alias. §   Check all table columns used / mapped are using the correct datatypes and column length. §   Check if all required SET based options enable...
Post a Comment
Read more

Code Review - Tips

1.      Check for all the warnings given on compiling of the code and try to remove the warnings 2.      Perform  Code analysis tool and check for the following a.      Constraint Violators b.      Redundancies in Code c.       Redundancies in Symbol Declarations d.      And most important – Solve the Compiler Error 3.      “null” check needs to be performed where every application to avoid Null Reference Exception error. 4.      Naming Conventions should be followed with all variables following Camel Casing , and Class names following Pascal Casing. This rule helps in making Code better readable and this makes it more maintainable. 5.      Liskov substitution – Any derived class should not modify the behaviour of the Base Class. 6.      Code Reusability –...
Post a Comment
Read more