January 30, 2018 at 7:06 am
Hello
I will need to set up an automatism
that checks the recovery mode of the bases
If base other than SIMPLE, force the transition to simple mode and shrink database import-module sqlps -DisableNameChecking
#[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
Add-Type -AssemblyName "Microsoft.SqlServer.Smo"
#Force change psdrive from SQLserver to c:\ -> without UNC link not working
cd c:
$mode_recuperation='simple'
$Server='sqlprod'
$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $Server
$SMOserver.Databases | where {$_.IsSystemObject -eq $false} | select Name, RecoveryModel | Format-Table # Ce ci pour enlever les bases systéme
$SMOserver.Databases | where {$_.IsSystemObject -eq $false} | foreach {$_.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple; $_.Alter()}#Forcer le passage vers database simpel
$SMOserver.Databases | where {$_.IsSystemObject -eq $false} | select Name, RecoveryModel | Format-Table #simple affichage
Who has an idea how I can modify my script to add the shrink part ?
January 30, 2018 at 7:13 am
Before you go ahead with this, are you aware of possible issues with shrinking databases?
You may like to read here for more information.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 30, 2018 at 7:28 am
yes
or it is an exceptional action
I have to run it only if I have disk saturation
January 30, 2018 at 7:31 am
joujousagem2006 1602 - Tuesday, January 30, 2018 7:28 AMyes
or it is an exceptional action
I have to run it only if I have disk saturation
Doesn't that mean that your database needs more disk space?
Changing recovery mode to simple is not an exceptional action.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 30, 2018 at 9:10 am
Not that I think this is a good idea, but once you have the db, or db files, you would use a PoSh command to execute dbcc shrinkdatabase or dbccshrinkfile.
You would be better off using placeholders to free up space when needed and then deal with the issue. https://voiceofthedba.com/2014/12/01/creating-placeholder-files/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply