Shrinking log files with SMO

Question

I know I can use the following t-SQL to shrink a log file:

BACKUP LOG db1 TO DISK = '\\server\share\db1_log1.trn';
DBCC ShrinkFile([db1_log], 0);

How do I do the same with SMO? I tried:

$server = new-Object Microsoft.SqlServer.Management.Smo.Server()

$dbBackup = new-object Microsoft.SqlServer.Management.Smo.Backup
$dbBackup.Action = "Log"
$dbBackup.Database = "db1"
$dbBackup.Devices.AddDevice("\\server\share\db1_log1.trn", "File")
$dbBackup.SqlBackup($server)

$db = $srv.Databases.Item("db1")
$db.TruncateLog()

But here, I found that the TruncateLog() method does not work with anything higher than SQL 2005. That link recommended using:

$db.RecoveryModel = RecoveryModel.Simple
$db.Alter()
$db.LogFiles[0].Shrink(0, ShrinkMethod.TruncateOnly)

However, switching to Simple Recovery Mode is going to break my log chain and I don't want to do that. Shrinking the log file doesn't break my log chain so I'm looking for an equivalent SMO method to do that. Does such a method exist?

Have you looked at this: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.shrink%28v=sql.105%29.aspx — , Apr 24 '13 at 20:40
@ShawnMelton, yes, that doesn't let you shrink the log when the version of SQL is greater than SQL 2005. — Elijah W. Gagne, Apr 25 '13 at 13:34
@ShawnMelton The command is available in SMO for SQL 2008 R2, but it's only there for backwards compatibility for old version of SQL. See the link I provided in the question to http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/99596b45-2813-4803-92bb-3f4c117abb26/ — Elijah W. Gagne, Apr 25 '13 at 15:44

score 4 · Answer 1 · edited Apr 13 '17 at 12:42

4

Read up here before you shrink your log file.

You dont have to change the recovery model ...

Update:

you can use Invoke-sqlcmd cmdlet.

eg.

$query = "DBCC SHRINKFILE(db1_log)"
Invoke-Sqlcmd -ServerInstance $instanceName -Query $query

EDIT: Below is an excerpt from Powershell V3 cookbook ...

Not all DBCC commands are wrapped in SMO methods. For other DBCC commands that are not nicely wrapped in methods, you can still execute them using the Invoke-Sqlcmd cmdlet.

edited Apr 13 '17 at 12:42

Community

1

answered Apr 24 '13 at 20:48

Kin Shah

62,225
6
120
236

1

OP intends to shrink ONLY the log file. The shrink command provided shrink the database (as equivalent to DBCC SHRINKDATABASE) – Travis Apr 24 '13 at 20:55
Apologies, but updated it with the correct answer. Thanks ! – Kin Shah Apr 24 '13 at 21:10
I hate to be picky because I know Invoke-Sqlcmd is still PowerShell. However, I'm really looking for a SMO based solution. – Elijah W. Gagne Apr 24 '13 at 22:02
I have updated my answer, as not all DBCC SHRINKFILE cannot be wrapped in SMO methods. – Kin Shah Apr 25 '13 at 13:22
@Kin, I'm looking for a SMO solution because most of the servers I'm working with do not have SQLPS installed on them, but they all have SMO. As ShawnMelton pointed out, there is a SMO solution, except that that is the same solution I found previously it is only supported with SQL 2005 and lower. It appears that the answer to this question is that there is no SMO solution for SQL 2008 and higher. Therefore I'll accept your answer. – Elijah W. Gagne Apr 25 '13 at 13:37

score 2 · Accepted Answer · answered May 10 '13 at 18:30

@elijah,

SMO does have function to shrink file. The PowerShell script below shows the log.shrink method is used to shrink ONLY the log file. The shrink with default or truncateonly option work for me with full recovery model.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

$server = Read-Host 'Enter your SQL Server instance'
$instance = New-Object Microsoft.SqlServer.Management.Smo.Server $server

$dbname = 'Testdb'
$db = New-Object Microsoft.SqlServer.Management.Smo.Database $instance, $dbname
$db.RecoveryModel = 'FULL'

$filegroup = New-Object Microsoft.SqlServer.Management.Smo.FileGroup $db, 'PRIMARY'
$db.FileGroups.Add($filegroup)

$datafilename = 'Testdb_data'
$datafile = New-Object Microsoft.SqlServer.Management.Smo.DataFile $filegroup, $datafilename
$datafile.FileName =  $instance.MasterDBPath + '\' + $datafilename + '.mdf'
$datafile.Size = (5 * 1024)
$filegroup.Files.Add($datafile)

$logfilename = 'Testdb_log'
$logfile = New-Object Microsoft.SqlServer.Management.Smo.LogFile $db, $logfilename
$logfile.FileName = $instance.MasterDBLogPath + '\' + $logfilename + '.ldf'
$logfile.Size = (1 * 1024)
$db.LogFiles.Add($logfile)

$db.Create()

write-host ""
write-host "Data file size before log expand, " $db.FileGroups['PRIMARY'].Files[0].Size
write-host "Log file size before log expand, " $db.LogFiles[0].Size

$db.LogFiles[0].Size = (10 * 1024)
$db.Alter()

write-host ""
write-host "Data file size before log shrink, " $db.FileGroups['PRIMARY'].Files[0].Size
write-host "Log file size before log shrink, " $db.LogFiles[0].Size

$db.LogFiles[0].Shrink(1, [Microsoft.SqlServer.Management.Smo.ShrinkMethod]::TruncateOnly)
$db.LogFiles[0].Refresh()

write-host ""
write-host "Data file size after log shrink, " $db.FileGroups['PRIMARY'].Files[0].Size
write-host "Log file size after log shrink, " $db.LogFiles[0].Size

$db.Drop()

The script show the log file was initially set at 1MB, change the size to 10MB, and shrink it back down to 1MB. Data file size remains at its initial size, 5MB.

Shrinking log files with SMO

2 Answers2