deleting restored dB that is read-only

I restored a dB and set it as read-only. The dB is being
used for replication, so in my Replication Monitor in EP,
all the publications are listed numerous times. I cannot
detach the dB because it is being used for replication.
I cannot use sp_removedbreplication because it is read-
only. So the question is, how do I remove the dB? How
do I change the read-only flag off?
Larry,
this should remove the database for you (just need to replace xxx with your
database name).
alter database xxx set read_write with rollback immediate
go
exec sp_removedbreplication xxx
go
use master
go
drop database xxx
go
Regards,
Paul Ibison
|||Paul,
I ran the commands you suggested and received the
following errors...
Server: Msg 5063, Level 16, State 1, Line 1
Database 'StoreMain-lpr10' is in warm standby. A warm-
standby database is read-only.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Server: Msg 3906, Level 16, State 1, Procedure
sp_dropsubscription, Line 441
Could not run BEGIN TRANSACTION in database 'StoreMain-
lpr10' because the database is read-only.
Server: Msg 3724, Level 16, State 3, Line 1
Cannot drop the database 'StoreMain-lpr10' because it is
being used for replication.
HELP!!!
Larry...
|||Larry,
this database is in RO mode because it has been restored without recovery.
The following script should be run before my original one:
alter database StoreMain-lpr10 set single_user with rollback immediate
go
restore database StoreMain-lpr10 with recovery
go
Regards,
Paul Ibison

Deleting all but top record.

Hey Guys,

I have Performance Monitor running and storing the network usage to my MsSQL database, and this is done a few times a minute. I have a page that then shows show much of my bandwidth is being used. As you can gather, the database quickly starts filling up with hundrreds of records so I could do with a script that delete these records.
I cant simply delete all records because that would cause my webpage to fail so I need a way to delete all records apart from the latest one.
Wondering if anyone would know how I could do this?delete from tableRecords where recordid < (select max(recordid) from tablerecords)

Nick|||

Thank you, that works great.
I've just however noticed a potential problem. I understand that the RecordID can only go so high then will give errors. The thing is, the PerfMon has only been running a few hours and already it has produced 1800+ records. With your script I can just keep it as one record in the table, but the RecordID will get higher and higher. Im wondering what will happening if I let this run for a week or so, eventually it will stop working.
Anyone got any ideas?

|||Dont use an auto-identity field.
insert into table
select max(recordid) + 1 from table, [rest of fields]
Since you will only have one record in there at a time, this shouldnt hurt your performance.
Nick

delete remnants of log shipping

I deleted all the maintenance plans but somehow the log shipping monitor
showing the log shipping pair still remains. How can i clear all the log
shipping ?Log shipping can be removed from the Maintenance Wizard. Is that where you
removed log shipping? Run the wizard and when you get to the Log Shipping
tab, click on Remove Log Shipping.
To remove a Log Shipping pair from the monitor, use
sp_delete_log_shipping_monitor_info. This is discussed in detail in BOL so
do a search and you'll find all you need.
joe.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eLoO1N4UFHA.3292@.TK2MSFTNGP14.phx.gbl...
>I deleted all the maintenance plans but somehow the log shipping monitor
> showing the log shipping pair still remains. How can i clear all the log
> shipping ?
>|||Not that MS supports hacking system tables, but msdb..log_shipping_monitor is
where this info is stored.
If the stored proc doesn't work, I've had to restort to cleaning this out
manually.
DO this at your own risk.
<standard disclaimer inserted here>
Donna Lambert
"Joe Yong" wrote:
> Log shipping can be removed from the Maintenance Wizard. Is that where you
> removed log shipping? Run the wizard and when you get to the Log Shipping
> tab, click on Remove Log Shipping.
> To remove a Log Shipping pair from the monitor, use
> sp_delete_log_shipping_monitor_info. This is discussed in detail in BOL so
> do a search and you'll find all you need.
>
> joe.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:eLoO1N4UFHA.3292@.TK2MSFTNGP14.phx.gbl...
> >I deleted all the maintenance plans but somehow the log shipping monitor
> > showing the log shipping pair still remains. How can i clear all the log
> > shipping ?
> >
> >
>
>