Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Have you looked at this: msdn.microsoft.com/en-us/library/… –  Shawn Melton 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 social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/… –  Elijah W. Gagne Apr 25 '13 at 15:44
add comment

2 Answers

up vote 1 down vote accepted

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

share|improve this answer
add comment

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.

share|improve this answer
1  
OP intends to shrink ONLY the log file. The shrink command provided shrink the database (as equivalent to DBCC SHRINKDATABASE) –  Travis Gan Apr 24 '13 at 20:55
    
Apologies, but updated it with the correct answer. Thanks ! –  Kin 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 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.