Can we shrink datafile of a database which is configured with log shipping
-
Hello Everyone,
I have a primary database Server A and Secondary Database Server B Log shipping configured on A to B of a database,
1) Database size 500 GB, We would like to delete old data and make it to 250 GB, Can we do this without breaking of log shipping?
2) After this we need to shrink the data file not log file, Can we shrink data file without breaking of log shipping?
If we do shrinking do i need to stop log shipping jobs or will it break log shipping configuration?
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Save a tree... Please don't waste paper unless you really need to!
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'
-
info.sqldbamail (8/19/2016)
If we do shrinking do i need to stop log shipping jobs or will it break log shipping configuration?No and no.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
info.sqldbamail (8/19/2016)
Hello Everyone,I have a primary database Server A and Secondary Database Server B Log shipping configured on A to B of a database,
1) Database size 500 GB, We would like to delete old data and make it to 250 GB, Can we do this without breaking of log shipping?
2) After this we need to shrink the data file not log file, Can we shrink data file without breaking of log shipping?
If we do shrinking do i need to stop log shipping jobs or will it break log shipping configuration?
If you can tolerate the empty space usage, don't shrink the database. Just leave it be and it'll never have to grow again.
If you do shrink the datafile, you will need to rebuild the indexes because of the extreme fragmentation that shrink causes. That means that you'll need about 120% of the largest index you have (usually the clustered index on your largest GB table) on the MDF and, unless you're in the BULK Logged or Simple (which I avoid) Recovery Model, you'll need about that much space in your logfile, as well. If you do a whole table with the "ALL" option, all the indexes are built in a transaction and you'll need 120% of all the indexes combined including the clustered index.
Note that (IIRC) switching to BULK LOGGED may have an impact (as in break it) on log shipping so you may be stuck with having the log file grow a fair bit.
Like I said... better to just leave it alone if you don't actually need the space.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This was removed by the editor as SPAM
-
JasonClark (8/21/2016)
you can use NOTRUNCATE option with DBCC shrinkfile command.That will certainly avoid fragmentation problems but will provide mixed results and will likely not return all unused space because the file may have "gaps" in it. It will only truncate back to where then last extent is present in the file for MDF/NDF files and only back to the last "dirty" VLF for log files.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (8/19/2016)
Note that (IIRC) switching to BULK LOGGED may have an impact (as in break it) on log shipping so you may be stuck with having the log file grow a fair bit.
Log shipping works with Full or Bulk-Logged recovery. That said, the log backups will still be large in bulk-logged, and they need to be copied across and restored to the seconday. Probably won't be pretty.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Jeff Moden (8/22/2016)
and only back to the last "dirty" VLF for log files.Any shrink to the log file only removes space to the last active VLF. VLFs cannot be moved. Truncate_Only option is ignored when shrinking log files (as it's effectivly the default).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (8/22/2016)
Jeff Moden (8/22/2016)
and only back to the last "dirty" VLF for log files.Any shrink to the log file only removes space to the last active VLF. VLFs cannot be moved.
That's what I was saying but...
Truncate_Only option is ignored when shrinking log files (as it's effectivly the default).
... definitely learned something new there. Thanks, Gail.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply