3

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?

Improve this question
1
  • 1
    After you have shrunk the files, please make sure you apply SQL Server 2012 Sp2, RTM version is not supported and many bugs are fixed in Sp2
    – Shanky
    Commented Aug 19, 2016 at 9:46

2 Answers 2

Reset to default
1

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/

Improve this answer
4
  • Thanks for answering, To archive the database we are deleting the old data and making the disk drive to be free and use, shrinking the database or data file causes fragmentation i know it but to release space we need to shrink data file.
    – Siva
    Commented Aug 19, 2016 at 9:24
  • I have log backups every 5 minutes, will this reduce overload or increase for log file while shrinking.
    – Siva
    Commented Aug 19, 2016 at 9:31
  • You can start with TRUNCATEONLY option, but: 1) it might be not enough (data in files might be scattered even when indexes are defragmented); 2) with multiple data files and trace flag 1118 it might require growing up some of files back to the size of biggest one (don't have experience with shrinking in this configuration, so I'm not 100% sure how it will behave). Still, if it will work well enough for You, You will be able to skip defragmenting after that.
    – Marcin S.
    Commented Aug 19, 2016 at 9:32
  • Logs will probably grow anyway, with 5 minutes frequency whole process should be a lot smoother than with, say, 1 hour between each backup/restore job.
    – Marcin S.
    Commented Aug 19, 2016 at 9:34
0

You can perform shrink on primary database it will be carried over to secondary database, it will not break your log shipping.But only if your secondary is in read only state.If you have configured your secondary in Norecovery mode the changes will not be carried over to secondary as no operation can be performed in norecovery mode. But remember shrinking Data file is not a best practice

Improve this answer
4
  • 1
    Thanks for your answer, My secondary database is in read-only mode, i hope my database will be safe if i do shrinking, I know if we shrink the database it will be fragmented, but our business client will not listen, they say to acquire the space.
    – Siva
    Commented Aug 19, 2016 at 9:28
  • 1
    I have log backups every 5 minutes, will this reduce overload or increase for log file while shrinking.
    – Siva
    Commented Aug 19, 2016 at 9:34
  • 1
    I don't think apart from fragmentation there will be any other problem in shrinking data file..but still shrinking 250 GB at one go !!! i don't prefer that
    – user103326
    Commented Aug 19, 2016 at 10:22
  • @AdroitSqlServ dbcc shrinkfile(N'DATAFILE', TRUNCATEONLY) works with datafiles too afaik. What issues have You had with that option? MSDN doesn't say anything about it: msdn.microsoft.com/en-us/library/ms189493.aspx
    – Marcin S.
    Commented Aug 19, 2016 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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