SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is detaching and again Attaching a Database will affect the Replication


Is detaching and again Attaching a Database will affect the Replication

Author
Message
gogia.jaswinder
gogia.jaswinder
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 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
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24964 Visits: 13698
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)

---------------------------------------------------------------------
Steve Jones
Steve Jones
SSC Guru
SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)

Group: Administrators
Points: 147968 Visits: 19443
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
My Blog: www.voiceofthedba.com
gogia.jaswinder
gogia.jaswinder
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 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
gogia.jaswinder
gogia.jaswinder
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 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
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24964 Visits: 13698
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.

---------------------------------------------------------------------
gogia.jaswinder
gogia.jaswinder
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 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?
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24964 Visits: 13698
if it was me i would stop all agents

---------------------------------------------------------------------
gogia.jaswinder
gogia.jaswinder
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 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
Jpotucek
Jpotucek
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4400 Visits: 1680
Do you also have to stop all the replication agents if you are dropping or adding article to the subscription?



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search