September 20, 2007 at 7:00 am
i need to move the primary log files of two databases on a subscriber server to a different disk. i know i can't detach them. i was thinking of stopping MSSQL service and just manually copying the files.
i'll get errors when i turn sql back on, how would i tell it the new location of the files? i'm trying to do this without breaking replication because it's going to take days to rerun the snapshots
October 1, 2007 at 2:55 pm
You should be able to detach subscriber databases. It's the publisher and distributor which cannot be detached.
October 1, 2007 at 6:11 pm
no can do, i get an error about the db being part of the replication or something like that
October 2, 2007 at 9:38 am
quiesce replication, make sure all transactions have reached the subscriber, backup up subscriber and restore with move option to new file locations (and reset dbowner if necessary)
---------------------------------------------------------------------
October 2, 2007 at 1:35 pm
SQL Noob (10/1/2007)
no can do, i get an error about the db being part of the replication or something like that
if the subscribers are "pull" subscribers I know for a fact that you can detach without any issues. I ma not sure about "push" subscribers though. What kind of subscription are you using ?
* Noel
October 4, 2007 at 3:07 am
If you are just moving the log file, do an ALTER DATABASE xxx ADD FILE to get a new log file wher you want it. You then need to get rid of your old logfile, which is can be an issue.
You can try removing the file in SSMS, but SQL may complain it is still in use. If so, I suggest you do a DBCC SHRINKFILE(oldlogid,1) to get your file size as small as possible, then set the old log to a fixed size so it will not grow. Finally do a DBCC SHRINKFILE (oldlogid,EMPTYFILE) and retry the remove.
If you start with the EMPTYFILE, you may end up being unable to do anything else to the file until the database is closed and reopened. Setting it to read-only and back to read-write, or restarting SQL, will do the job but these are options you probably want to avoid. If you start by shrinking the old log as much as possible, at least you minimise the impact if it cannot be removed without a db close and open.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply