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.
Showing posts with label cant. Show all posts
Showing posts with label cant. Show all posts
Sunday, March 25, 2012
Monday, March 19, 2012
Drop 'syncobj_0x' View
Hi,
I have found many view objects starting with the 'syncobj_0x' in publication
database but I can't drop these views because of dependancy on itself. How
can I purge them?
Thx
Ron
Do not drop these views - they don't take up appreciable space in your
database and they are required to build your snapshot.
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
"555 Rider" <ronchukw@.yahoo.com> wrote in message
news:%23x7gEFwpFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have found many view objects starting with the 'syncobj_0x' in
publication
> database but I can't drop these views because of dependancy on itself. How
> can I purge them?
>
> Thx
> Ron
>
I have found many view objects starting with the 'syncobj_0x' in publication
database but I can't drop these views because of dependancy on itself. How
can I purge them?
Thx
Ron
Do not drop these views - they don't take up appreciable space in your
database and they are required to build your snapshot.
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
"555 Rider" <ronchukw@.yahoo.com> wrote in message
news:%23x7gEFwpFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have found many view objects starting with the 'syncobj_0x' in
publication
> database but I can't drop these views because of dependancy on itself. How
> can I purge them?
>
> Thx
> Ron
>
Wednesday, March 7, 2012
Drop a suspect database
a sql 7.0 database goes suspect because the HD fails. Now i cant restore the database from the backup neither delete the database. How can i do it
In the fail i lose the mdf archiveRestart the sqlserver in mimum configuration mode.
Then delete the entry in the master.dbo.sysdatabases.
Restart the sqlserver.
"Rafael Araujo" <raraujoh@.hotmail.com> wrote in message
news:B1E10C04-6C75-4B0B-9958-2D38B709CF7C@.microsoft.com...
> a sql 7.0 database goes suspect because the HD fails. Now i cant restore
the database from the backup neither delete the database. How can i do it?
> In the fail i lose the mdf archive|||So you want to drop the database in order to be able to restore it from a
backup?
If so, first try DROP DATABASE from query analyzer. If that doesn't work,
try sp_dbremove.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rafael Araujo" <raraujoh@.hotmail.com> wrote in message
news:B1E10C04-6C75-4B0B-9958-2D38B709CF7C@.microsoft.com...
> a sql 7.0 database goes suspect because the HD fails. Now i cant restore
the database from the backup neither delete the database. How can i do it?
> In the fail i lose the mdf archive
In the fail i lose the mdf archiveRestart the sqlserver in mimum configuration mode.
Then delete the entry in the master.dbo.sysdatabases.
Restart the sqlserver.
"Rafael Araujo" <raraujoh@.hotmail.com> wrote in message
news:B1E10C04-6C75-4B0B-9958-2D38B709CF7C@.microsoft.com...
> a sql 7.0 database goes suspect because the HD fails. Now i cant restore
the database from the backup neither delete the database. How can i do it?
> In the fail i lose the mdf archive|||So you want to drop the database in order to be able to restore it from a
backup?
If so, first try DROP DATABASE from query analyzer. If that doesn't work,
try sp_dbremove.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rafael Araujo" <raraujoh@.hotmail.com> wrote in message
news:B1E10C04-6C75-4B0B-9958-2D38B709CF7C@.microsoft.com...
> a sql 7.0 database goes suspect because the HD fails. Now i cant restore
the database from the backup neither delete the database. How can i do it?
> In the fail i lose the mdf archive
Subscribe to:
Posts (Atom)