3.Powershell: SQL Script to truncate SQL log files for all user databases
In active SQL databases, log files may grow to very large sizes and cause performance degradation. The following script truncates SQL log files for all databases on the current SQL server.
This script needs SQLPS module. This is installed by default with SQL 2012 and higher versions. If you’re using an older SQL version, you need to download and install the following 3 components in order:
Microsoft® System CLR Types for Microsoft® SQL Server® 2012 (SQLSysClrTypes.msi)
Microsoft® SQL Server® 2012 Shared Management Objects (SharedManagementObjects.msi)
Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012 (PowerShellTools.msi)
You also need to remember to run the command:
Import-Module SQLPS
then close ISE and reopen it again. After that you can run the script.
# Script to truncate SQL log file for all user DBs on current SQL server
# Sam Boutros - 6/5/2014 - V1.0
# 7/28/14 - V1.1 - Cosmetic re-write..
# Truncate-Log.ps1
#
function Log {
[CmdletBinding()]
param(
[Parameter (Mandatory=$true,Position=1,HelpMessage="String to be saved to log file and displayed to screen: ")][String]$String,
[Parameter (Mandatory=$false,Position=2)][String]$Color = "White",
[Parameter (Mandatory=$false,Position=3)][String]$Logfile = $myinvocation.mycommand.Name.Split(".")[0] + "_" + (Get-Date -format yyyyMMdd_hhmmsstt) + ".txt"
)
write-host $String -foregroundcolor $Color
((Get-Date -format "yyyy.MM.dd hh:mm:ss tt") + ": " + $String) | out-file -Filepath $Logfile -append
}
#
# Import-Module SQLPS # See notes..
$Logfile = (Get-Location).path + "\Truncate_" + $env:COMPUTERNAME + (Get-Date -format yyyyMMdd_hhmmsstt) + ".txt"
# skipping first 4 databases: master, tempdb, model, msdb
(Invoke-SQLCMD -Query "SELECT * FROM sysdatabases WHERE dbid > 4") | ForEach-Object {
$SQLLogString = "N'" + (Invoke-SQLCMD -Query ("SELECT name FROM sys.master_files WHERE database_id = " + $_.dbid + " AND type = 1;")).name + "'"
Set-Location -Path ($Logfile.Split(":")[0] + ":")
log ("Truncating log file $SQLLogString for database " + $_.name + " (database_id = " + $_.dbid + ")") Cyan $Logfile
Invoke-SQLCMD -Query ("USE [" + $_.name + "]; ALTER DATABASE [" + $_.name + "] SET RECOVERY SIMPLE WITH NO_WAIT;")
Invoke-SQLCMD -Query ("USE [" + $_.name + "]; DBCC SHRINKFILE($SQLLogString, 1); ALTER DATABASE [" + $_.name + "] SET RECOVERY FULL WITH NO_WAIT")
}
|