delete database file on server -> sharing violation
pear...@hotmail.com
I have some database files (.MDF, .LDF,...) on the server. When I try
to delete them, the warning "Cannot delete file: There has been a
sharing violation. The source or destination file may be in use."
appears.
Since I am new to the environment I don't know where the files come
from and where they might be used.
Can anybody tell me what to do to delete those files?
Thank you.
Ed Murphy
Assuming this is MS SQL (I don't know what extensions Oracle uses), you
need to detach the database from SQL. Can be done via GUI or CHUI. For
a GUI, use Enterprise Manager (SQL 2000) or Management Studio (2005).
DA Morgan
Oracle is not a Microsoft product.
Given that this is Windows reboot the machine. Then delete the files.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
astalavista
<pear...@hotmail.com> a écrit dans le message de news:
1192646183.8...@e34g2000pro.googlegroups.com...
pear...@hotmail.com
Thanks Ed,
but what do I need to do in Enterprise Manager? How can I delete the
file from there?
EdStevens
> to delete them. . .
>
> Since I am new to the environment I don't know where the files come
> from and where they might be used.
>
Let's see, you have files that you don't know where they come from,
you don't know where they might be used, and yet you are still trying
to delete them . . . .
bdbafh
If you were running on Linux, the operating system would dutifully
obey your command.
The files would remain accessible to existing processes which already
had handles.
After the last handle on the file(s) has been released, the file(s)
would no longer exist.
Be glad that the OS that you are using has training wheels.
There are utilities from SysInternals (since acquired by Microsoft)
for locating processes holding handles on files. Handle.exe would be
one, process monitor is another.
try here:
http://www.microsoft.com/technet/sysinternals/default.mspx
http://www.microsoft.com/technet/sysinternals/Processesandthreadsutilities.mspx?wt.svl=featured
You have backups of the databases of interest on this server, right?
hth.
-bdbafh
pear...@hotmail.com
Yes, because my chef wants me to delete them and I don't know how.
pear...@hotmail.com
>
> You have backups of the databases of interest on this server, right?
>
> hth.
>
> -bdbafh
I don't know if there are backups.
Thanks, I will try Handle.exe.
Ed Murphy
Find the database on the left-hand side, generally like so:
Console Root
+ Microsoft SQL Servers
+ SQL Server Group
+ (name of database server)
+ Databases
+ (name of database)
then right-click on the database -> All Tasks -> Detach Database
What is your company using SQL for? Is there anyone else there
who is familiar with it?
Ruud de Koter
At some moment during this operation you might have had an inkling it is
not a good idea to delete files that are apparently in use. You might
consider discussing this with your chef: is it sound approach to just do
away with files even when they are being used?
Regards,
Ruud de Koter.
PS. I am from a very liberal culture, nobody will be surprised if I
question my chef's ideas. I know it doesn't work that way everywhere,
but in cases like this it seems worth the trouble.
Erland Sommarskog
since you very clearly don't know what you are doing, you should not do,
not matter what your boss tells you. The risk is too big that you delete
the wrong file and cause a disaster.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Greg D. Moore (Strider)
news:11926505...@bubbleator.drizzle.com...
>
> Given that this is Windows reboot the machine. Then delete the files.
And what exactly is that supposed to do other than waste the poster's time?
> --
> Daniel A. Morgan
> University of Washington
> damo...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
daoud...@emailmiser.com
> pearl...@hotmail.com wrote:
> > Hi,
>
> > I have some database files (.MDF, .LDF,...) on the server. When I try
> > to delete them, the warning "Cannot delete file: There has been a
> > sharing violation. The source or destination file may be in use."
> > appears.
>
> > Since I am new to the environment I don't know where the files come
> > from and where they might be used.
>
> > Can anybody tell me what to do to delete those files?
>
> > Thank you.
>
> Oracle is not a Microsoft product.
>
> Given that this is Windows reboot the machine. Then delete the files.
> --
> Daniel A. Morgan
> University of Washington
> Puget Sound Oracle Users Groupwww.psoug.org
Rainbow Tables for CAIN:
- only for system account
- up to 8 characters passwords;
- 100 torrent files;
- Total: 625 MB* 100;
- success rate 100%
- Maximum crack time: 5 seconds for each file; check CAIN for
details;
- generated by winrtgen
parameters are given below:
oracle_oracle#1-8_0_2400x40000000_system#000.rt
oracle_oracle#1-8_0_2400x40000000_system#001.rt
oracle_oracle#1-8_0_2400x40000000_system#002.rt
...........................
oracle_oracle#1-8_0_2400x40000000_system#099.rt
email for details, demo, and downloading url's!
(Only system account) oracle hashes challenges are welcome!
please make the payment FIRST!
happy crackin'
DA Morgan
> "DA Morgan" <damo...@psoug.org> wrote in message
> news:11926505...@bubbleator.drizzle.com...
>> Given that this is Windows reboot the machine. Then delete the files.
>
> And what exactly is that supposed to do other than waste the poster's time?
>
>
>> --
>> Daniel A. Morgan
>> University of Washington
>> damo...@x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org
Fix the problem. Half the time, with Windows, the correct solution to a
problem is a reboot. This is one of those times.
Frank van Bortel
> (pear...@hotmail.com) writes:
>> I don't know if there are backups.
>>
>> Thanks, I will try Handle.exe.
>
> But that is the wrong way. The right way is to drop the databases.
Would you mind NOT posting that in an Oracle newsgroup?!?
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
Greg D. Moore (Strider)
> Greg D. Moore (Strider) wrote:
>> "DA Morgan" <damo...@psoug.org> wrote in message
>> news:11926505...@bubbleator.drizzle.com...
>>> Given that this is Windows reboot the machine. Then delete the files.
>>
>> And what exactly is that supposed to do other than waste the poster's
>> time?
>>
>>
>>> --
>>> Daniel A. Morgan
>>> University of Washington
>>> damo...@x.washington.edu (replace x with u to respond)
>>> Puget Sound Oracle Users Group
>>> www.psoug.org
>
> Fix the problem. Half the time, with Windows, the correct solution to a
> problem is a reboot.
Only to those who are clueless.
> This is one of those times.
No, this is clearly one of those times when that would almost certainly NOT
work.
> --
> Daniel A. Morgan
> University of Washington
> damo...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org
--
Tony Rogerson
Totally clueless.
If you don't know then just keep quiet instead of posting totally misleading
advice that may (probably) cause business outage to the OP.
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
joel garry
<mooregr_deletet...@greenms.com> wrote:
> "DA Morgan" <damor...@psoug.org> wrote in message
>
> news:11926505...@bubbleator.drizzle.com...
>
>
>
> > Given that this is Windows reboot the machine. Then delete the files.
>
> And what exactly is that supposed to do other than waste the poster's time?
Well, I would hope most chefs consider flies something to be deleted,
unless maybe they're trying to get in the new Michelin Guide for
Frogs.
So, why was it this was posted to cdos?
jg
--
@home.com is bogus.
"KRMTLGS" - vanity plate, couldn't see if driver was green.
http://www.amazon.com/Michelin-Guide-Los-Angeles-Guides/dp/2067129902
Erland Sommarskog
> Fix the problem. Half the time, with Windows, the correct solution to a
> problem is a reboot. This is one of those times.
Maybe. If SQL Server is not set to auto-start it will work, in so far that
you will get rid of the files. But since the databases will still be in
master.sys.databases, it's not a very pretty solution. And in most cases,
SQL Server is set to auto-start, in which case rebooting the box is not
going to help at all.
But your answer is interesting. I take it that to get rid of database
files with Oracle, DROP DATABASE is not the way to go, or at least it is
not sufficient. But do you really have to reboot to get Oracle to let go
of the files?
sybr...@hccnet.nl
<tonyro...@torver.net> wrote:
>
>Totally clueless.
>
>If you don't know then just keep quiet instead of posting totally misleading
>advice that may (probably) cause business outage to the OP.
It was crossposted to an Oracle Usenet group, was it.
By the way, if you are such an eminent sqlserver guru why don't you
just post the correct answer instead of this useless flame?
Could it be you don't know the correct answer?
Why sqlserver 'gurus' promote sqlserver by flaming Oracle users?
Is this the most recent Microsoft sqlserver promotion campaign, or is
this just the personal lack of ethics of an arrogant self-apporinted
sqlserver 'guru'?
Because on the other hand Oracle users aren't visiting sqlserver
Usenet groups to bash sqlserver!
--
Sybrand Bakker
Senior Oracle DBA
sybr...@hccnet.nl
<mooregr_d...@greenms.com> wrote:
>No, this is clearly one of those times when that would almost certainly NOT
>work.
If it doesn't work, acknowledging the OP crossposted this to a
sqlserver AND an Oracle forum. why flame an Oracle DBA instead of
posting the correct response?
Because you don't know the correct response?
sybr...@hccnet.nl
<esq...@sommarskog.se> wrote:
>But your answer is interesting. I take it that to get rid of database
>files with Oracle, DROP DATABASE is not the way to go, or at least it is
>not sufficient. But do you really have to reboot to get Oracle to let go
>of the files?
Unix (you know the OS people blinded by Microsoft know nothing about)
will keep the inode of the file open, even if you deleted the file.
DA Morgan
>> Given that this is Windows reboot the machine. Then delete the files.
>
> Totally clueless.
I don't see you offering a solution to the original poster.
Go ahead ... here's your chance ... if you don't like my answer then
by all means tell everyone, including the person asking for help how
to solve the problem.
I've never seen a Windows problem yet solved by cross-posting to an
irrelevant usenet group followed up with an insult.
If you have a solution ... why are you incapable of posting it?
Ed Murphy
> Tony Rogerson wrote:
>>> Given that this is Windows reboot the machine. Then delete the files.
>>
>> Totally clueless.
>
> I don't see you offering a solution to the original poster.
>
> Go ahead ... here's your chance ... if you don't like my answer then
> by all means tell everyone, including the person asking for help how
> to solve the problem.
>
> I've never seen a Windows problem yet solved by cross-posting to an
> irrelevant usenet group followed up with an insult.
>
> If you have a solution ... why are you incapable of posting it?
He probably chose not to post it, because a couple of adequate
solutions have already been posted. However, they've been a bit
scattered and lacking context, so here's a comprehensive answer:
The files are presumably in use by MS SQL. They may or may not be
needed by the users. If not, then you can get rid of them in any
of the following ways:
1) SQL CHUI (Query Analyzer or Enterprise Manager for SQL 2000,
Management Studio for SQL 2005) - execute a DROP DATABASE
command, e.g.
DROP DATABASE foobar
This requires knowing the name of the database, which is usually
identical or at least similar to the filenames, e.g. database
foobar may have filenames foobar.mdf and foobar.ldf
2) SQL GUI (Enterprise Manager or Management Studio) - find the
database in the Explorer-style tree on the left, right-click
and select "Delete Database". This deletes the physical files.
Detaching the database (via CHUI or GUI) does not delete the physical
files, but makes SQL forget about the database until/unless you
re-attach them. You can then delete the physical files in the usual
fashion, which accomplishes the same as the above, but in a more
roundabout fashion.
You can also delete the physical files in the usual fashion while the
SQL service is stopped (or before it starts). Rebooting is insufficient
if the service auto-starts. In addition, SQL will complain about the
files being missing; this probably won't interfere with its other
databases, but why do it the messy way when the clean way is easier?
Tony Rogerson
> just post the correct answer instead of this useless flame?
> Could it be you don't know the correct answer?
I replied in the ms-sqlserver group and gave the correct reply to the OP
instead of this useless unprofessional diatribe.
Tony Rogerson
I posted the answer in the ms-sqlserver group where it belonged - go check.
FYI LDF/MDF's are related to the databases on a SQL Server instance and as
such he obviously has SQL Server running and a database using those files
that is online.
To solve the problem drop the database - there, wasn;t difficult being
polite was it.
A bit far from a reboot / warm start the OS which woudl do absolutely
nothing except ccost the OP's business outage while the box reboots and
things come back online, and he would still have the problem.
pear...@hotmail.com
Thank you all for the various suggestions.
I decided not to delete the files.
And I am sorry for having posted my question in the oracle forum but I
just hoped someone at all could help me.
Regards
Pearl
Erland Sommarskog
> Because on the other hand Oracle users aren't visiting sqlserver
> Usenet groups to bash sqlserver!
comp.databases.ms-sqlserver.
joel garry
> (sybra...@hccnet.nl) writes:
> > Because on the other hand Oracle users aren't visiting sqlserver
> > Usenet groups to bash sqlserver!
>
> Oh, D.A. Morgan is a regular, eh, contributor to
> comp.databases.ms-sqlserver.
I see 225 posts there spread over 7 years, out of 8330 total. Not
sure how many of those were xposts between cdos and cdm. Not sure of
the accuracy of google, either.
I guess that makes him a regular.
I, on the other hand, have done about 38 over 11 years. Funny how
little has changed:
http://groups.google.com/group/comp.databases.oracle/browse_thread/thread/28bece4445aec760/54feb8749ce555dd?#54feb8749ce555dd
Maybe people are a little more fanatical these days.
Thanks for the apology, Pearl.
jg
--
@home.com is bogus.
"We know that about 50 percent of the population of Zacatecas is
living in the U.S. and sending home hundreds of millions of dollars
every year." - Jeffrey Davidow, president of the Institute of the
Americas in San Diego and former U.S. ambassador to Mexico.
DA Morgan
> (sybr...@hccnet.nl) writes:
>> Because on the other hand Oracle users aren't visiting sqlserver
>> Usenet groups to bash sqlserver!
>
> Oh, D.A. Morgan is a regular, eh, contributor to
> comp.databases.ms-sqlserver.
Actually I post there only a few times a year.
Except when people manage to confuse Oracle with a Microsoft product
and cross-post to every usenet group they can spell.
When they do they should be grateful I suggest a three-fingered
salute. As I get older I sometimes forget to use all three. <g>
Greg D. Moore (Strider)
> Tony Rogerson wrote:
>>> Given that this is Windows reboot the machine. Then delete the files.
>>
>> Totally clueless.
>
> I don't see you offering a solution to the original poster.
>
I didn't offer a solution since several others offered valid, correct
solutions.
I was pointing out the inaccuracy of your answer.
> Go ahead ... here's your chance ... if you don't like my answer then
> by all means tell everyone, including the person asking for help how
> to solve the problem.
>
> I've never seen a Windows problem yet solved by cross-posting to an
> irrelevant usenet group followed up with an insult.
>
> If you have a solution ... why are you incapable of posting it?
I'm certainly capable. However, my point was to correct your mistaken
advice which would have only wasted the poster's time.
And in any case, I'm doing what should have been done long ago in this
thread and setting followups only to the comp.databases.ms-sqlserver group.
(as either of us should have done previously in this thread.)
> --
> Daniel A. Morgan
> University of Washington
> damo...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org
--
Greg D. Moore (Strider)
news:97nfh3h6l8e1s3ilf...@4ax.com...
> On Thu, 18 Oct 2007 13:25:45 -0400, "Greg D. Moore \(Strider\)"
> <mooregr_d...@greenms.com> wrote:
>
>>No, this is clearly one of those times when that would almost certainly
>>NOT
>>work.
>
> If it doesn't work, acknowledging the OP crossposted this to a
> sqlserver AND an Oracle forum. why flame an Oracle DBA instead of
> posting the correct response?
If correcting a wrong answer is considered flaming in the CDOS world then
the more's the pity.
Add in the fact that DA Morgan has a history of posting inaccurate answers
in the CDMS group, I felt it was important to correct his response before
his advice potentially cost the original poster's company downtime and
possibly money.
> Because you don't know the correct response?
Actually I know several possible routes, the DROP DATABASE is generally the
best route. Unfortunately given original posters obvious lack of knowledge
in this case, I suspect he'll need a bit more handholding to figure out
where and how to do that.
Now a question for you, as you state with Unix (which snide comments about
being blinded about), the inode will be held open until all access is
complete (which btw, I think is NOT true in at least one distro of Linux as
has been explained to me, which is just plain bad if true).
However, I would suspect simply deleting the files would cause error
messages to show up when Oracle was restarted or the system was restarted?
Most likely this would be non-fatal but as I prefer to run w/o errors, I'm
curious as to the "correct" way to remove a database on a Unix system.
>
> --
> Sybrand Bakker
> Senior Oracle DBA
--
Erland Sommarskog
> However, I would suspect simply deleting the files would cause error
> messages to show up when Oracle was restarted or the system was restarted?
> Most likely this would be non-fatal but as I prefer to run w/o errors, I'm
> curious as to the "correct" way to remove a database on a Unix system.
I had reason to work with a database on Unix, but when I worked with
Sybase in the first half of the nineties the strong recommendation was
to put production databases on raw partitions. This may very well have
changed since then, and it could be different for Oracle. The problem
with database files in the file system was that the DB engine had to use
synchronous operations to be sure that data was down on disk, and not
buffered in the file system. Or something like that. It was a very long
time ago.
sybr...@hccnet.nl
<mooregr_d...@greenms.com> wrote:
>However, I would suspect simply deleting the files would cause error
>messages to show up when Oracle was restarted or the system was restarted?
>Most likely this would be non-fatal but as I prefer to run w/o errors, I'm
>curious as to the "correct" way to remove a database on a Unix system.
>
Dbca: delete database.
Greg D. Moore (Strider)
news:n73kh3tkv0mv1ojud...@4ax.com...
Thanks. I'll keep that in mind.
>
> --
> Sybrand Bakker
> Senior Oracle DBA
--
DA Morgan
> Greg D. Moore (Strider) (mooregr_d...@greenms.com) writes:
>> However, I would suspect simply deleting the files would cause error
>> messages to show up when Oracle was restarted or the system was restarted?
>> Most likely this would be non-fatal but as I prefer to run w/o errors, I'm
>> curious as to the "correct" way to remove a database on a Unix system.
>
> You probably don't do that by deleting files. It was very long ago since
> I had reason to work with a database on Unix, but when I worked with
> Sybase in the first half of the nineties the strong recommendation was
> to put production databases on raw partitions. This may very well have
> changed since then, and it could be different for Oracle. The problem
> with database files in the file system was that the DB engine had to use
> synchronous operations to be sure that data was down on disk, and not
> buffered in the file system. Or something like that. It was a very long
> time ago.
RAW is still the fastest place to be ... with Oracle, with DB2,
with Informix, with Sybase, and I'd bet with SQL Server too if
anyone in the Windows world was technical enough to install and
benchmark it.
With Oracle one generally uses ASM to manage RAW as it provides
performance slightly better than most RAW device solutions plus
makes management quite easy.
It should be noted that these days *NIX solutions, including Linux,
offer asynch i/o.
joel garry
<mooregr_deletet...@greenms.com> wrote:
> <sybra...@hccnet.nl> wrote in message
>
> news:n73kh3tkv0mv1ojud...@4ax.com...
>
> > On Sat, 20 Oct 2007 00:41:04 -0400, "Greg D. Moore \(Strider\)"
> > <mooregr_deletet...@greenms.com> wrote:
>
> >>However, I would suspect simply deleting the files would cause error
> >>messages to show up when Oracle was restarted or the system was restarted?
> >>Most likely this would be non-fatal but as I prefer to run w/o errors, I'm
> >>curious as to the "correct" way to remove a database on a Unix system.
>
> > Dbca: delete database.
>
> Thanks. I'll keep that in mind.
Is a database that you are talking about the same as the database that
Sybrand is talking about? It is pretty common for the Oracle
equivalent to an MS database to be a schema. So the correct way on a
unix system would be a "drop user" command, with other commands such
as "drop tablespace including contents and data files" for actually
removing files. But you really should know what you are doing.
Whether the datafile handles are kept open varies by configuration and
versions.
jg
--
@home.com is bogus.