Remove files from databases

Hi, I've created a new filegroup, then added to it a new filename, then create a table pointing to this filegroup. So far everything is ok, but if I want to revert the process in this way:

1- Drop the table - OK

2- Drop the filegroup - OK

3- Drop the filename - ERROR: Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized.

When I query a catalog view with this query:

Select*FromSys.Database_Files

I get the file that I had recently deleted, it is offline but I can not delete it using the ALTER DATABASE instructions.

This is the code I use:

Use [TESTING ]

Go

-- Add a FileGroup to the Database

AlterDatabase TESTING AddFileGroup FG01

Go

-- Add a file to a FileGroup

AlterDatabase TESTING AddFile(

NAME= TESTING_DATA01,

FILENAME='D:\Sql Server\Data\Testing_Data01.ndf',

SIZE= 1 MB,

MAXSIZE= 10 MB,

FILEGROWTH= 1 MB

)ToFileGroup FG01

Go

-- Create the table using a specific FileGroup

CreateTable TABLE1 (

Id IntNotNull,

FirstName VarChar(30)NotNull,

LastName VarChar(30)NotNull,

BirthDate SmallDateTime

)

On FG01

So far everything works ok, but in the next code there is an error:

-- Delete the table

Drop Table TABLE1

Go

-- Remove a FileGroup from the Database

AlterDatabase TESTING Remove FileGroup FG01

Go

-- Try to remove the file

AlterDatabase TESTING Remove File TESTING_DATA01

When I try to remove the file there is an error:

"Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized"

You simply need to change the order of your Remove statements to remove the file BEFORE you remove the filegroup.|||Thanks for the answer, but how can I delete the entries in the Sys.Database_Files that already exists ?

Remove all copies of the same record from merge-replicated table in multiple databases

I would like to know if this is OK to remove all the copies of the
same record from a merge-replicated table in multiple databases
servers.
What I am trying to do is:
- Let say we have a table called tbProductionBatch in database
servers in three branch offices.
- Each branch office can add/update info in the local copy of this
table in the local database server.
- The data in this table is being replicated between these three
database servers using merge replication.
- Let say we want to get rid of a "batch" record from this table and
we want to get rid of all the copies of this "batch" record from all
three database server (then, we can see this "batch" goes away sooner
rather than waiting for merge replication to kick in).
- Soon after this, we want to add a similar "batch" record back to
this table in one database server, and we let the hourly merge
replication to replicate the info on this "batch" to other database
servers.
My questions are:
- Will this works?
- Is there a better alternative?
Thanks in advance for any info.
Jay Chan
yes it will work, but the "conflict", ie trying to delete a record which has
already being deleted will be logged in the conflict tables.
To the end user this will be transparent and will only show up when the dba
or user use conflict viewer to examine conflicts.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jay Chan" <jaykchan@.hotmail.com> wrote in message
news:c7e5acb2.0407210720.63035aaa@.posting.google.c om...
> I would like to know if this is OK to remove all the copies of the
> same record from a merge-replicated table in multiple databases
> servers.
> What I am trying to do is:
> - Let say we have a table called tbProductionBatch in database
> servers in three branch offices.
> - Each branch office can add/update info in the local copy of this
> table in the local database server.
> - The data in this table is being replicated between these three
> database servers using merge replication.
> - Let say we want to get rid of a "batch" record from this table and
> we want to get rid of all the copies of this "batch" record from all
> three database server (then, we can see this "batch" goes away sooner
> rather than waiting for merge replication to kick in).
> - Soon after this, we want to add a similar "batch" record back to
> this table in one database server, and we let the hourly merge
> replication to replicate the info on this "batch" to other database
> servers.
> My questions are:
> - Will this works?
> - Is there a better alternative?
> Thanks in advance for any info.
> Jay Chan
|||> yes it will work, but the "conflict", ie trying to delete a record which has
> already being deleted will be logged in the conflict tables.
> To the end user this will be transparent and will only show up when the dba
> or user use conflict viewer to examine conflicts.
Great! I have this question at the back of my mind for years. Finally,
I get this answered. Now, I can go ahead to remove the copies of the
same record from all the databases.
Thanks a lot!
Jay Chan

remove additional sql server named instance

Hi
I need to remove an additional named instance of sql server. It has nothing running against it and no user databases have been created on this instance
I intend to use ADD/REMOVE Programs but I am nervous that I will be prompted with a whole series of "such and such file is being used by another program do you still wish to remove" and because we are running an actual default instance used as a live production server (with two mission critical apps) I need to know what the best way to remove the named instance and are there any risks/precautions I need to make before it's removed
Another thing I noticed was that the default instance is 39MB and the named instance is 107MB. Any reason why the big difference in size
Thanks!!
ShelleyAdd/remove programs is the correct way to remove the unnecessary named
instance...
interesting question about the size. I assume you're getting that number
from add/remove? I've never really looked at that info since I would
normally get my sizing info dirrectly from SQL. I would guess that the
difference might come from space used in system databases such as master and
msdb but I really am just guessing. You might look at the size differences
between the system db's to see if that accounts for it...
--
Brian
"shelley" <anonymous@.discussions.microsoft.com> wrote in message
news:649E12B5-B0E2-4D64-8332-491C1E8D9920@.microsoft.com...
> Hi!
> I need to remove an additional named instance of sql server. It has
nothing running against it and no user databases have been created on this
instance.
> I intend to use ADD/REMOVE Programs but I am nervous that I will be
prompted with a whole series of "such and such file is being used by another
program do you still wish to remove" and because we are running an actual
default instance used as a live production server (with two mission critical
apps) I need to know what the best way to remove the named instance and are
there any risks/precautions I need to make before it's removed?
> Another thing I noticed was that the default instance is 39MB and the
named instance is 107MB. Any reason why the big difference in size?
> Thanks!!!
> Shelley|||Brian,
I wasn't around for the installation of named instance and when I try to start up the service for the this instance I receive the message: Remove SQL Server Instance: Could not start the MSSQL$MCDERMOTT service on the local computer. Error 3: The system could not find the path specified.
So I'm not sure where the 107MB is located either. When I run the add/remove will I be prompted about the deletion of shared files. Should I say 'No' to deleting all of them? I do not want anything to impact our default production instance.
Thanks!
Shelley

removal of merge subscriptions

I try to restore a set of replicas where the main server has been stolen.
Fortunately I have the databases at another server but when i try to push a
merge replication to a new main server i can not create a merge publication
beacuse the database has merge subscriptions that are anonymous or that use
the priority of the publisher to solve conflicts. Drop...
the problem is that I can not drop the subscriptions. If I delete it in
enterprise manager I still get the same error messages. I also used the
sp_dropmergesubscription and sp_mergesubscription_cleanup but that does not
help. Is there anyone who got any idea?
Thanks for your help in advance
Mats
Best wishes
Was the stolen server a publisher?
"Mats" wrote:

> I try to restore a set of replicas where the main server has been stolen.
> Fortunately I have the databases at another server but when i try to push a
> merge replication to a new main server i can not create a merge publication
> beacuse the database has merge subscriptions that are anonymous or that use
> the priority of the publisher to solve conflicts. Drop...
> the problem is that I can not drop the subscriptions. If I delete it in
> enterprise manager I still get the same error messages. I also used the
> sp_dropmergesubscription and sp_mergesubscription_cleanup but that does not
> help. Is there anyone who got any idea?
> Thanks for your help in advance
> Mats
> --
> Best wishes
|||Yes it was!
"Jim Breffni" wrote:
[vbcol=seagreen]
> Was the stolen server a publisher?
>
> "Mats" wrote:
|||If you have no other publishers you must drop all subscriptions.
Backup a subscription database.
Restore subscription database to NEW publisher.
Re-create publication.
Re-create Subscriptions
Note: If you think your subscriptions contain slightly different data then
you will need to manually sync these up to the publication before creating
the new subscriptions.
DON'T FORGET TO MAKE A BACKUP OF ANY DATABASES BEFORE MAKING ANY CHANGES!
Hilary also posted a link that helps to remove subsciptions
You
"Mats" wrote:
[vbcol=seagreen]
> Yes it was!
> "Jim Breffni" wrote:
|||Thanks for your answer:
Could you please tell me where is the link you mentioned
Mats
"Jim Breffni" wrote:
[vbcol=seagreen]
> If you have no other publishers you must drop all subscriptions.
> Backup a subscription database.
> Restore subscription database to NEW publisher.
> Re-create publication.
> Re-create Subscriptions
> Note: If you think your subscriptions contain slightly different data then
> you will need to manually sync these up to the publication before creating
> the new subscriptions.
> DON'T FORGET TO MAKE A BACKUP OF ANY DATABASES BEFORE MAKING ANY CHANGES!
> Hilary also posted a link that helps to remove subsciptions
> You
> "Mats" wrote:
|||On 2004-12-07, Mats <Mats@.discussions.microsoft.com> wrote:
> Thanks for your answer:
> Could you please tell me where is the link you mentioned
>
Can you please post the resolution of your problem, sucssesfull or not?
I had similair situation where I needed to set up replication between
publisher and subscriber where subscriber was previousley subscribed to
another publisher. I needed to issue sp_dropsubscriber at the subscriber (or
some other stored procedure, can't find it now) befure I was able to push
the snapshots to those subscribers.
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||Here you go...
http://groups-beta.google.com/group/...a?dmode=source
"Mats" wrote:
[vbcol=seagreen]
> Thanks for your answer:
> Could you please tell me where is the link you mentioned
> Mats
> "Jim Breffni" wrote:
|||I thank you very much for your effort! But when i run the script it works OK.
When I then try to create a merge publication I get an error 208 when
creating the articles which means that a reference is missing. Any ideas?
Mats
"Jim Breffni" wrote:
[vbcol=seagreen]
> Here you go...
> http://groups-beta.google.com/group/...a?dmode=source
>
>
> "Mats" wrote:

Remote Server

Hi all,

I am trying to locate some scripts for create/update and a nightly scripts that run to update some databases.

Now within the server itself there are no scripts that are running beside the normal system stored procedure.

I notice that the server is a remote server is it possible for the scripts that are running coming from another server.

Once I click on the server (NTBVM) and right click on the property RPC is check, and 'map remote logins to differents local logins' has the radio dial marked however when you look at the list below no information is recorded.

Please help

Thanking you in advanceGreetings, M'Lady! It's good to see you again.

While it is unlikely, it is possible that the scripts would run on another server.

Is this something you are investigating because it is happening now, or something you are planning so it will happen soon?

-PatP|||It something that I was asked to fix and give a time line.

One of the task is to modify registry settings for a OSI servlet(What is the world).

The network admin said that there are scripts that run and I've look everywhere for the scripts and can't find them.

The frontend of this tool call OSI and the backend is SQL. I wonder if I would be able to find the scripts in the tool itself.

I don't have a clue.

Thanks for responding so fast.

Lystra|||Ok, it is pretty easy to define OSI (http://searchnetworking.techtarget.com/sDefinition/0,,sid7_gci212725,00.html), and servlet (http://searchdatabase.techtarget.com/sDefinition/0,,sid13_gci212966,00.html), although it is somewhat peculiar to see them used together that way.

One thing that might help you is to run the SQL profiler overnight to see who/what connects to your server. That will at least give you a few "breadcrumbs" to start from!

Another "high probability" place to check is the SQL Agent running on the SQL Server. It is quite easy to schedule scripts (and many other things) to run as needed against your SQL server or other machines.

-PatP|||I have check the sql server agent nothing.

I will try profiler overnight.

Since servlet--is a small program that runs on a server, maybe the scripts are running from there?

Thanks

Lystra|||If there was nothing in the SQL Agent, the servlet would be my next guess. Do any of the network/admin/IT types have any kind of documentation for this system whatsoever? It sounds to me like you are the new kid on the block, being sent out to clean up a runaway freight train that has been careening unattended through the office for quite some time!

-PatP|||Yup, you can't be more right. This is my three week at this job. I could tell you the horror stories from the first week.

But I have to admit I love this job because it is pushing me to be a better dba. My last job was a contractor for the government and I was not able to use my troubleshooting skills to the fullest. Here I have to.

Lystra|||A friend of mine is in a similar situation in the DC area. She's been developing MS-Access for one of the government's largest construction contractors for an age. She's reached the point where she wants to go further than they are willing/able to manage, and she's trying to figure out what her next step should be...

Unfortunately, I've been out of the DC area for too long, so I've really lost touch with the IT/business community there. Oh well, can't win them all!

-PatP|||It's pretty much the same as you have left it.

Nothing have change.

Lystra

Remote Procedure Call Failed

Hi,
We are running IIS 4 under NT Server 4.0 Server, Serice pack 3; SQL
Server 6.5, Service Pack 3 and ASP 2.0, for our development databases.
We recently upgraded to IIS 4 and since then we keep getting thid
Remote Procedure error.
The exact error reads:
The server has reached the maximum recovery limit for the application
during the processing of your request. Please contact the server
administrator for assistance.
The next message that comes up is:
Server Application Error
The server has reached the maximum recovery limit for the application
during the processing of your request. Please contact the server
administrator for assistance.HTTP/1.1 500 Server Error Server:
Microsoft-IIS/4.0 Date: Mon, 29 Mar 1999 22:09:57 GMT Connection:
close Content-Type: text/html Content-Length: 93 The remote procedure
call failed.
We keep rebooting the server but this error persists. I can't find
anything that points to what can be causing this.
Has anyone else come across this problem?
Thanx in advance,
-=lata=-Lata,
1. Does this error happen only on the IIS? At the time of error, are you
able to use Query analyser and enterpirse manager and other client
connections to connect to sql server?
2. When you say you rebooted the server, is it the IIS server or the sQL
Server or the Windows server?
If other sql server applications and client connections are running fine,
then it could be an IIS issue.
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Remote Procedure Call Failed

Hi,
We are running IIS 4 under NT Server 4.0 Server, Serice pack 3; SQL
Server 6.5, Service Pack 3 and ASP 2.0, for our development databases.
We recently upgraded to IIS 4 and since then we keep getting thid
Remote Procedure error.
The exact error reads:
The server has reached the maximum recovery limit for the application
during the processing of your request. Please contact the server
administrator for assistance.
The next message that comes up is:
Server Application Error
The server has reached the maximum recovery limit for the application
during the processing of your request. Please contact the server
administrator for assistance.HTTP/1.1 500 Server Error Server:
Microsoft-IIS/4.0 Date: Mon, 29 Mar 1999 22:09:57 GMT Connection:
close Content-Type: text/html Content-Length: 93 The remote procedure
call failed.
We keep rebooting the server but this error persists. I can't find
anything that points to what can be causing this.
Has anyone else come across this problem?
Thanx in advance,
-=lata=-
Lata,
1. Does this error happen only on the IIS? At the time of error, are you
able to use Query analyser and enterpirse manager and other client
connections to connect to sql server?
2. When you say you rebooted the server, is it the IIS server or the sQL
Server or the Windows server?
If other sql server applications and client connections are running fine,
then it could be an IIS issue.
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Remote creation of a Maintenance plan

I'm trying to remotely create a maintenance plan using the management studio in order to backup databases. I get the following error.

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

TITLE: Maintenance Plan Wizard Progress

Create maintenance plan failed.

ADDITIONAL INFORMATION:

Create failed for JobStep 'Subplan'. (Microsoft.SqlServer.MaintenancePlanTasks)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The specified '@.subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

The server doesn't have the Management studio client installed so I can't create the maintenance plan locally. If I install just the Management studio will the data currently in the server be effected? Any solution to the remote problem?

Thanks,

Neil

SSIS(SQL Server integration services) need to be installed along with the server, for executing maintenace plans successfully.

For SP1, the current plan is either SSIS or tools installed on the server box would address this issue.

Gops Dwarak

Remote creation of a Maintenance plan

I'm trying to remotely create a maintenance plan using the management studio in order to backup databases. I get the following error.

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

TITLE: Maintenance Plan Wizard Progress

Create maintenance plan failed.

ADDITIONAL INFORMATION:

Create failed for JobStep 'Subplan'. (Microsoft.SqlServer.MaintenancePlanTasks)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The specified '@.subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

The server doesn't have the Management studio client installed so I can't create the maintenance plan locally. If I install just the Management studio will the data currently in the server be effected? Any solution to the remote problem?

Thanks,

Neil

SSIS(SQL Server integration services) need to be installed along with the server, for executing maintenace plans successfully.

For SP1, the current plan is either SSIS or tools installed on the server box would address this issue.

Gops Dwarak