Question about sending a large db backup/restore then replicating

OK.
lets say i have a 100gb db. I really just want to replicate 1 table
within that db that is 65gb at like 100,000,000 rows(but who cares, we
will use the entire db for this example). This is a production db that
is constantly being updated. How would I be able to do something like a
no sync or something of that nature on a db that is constantly being
updated?
Wouldn't I need to shut off access of anything entering that db from the
point I started the backup to the point the subscriber does the restore,
and I can then setup the publication with "nosync"?? I guess I could
setup no sync and then manually dts the missing 1 day of information
over some other way and then we can manually sync items from there?
The other option i was thinking about was starting the 100gb snapshot,
disabling the distribution agent from starting to sync. Copy those
..bcp,.sch,.idx files to tape. Over night the tape to the subscriber, in
the mean time let the log file for the db grow like crazy since their
are articles marked for replication that cannot be delivered because the
agent is disabled. Then once they do a restore enable the distribution
agent and tell it to skip looking for the snapshot that was once there
and just deliver the transactions that are waiting in the log? Does
that make sense.
The short of this, Is that I am trying to figure out how to replicate a
100gb db without shutting off access to it. This db is updated 24/7.
tia, and sorry to be so long winded
-comb
Ideally you will kick off all of your users and backup and restore, bcp the
data out, or use DTS.
What I do is create a no-sync subscription at lets say midnight. Then I get
my data over, usually using bcp (if you can use snapshot isolation within
SQL 2005), and the deploy it on my subscriber. Then I start up my
distribution agent with the allow data consistency errors and I try to fix
the consistency issues.
Its very difficult but it seems to be the best option.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"combfilter" <asdf@.adsf.com> wrote in message
news:MPG.1e3843bad612d2a99896e4@.news.newsreader.co m...
> OK.
> lets say i have a 100gb db. I really just want to replicate 1 table
> within that db that is 65gb at like 100,000,000 rows(but who cares, we
> will use the entire db for this example). This is a production db that
> is constantly being updated. How would I be able to do something like a
> no sync or something of that nature on a db that is constantly being
> updated?
> Wouldn't I need to shut off access of anything entering that db from the
> point I started the backup to the point the subscriber does the restore,
> and I can then setup the publication with "nosync"?? I guess I could
> setup no sync and then manually dts the missing 1 day of information
> over some other way and then we can manually sync items from there?
> The other option i was thinking about was starting the 100gb snapshot,
> disabling the distribution agent from starting to sync. Copy those
> .bcp,.sch,.idx files to tape. Over night the tape to the subscriber, in
> the mean time let the log file for the db grow like crazy since their
> are articles marked for replication that cannot be delivered because the
> agent is disabled. Then once they do a restore enable the distribution
> agent and tell it to skip looking for the snapshot that was once there
> and just deliver the transactions that are waiting in the log? Does
> that make sense.
> The short of this, Is that I am trying to figure out how to replicate a
> 100gb db without shutting off access to it. This db is updated 24/7.
> tia, and sorry to be so long winded
> -comb

Question about restore replication database

Hi Guys,
I got a trouble that I tried to restore a database that has a replication
created on and I got an error after restore. In log reader agent and
distribution agent became red color and error message like "Could not execute
sp_repldone/sp_replcounters.." Sorry I cannot remember error message
exactly, and I am unable to restart the agent. I guess the reason might be
that I didn't restore distributiuon because I didn't backup this database. Do
I need to restore distribution databse when I restore publication database?
thanks.
Ideally your publication and distribution database would be in lock step by
using the sync with backup option.
Otherwise you should use sp_replrestart to get around problems like this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:63B4A13A-87EC-48B3-8867-EBD74B228A24@.microsoft.com...
> Hi Guys,
> I got a trouble that I tried to restore a database that has a replication
> created on and I got an error after restore. In log reader agent and
> distribution agent became red color and error message like "Could not
> execute
> sp_repldone/sp_replcounters.." Sorry I cannot remember error message
> exactly, and I am unable to restart the agent. I guess the reason might be
> that I didn't restore distributiuon because I didn't backup this database.
> Do
> I need to restore distribution databse when I restore publication
> database?
> thanks.
>
|||What is "sync with backup option"? Also, I looked in BOL and could not find
sp_replrestart. I am interested in this because we are seeting up a new
merge publication and am interested in how it affects backup/restore.
Thanks.
David
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23GOSOVNOHHA.780@.TK2MSFTNGP03.phx.gbl...
> Ideally your publication and distribution database would be in lock step
> by using the sync with backup option.
> Otherwise you should use sp_replrestart to get around problems like this.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Iter" <Iter@.discussions.microsoft.com> wrote in message
> news:63B4A13A-87EC-48B3-8867-EBD74B228A24@.microsoft.com...
>
|||This is for transactional replication and not merge replication.
for more info on sync with backup consult -
http://msdn2.microsoft.com/en-us/library/aa237094(SQL.80).aspx
for more info repl_restart consult
http://msdn2.microsoft.com/en-us/library/ms174390.aspx
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:%234Q6oLPOHHA.2232@.TK2MSFTNGP02.phx.gbl...
> What is "sync with backup option"? Also, I looked in BOL and could not
> find sp_replrestart. I am interested in this because we are seeting up a
> new merge publication and am interested in how it affects backup/restore.
> Thanks.
> David
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23GOSOVNOHHA.780@.TK2MSFTNGP03.phx.gbl...
>

Question about DBCC Shrinkfile

I've got a SQL Server database that was restored from production (SQL Server 2000) to a development instance (SQL Server 2005). After the restore I deleted about 90% of the user data, by copying 10% to a temp table, truncating, and re-inserting the data.

I tried to shrink the files, but the biggest file won't shrink. Running sp_helpfile, I see:

name fileid filename filegroup size maxsize growth usage
MyDB_Data 1 M:\Data\MyDB_Data.MDF PRIMARY 190504832 KB Unlimited 1024000 KB data only

So the file is about 181 GB, but I know that it's mostly empty space. Running sp_spaceused gives me 142531.77 MB unallocated space, which I know is mostly in the MDF file. When I run DBCC Shrinkfile(MyDB_Data, 170000), the command runs successfully but there is no change in the file size. Does anyone have any idea why the file won't shrink? I've seen this in log files before but never in data files.

Results from shrinkfile:

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
5 1 23813104 2560000 5686656 5686656

Dylan Peters

shrinkdb only shrinks the database down at most to its original created size. That means if the db file can only shrink down to the MinimumSize.|||

oj wrote:

shrinkdb only shrinks the database down at most to its original created size. That means if the db file can only shrink down to the MinimumSize.

In this case the minimum size is 2560000 pages or 10000 MB, just 10 GB. The current file size is 23813104 pages or 93019 MB, and the pages used comes out to 22214 MB. So I should be able to get it down to about 22 GB in theory. The BOL for shrinkfile says it can be used to shrink the file to less than its original created size, but I'm not going below 10 GB anyway.

Dylan Peters

|||No. You cannot shrink it less than the original created size.

http://msdn2.microsoft.com/en-us/library/ms190488.aspx

"The database cannot be made smaller than the minimum size of the database. The

minimum size is the size specified when the database is originally created, or

the last size explicitly set by using a file size changing operation such as

DBCC SHIRNKFILE or ALTER DATABASE. For example, if a database is originally

created with a size of 10 MB in size and grows to 100 MB, the smallest the

database can be reduced to is 10 MB, even if all the data in the database has

been deleted."

But that is not your current problem. My guess is that you have removed a bunch of blob but you can't reclaim the space. See the following kb for detail.

http://support.microsoft.com/default.aspx/kb/324432