Re: Replication left the database in a weird state

Tech-Archive recommends: Fix windows errors by optimizing your registry

I feel your pain.

answers in line.

--
RelevantNoise.com - dedicated to mining blogs for business intelligence.

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
"InvestorTrade" <shija03@xxxxxxxxx> wrote in message
news:1191964317.930367.12100@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On my main DB Server (Running MSSQL Server 2000 latest SP) I'm
publishing the database, mybigdb (540Gb data file - my drive is
560Gb), using transactional and merging replication. Everything was
great until this morning, when I ran out of HDD space - so in order to
clear up some drive space I wanted to DBCC SHRINKFILE the Log
transactional file (which worked in the past), which requires for the
replications to be dropped, the Log transactions to be marked as
reset, and finally I could shrink the Log file. I started taking the
first step:

- Run the SQL Scripts to delete the two replications (Transactional
and Merging). During the dropping process the delete script started
hanging, I waited long enough, until I decided to stop SQL Agent and
SQL Server and restart it... This is when all the problems started...

When I restarted SQL Server...
- Enterprise Manager could not expand the "Databases" folder - it hung
- SQL Query Analyzer could not access the Databases catalog either (it
gave time outs)

I stopped, once more, SQL Server and moved the database 'mybigdb' to a
different directory outside the %SQLSERVER%\DATA directory to
somewhere else (this only required the inode to change without
requiring storage for copy). I then restarted the SQL Server service
once more, this time I could actually drill in the Enterprise Manager
to the Databases folder and the same worked for SQL Query Analyzer.
At this point the 'mybigdb' database was marked by Enterprise Manager
as a 'SUSPECT' database.

What can I do? I need to be able to bring this database back online -
I've taken the following steps, but I am not sure how to proceed to
bring the DB back - also there are a few 'bad' facts about this DB:

Bad facts:
1- No backup of the DB was ever made - so all I have is the MDF and
LDF file of mybigdb.
2- There are no additional drives on the system, so all I have is the
560Gb drive with 20Gb left of space.

New Facts:
1- Bought a 1Tb drive and copied the 560Gb data and log file...
2- Backed up all other DBs (msdb, myappdb,...) on the the server to
the new 1Tb drive

So here are my main questions:

1- Could it be that MSSQL Server, when it tries to start my 560Gb it
needs more than the available 20Gb storage left in the drive?

It will requre some space as checkpoints and redo and undo are done, but
probably not 20 gigs.

2- When I look at the publishing server it still believes that there
is a transactional publication of 'mybigdb', but I can not remove it
because the DB is under SUSPECT on Enterprise Manager (and the file is
no longer under the expected location - remember, I moved it).

you should be able to remove the entries from
distribution.dbo.MSpublisher_databases, mspublications,MSsubscriber_info
MSsubscriber_schedule, MSsnapshot_agents, MSlogreader_agents,
MSdistribution_agents, and MSsubscriptions.

Then make sure you delete the jobs from msdb.

3- I've tried to DELETE the database from Enterprise Manager (without
deleting the actual file), but Enterprise Manager complains that the
DB is under replication (which it shouldn't be the case)

Backup your master database, start sql server in console mode with the -f
switch. Then edit out the database reference from sysdatabases.
4- I've Googled around and seen that people state to place the DB
under Safe Mode or Test Mode, so one can access it and stat taking
DBCC dump of it - which I don't want to think is the case yet - since
I have stored procedures and many other things.


try sp_reset_Status to put it into emergency mode. You should be able to
access most objects in this mode (emergency mode).

If this fails try:

update sysdatabases
set status = status & ~256
where name = 'MySuspectDatabase'


I've learned my lesson, and I will NEVER allow for any other
deployment to go without a required backup plan. Any help or
assistance would be greatly appreciated.


We all feel your pain. Most of us encounter this at least once a lifetime.


.

Relevant Pages

  • Re: How to Replicate an SQL Server 2000 Database
    ... If i do this in Query Analyser should i have the DB closed? ... Now regarding Replication, i am brand new to this... ... And then stop and start SQL Server. ... Director of Text Mining and Database Strategy ...
    (microsoft.public.sqlserver.replication)
  • Re: Advice Requested : Disaster Recovery with 2 Drives (No Raid) with SQL Server 2008
    ... so we need frequent copies of the database for testing, ... Which drive for logfile backups? ... Why are you using Enterprise edition of SQL Server if you only have Std ... drives with no Raid. ...
    (microsoft.public.sqlserver.programming)
  • Re: Adding another server instance to another hard drive?
    ... A disaster recovery plan will help you minimize data loss. ... If your computer crashes and/or you lose your SQL Server drive, ... get lucky with a successful attach of the database files if your database ... > drives to actively store data. ...
    (microsoft.public.sqlserver.server)
  • Re: Replication Suggestions
    ... Looking for a SQL Server replication book? ... >I have an application with a sql backend. ... > database on the webbox and an internal database for inter-company use. ...
    (microsoft.public.sqlserver.replication)
  • Replicate to MS Access via the Internet
    ... Can I use SQL Server’s inbuilt replication to replicate between a SQL Server ... database and a number of MS Access 97, 2000 and 2002 databases over the ... The clients modify their local ...
    (microsoft.public.sqlserver.replication)