Sql-server – Can we shrink datafile of a database which is configured with log shipping

log-shippingshrinksql serversql-server-2012transaction-log

I am using MS SQL Server 2012 version RTM, below is our environment,

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?

Best Answer

No, shrink won't break Your log shipping configuration. But You must be aware that both shrink (and rebuild/reorganize You will have to do afterwards) will make Your transaction log files grow a lot. All of those operations cause a lot of I/O load that is logged to transaction logs.

This, in practice, might mean that while Your log shipping won't break, it will make restore last a lot longer, depending on Your backup/copy/restore jobs frequency of course. That might lead to secondary falling behind a bit till shrink's (and defrag's) end.

Remember that shrink has serious issues, read Paul Randal's post about it, if You haven't already:

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance/

https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

Related Solutions

Sql-server – Will removing a 2nd log file break log shipping

Removing the extra file won't give you any problems.

Sql-server – Log shipping in sql server

Assuming you have taken log backups since the log file grew, you should be able to shrink the file without changing recovery models.

But you absolutely, 100% have to understand that shrinking the log file is an absolutely useless activity if the log file is going to grow again. Let it stay as big as it's going to get during normal operations, because shrinking it to free space temporarily doesn't gain you anything. What will you use that space for today when you know that tomorrow the log file will grow again? This is like watering the lawn right before a thunderstorm. Perhaps you are not backing up the log often enough? Doing it more often

Please read this in full before proceeding. And I really mean in full. Like, the whole thing.

Now, if you have read that entire page, and are absolutely, 100% sure that you need to shrink the log file, and that it is a worthwhile activity because you know the log file won't grow again, then:

USE your_database;
GO
DBCC SHRINKFILE(your_database_log, 100); -- hypothetical, target size is in MB
GO

If you don't know that the log file won't grow again, put the mouse down and leave the log file alone.

Best Answer

Related Solutions

Sql-server – Will removing a 2nd log file break log shipping

Sql-server – Log shipping in sql server

Related Question