Query to truncate log files or shrink all MS SQL Databases

1 post / 0 new
#1 20 April, 2017 - 18:40
Divya
Divya's picture

Query to truncate log files or shrink all MS SQL Databases

Execute the following query to shrink and truncate log files of all the databases in the instance. For MS SQL version 2000 & 2005
  1. USE master
  2. declare @DBName varchar(35),
  3. @str varchar (255),
  4. @str1 varchar(1000),
  5. @str2 varchar(1000),
  6. @str3 varchar(1000),
  7. @defaultRecoveryModel Varchar(100)
  8. declare DBRecoveryModelGenerator_cursor cursor FOR
  9. SELECT name FROM sysdatabases
  10. WHERE category IN ('0', '1','16')
  11. ORDER BY name
  12. open DBRecoveryModelGenerator_cursor
  13. fetch next FROM DBRecoveryModelGenerator_cursor INTO @DBName while (@@fetch_status <> -1)
  14. begin
  15. IF (@@fetch_status <> -2)
  16. begin
  17. SELECT @defaultRecoveryModel=Convert(Varchar(100),DATABASEPROPERTYEX (@DBName, 'Recovery'))
  18. IF(@defaultRecoveryModel<>'SIMPLE')
  19. BEGIN
  20. SELECT @str1 = 'ALTER DATABASE' + ' ' <a href="mailto:+@DBName">+@DBName</a>+ ' ' + 'SET RECOVERY SIMPLE'
  21. SELECT @str2 = 'BACKUP LOG' + ' ' <a href="mailto:+@DBName">+@DBName</a>+ ' ' + 'WITH NO_LOG'
  22. SELECT @str3 = 'DBCC SHRINKDATABASE(' + '' <a href="mailto:+@DBName">+@DBName</a>+ '' + ', TRUNCATEONLY)'
  23. PRINT 'EXECUTING - ' + @str1
  24. exec (@str1)
  25. PRINT 'EXECUTING - ' + @str2
  26. exec (@str2)
  27. PRINT 'EXECUTING - ' + @str3
  28. exec (@str3)
  29. SELECT @str1 = 'ALTER DATABASE' + ' ' <a href="mailto:+@DBName">+@DBName</a>+ ' ' + 'SET RECOVERY ' + @defaultRecoveryModel
  30. PRINT 'EXECUTING - ' + @str1
  31. exec(@str1)
  32.  
  33. END
  34. ELSE
  35. SELECT @str2 = 'BACKUP LOG' + ' ' <a href="mailto:+@DBName">+@DBName</a>+ ' ' + 'WITH NO_LOG'
  36. SELECT @str3 = 'DBCC SHRINKDATABASE(' + '' <a href="mailto:+@DBName">+@DBName</a>+ '' + ', TRUNCATEONLY)'
  37.  
  38. PRINT 'EXECUTING - ' + @str2
  39. exec (@str2)
  40. PRINT 'EXECUTING - ' + @str3
  41. exec (@str3)
  42.  
  43. end fetch next FROM DBRecoveryModelGenerator_cursor INTO @DBName end
  44. close DBRecoveryModelGenerator_cursor
  45. DEALLOCATE DBRecoveryModelGenerator_cursor
  46. go