Truncating SQL Log files using Powershell


SQLIn 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.

The script can be downloaded from the Microsoft Script Center Repository.

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:

  1. Microsoft® System CLR Types for Microsoft® SQL Server® 2012 (SQLSysClrTypes.msi)
  2. Microsoft® SQL Server® 2012 Shared Management Objects (SharedManagementObjects.msi)
  3. Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012 (PowerShellTools.msi)

I’ve linked to the x64 versions of these files, but you need to get the version that matches your OS.

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.


To run this script as a scheduled task:

# To schedule a Powershell script:

$ScriptPath = ‘C:\Scripts\Truncate-SQL.ps1’
$TaskName = ‘TruncateSQL’
$TaskRun = “powershell.exe -NoLogo -NonInteractive -WindowStyle Hidden -Command “”$ScriptPath”””

# Example: Weekly on Sundays at 2 AM
SCHTASKS.EXE /Create /S $Env:COMPUTERNAME /RU SYSTEM /SC WEEKLY /D SUN /TN $TaskName /TR $TaskRun /ST 02:00 /RL HIGHEST /F
break

# Example: Daily at 7 AM
SCHTASKS.EXE /Create /S $Env:COMPUTERNAME /RU SYSTEM /SC DAILY /TN $TaskName /TR $TaskRun /ST 07:00 /RL HIGHEST /F
break

# Run now:
SCHTASKS /Run /TN “\$TaskName”
break

<#
Use
SchTasks.exe /Create /?
To see switch details
#>

Advertisements

One response

  1. Pingback: You don’t need to hunt for Powershell modules anymore | Sam's Corner

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s