Droping suspect publication database

Hi.
I have a publication databse that become suspect. It is a test database, and
I don't have backup.
I'm trying drop this, but I can't, SQL Server return a error message: Cannot
drop the database 'teste_replica' because it is being used for replication.
I tried reset suspect status with sp_resetstatus but it doesn't worked.
Any suggestion?
Tanks.
sio Nunes
If you have tried sp_resetstatus, I would just like to know whether you
STOPPED and RE-STARTED services of SQL thereafter?
I hope this helps !
Reetesh B. Chhatpar
ShawMan Software Enterprises
www.shawmansoftware.com
Diamond is just another piece of coal that did well under pressure.
"Esio Nunes" <esio_nunes@.hotmail.com> wrote in message
news:Or%23B$gV0EHA.3120@.TK2MSFTNGP12.phx.gbl...
> Hi.
> I have a publication databse that become suspect. It is a test database,
and
> I don't have backup.
> I'm trying drop this, but I can't, SQL Server return a error message:
Cannot
> drop the database 'teste_replica' because it is being used for
replication.
> I tried reset suspect status with sp_resetstatus but it doesn't worked.
> Any suggestion?
> Tanks.
> sio Nunes
>
|||what happens when you issue a
sp_replicationdboption 'PublicationDatabase','publish', 'false'
or
sp_replicationdboption 'PublicationDatabase','merge publish', 'false'
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Reetesh B. Chhatpar" <reetesh@.widesystems.com> wrote in message
news:exURfqV0EHA.3596@.TK2MSFTNGP12.phx.gbl...
> If you have tried sp_resetstatus, I would just like to know whether you
> STOPPED and RE-STARTED services of SQL thereafter?
> I hope this helps !
> Reetesh B. Chhatpar
> ShawMan Software Enterprises
> www.shawmansoftware.com
> Diamond is just another piece of coal that did well under pressure.
>
> "Esio Nunes" <esio_nunes@.hotmail.com> wrote in message
> news:Or%23B$gV0EHA.3120@.TK2MSFTNGP12.phx.gbl...
> and
> Cannot
> replication.
>
|||I can't acess this database, since it is suspect.
Error messeage with sp_replicationdboption:
Server: Msg 945, Level 14, State 2, Line 183
Database 'TESTE_REPLICA' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for details.
sio
"Hilary Cotter" <hilary.cotter@.gmail.com> escreveu na mensagem
news:uHh30zV0EHA.416@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> what happens when you issue a
> sp_replicationdboption 'PublicationDatabase','publish', 'false'
> or
> sp_replicationdboption 'PublicationDatabase','merge publish', 'false'
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Reetesh B. Chhatpar" <reetesh@.widesystems.com> wrote in message
> news:exURfqV0EHA.3596@.TK2MSFTNGP12.phx.gbl...
database,
>
|||I restarted the server, but it doesn't work.
sio
"Reetesh B. Chhatpar" <reetesh@.widesystems.com> escreveu na mensagem
news:exURfqV0EHA.3596@.TK2MSFTNGP12.phx.gbl...
> If you have tried sp_resetstatus, I would just like to know whether you
> STOPPED and RE-STARTED services of SQL thereafter?
> I hope this helps !
> Reetesh B. Chhatpar
> ShawMan Software Enterprises
> www.shawmansoftware.com
> Diamond is just another piece of coal that did well under pressure.
>
> "Esio Nunes" <esio_nunes@.hotmail.com> wrote in message
> news:Or%23B$gV0EHA.3120@.TK2MSFTNGP12.phx.gbl...
> and
> Cannot
> replication.
>
|||I've run into this problem.
I think the best way to get around it is to stop sql server and then
manually move the related database files to another location.
Then restart SQL Server and delete the rows from the sysdatabases. Then
recreate the database, and restore from backup.
I would advise you to open a support incident with Microsoft PSS on this one
for getting a supported way to fix this problem.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Esio Nunes" <esio_nunes@.hotmail.com> wrote in message
news:OuZP66V0EHA.1860@.TK2MSFTNGP15.phx.gbl...
>I can't acess this database, since it is suspect.
> Error messeage with sp_replicationdboption:
> Server: Msg 945, Level 14, State 2, Line 183
> Database 'TESTE_REPLICA' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
>
> sio
> "Hilary Cotter" <hilary.cotter@.gmail.com> escreveu na mensagem
> news:uHh30zV0EHA.416@.TK2MSFTNGP10.phx.gbl...
> database,
>
|||1. Put the db into EMERGENCY BYPASS STATUS (32768)
-- so you can rebuild the log
sp_configure 'allow', 1
go
reconfigure with override
update sysdatabases
set status = -32768
where name='yourDBname'
2. Stop sql server - rename log file (You will delete
it later)
3. Start sql server. Rebuild the log.
dbcc rebuild_log
('yourDBname', 'TheDriveForLogFiles:\PathToLogFiles\logfilename.l df')
3. Put db back from EMERGENCY BYPASS STATUS (32768) to
status 16
update sysdatabases
set status = 16 -- or 24
where 'yourDBname'
4. Clean up
sp_configure 'allow', 0
go
reconfigure with override
Now you have solved the most probable cause of your 'Suspect’ status: a
corrupted log file. After this you have a clean log file and SQL will be able
to start the DB not heaving to roll back or forward corrupted transactions.
Then you can run DBCC CheckDB to see if the data file is OK or you can drop
the database as you wish.
|||I tryied this but the problem is with the data file, not log file.
sio
"Adrian Mos" <AdrianMos@.discussions.microsoft.com> escreveu na mensagem
news:3EF556B1-B7A2-4CC8-B461-7093574C68F3@.microsoft.com...
> 1. Put the db into EMERGENCY BYPASS STATUS (32768)
> -- so you can rebuild the log
> sp_configure 'allow', 1
> go
> reconfigure with override
> update sysdatabases
> set status = -32768
> where name='yourDBname'
> 2. Stop sql server - rename log file (You will delete
> it later)
> 3. Start sql server. Rebuild the log.
> dbcc rebuild_log
> ('yourDBname', 'TheDriveForLogFiles:\PathToLogFiles\logfilename.l df')
> 3. Put db back from EMERGENCY BYPASS STATUS (32768) to
> status 16
> update sysdatabases
> set status = 16 -- or 24
> where 'yourDBname'
> 4. Clean up
> sp_configure 'allow', 0
> go
> reconfigure with override
>
> Now you have solved the most probable cause of your 'Suspect' status: a
> corrupted log file. After this you have a clean log file and SQL will be
able
> to start the DB not heaving to roll back or forward corrupted
transactions.
> Then you can run DBCC CheckDB to see if the data file is OK or you can
drop
> the database as you wish.