Posts tagged “Poweshell

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