Can't get exclusive access

While attempting to restore a db, I get the following error message:
"RESTORE DATABASE is terminating abnormally.
Exclusive access could not be obtained because the database is in use."
But looking at Current Activity in EM, I see nothing logged into the
database. The database I am restoring to is set to Single-User. Any
suggestions?
SQL 2000, sp4
Thanks,
SteveRunning the statement first
ALTER DATABASE <Nameofthedb> SET SINGLE_USER with rollback immediate
If you want to do that from Query Analyzer, make sure that you cursor is NOT
positioned on the DB node you want to restore.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Steve Deering" <Steve@.vin.com> schrieb im Newsbeitrag
news:1118060114.644682.280310@.g43g2000cwa.googlegroups.com...
> While attempting to restore a db, I get the following error message:
> "RESTORE DATABASE is terminating abnormally.
> Exclusive access could not be obtained because the database is in use."
> But looking at Current Activity in EM, I see nothing logged into the
> database. The database I am restoring to is set to Single-User. Any
> suggestions?
> SQL 2000, sp4
> Thanks,
> Steve
>|||If it still complains, disconnect all your EM sessions.
"Steve Deering" <Steve@.vin.com> wrote in message
news:1118060114.644682.280310@.g43g2000cwa.googlegroups.com...
> While attempting to restore a db, I get the following error message:
> "RESTORE DATABASE is terminating abnormally.
> Exclusive access could not be obtained because the database is in use."
> But looking at Current Activity in EM, I see nothing logged into the
> database. The database I am restoring to is set to Single-User. Any
> suggestions?
> SQL 2000, sp4
> Thanks,
> Steve
>|||Hi,
Try the below:-
1. From Query analyzer set the database to SINLE USER.
USE MASTER
GO
ALTER DATABASE <DBNAME> SET SINGLE_USER with rollback immediate
2. Restore the database
RESTORE DATABASE <DBNAME> FROM DISK='FILENAME WITH PATH' WITH STATS=10
Incase if the restore still fails then, close the query analyzer fully once
and try the RESTORE DATABASE again.
Thanks
Hari
SQL Server MVP
"Steve Deering" <Steve@.vin.com> wrote in message
news:1118060114.644682.280310@.g43g2000cwa.googlegroups.com...
> While attempting to restore a db, I get the following error message:
> "RESTORE DATABASE is terminating abnormally.
> Exclusive access could not be obtained because the database is in use."
> But looking at Current Activity in EM, I see nothing logged into the
> database. The database I am restoring to is set to Single-User. Any
> suggestions?
> SQL 2000, sp4
> Thanks,
> Steve
>|||Make sure that the database is NOT selected in EM. I've had the exact same
problem and when I selected a different db in EM (master for instance) and
re-ran the restore from Query Analyzer it worked fine.
"Steve Deering" wrote:
> While attempting to restore a db, I get the following error message:
> "RESTORE DATABASE is terminating abnormally.
> Exclusive access could not be obtained because the database is in use."
> But looking at Current Activity in EM, I see nothing logged into the
> database. The database I am restoring to is set to Single-User. Any
> suggestions?
> SQL 2000, sp4
> Thanks,
> Steve
>|||If you are married to using the SQLEM, then here is a trick I often use:
1. Right-click the database.
2. Detach the database. If there are users currently connected, then select
to disconnect them all without notifying them. When it is done, cancel so
you don't actually detach the database.
3. Now, right-click again and set Offline.
Now, you should be able to restore the database without any problems. Yes,
you can restore a database if it is marked offline.
Sometimes, however, you can not click fast enough to set it offline before
another user logs in. You can try this procedures a few time, but at some
point, you are just going to be stuck with script.
I usually use the same syntax as everyone else has shown you, but I like to
set the database offline instead of single-user.
Regardless, before SQL Server 2000, there was no other way to disconnect
users other than scripting out multiple KILL commands to get rid of all the
connections.
Sincerely,
Anthony Thomas
"Steve Deering" <Steve@.vin.com> wrote in message
news:1118060114.644682.280310@.g43g2000cwa.googlegroups.com...
While attempting to restore a db, I get the following error message:
"RESTORE DATABASE is terminating abnormally.
Exclusive access could not be obtained because the database is in use."
But looking at Current Activity in EM, I see nothing logged into the
database. The database I am restoring to is set to Single-User. Any
suggestions?
SQL 2000, sp4
Thanks,
Steve

Can't get exclusive access

While attempting to restore a db, I get the following error message:
"RESTORE DATABASE is terminating abnormally.
Exclusive access could not be obtained because the database is in use."
But looking at Current Activity in EM, I see nothing logged into the
database. The database I am restoring to is set to Single-User. Any
suggestions?
SQL 2000, sp4
Thanks,
Steve
Running the statement first
ALTER DATABASE <Nameofthedb> SET SINGLE_USER with rollback immediate
If you want to do that from Query Analyzer, make sure that you cursor is NOT
positioned on the DB node you want to restore.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Steve Deering" <Steve@.vin.com> schrieb im Newsbeitrag
news:1118060114.644682.280310@.g43g2000cwa.googlegr oups.com...
> While attempting to restore a db, I get the following error message:
> "RESTORE DATABASE is terminating abnormally.
> Exclusive access could not be obtained because the database is in use."
> But looking at Current Activity in EM, I see nothing logged into the
> database. The database I am restoring to is set to Single-User. Any
> suggestions?
> SQL 2000, sp4
> Thanks,
> Steve
>
|||If it still complains, disconnect all your EM sessions.
"Steve Deering" <Steve@.vin.com> wrote in message
news:1118060114.644682.280310@.g43g2000cwa.googlegr oups.com...
> While attempting to restore a db, I get the following error message:
> "RESTORE DATABASE is terminating abnormally.
> Exclusive access could not be obtained because the database is in use."
> But looking at Current Activity in EM, I see nothing logged into the
> database. The database I am restoring to is set to Single-User. Any
> suggestions?
> SQL 2000, sp4
> Thanks,
> Steve
>
|||Hi,
Try the below:-
1. From Query analyzer set the database to SINLE USER.
USE MASTER
GO
ALTER DATABASE <DBNAME> SET SINGLE_USER with rollback immediate
2. Restore the database
RESTORE DATABASE <DBNAME> FROM DISK='FILENAME WITH PATH' WITH STATS=10
Incase if the restore still fails then, close the query analyzer fully once
and try the RESTORE DATABASE again.
Thanks
Hari
SQL Server MVP
"Steve Deering" <Steve@.vin.com> wrote in message
news:1118060114.644682.280310@.g43g2000cwa.googlegr oups.com...
> While attempting to restore a db, I get the following error message:
> "RESTORE DATABASE is terminating abnormally.
> Exclusive access could not be obtained because the database is in use."
> But looking at Current Activity in EM, I see nothing logged into the
> database. The database I am restoring to is set to Single-User. Any
> suggestions?
> SQL 2000, sp4
> Thanks,
> Steve
>
|||Make sure that the database is NOT selected in EM. I've had the exact same
problem and when I selected a different db in EM (master for instance) and
re-ran the restore from Query Analyzer it worked fine.
"Steve Deering" wrote:

> While attempting to restore a db, I get the following error message:
> "RESTORE DATABASE is terminating abnormally.
> Exclusive access could not be obtained because the database is in use."
> But looking at Current Activity in EM, I see nothing logged into the
> database. The database I am restoring to is set to Single-User. Any
> suggestions?
> SQL 2000, sp4
> Thanks,
> Steve
>
|||If you are married to using the SQLEM, then here is a trick I often use:
1. Right-click the database.
2. Detach the database. If there are users currently connected, then select
to disconnect them all without notifying them. When it is done, cancel so
you don't actually detach the database.
3. Now, right-click again and set Offline.
Now, you should be able to restore the database without any problems. Yes,
you can restore a database if it is marked offline.
Sometimes, however, you can not click fast enough to set it offline before
another user logs in. You can try this procedures a few time, but at some
point, you are just going to be stuck with script.
I usually use the same syntax as everyone else has shown you, but I like to
set the database offline instead of single-user.
Regardless, before SQL Server 2000, there was no other way to disconnect
users other than scripting out multiple KILL commands to get rid of all the
connections.
Sincerely,
Anthony Thomas

"Steve Deering" <Steve@.vin.com> wrote in message
news:1118060114.644682.280310@.g43g2000cwa.googlegr oups.com...
While attempting to restore a db, I get the following error message:
"RESTORE DATABASE is terminating abnormally.
Exclusive access could not be obtained because the database is in use."
But looking at Current Activity in EM, I see nothing logged into the
database. The database I am restoring to is set to Single-User. Any
suggestions?
SQL 2000, sp4
Thanks,
Steve

Can't get exclusive access

While attempting to restore a db, I get the following error message:
"RESTORE DATABASE is terminating abnormally.
Exclusive access could not be obtained because the database is in use."
But looking at Current Activity in EM, I see nothing logged into the
database. The database I am restoring to is set to Single-User. Any
suggestions?
SQL 2000, sp4
Thanks,
SteveRunning the statement first
ALTER DATABASE <Nameofthedb> SET SINGLE_USER with rollback immediate
If you want to do that from Query Analyzer, make sure that you cursor is NOT
positioned on the DB node you want to restore.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Steve Deering" <Steve@.vin.com> schrieb im Newsbeitrag
news:1118060114.644682.280310@.g43g2000cwa.googlegroups.com...
> While attempting to restore a db, I get the following error message:
> "RESTORE DATABASE is terminating abnormally.
> Exclusive access could not be obtained because the database is in use."
> But looking at Current Activity in EM, I see nothing logged into the
> database. The database I am restoring to is set to Single-User. Any
> suggestions?
> SQL 2000, sp4
> Thanks,
> Steve
>|||If it still complains, disconnect all your EM sessions.
"Steve Deering" <Steve@.vin.com> wrote in message
news:1118060114.644682.280310@.g43g2000cwa.googlegroups.com...
> While attempting to restore a db, I get the following error message:
> "RESTORE DATABASE is terminating abnormally.
> Exclusive access could not be obtained because the database is in use."
> But looking at Current Activity in EM, I see nothing logged into the
> database. The database I am restoring to is set to Single-User. Any
> suggestions?
> SQL 2000, sp4
> Thanks,
> Steve
>|||Hi,
Try the below:-
1. From Query analyzer set the database to SINLE USER.
USE MASTER
GO
ALTER DATABASE <DBNAME> SET SINGLE_USER with rollback immediate
2. Restore the database
RESTORE DATABASE <DBNAME> FROM DISK='FILENAME WITH PATH' WITH STATS=10
Incase if the restore still fails then, close the query analyzer fully once
and try the RESTORE DATABASE again.
Thanks
Hari
SQL Server MVP
"Steve Deering" <Steve@.vin.com> wrote in message
news:1118060114.644682.280310@.g43g2000cwa.googlegroups.com...
> While attempting to restore a db, I get the following error message:
> "RESTORE DATABASE is terminating abnormally.
> Exclusive access could not be obtained because the database is in use."
> But looking at Current Activity in EM, I see nothing logged into the
> database. The database I am restoring to is set to Single-User. Any
> suggestions?
> SQL 2000, sp4
> Thanks,
> Steve
>|||Make sure that the database is NOT selected in EM. I've had the exact same
problem and when I selected a different db in EM (master for instance) and
re-ran the restore from Query Analyzer it worked fine.
"Steve Deering" wrote:

> While attempting to restore a db, I get the following error message:
> "RESTORE DATABASE is terminating abnormally.
> Exclusive access could not be obtained because the database is in use."
> But looking at Current Activity in EM, I see nothing logged into the
> database. The database I am restoring to is set to Single-User. Any
> suggestions?
> SQL 2000, sp4
> Thanks,
> Steve
>|||If you are married to using the SQLEM, then here is a trick I often use:
1. Right-click the database.
2. Detach the database. If there are users currently connected, then select
to disconnect them all without notifying them. When it is done, cancel so
you don't actually detach the database.
3. Now, right-click again and set Offline.
Now, you should be able to restore the database without any problems. Yes,
you can restore a database if it is marked offline.
Sometimes, however, you can not click fast enough to set it offline before
another user logs in. You can try this procedures a few time, but at some
point, you are just going to be stuck with script.
I usually use the same syntax as everyone else has shown you, but I like to
set the database offline instead of single-user.
Regardless, before SQL Server 2000, there was no other way to disconnect
users other than scripting out multiple KILL commands to get rid of all the
connections.
Sincerely,
Anthony Thomas
"Steve Deering" <Steve@.vin.com> wrote in message
news:1118060114.644682.280310@.g43g2000cwa.googlegroups.com...
While attempting to restore a db, I get the following error message:
"RESTORE DATABASE is terminating abnormally.
Exclusive access could not be obtained because the database is in use."
But looking at Current Activity in EM, I see nothing logged into the
database. The database I am restoring to is set to Single-User. Any
suggestions?
SQL 2000, sp4
Thanks,
Steve

cant find file id 3 when trying to restore

Hi I am trying to restore a dbase in mssql2000 and am getting the error
cannot find file id 3 on device so it does not restore, just wondering what
the problem may be. Thanks Paul.
Paul G
Software engineer.
Hi,
Use RESTORE HEADERONLY command to get the files associted with the backup
file.
Thanks
Hari
SQL Server MVP.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A7B258E8-ED47-4293-BAB7-5E0639B50DE4@.microsoft.com...
> Hi I am trying to restore a dbase in mssql2000 and am getting the error
> cannot find file id 3 on device so it does not restore, just wondering
> what
> the problem may be. Thanks Paul.
> --
> Paul G
> Software engineer.
|||ok thanks for the information.
Paul G
Software engineer.
"Hari Prasad" wrote:

> Hi,
> Use RESTORE HEADERONLY command to get the files associted with the backup
> file.
> Thanks
> Hari
> SQL Server MVP.
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A7B258E8-ED47-4293-BAB7-5E0639B50DE4@.microsoft.com...
>
>

cant find file id 3 when trying to restore

Hi I am trying to restore a dbase in mssql2000 and am getting the error
cannot find file id 3 on device so it does not restore, just wondering what
the problem may be. Thanks Paul.
--
Paul G
Software engineer.Hi,
Use RESTORE HEADERONLY command to get the files associted with the backup
file.
Thanks
Hari
SQL Server MVP.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A7B258E8-ED47-4293-BAB7-5E0639B50DE4@.microsoft.com...
> Hi I am trying to restore a dbase in mssql2000 and am getting the error
> cannot find file id 3 on device so it does not restore, just wondering
> what
> the problem may be. Thanks Paul.
> --
> Paul G
> Software engineer.|||ok thanks for the information.
--
Paul G
Software engineer.
"Hari Prasad" wrote:

> Hi,
> Use RESTORE HEADERONLY command to get the files associted with the backup
> file.
> Thanks
> Hari
> SQL Server MVP.
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A7B258E8-ED47-4293-BAB7-5E0639B50DE4@.microsoft.com...
>
>

cant find file id 3 when trying to restore

Hi I am trying to restore a dbase in mssql2000 and am getting the error
cannot find file id 3 on device so it does not restore, just wondering what
the problem may be. Thanks Paul.
--
Paul G
Software engineer.Hi,
Use RESTORE HEADERONLY command to get the files associted with the backup
file.
Thanks
Hari
SQL Server MVP.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A7B258E8-ED47-4293-BAB7-5E0639B50DE4@.microsoft.com...
> Hi I am trying to restore a dbase in mssql2000 and am getting the error
> cannot find file id 3 on device so it does not restore, just wondering
> what
> the problem may be. Thanks Paul.
> --
> Paul G
> Software engineer.|||ok thanks for the information.
--
Paul G
Software engineer.
"Hari Prasad" wrote:
> Hi,
> Use RESTORE HEADERONLY command to get the files associted with the backup
> file.
> Thanks
> Hari
> SQL Server MVP.
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A7B258E8-ED47-4293-BAB7-5E0639B50DE4@.microsoft.com...
> > Hi I am trying to restore a dbase in mssql2000 and am getting the error
> > cannot find file id 3 on device so it does not restore, just wondering
> > what
> > the problem may be. Thanks Paul.
> > --
> > Paul G
> > Software engineer.
>
>sql

can't find database when trying to restore

Hi I am trying to copy a complete database and did the following.
backup database, saved file at location
C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\database.bak
on machine 1-source machine.
I then copied this to
C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
on machine two-destination machine.
I was thinking I could then use the restore from EM connected to the
destination machine but when I select restore the database name does not show
up as one of the databases that can be selected.
Thanks.
Paul G
Software engineer.
No it wont show the database in Restore database window. It will shoe only
the current database. Use have to use "From Device" option and select the bak
file from there and type the name of database.
Thanks
Ravi
"Paul" wrote:

> Hi I am trying to copy a complete database and did the following.
> backup database, saved file at location
> C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\database.bak
> on machine 1-source machine.
> I then copied this to
> C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
> on machine two-destination machine.
> I was thinking I could then use the restore from EM connected to the
> destination machine but when I select restore the database name does not show
> up as one of the databases that can be selected.
> Thanks.
> --
> Paul G
> Software engineer.
|||ok thanks it worked!.
Paul G
Software engineer.
"Ravi" wrote:
[vbcol=seagreen]
> No it wont show the database in Restore database window. It will shoe only
> the current database. Use have to use "From Device" option and select the bak
> file from there and type the name of database.
>
> --
> Thanks
> Ravi
>
> "Paul" wrote:

can't find database when trying to restore

Hi I am trying to copy a complete database and did the following.
backup database, saved file at location
C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\database.bak
on machine 1-source machine.
I then copied this to
C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
on machine two-destination machine.
I was thinking I could then use the restore from EM connected to the
destination machine but when I select restore the database name does not sho
w
up as one of the databases that can be selected.
Thanks.
--
Paul G
Software engineer.No it wont show the database in Restore database window. It will shoe only
the current database. Use have to use "From Device" option and select the ba
k
file from there and type the name of database.
Thanks
Ravi
"Paul" wrote:

> Hi I am trying to copy a complete database and did the following.
> backup database, saved file at location
> C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\database.bak
> on machine 1-source machine.
> I then copied this to
> C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
> on machine two-destination machine.
> I was thinking I could then use the restore from EM connected to the
> destination machine but when I select restore the database name does not s
how
> up as one of the databases that can be selected.
> Thanks.
> --
> Paul G
> Software engineer.|||ok thanks it worked!.
--
Paul G
Software engineer.
"Ravi" wrote:
[vbcol=seagreen]
> No it wont show the database in Restore database window. It will shoe only
> the current database. Use have to use "From Device" option and select the
bak
> file from there and type the name of database.
>
> --
> Thanks
> Ravi
>
> "Paul" wrote:
>sql

can't find database when trying to restore

Hi I am trying to copy a complete database and did the following.
backup database, saved file at location
C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\database.bak
on machine 1-source machine.
I then copied this to
C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
on machine two-destination machine.
I was thinking I could then use the restore from EM connected to the
destination machine but when I select restore the database name does not show
up as one of the databases that can be selected.
Thanks.
--
Paul G
Software engineer.No it wont show the database in Restore database window. It will shoe only
the current database. Use have to use "From Device" option and select the bak
file from there and type the name of database.
Thanks
Ravi
"Paul" wrote:
> Hi I am trying to copy a complete database and did the following.
> backup database, saved file at location
> C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\database.bak
> on machine 1-source machine.
> I then copied this to
> C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
> on machine two-destination machine.
> I was thinking I could then use the restore from EM connected to the
> destination machine but when I select restore the database name does not show
> up as one of the databases that can be selected.
> Thanks.
> --
> Paul G
> Software engineer.|||ok thanks it worked!.
--
Paul G
Software engineer.
"Ravi" wrote:
> No it wont show the database in Restore database window. It will shoe only
> the current database. Use have to use "From Device" option and select the bak
> file from there and type the name of database.
>
> --
> Thanks
> Ravi
>
> "Paul" wrote:
> > Hi I am trying to copy a complete database and did the following.
> > backup database, saved file at location
> > C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\database.bak
> > on machine 1-source machine.
> > I then copied this to
> > C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
> > on machine two-destination machine.
> > I was thinking I could then use the restore from EM connected to the
> > destination machine but when I select restore the database name does not show
> > up as one of the databases that can be selected.
> > Thanks.
> > --
> > Paul G
> > Software engineer.

Can't Drop table in Replication

Dear friends

I restore one database in two database servers which is running on SQL server 2000.I replicated these two through snapshot relication.Snapshot agent is creating snapshot.But when I am starting to synchronize it's telling can't drop table because that table is in replication. Backup I taken from a replicated database.so it's having rowguid both the servers.Another thing the table which it is telling not able to drop it's having primary key.please tell me what may be the problem.In replication why it is going to drop table it's only what to transport data na

Filson

The database that's restored at the subscriber probably still has replication bits set. You can clean up replication at the subscriber database by calling proc sp_removedbreplication.|||

Greg Y wrote:

The database that's restored at the subscriber probably still has replication bits set. You can clean up replication at the subscriber database by calling proc sp_removedbreplication.

I done like that.But I got a problem .I am using backup from replicated database .This backup only i restored on my publisher & subscriber.When I am subscribing three system stored procedure should be generate na for Del,Ins.Upd operations .This is not happening.I tried to delete this system generated procedures in the restored database .But it's not allowing.Then I applied replication Pubs database which is coming along with SQL Server .Then what I seen I published all Tables(10).But Stored procedure only for 5 tables is generated .in these tables whatever changes i am making it's affecting through Replication,Example publisher table in pubs database is not getting any stored procedure after subscription.So it's not getting any change through replication.Then I am getting error message'procedure Sp_MsIns_Publisher not found'.So please tell me what to do remove all old Stored procedures for replication in restored database and how to create the stored procedures for all the tables published

Thanks in Advance

Filson

|||

You need to restore the database at the subscriber and remove all replication components. When using the wizard to set up the subscription, specify the option that the subscriber has the data. if you're doing it via TSQL, then specify 'nosync' for paramter @.sync_type in sp_addsubscription. After the first sync, you can then run sp_scriptpublicationcustomprocs at the subscriber to create the necessary procs for the distribution agent.

|||

Greg Y wrote:

You need to restore the database at the subscriber and remove all replication components. When using the wizard to set up the subscription, specify the option that the subscriber has the data. if you're doing it via TSQL, then specify 'nosync' for paramter @.sync_type in sp_addsubscription. After the first sync, you can then run sp_scriptpublicationcustomprocs at the subscriber to create the necessary procs for the distribution agent.

Greg thanks for suggestion.Previously itself I tried these ways to remove replication components.But still System stored procedures for table Ins,Del,Upd is not dropping.I want a specific way to drop it out.I think i mentioned this in earlier post.So kindly suggest me a better way for that

Filson

|||Sorry, it's not clear to me what the problem is. Are you trying to drop or create the sp_MSins/upd/del stored procedures? And where - at the publisher or subscriber? I'm not too familiar with SQL 2000, maybe you have to manually delete these stored procedures.|||

Greg Y wrote:

Sorry, it's not clear to me what the problem is. Are you trying to drop or create the sp_MSins/upd/del stored procedures? And where - at the publisher or subscriber? I'm not too familiar with SQL 2000, maybe you have to manually delete these stored procedures.

I am describing my problem below .I taken a backup of replicated database.That I restored on another server.I made that one as a publisher.Another instance I made as a subscriber.I restored same database on this subscriber.Then I tried for transactional replication.While synchronizing I got error on my subscriber 'Sp_MsIns_AgentCode is not found'.This I got while Inserting records to AgentCode Table in publisher.Then I come to know for each table article published in Transactional replication will have three system generated stored procedures 1)insertion 2)updation 3)Deletion.This is not generating when i subscribing to my publisher..Here when I am pulling the subscription I am specifying 'No Shema & data transfer'.So I am not able to transport this System generated Procedures.If I am selecting 'Schema & data Tansfer'.It won't able to initialize in subscriber due to Foreign Key criterias. after completing Subscription through Wizard,Can I trnsfer UPD,INS,DEL Procedure Schema through any Sp_procedure call? Give me some better way

Filson

|||

I mentioned above to run stored procedure sp_scriptpublicationcustomprocs at the subscriber database after applying the snapshot, did you do that? That proc is supposed to generate the missing sp_MSins/upd/del procs that the distribution agent is trying to execute.

Can't do backup or restore

Dear all
I'm trying to do a backup from one of my database, but i
always got this message :
The Media Set for database "DBNAME" has 2 family members
but only 1 provided. All member must be provided.
Backup Database is terminated abnormally.
Can some one know what went wrong, cause i don't have any
idea!
Thx alot!
Jack
jackchan@.nedcor.com.hkSeems like the place where you want to backup to (the destination) has been involved in a striped
backup once upon a time. If you no longer want that to be involved in a striped backup, do this
backup using the FORMAT option of the backup command.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Jack" <jackchan@.nedcor.com.hk> wrote in message news:2c88601c393d2$9badcde0$a601280a@.phx.gbl...
> Dear all
> I'm trying to do a backup from one of my database, but i
> always got this message :
> The Media Set for database "DBNAME" has 2 family members
> but only 1 provided. All member must be provided.
> Backup Database is terminated abnormally.
> Can some one know what went wrong, cause i don't have any
> idea!
> Thx alot!
> Jack
> jackchan@.nedcor.com.hk

Can't delete SQL 2005 user from SQL 2000 database

I have a SQL 2000 database from another system. The database has user
IIS_WPG.
I restore a backup of this to a SQL 2005 database (working on a migration to
SQL 2005). Since IIS_WPG is not a Security Login on SQL 2005, when I go to
delete the database user IIS_WPG, it fails with "Drop failed for user
IIS_WPG".
How can I delete this user from the database instance?
Thanks. AmilThe fact that the DB came from SQL 2000 originally ought to be
irrelevant. As soon as you restore it on the SQL 2005 server it becomes
a SQL 2005 database (albeit in 8.0 compatibility mode but that's also
not important). You should be able to drop any users of the DB (except
dbo, which is a special user) with a simple DROP USER
<http://msdn2.microsoft.com/en-us/library/ms189438.aspx> statement in
T-SQL. For example:
use MyDB;
drop user IIS_WPG;
If this doesn't work can you please post the exactly error message that
SQL Server raises. Also posting the result of:
use MyDB;
select current_user;
select
principal_id,
cast(name as varchar(50)) as name,
type_desc
from sys.database_principals
where is_fixed_role = 0;
might help troubleshooting.
*mike hodgson*
http://sqlnerd.blogspot.com
Amil Hanish wrote:

>I have a SQL 2000 database from another system. The database has user
>IIS_WPG.
>I restore a backup of this to a SQL 2005 database (working on a migration t
o
>SQL 2005). Since IIS_WPG is not a Security Login on SQL 2005, when I go to
>delete the database user IIS_WPG, it fails with "Drop failed for user
>IIS_WPG".
>How can I delete this user from the database instance?
>Thanks. Amil
>
>|||Mike, that didn't work.
The iis_wpg user doesn't appear in the Security | Logins so I don't know why
it's complaining. Here is the query info.
use mydb;
drop user iis_wpg
Msg 15138, Level 16, State 1, Line 2
The database principal owns a schema in the database, and cannot be dropped.
=======================
use mydb;
select current_user;
select
principal_id,
cast(name as varchar(50)) as name,
type_desc
from sys.database_principals
where is_fixed_role = 0;
0 public DATABASE_ROLE
1 dbo WINDOWS_USER
2 guest SQL_USER
3 INFORMATION_SCHEMA SQL_USER
4 sys SQL_USER
5 ASPNET WINDOWS_USER
6 IIS_WPG WINDOWS_GROUP
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
news:ufZs8F%23cGHA.3888@.TK2MSFTNGP02.phx.gbl...
> The fact that the DB came from SQL 2000 originally ought to be
> irrelevant. As soon as you restore it on the SQL 2005 server it becomes
> a SQL 2005 database (albeit in 8.0 compatibility mode but that's also
> not important). You should be able to drop any users of the DB (except
> dbo, which is a special user) with a simple DROP USER
> <http://msdn2.microsoft.com/en-us/library/ms189438.aspx> statement in
> T-SQL. For example:
> use MyDB;
> drop user IIS_WPG;
> If this doesn't work can you please post the exactly error message that
> SQL Server raises. Also posting the result of:
> use MyDB;
> select current_user;
> select
> principal_id,
> cast(name as varchar(50)) as name,
> type_desc
> from sys.database_principals
> where is_fixed_role = 0;
> might help troubleshooting.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Amil Hanish wrote:
>
>|||Ah. Now we're getting somewhere. The IIS_WPG user owns a schema! OK,
you have 2 options: 1) transfer the ownership of the schema(s) owned by
IIS_WPG to a different user in the database or 2) drop the schema(s)
owned by IIS_WPG.
First you need to know which schema(s) IIS_WPG owns (obviously this
should all be done in the context of the database in question):
use myDB;
select s.* from sys.schemas as s
inner join sys.database_principals as dp on dp.principal_id =
s.principal_id
where dp.[name] = 'IIS_WPG';
Next you should check to see if there are any objects within that/those
schema(s):
select s.[name] as schemaname, o.[name] as objectname, o.type_desc
from sys.objects as o
inner join sys.schemas as s on s.schema_id = o.schema_id
inner join sys.database_principals as dp on dp.principal_id =
s.principal_id
where dp.[name] = 'IIS_WPG'
order by s.[name], o.type_desc, o.[name];
If there any no objects in the schema then you may as well just drop the
schema and be done with it:
drop schema foo;
where foo is the name of the schema. (Do this for each schema you want
to drop.) However, if the schema contains objects (views, tables,
procs, functions, etc.) that you wish to keep then you should either
transfer them to another schema (so you can drop their original schema,
which is owned by IIS_WPG) with the ALTER SCHEMA statement or transfer
ownership of the schema(s) that IIS_WPG currently owns. Transferring
ownership is easier and less likely to break code referencing those
objects. You transfer ownership with the ALTER AUTHORIZATION statement:
alter authorization on schema::foo to MyOtherDBUser;
where foo is the name of the schema you're transferring ownership of and
MyOtherDBUser is another user in the DB to which you wish to transfer
ownership (the dbo user is always a potential candidate). You'd do this
for each schema owned by IIS_WPG.
After all that the IIS_WPG user shouldn't own any schemas any more and
you should be able to drop the user with a DROP USER statement:
drop user IIS_WPG;
This is all documented in BOL if you need any more info about any of
these individual statements (including the sys.objects, sys.schemas &
sys.database_principals catalog views).
Hope this helps.
*mike hodgson*
http://sqlnerd.blogspot.com
Amil Hanish wrote:

>Mike, that didn't work.
>The iis_wpg user doesn't appear in the Security | Logins so I don't know wh
y
>it's complaining. Here is the query info.
>use mydb;
>drop user iis_wpg
>Msg 15138, Level 16, State 1, Line 2
>The database principal owns a schema in the database, and cannot be dropped
.
>=======================
>use mydb;
> select current_user;
> select
> principal_id,
> cast(name as varchar(50)) as name,
> type_desc
> from sys.database_principals
> where is_fixed_role = 0;
>0 public DATABASE_ROLE
>1 dbo WINDOWS_USER
>2 guest SQL_USER
>3 INFORMATION_SCHEMA SQL_USER
>4 sys SQL_USER
>5 ASPNET WINDOWS_USER
>6 IIS_WPG WINDOWS_GROUP
>
>"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
>news:ufZs8F%23cGHA.3888@.TK2MSFTNGP02.phx.gbl...
>
>
>|||Mike...much thanks! All your info worked like a charm and without issues.
Since the schema had no object, I was able to simply delete. I'll save your
help in case I need it in the future.
Amil
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
news:%233VIaFIdGHA.1208@.TK2MSFTNGP04.phx.gbl...
> Ah. Now we're getting somewhere. The IIS_WPG user owns a schema! OK,
> you have 2 options: 1) transfer the ownership of the schema(s) owned by
> IIS_WPG to a different user in the database or 2) drop the schema(s)
> owned by IIS_WPG.
> First you need to know which schema(s) IIS_WPG owns (obviously this
> should all be done in the context of the database in question):
> use myDB;
> select s.* from sys.schemas as s
> inner join sys.database_principals as dp on dp.principal_id =
> s.principal_id
> where dp.[name] = 'IIS_WPG';
> Next you should check to see if there are any objects within that/those
> schema(s):
> select s.[name] as schemaname, o.[name] as objectname, o.type_d
esc
> from sys.objects as o
> inner join sys.schemas as s on s.schema_id = o.schema_id
> inner join sys.database_principals as dp on dp.principal_id =
> s.principal_id
> where dp.[name] = 'IIS_WPG'
> order by s.[name], o.type_desc, o.[name];
> If there any no objects in the schema then you may as well just drop the
> schema and be done with it:
> drop schema foo;
> where foo is the name of the schema. (Do this for each schema you want
> to drop.) However, if the schema contains objects (views, tables,
> procs, functions, etc.) that you wish to keep then you should either
> transfer them to another schema (so you can drop their original schema,
> which is owned by IIS_WPG) with the ALTER SCHEMA statement or transfer
> ownership of the schema(s) that IIS_WPG currently owns. Transferring
> ownership is easier and less likely to break code referencing those
> objects. You transfer ownership with the ALTER AUTHORIZATION statement:
> alter authorization on schema::foo to MyOtherDBUser;
> where foo is the name of the schema you're transferring ownership of and
> MyOtherDBUser is another user in the DB to which you wish to transfer
> ownership (the dbo user is always a potential candidate). You'd do this
> for each schema owned by IIS_WPG.
> After all that the IIS_WPG user shouldn't own any schemas any more and
> you should be able to drop the user with a DROP USER statement:
> drop user IIS_WPG;
> This is all documented in BOL if you need any more info about any of
> these individual statements (including the sys.objects, sys.schemas &
> sys.database_principals catalog views).
> Hope this helps.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Amil Hanish wrote:
>
>

Cant create database with > 4 GB log

Hi,

I got a backup file from my friend which needs 11 GB to hold the
transaction log. I tried to restore this database but an error
message:

"MODIFY FILE encountered operating system error 112(error not found)
while attempting to expand the physical file."

appeared. The free space is more than enough. I then tried another way
to create a database with a 11 gb log file but it fails with the same
error message whenever I tried to create any database with a file
larger than 4 GB. I'm using SQLServer 2000 pro. running on WinXP. Is
this the cause of this limitation?

p.s. I'm very new to the SQLServer.

Pls Help,
Will.pongthai@.msn.com (Pongthai) wrote in message news:<4bf99b6f.0405042008.9cc4e14@.posting.google.com>...
> Hi,
> I got a backup file from my friend which needs 11 GB to hold the
> transaction log. I tried to restore this database but an error
> message:
> "MODIFY FILE encountered operating system error 112(error not found)
> while attempting to expand the physical file."
> appeared. The free space is more than enough. I then tried another way
> to create a database with a 11 gb log file but it fails with the same
> error message whenever I tried to create any database with a file
> larger than 4 GB. I'm using SQLServer 2000 pro. running on WinXP. Is
> this the cause of this limitation?
> p.s. I'm very new to the SQLServer.
>
> Pls Help,
> Will.

I believe that 4GB is the maximum file size on a FAT32 filesystem, so
I would guess your hard drive is formatted with FAT32, not NTFS. You
should be able to convert it, as described here (make sure you back up
your data first):

http://support.microsoft.com/defaul...kb;en-us;307881

NTFS is the recommended filesystem for both Windows XP and SQL Server.

Simon|||Dear Will,

Umm yeh interesting, I am not sure but if the problem is "4GB", it may
be due to 4GB Memory constraint... However, the error number you
report belongs to operating system not SQL Server and it means disk is
full, no space for any more 0101010 on the disk!
:-)

Exact description of Error
"112 there is not enough space on the disk. ERROR_DISK_FULL"
http://msdn.microsoft.com/library/d...des__0-499_.asp

Please describe things a lil more

Thanks!
Me,
Saghir Taj
MCDBA

pongthai@.msn.com (Pongthai) wrote in message news:<4bf99b6f.0405042008.9cc4e14@.posting.google.com>...
> Hi,
> I got a backup file from my friend which needs 11 GB to hold the
> transaction log. I tried to restore this database but an error
> message:
> "MODIFY FILE encountered operating system error 112(error not found)
> while attempting to expand the physical file."
> appeared. The free space is more than enough. I then tried another way
> to create a database with a 11 gb log file but it fails with the same
> error message whenever I tried to create any database with a file
> larger than 4 GB. I'm using SQLServer 2000 pro. running on WinXP. Is
> this the cause of this limitation?
> p.s. I'm very new to the SQLServer.
>
> Pls Help,
> Will.|||Thanks for both answers. I'll have one of my hdd partition changed to
NTFS and try again.

Thanks,
Will.|||Simon may be right this may be one of cause!.. i would like to know
the exact cause of problem.. if you get some fix or exact descripton
of problem please let me know!

Me,
Saghir Taj
MCDBA

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0405050216.555cf13c@.posting.google.com>...
> pongthai@.msn.com (Pongthai) wrote in message news:<4bf99b6f.0405042008.9cc4e14@.posting.google.com>...
> > Hi,
> > I got a backup file from my friend which needs 11 GB to hold the
> > transaction log. I tried to restore this database but an error
> > message:
> > "MODIFY FILE encountered operating system error 112(error not found)
> > while attempting to expand the physical file."
> > appeared. The free space is more than enough. I then tried another way
> > to create a database with a 11 gb log file but it fails with the same
> > error message whenever I tried to create any database with a file
> > larger than 4 GB. I'm using SQLServer 2000 pro. running on WinXP. Is
> > this the cause of this limitation?
> > p.s. I'm very new to the SQLServer.
> > Pls Help,
> > Will.
> I believe that 4GB is the maximum file size on a FAT32 filesystem, so
> I would guess your hard drive is formatted with FAT32, not NTFS. You
> should be able to convert it, as described here (make sure you back up
> your data first):
> http://support.microsoft.com/defaul...kb;en-us;307881
> NTFS is the recommended filesystem for both Windows XP and SQL Server.
> Simon|||hi

has the problem solved

i am also facing the same problem

sara

Cant create database with > 4 GB log

Hi,

I got a backup file from my friend which needs 11 GB to hold the
transaction log. I tried to restore this database but an error
message:

"MODIFY FILE encountered operating system error 112(error not found)
while attempting to expand the physical file."

appeared. The free space is more than enough. I then tried another way
to create a database with a 11 gb log file but it fails with the same
error message whenever I tried to create any database with a file
larger than 4 GB. I'm using SQLServer 2000 pro. running on WinXP. Is
this the cause of this limitation?

p.s. I'm very new to the SQLServer.

Pls Help,
Will.pongthai@.msn.com (Pongthai) wrote in message news:<4bf99b6f.0405042008.9cc4e14@.posting.google.com>...
> Hi,
> I got a backup file from my friend which needs 11 GB to hold the
> transaction log. I tried to restore this database but an error
> message:
> "MODIFY FILE encountered operating system error 112(error not found)
> while attempting to expand the physical file."
> appeared. The free space is more than enough. I then tried another way
> to create a database with a 11 gb log file but it fails with the same
> error message whenever I tried to create any database with a file
> larger than 4 GB. I'm using SQLServer 2000 pro. running on WinXP. Is
> this the cause of this limitation?
> p.s. I'm very new to the SQLServer.
>
> Pls Help,
> Will.

I believe that 4GB is the maximum file size on a FAT32 filesystem, so
I would guess your hard drive is formatted with FAT32, not NTFS. You
should be able to convert it, as described here (make sure you back up
your data first):

http://support.microsoft.com/defaul...kb;en-us;307881

NTFS is the recommended filesystem for both Windows XP and SQL Server.

Simon|||Dear Will,

Umm yeh interesting, I am not sure but if the problem is "4GB", it may
be due to 4GB Memory constraint... However, the error number you
report belongs to operating system not SQL Server and it means disk is
full, no space for any more 0101010 on the disk!
:-)

Exact description of Error
"112 there is not enough space on the disk. ERROR_DISK_FULL"
http://msdn.microsoft.com/library/d...des__0-499_.asp

Please describe things a lil more

Thanks!
Me,
Saghir Taj
MCDBA

pongthai@.msn.com (Pongthai) wrote in message news:<4bf99b6f.0405042008.9cc4e14@.posting.google.com>...
> Hi,
> I got a backup file from my friend which needs 11 GB to hold the
> transaction log. I tried to restore this database but an error
> message:
> "MODIFY FILE encountered operating system error 112(error not found)
> while attempting to expand the physical file."
> appeared. The free space is more than enough. I then tried another way
> to create a database with a 11 gb log file but it fails with the same
> error message whenever I tried to create any database with a file
> larger than 4 GB. I'm using SQLServer 2000 pro. running on WinXP. Is
> this the cause of this limitation?
> p.s. I'm very new to the SQLServer.
>
> Pls Help,
> Will.|||Thanks for both answers. I'll have one of my hdd partition changed to
NTFS and try again.

Thanks,
Will.|||Simon may be right this may be one of cause!.. i would like to know
the exact cause of problem.. if you get some fix or exact descripton
of problem please let me know!

Me,
Saghir Taj
MCDBA

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0405050216.555cf13c@.posting.google.com>...
> pongthai@.msn.com (Pongthai) wrote in message news:<4bf99b6f.0405042008.9cc4e14@.posting.google.com>...
> > Hi,
> > I got a backup file from my friend which needs 11 GB to hold the
> > transaction log. I tried to restore this database but an error
> > message:
> > "MODIFY FILE encountered operating system error 112(error not found)
> > while attempting to expand the physical file."
> > appeared. The free space is more than enough. I then tried another way
> > to create a database with a 11 gb log file but it fails with the same
> > error message whenever I tried to create any database with a file
> > larger than 4 GB. I'm using SQLServer 2000 pro. running on WinXP. Is
> > this the cause of this limitation?
> > p.s. I'm very new to the SQLServer.
> > Pls Help,
> > Will.
> I believe that 4GB is the maximum file size on a FAT32 filesystem, so
> I would guess your hard drive is formatted with FAT32, not NTFS. You
> should be able to convert it, as described here (make sure you back up
> your data first):
> http://support.microsoft.com/defaul...kb;en-us;307881
> NTFS is the recommended filesystem for both Windows XP and SQL Server.
> Simon