Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is detaching and again Attaching a Database will affect the Replication Expand / Collapse
Author
Message
Posted Saturday, July 12, 2008 2:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 27, 2010 12:44 PM
Points: 14, Visits: 89
I need to detach and attach the database being replicated to shrink the database.
Is it will affect the replication process currently being runnning.
Secondly If i will backup and restore the database with the same name as replication process going on, will this make any affect on replication.

Please advise..

Thanks
Post #533044
Posted Saturday, July 12, 2008 3:18 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:27 PM
Points: 5,974, Visits: 12,875
you don't need to detach\attach the database to shrink it.

you can backup and restore the database without affecting replication as long as replication is quiesced (stop all the replication agents)


---------------------------------------------------------------------

Post #533047
Posted Saturday, July 12, 2008 5:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:30 PM
Points: 33,055, Visits: 15,167
You also don't want to shrink it unless some event made it much larger than needed. You should have free space in the file to handle data and index growth (and rebuilding indexes) as file growth can be an expensive operation.

Shrinking can also cause fragmentation in your tables, so you'd need to rebuild indexes afterwards, which means you'd need 2x the largest index in space.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #533062
Posted Monday, July 14, 2008 9:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 27, 2010 12:44 PM
Points: 14, Visits: 89
On secure side,I did take backup of DB and restore on another server and found DB shrink to approximately 50%.
As on actual server where DB resides, Many users are getting timeout from application queries.
As well as replication is running of that DB.
Can you please advise me, if i have to backup and restore the DB,, what steps I have to take that will not affect replication.

Thanks

Jaswinder Gogia
Post #533685
Posted Monday, July 14, 2008 9:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 27, 2010 12:44 PM
Points: 14, Visits: 89
On secure side,I did take backup of DB and restore on another server and found DB shrink to approximately 50%.
As on actual server where DB resides, Many users are getting timeout from application queries.
As well as replication is running of that DB.
Can you please advise me, if i have to backup and restore the DB,, what steps I have to take that will not affect replication.

Thanks

Jaswinder Gogia
Post #533686
Posted Monday, July 14, 2008 3:33 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:27 PM
Points: 5,974, Visits: 12,875
as long as you completely stop replication, you can safely backup db and restore it to a new location (on the same server) if that is what you want to do.
If you are restoring it to an earlier point in time you will have to reinitialise replication.
If you are moving to a new server you will gave to remove replication from the otiginal server first, then recreate on the new server, (script it out)
If you are getting timeouts thats likely to be poor code or perhaps indexes need de-fragmenting or stats updating.


---------------------------------------------------------------------

Post #533941
Posted Monday, July 21, 2008 10:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 27, 2010 12:44 PM
Points: 14, Visits: 89
Do I have to stop the replication agent for that subscription only where I have to backup and restore the Database.
Or should I have to stop the whole replication?

Post #537791
Posted Monday, July 21, 2008 10:48 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:27 PM
Points: 5,974, Visits: 12,875
if it was me i would stop all agents

---------------------------------------------------------------------

Post #537828
Posted Monday, July 21, 2008 12:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 27, 2010 12:44 PM
Points: 14, Visits: 89
Can you also let me know How I can update stastics
UPDATE STATISTICS TableName
Will it enhance the execution of queries.


Thanks
Jaswinder
Post #537905
Posted Tuesday, July 22, 2008 5:36 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:50 AM
Points: 743, Visits: 1,542
Do you also have to stop all the replication agents if you are dropping or adding article to the subscription?


Post #538343
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse