Delete directory

Can any one let me know like how can I remove one directory, which contains
couple of file, through xp_cmdshell extended stored procedure.
Thanks in advance.
xp_cmdshell 'rd C:\Somedirectory /s' ?
Remember that fixed Drive letters are on the SQl Server only.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
news:uthGMXKVFHA.2700@.TK2MSFTNGP12.phx.gbl...
> Can any one let me know like how can I remove one directory, which
> contains
> couple of file, through xp_cmdshell extended stored procedure.
> Thanks in advance.
>
|||Thanks Jens Suessmeyer.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OXVSHdKVFHA.3432@.TK2MSFTNGP10.phx.gbl...
> xp_cmdshell 'rd C:\Somedirectory /s' ?
> Remember that fixed Drive letters are on the SQl Server only.
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
> news:uthGMXKVFHA.2700@.TK2MSFTNGP12.phx.gbl...
>
|||Can I see all the dos commands like if i write Help in dos command so it
will show all the commands, is there any commands available ?
Thanks
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OXVSHdKVFHA.3432@.TK2MSFTNGP10.phx.gbl...
> xp_cmdshell 'rd C:\Somedirectory /s' ?
> Remember that fixed Drive letters are on the SQl Server only.
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
> news:uthGMXKVFHA.2700@.TK2MSFTNGP12.phx.gbl...
>
|||Yes through HELP it's possible.....
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OXVSHdKVFHA.3432@.TK2MSFTNGP10.phx.gbl...
> xp_cmdshell 'rd C:\Somedirectory /s' ?
> Remember that fixed Drive letters are on the SQl Server only.
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
> news:uthGMXKVFHA.2700@.TK2MSFTNGP12.phx.gbl...
>

Delete DB Transaction Log file

I have a SQL Server 2000 database that has three transaction log files. I
would like to delete one of the files. I know the file has to be empty
before you can delete it.
Presently the one transaction file that I would like to delete is 400 MB in
size. I have tried several days to shrink the file but no luck. Is there
away to concatenate these files?
Any recommendations on procedures to delete a transaction log file.
Thanks,Use DBCC SHRINKFILE with the Emptyfile option on the file that was created
last. Once that is run and there are no open or active transactions in that
file you can remove it with the alter database command. Then do that for
the second one. You can not remove the primary (the one created first) one.
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:5E20DA95-B2E2-4E10-95EE-BA09C55AF5B1@.microsoft.com...
> I have a SQL Server 2000 database that has three transaction log files. I
> would like to delete one of the files. I know the file has to be empty
> before you can delete it.
> Presently the one transaction file that I would like to delete is 400 MB
> in
> size. I have tried several days to shrink the file but no luck. Is there
> away to concatenate these files?
> Any recommendations on procedures to delete a transaction log file.
> Thanks,

Delete DB Transaction Log file

I have a SQL Server 2000 database that has three transaction log files. I
would like to delete one of the files. I know the file has to be empty
before you can delete it.
Presently the one transaction file that I would like to delete is 400 MB in
size. I have tried several days to shrink the file but no luck. Is there
away to concatenate these files?
Any recommendations on procedures to delete a transaction log file.
Thanks,Use DBCC SHRINKFILE with the Emptyfile option on the file that was created
last. Once that is run and there are no open or active transactions in that
file you can remove it with the alter database command. Then do that for
the second one. You can not remove the primary (the one created first) one.
--
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:5E20DA95-B2E2-4E10-95EE-BA09C55AF5B1@.microsoft.com...
> I have a SQL Server 2000 database that has three transaction log files. I
> would like to delete one of the files. I know the file has to be empty
> before you can delete it.
> Presently the one transaction file that I would like to delete is 400 MB
> in
> size. I have tried several days to shrink the file but no luck. Is there
> away to concatenate these files?
> Any recommendations on procedures to delete a transaction log file.
> Thanks,

Delete DB Transaction Log file

I have a SQL Server 2000 database that has three transaction log files. I
would like to delete one of the files. I know the file has to be empty
before you can delete it.
Presently the one transaction file that I would like to delete is 400 MB in
size. I have tried several days to shrink the file but no luck. Is there
away to concatenate these files?
Any recommendations on procedures to delete a transaction log file.
Thanks,
Use DBCC SHRINKFILE with the Emptyfile option on the file that was created
last. Once that is run and there are no open or active transactions in that
file you can remove it with the alter database command. Then do that for
the second one. You can not remove the primary (the one created first) one.
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:5E20DA95-B2E2-4E10-95EE-BA09C55AF5B1@.microsoft.com...
> I have a SQL Server 2000 database that has three transaction log files. I
> would like to delete one of the files. I know the file has to be empty
> before you can delete it.
> Presently the one transaction file that I would like to delete is 400 MB
> in
> size. I have tried several days to shrink the file but no luck. Is there
> away to concatenate these files?
> Any recommendations on procedures to delete a transaction log file.
> Thanks,

Delete Database Transaction Log File

My database transaction log file grew very fast and used
all the space on F: drive except 25 MB on my server. So I
created another database transaction log file on my K:
drive on this server.
I have resolved the problem with transaction log growing
very large.
I would like to delete the second transaction log file on
the K: drive. How can I completed this task?
Thanks,
Dan
I believe the command goes something like:
ALTER DATABASE youdb
REMOVE FILE tranlog_on_K_drive
The Tranlog file of course will need to be removed.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan
|||USE databasename
GO
-- if you need to, get the filename from EXEC sp_helpfile
-- you may also want to:
-- BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE(filename, EMPTYFILE)
GO
USE Master
GO
ALTER DATABASE databasename REMOVE FILE filename
GO
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan

Delete Database Transaction Log File

My database transaction log file grew very fast and used
all the space on F: drive except 25 MB on my server. So I
created another database transaction log file on my K:
drive on this server.
I have resolved the problem with transaction log growing
very large.
I would like to delete the second transaction log file on
the K: drive. How can I completed this task?
Thanks,
DanI believe the command goes something like:
ALTER DATABASE youdb
REMOVE FILE tranlog_on_K_drive
The Tranlog file of course will need to be removed.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan|||USE databasename
GO
-- if you need to, get the filename from EXEC sp_helpfile
-- you may also want to:
-- BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE(filename, EMPTYFILE)
GO
USE Master
GO
ALTER DATABASE databasename REMOVE FILE filename
GO
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan

Delete Database Transaction Log File

My database transaction log file grew very fast and used
all the space on F: drive except 25 MB on my server. So I
created another database transaction log file on my K:
drive on this server.
I have resolved the problem with transaction log growing
very large.
I would like to delete the second transaction log file on
the K: drive. How can I completed this task?
Thanks,
DanI believe the command goes something like:
ALTER DATABASE youdb
REMOVE FILE tranlog_on_K_drive
The Tranlog file of course will need to be removed.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan|||USE databasename
GO
-- if you need to, get the filename from EXEC sp_helpfile
-- you may also want to:
-- BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE(filename, EMPTYFILE)
GO
USE Master
GO
ALTER DATABASE databasename REMOVE FILE filename
GO
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan

delete database file on server -> sharing violation

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.pearl146@.hotmail.com wrote:

Quote:

Originally Posted by

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?

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).|||pearl146@.hotmail.com wrote:

Quote:

Originally Posted by

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
damorgan@.x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||see sqlservr.exe

<pearl146@.hotmail.coma crit dans le message de news:
1192646183.822289.234660@.e34g2000pro.googlegroups. com...

Quote:

Originally Posted by

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.
>

|||On Oct 17, 3:02 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

pearl...@.hotmail.com wrote:

Quote:

Originally Posted by

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.

>

Quote:

Originally Posted by

Since I am new to the environment I don't know where the files come
from and where they might be used.

>

Quote:

Originally Posted by

Can anybody tell me what to do to delete those files?

>
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).

Thanks Ed,
but what do I need to do in Enterprise Manager? How can I delete the
file from there?|||On Oct 17, 1:36 pm, pearl...@.hotmail.com wrote:

Quote:

Originally Posted by

Hi,
>
I have some database files ... on the server. When I try
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.
>

<snip>

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 . . . .|||pearl...@.hotmail.com wrote:

Quote:

Originally Posted by

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.

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/sy...ls/default.mspx
http://www.microsoft.com/technet/sy...wt.svl=featured
You have backups of the databases of interest on this server, right?

hth.

-bdbafh|||On Oct 17, 4:22 pm, EdStevens <quetico_...@.yahoo.comwrote:

Quote:

Originally Posted by

On Oct 17, 1:36 pm, pearl...@.hotmail.com wrote:Hi,
>

Quote:

Originally Posted by

I have some database files ... on the server. When I try
to delete them. . .

>

Quote:

Originally Posted by

Since I am new to the environment I don't know where the files come
from and where they might be used.

>
<snip>
>
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 . . . .

Yes, because my chef wants me to delete them and I don't know how.|||On Oct 17, 5:05 pm, bdbafh <bdb...@.gmail.comwrote:

Quote:

Originally Posted by

pearl...@.hotmail.com wrote:

Quote:

Originally Posted by

Hi,

>

Quote:

Originally Posted by

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.

>

Quote:

Originally Posted by

Since I am new to the environment I don't know where the files come
from and where they might be used.

>

Quote:

Originally Posted by

Can anybody tell me what to do to delete those files?

>

Quote:

Originally Posted by

Thank you.

>
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/sy...sandthreadsutil...
>
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.|||pearl146@.hotmail.com wrote:

Quote:

Originally Posted by

On Oct 17, 3:02 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

>pearl...@.hotmail.com wrote:

Quote:

Originally Posted by

>>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?

>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).

>
Thanks Ed,
but what do I need to do in Enterprise Manager? How can I delete the
file from there?

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?|||pearl146@.hotmail.com wrote:

Quote:

Originally Posted by

On Oct 17, 4:22 pm, EdStevens <quetico_...@.yahoo.comwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>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 . . . .

>
Yes, because my chef wants me to delete them and I don't know how.
>

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.|||(pearl146@.hotmail.com) writes:

Quote:

Originally Posted by

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. But
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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"DA Morgan" <damorgan@.psoug.orgwrote in message
news:1192650569.162054@.bubbleator.drizzle.com...

Quote:

Originally Posted by

>
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?

Quote:

Originally Posted by

--
Daniel A. Morgan
University of Washington
damorgan@.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|||On Oct 17, 9:49 pm, DA Morgan <damor...@.psoug.orgwrote:

Quote:

Originally Posted by

pearl...@.hotmail.com wrote:

Quote:

Originally Posted by

Hi,

>

Quote:

Originally Posted by

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.

>

Quote:

Originally Posted by

Since I am new to the environment I don't know where the files come
from and where they might be used.

>

Quote:

Originally Posted by

Can anybody tell me what to do to delete those files?

>

Quote:

Originally Posted by

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
damor...@.x.washington.edu (replace x with u to respond)
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'|||Greg D. Moore (Strider) wrote:

Quote:

Originally Posted by

"DA Morgan" <damorgan@.psoug.orgwrote in message
news:1192650569.162054@.bubbleator.drizzle.com...

Quote:

Originally Posted by

>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?
>
>

Quote:

Originally Posted by

>--
>Daniel A. Morgan
>University of Washington
>damorgan@.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.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||Erland Sommarskog wrote:

Quote:

Originally Posted by

(pearl146@.hotmail.com) writes:

Quote:

Originally Posted by

>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...|||"DA Morgan" <damorgan@.psoug.orgwrote in message
news:1192720293.955947@.bubbleator.drizzle.com...

Quote:

Originally Posted by

Greg D. Moore (Strider) wrote:

Quote:

Originally Posted by

>"DA Morgan" <damorgan@.psoug.orgwrote in message
>news:1192650569.162054@.bubbleator.drizzle.com...

Quote:

Originally Posted by

>>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?
>>
>>

Quote:

Originally Posted by

>>--
>>Daniel A. Morgan
>>University of Washington
>>damorgan@.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.

Quote:

Originally Posted by

This is one of those times.

No, this is clearly one of those times when that would almost certainly NOT
work.

Quote:

Originally Posted by

--
Daniel A. Morgan
University of Washington
damorgan@.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|||Given that this is Windows reboot the machine. Then delete the files.

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]|||I have some database files (.MDF, .LDF,...) on the server. When I try

Quote:

Originally Posted by

to delete them, the warning "Cannot delete file: There has been a
sharing violation. The source or destination file may be in use."
appears.

these are files associated with a SQL Server database - you need to isolate
the database and if you don't need it anymore then DROP DATABASE it through
SQL Server management tools

--
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]|||If you saw a pan of potatoes cooking on the stove would you lift it up and
put it in the bin?

No, they are in use and as such there is a purpose.

Find out what database these are for, undoubtedly it will be some important
database.

--
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]|||On Oct 18, 4:42 am, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:

Quote:

Originally Posted by

"DA Morgan" <damor...@.psoug.orgwrote in message
>
news:1192650569.162054@.bubbleator.drizzle.com...
>
>
>

Quote:

Originally Posted by

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-Guid...s/dp/2067129902|||DA Morgan (damorgan@.psoug.org) writes:

Quote:

Originally Posted by

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?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Thu, 18 Oct 2007 19:35:59 +0100, "Tony Rogerson"
<tonyrogerson@.torver.netwrote:

Quote:

Originally Posted by

>
>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|||On Thu, 18 Oct 2007 13:25:45 -0400, "Greg D. Moore \(Strider\)"
<mooregr_deleteth1s@.greenms.comwrote:

Quote:

Originally Posted by

>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?

--
Sybrand Bakker
Senior Oracle DBA|||On Thu, 18 Oct 2007 21:15:21 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>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.

--
Sybrand Bakker
Senior Oracle DBA|||Tony Rogerson wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>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?
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||DA Morgan wrote:

Quote:

Originally Posted by

Tony Rogerson wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>>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?|||By the way, if you are such an eminent sqlserver guru why don't you

Quote:

Originally Posted by

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, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]|||I don't see you offering a solution to the original poster.

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.

--
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]|||On 18 Okt., 18:23, sybra...@.hccnet.nl wrote:

Quote:

Originally Posted by

On Thu, 18 Oct 2007 19:35:59 +0100, "Tony Rogerson"
>
<tonyroger...@.torver.netwrote:
>

Quote:

Originally Posted by

Totally clueless.

>

Quote:

Originally Posted by

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

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|||(sybrandb@.hccnet.nl) writes:

Quote:

Originally Posted by

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.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

(sybrandb@.hccnet.nl) writes:

Quote:

Originally Posted by

>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>
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||"DA Morgan" <damorgan@.psoug.orgwrote in message
news:1192757291.109235@.bubbleator.drizzle.com...

Quote:

Originally Posted by

Tony Rogerson wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>>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.

Quote:

Originally Posted by

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.)

Quote:

Originally Posted by

--
Daniel A. Morgan
University of Washington
damorgan@.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|||<sybrandb@.hccnet.nlwrote in message
news:97nfh3h6l8e1s3ilfpb2ao7t6qf5a3i784@.4ax.com...

Quote:

Originally Posted by

On Thu, 18 Oct 2007 13:25:45 -0400, "Greg D. Moore \(Strider\)"
<mooregr_deleteth1s@.greenms.comwrote:
>

Quote:

Originally Posted by

>>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.

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

>
--
Sybrand Bakker
Senior Oracle DBA

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On Sat, 20 Oct 2007 00:41:04 -0400, "Greg D. Moore \(Strider\)"
<mooregr_deleteth1s@.greenms.comwrote:

Quote:

Originally Posted by

>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.

--
Sybrand Bakker
Senior Oracle DBA|||<sybrandb@.hccnet.nlwrote in message
news:n73kh3tkv0mv1ojudea4nskfd1amodq4dl@.4ax.com...

Quote:

Originally Posted by

On Sat, 20 Oct 2007 00:41:04 -0400, "Greg D. Moore \(Strider\)"
<mooregr_deleteth1s@.greenms.comwrote:
>

Quote:

Originally Posted by

>>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.

Quote:

Originally Posted by

>
--
Sybrand Bakker
Senior Oracle DBA

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On Oct 20, 9:06 am, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:

Quote:

Originally Posted by

<sybra...@.hccnet.nlwrote in message
>
news:n73kh3tkv0mv1ojudea4nskfd1amodq4dl@.4ax.com...
>

Quote:

Originally Posted by

On Sat, 20 Oct 2007 00:41:04 -0400, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

>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.

>

Quote:

Originally Posted by

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.
http://bofh.ntk.net/StickyMags.html

Delete data, but file size increase

I encounter one weird problem, I have a database with around 7 GB ...
when I delete a bunch of data from it, it suppose to reduce the
database file size, but weirdly, the file size increase to 8 GB.

Wondering why. Is it suppose to be like that?
Is it the architecture is designed to work like that?

Is there any way for me to reduce the database file size?

Thanks.

Peter CCHIf you take back daily with append data option, the size gets increased

Madhivanan|||What utility are you using to measure the database size?

Are you using sp_spaceused and just noting the "Database Size" column?
This also includes the transaction log, and this will increase when you
delete data from the database, until you either truncate or backup your
transaction log.

You can also get wrong space values from sp_spaceused if you are making
frequent and large changes to data, such as updates and deletes, as the
counters that record the new extent allocations and deallocations don't
get updated dynamically every time.

Try running sp_spaceused in the database, and note the values in all
columns.
Now, repeat this, but execute sp_spaceused @.updateusage='true'

(N.B. This can take a few minutes to run. I have never had a problem
running this on a live database during the day, but be aware that it
runs DBCC UPDATEUSAGE and forces updates to the sysindexes catalog; it
is less-risky to run it out of hours)

Here's a good example from one of my databases:

sp_spaceused
go

database_name, database_size, unallocated space
Roms, 22541.00 MB, -4407.84 MB

reserved, data, index_size, unused
27589472 KB, 16362352 KB, 11146992 KB, 80128 KB

Note the negative Unallocated Space value.

Now I run:

sp_spaceused @.updateusage='true'

database_name, database_size, unallocated space
Roms, 22541.00 MB, 1.43 MB

reserved, data, index_size, unused
23074376 KB, 15558360 KB, 7447464 KB, 68552 KB

You can see that, prior to this, the Reserved, Data and Index_Size
columns were all showing more space being used than the true value. I
suggest you try this after you delete your data in future, and see if
you get the values you expect.sql

delete data without writing to the log file

I will be deleting a large amount of data from our database , is there a way
to prevent this from writing to the log file?
Thanks in advance.As long as your tables do not have foreign keys AND you want to delete =all the data from a specific table you could use TRUNCATE. Otherwise =deleting in batches and then clearing the transaction log is your best =method.
-- Keith
"Fulya Erol" <ferol@.no.nospam.mezun.com> wrote in message =news:uaeNC11uDHA.1596@.TK2MSFTNGP10.phx.gbl...
> I will be deleting a large amount of data from our database , is there =a way
> to prevent this from writing to the log file?
> > Thanks in advance.
> >|||Hi
You could use the simple recovery model, but this is not advisable for a
production system.
John
"Fulya Erol" <ferol@.no.nospam.mezun.com> wrote in message
news:uaeNC11uDHA.1596@.TK2MSFTNGP10.phx.gbl...
> I will be deleting a large amount of data from our database , is there a
way
> to prevent this from writing to the log file?
> Thanks in advance.
>|||That won't prevent the data from being written to the log. SQL Server will
auto-truncate the log when the checkpoint process wakes up... but
transactional integrity is maintianed in SIMPLE which means the entirer
transaction and all it's IO still goes to the log...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:k86Ab.110$NX6.916578@.news-text.cableinet.net...
> Hi
> You could use the simple recovery model, but this is not advisable for a
> production system.
> John
> "Fulya Erol" <ferol@.no.nospam.mezun.com> wrote in message
> news:uaeNC11uDHA.1596@.TK2MSFTNGP10.phx.gbl...
> > I will be deleting a large amount of data from our database , is there a
> way
> > to prevent this from writing to the log file?
> >
> > Thanks in advance.
> >
> >
>

Delete connection file

I've inherited a procedure that performs a few transforms and writes to flat files in a working directory using a connection in connection manager.

After the manipulation, the system cleans up by deleting the working files. Works fine in development, but from the command line gives:

The process cannot access the file <filename> because it is being used by another process.".

I suspect this is because connection manager still has the file open.

Any ideas?

Thanks

Guy

Yes, you are right.try closing the connection.or try a switch from command line to do it.|||Any ideas on how to close a connection? As far as I can see a connection is opened by reference to it, but I can't see any way to close a connection.|||

//release here

managedOleInstance.AcquireConnections(null);

managedOleInstance.ReinitializeMetaData();

managedOleInstance.ReleaseConnections();

|||

So this would be in a script componenent after the last valid use of the connection and before the deletion I take it?

I would also probably need to force RetainSameConnection to use only one connection the whole way through.

|||

Can u show the code you have written?

Then i can favor you better.

Also, can u solve my problem?

I have a flat file with name "Employee.txt " (Full url: C:\Employee.txt ) .The File content is like this

Anil,Engineering,1997
Sunil,Sales,1981
Kumar,Inventory,1991
Rajesh,Engineering,1992

(Note: Items are Comma Seperated)

Now, i have a SQL Server database called "EmployeeDB" which has 2 tables "TblEmp1", "TblEmp2".
The Table is like this.

TblEmp1 : Columns
EmpName EmpDept EmpjoinDate

TblEmp2 : Columns
EName EDate Edept

using integration services (SSIS) i need code to Create a dtsx package so that i can push the flat file content to these 2 tables.
And the condition is :

After Executing the package Data loaded in TblEmp1 should be like this

EmpName EmpDept EmpjoinDate
Anil Engineering 1997
Sunil Sales 1981
Kumar Inventory 1991
Rajesh Engineering 1992

(No change in order compared to source)
And Data loaded inTBLEmp2 should be like this

EName EDate Edept
Anil 1997 Engineering
Sunil 1981 Sales
Kumar 1991 Inventory
Rajesh 1992 Engineering

Now, i know that we need to do like this in wizard
1) Create a flat file source component.
2) Create flat file connection and set the properties of flat file (delimeters and other things)
3) Create a Multicast Component.
4) Create a Path between Flat file source and Multicast.
5) Create 2 destination component(each for a table).
6) Create path from multicast to 2 destination components
7) Create a OledbConnection and set table names for 2 destination components..
7) Now,i have to do mapping for destination1.
8) Now, i have to do mapping for destination2( this mapping will be different from mapping done for destination1 because iam not inserting the data in the same order in which iam doing for TBLEmp1.

I have done it in wizard.I need to do it through code and i know that its not complicated.The main problem is Mapping differently for 2 destinations from source.for 1st one we can have a forloop for mapping.but for 2nd one iam confused!!

Please Get back ASAP today.
Thanks in Advance,
Anil Kumar MS

|||

Sorry mate, our days in Sydney obviously end earlier than wherever you are...

Firstly, I don't have code, just components. I've already outlined the relevant bits of what happened.

Secondly, with respect of your problem. Is there a constraint linking the two tables? If not, what you're doing looks just dandy. If there is, you may need to change your order or temporalily relax the constraint.

If its just a question of mapping why not just use the mapping tab on the destination?

Regards

delete backup file

Hi,
I have a database which size is 64Go, and i backup it everyday with 1 day of retention.
But the maintenance plan don't delete the file older than 1 day.
This works fine for the other db.
Do you know what is the pb ?
regards.Hi,
I have a database which size is 64Go, and i backup it everyday with 1 day of retention.
But the maintenance plan don't delete the file older than 1 day.
This works fine for the other db.
Do you know what is the pb ?
regards.

Does the maintenance plan generate an error log? It should be in %Install Path%\MSSQL\LOGS. Look through there. As I recall, if there is any problem anywhere in the process of making the backup, the purge old files process will not work (this is by design).

Regards,

hmscott

Delete an excel worksheet only if it exists, in a dts package

Hi :

1. I import data into sqlserver, from an excel file, from a worksheet
within it, named 'input_data'
2. then i scrub the data in sqlserver
3. then i export some data into the same excelfile but into another
worksheet named 'output_data'

I have got it working and this is how i am doing it right now. I am
having a problem in the few steps identified below :

1. I delete the worksheet 'output_data' using an execsql task
DROP TABLE `output_data'

2. Then i have another execsql task to create it
CREATE TABLE `output_data`

3. Then i have datapump task to put data into worksheet.

The work fine, but i am running into a problem, when the output_data
worksheet doesnt exist.
So i need to write a query telling
IF `output_data` doesnt exist, then DROP IT

How do i do this
I am open to doing this in ActiveX Script(vbscript) and ExecSQL task

Also i do not have excel dll or runtime in my dbserver.

Please let me know
thanks
RSapologize for typo
I do have a problem, when 'output_data' worksheet does exist
and hence need a query telling,

if output_data worksheet exists, then drop it

thank you
RS

delete a text file

I have a Job which imports from a text file. The second step is to delete th
e
text file. I have use the following code and it reports success but the file
is still there. Any ideas?
EXEC master..xp_cmdshell 'Del D:\Documents and
Settings\Administrator\Desktop\testextra
ct.txt'Who is SQL Server service starting as? My guess is that the account does
not have write/modify permissions on a user's folder.
"Ray" <Ray@.discussions.microsoft.com> wrote in message
news:08D9D12B-26A1-4F4C-889C-8F32F274E0C9@.microsoft.com...
>I have a Job which imports from a text file. The second step is to delete
>the
> text file. I have use the following code and it reports success but the
> file
> is still there. Any ideas?
> EXEC master..xp_cmdshell 'Del D:\Documents and
> Settings\Administrator\Desktop\testextra
ct.txt'|||Who is SQL Server service starting as? My guess is that the account does
not have write/modify permissions on a user's folder.
"Ray" <Ray@.discussions.microsoft.com> wrote in message
news:08D9D12B-26A1-4F4C-889C-8F32F274E0C9@.microsoft.com...
>I have a Job which imports from a text file. The second step is to delete
>the
> text file. I have use the following code and it reports success but the
> file
> is still there. Any ideas?
> EXEC master..xp_cmdshell 'Del D:\Documents and
> Settings\Administrator\Desktop\testextra
ct.txt'|||Windows logon permission. I am always in as admin
"Aaron Bertrand [SQL Server MVP]" wrote:

> Who is SQL Server service starting as? My guess is that the account does
> not have write/modify permissions on a user's folder.
>
>
> "Ray" <Ray@.discussions.microsoft.com> wrote in message
> news:08D9D12B-26A1-4F4C-889C-8F32F274E0C9@.microsoft.com...
>
>|||> Windows logon permission. I am always in as admin
In every single installation I have ever seen, *YOU* are not the user
account SQL Server is running as.
Go to the control panel on the SQL Server machine, open up the services
control panel applet, go to MSSQLServer (or MSSQL$<instance> ) and look at
the Log On tab. Dollars to donuts says that account is not your account.
But it is a given that *that* account needs permissions on the folder where
xp_cmdshell is attempting to play.|||You are right!! Placed the folder on another drive and it worked perfectly.
Thanks so much
"Aaron Bertrand [SQL Server MVP]" wrote:

> In every single installation I have ever seen, *YOU* are not the user
> account SQL Server is running as.
> Go to the control panel on the SQL Server machine, open up the services
> control panel applet, go to MSSQLServer (or MSSQL$<instance> ) and look at
> the Log On tab. Dollars to donuts says that account is not your account.
> But it is a given that *that* account needs permissions on the folder wher
e
> xp_cmdshell is attempting to play.
>
>

Delete a file from the Server

I have a table in my database on SQL Server which holds a file name
that refers to a file that is stored on the server. I would like to
create a trigger to delete this file from the server if the row in the
table is deleted. I have been trying to use this command in a trigger
(<filename> is the name and path of the file):

xp_cmdshell "delete <filename>"

If some one could please help I would appreciate it very much. I
would love a code sample if you have one. Thank you so very much.

From,

RyanOn 28 Jul 2003 11:12:32 -0700 in comp.databases.ms-sqlserver,
rvanarnam@.aol.com (Ryan) wrote:

>I have a table in my database on SQL Server which holds a file name
>that refers to a file that is stored on the server. I would like to
>create a trigger to delete this file from the server if the row in the
>table is deleted. I have been trying to use this command in a trigger
>(<filename> is the name and path of the file):
>xp_cmdshell "delete <filename>"
>If some one could please help I would appreciate it very much. I
>would love a code sample if you have one. Thank you so very much.

(untried)
xp_cmdshell "cmd /c del <filename>"

--
Ride Free (but you still have to pay for the petrol)

(replace sithlord with trevor for email)|||"Ryan" <rvanarnam@.aol.com> wrote in message
news:3d47fdc9.0307281012.7f4575cc@.posting.google.c om...
> I have a table in my database on SQL Server which holds a file name
> that refers to a file that is stored on the server. I would like to
> create a trigger to delete this file from the server if the row in the
> table is deleted. I have been trying to use this command in a trigger
> (<filename> is the name and path of the file):
> xp_cmdshell "delete <filename>"
> If some one could please help I would appreciate it very much. I
> would love a code sample if you have one. Thank you so very much.
> From,
> Ryan

You can use xp_cmdshell in a trigger, but this may not be the best approach,
especially if there are very frequent deletes. Calling xp_cmdshell is a
relatively slow operation, and everything will be blocked while the trigger
runs. Also, if there are any problems executing xp_cmdshell, such as a
timeout on a network drive, or a slow file delete, then there may be a
significant impact on your main table. Finally, if someone deletes 20 rows
in a single delete statement, you will have to iterate through them with a
loop or cursor in your trigger, calling xp_cmdshell for each file name, and
that could really cause performance problems.

A more flexible approach might be to use the trigger to put the file name(s)
into a 'pending deletion' table, then have a SQL Server job which checks the
table every few minutes and deletes the file. Using xp_cmdshell for that
would be fine, because you don't care (as much) if it times out. Or even
have an external script read the table and do the deletion from the
operating system side. By doing it that way, any unexpected behaviour won't
impact your main processing.

Simonsql

Delete a backup

I orginally set up a backup to a file. I later was
instructed in setting up a backup device. I now have two
backups running for the same database. How do I delete
the original backup. It does not show on the "Backup"
list.You want to delete the original backup file, right?
If that's teh case, find the file in the file system (e.g.
using Windows Explorer) and delete it.
Linchi
>--Original Message--
>I orginally set up a backup to a file. I later was
>instructed in setting up a backup device. I now have two
>backups running for the same database. How do I delete
>the original backup. It does not show on the "Backup"
>list.
>.
>|||Or perhaps OP has two jobs running? If so, check EM, Management, Jobs.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:034d01c3c41c$fe5e7f00$a401280a@.phx.gbl...
> You want to delete the original backup file, right?
> If that's teh case, find the file in the file system (e.g.
> using Windows Explorer) and delete it.
> Linchi
> >--Original Message--
> >I orginally set up a backup to a file. I later was
> >instructed in setting up a backup device. I now have two
> >backups running for the same database. How do I delete
> >the original backup. It does not show on the "Backup"
> >list.
> >.
> >

Delete 2nd transaction log file

Hello
We have a SQL Server 2000 database with 2 transaction log files.
The 2nd file was created when we were running out of disk space and the person creating it was not familiar with the dbcc shrink command.

I now want to get rid of the 2nd log file. I ran the following steps with no success:

DBCC SHRINKFILE ('Log_file', EMPTYFILE )
--Message: Cannot shrink log file 3 (log_file) because all logical log files are in use.

ALTER DATABASE db1 REMOVE FILE 'Log_file'
--Message: The file 'Log_file' cannot be removed because it is not empty.

There are no users or open transactions in the database. I have also tried sp_detach_db and sp_attach_single_file_db but that does not work either as the database attaches both the transaction logs back.

Please advise.

Thanks

NinaTry running "checkpoint" after the DBCC SHRINKFILE. I think this was a requirement in the SQL 7.0 days, but I am not sure about SQL 2000.|||Hey
thanks for the help but it worked fine after i truncated the log file.

Delete + Log file

A) When i run a delete against a table that has 50 million records based
upon a where clause , what entries does the T log file hold ? Does it log 50
million delete statements along with 50 million inserts just incase it needs
to rollback.
B) Also if there is a clustered index on the coulimn thats part of the where
clause, what does the Tlog file hold ?
C) If there was a clustered index but not part of the column in the where
clause, what does the Tlog contain ?
D) During the time the delete is occuring, does it go ahead and start
deleting entries from the data pages in the data files or does it first log
entries in the Log file and then deletes ?
E) Finally if i did a backup log during the time the delete is occuring (
Say i noticed that the table with (nolock) option was decrementing ) and
then restored the logs on another database , will part of the deletes be
reflected on the other database if i specify the (nolock) option since the
delete did not complete
I am just trying to understand what entries the TLog contains.. I would
appreciate if you could provide answers to all the 5 parts. I am using SQL
2000
Thank youIf you want to see what is in the tran log you can run
this before you do a log backup:
select * from ::fn_dblog(null,null)
I will attempt to answer your questions
a) It logs 50 million delete statements (if you need to
delete everything out of a table and don't need the
ability to rollback, you might use truncate table as it is
faster because it is minimally logged)
b)I don't believe it matters if there is a clustered index
or not in regards to the T-log
c)I don't believe it matters if there is a clustered index
or not in regards to the T-log
d) Everything hits the log first, it hits the data when
the T-log does a checkpoint
e)That one I'm not sure on, would have to test that one or
maybe someone has tried this before that reads these
newsgroups, I would guess that if you tried backing up the
log during this transaction, it would wait until the tran
was finished, selecting from it with a nolog would only
show you, not the T-log, the data while in the transaction.
Again, not sure on that one.
HTH
Ray Higdon MCSE, MCDBA, CCNA
>--Original Message--
>A) When i run a delete against a table that has 50
million records based
>upon a where clause , what entries does the T log file
hold ? Does it log 50
>million delete statements along with 50 million inserts
just incase it needs
>to rollback.
>
>B) Also if there is a clustered index on the coulimn
thats part of the where
>clause, what does the Tlog file hold ?
>C) If there was a clustered index but not part of the
column in the where
>clause, what does the Tlog contain ?
>D) During the time the delete is occuring, does it go
ahead and start
>deleting entries from the data pages in the data files or
does it first log
>entries in the Log file and then deletes ?
>E) Finally if i did a backup log during the time the
delete is occuring (
>Say i noticed that the table with (nolock) option was
decrementing ) and
>then restored the logs on another database , will part of
the deletes be
>reflected on the other database if i specify the (nolock)
option since the
>delete did not complete
>I am just trying to understand what entries the TLog
contains.. I would
>appreciate if you could provide answers to all the 5
parts. I am using SQL
>2000
>Thank you
>
>.
>|||--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OuJAVQ#VDHA.392@.TK2MSFTNGP11.phx.gbl...
> A) When i run a delete against a table that has 50 million records based
> upon a where clause , what entries does the T log file hold ? Does it log
50
> million delete statements along with 50 million inserts just incase it
needs
> to rollback.
>
The log holds a copy of the record which was deleted...
> B) Also if there is a clustered index on the coulimn thats part of the
where
> clause, what does the Tlog file hold ?
NO change, the log has the copy of the deleted record.
> C) If there was a clustered index but not part of the column in the where
> clause, what does the Tlog contain ?
no change.
> D) During the time the delete is occuring, does it go ahead and start
> deleting entries from the data pages in the data files or does it first
log
> entries in the Log file and then deletes ?
>
Logging occurs first.
> E) Finally if i did a backup log during the time the delete is occuring (
> Say i noticed that the table with (nolock) option was decrementing ) and
> then restored the logs on another database , will part of the deletes be
> reflected on the other database if i specify the (nolock) option since the
> delete did not complete
>
After the restore, and recovery has run either all of the records will be
present or none of them...Each statement is a transaction.
> I am just trying to understand what entries the TLog contains.. I would
> appreciate if you could provide answers to all the 5 parts. I am using SQL
> 2000
> Thank you
>
>|||Thanks so to answer 5, where you said
"After the restore, and recovery has run either all of the records will be
present or none of them...Each statement is a transaction."
What if i restored log with standby mode so users can read from this standby
database, will i see some deletes in effect with (nolock)
Thanks once again to you all
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:eTPtkTCWDHA.2008@.TK2MSFTNGP11.phx.gbl...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it
community
> of SQL Server professionals.
> www.sqlpass.org
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OuJAVQ#VDHA.392@.TK2MSFTNGP11.phx.gbl...
> > A) When i run a delete against a table that has 50 million records based
> > upon a where clause , what entries does the T log file hold ? Does it
log
> 50
> > million delete statements along with 50 million inserts just incase it
> needs
> > to rollback.
> >
> The log holds a copy of the record which was deleted...
> >
> > B) Also if there is a clustered index on the coulimn thats part of the
> where
> > clause, what does the Tlog file hold ?
> NO change, the log has the copy of the deleted record.
>
> >
> > C) If there was a clustered index but not part of the column in the
where
> > clause, what does the Tlog contain ?
> no change.
> >
> > D) During the time the delete is occuring, does it go ahead and start
> > deleting entries from the data pages in the data files or does it first
> log
> > entries in the Log file and then deletes ?
> >
> Logging occurs first.
> > E) Finally if i did a backup log during the time the delete is occuring
(
> > Say i noticed that the table with (nolock) option was decrementing ) and
> > then restored the logs on another database , will part of the deletes be
> > reflected on the other database if i specify the (nolock) option since
the
> > delete did not complete
> >
> After the restore, and recovery has run either all of the records will be
> present or none of them...Each statement is a transaction.
> > I am just trying to understand what entries the TLog contains.. I would
> > appreciate if you could provide answers to all the 5 parts. I am using
SQL
> > 2000
> >
> > Thank you
> >
> >
> >
>sql

Delay Insert

I am parsing a file where along the flow I use a conditional split. One path of the split is the primary table (with IDENTITY) values. The rest of the paths have a FOREIGN KEY to the primary table.

It seems that SSIS is trying to insert the rows at the same time (which makes sense) but this is causing a problem with the secondary tables and their FK constraint since the primary table is not yet written.

Is there a way to delay the secondary tables until the primary table is done?

(I guess one way is to run through the file twice... once for the primary table and another for the rest but that seems wasteful to me...)

Thanks.

There is no way to delay paths inside a data flow, or set any precedence. One option I like is to stage the "secondary" data in a raw file. This is very efficient compared to most source and destination combinations. In your current Data Flow write the secondary data to a raw file then add another Data Flow task, with a raw file source and the your final destination.

Defragmenting Local Disk

Is there any performance gain from defragmenting the SQL
SERVER data and log file disk subsystem (or OS). I have
run the "Analyze" part of the windows disk defragmenter
and several of the data and log files have several
fragments within the file. Some of them have 20
fragments. Is there another tool that is preferred? Any
advice or past experienced is much appreciated.
Thanks,
Andy20 fragments is not much compared to the fragmentation you probably have int
ernally in the files. But sure,
stop the SQL Server service and do a defrag if you wish - no harm.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Andy" <anonymous@.discussions.microsoft.com> wrote in message news:12e6801c41674$4b7a4c20$a
601280a@.phx.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy|||OS level defrag is a good thing. SQL Server might report 0% fragmentation
for internal and internal fragmentation within it's data structures. But
that's just for SQL.
SQL Server can't know anything about where the bits are stored at the OS.
Consider a situation where SQL reports no fragmentation, and you're doing a
table scan. Imagine that the file with the big table is scattered all over
disk. Yes, there's be a benefit to OS frag in this case.
However, one thing to consider... defragging at the OS level doesn't always
have as big an impact as some people might expect. SQL Server pre-allocates
space when you create a file. If you create a 100M file on a disk with no
fragmentation... and put 1M of data on it. The file still uses 100M at the
OS and it would be contiguous if the file was created when the disk was not
fragmented... if the disk later becomes fragmented... that 100M file will
still be contiguous.
OS level frag tends to help the most with db files that have grown a number
of times since they were created... or if you're creating DB files on a
disks that are already fragmented...
also... don't forget that you'll need to shut down SQL Server before doing
the defrag. Open files aren't defragged...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:12e6801c41674$4b7a4c20$a601280a@.phx
.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy|||Sure, just like any other file access, defragmenting your database files
will improve performance if they're badly fragmented. I'm not sure if 20
fragments is that bad, however. It really depends on the size of the files
(if the files are very small, 20 fragments may be bad; if they're quite
large, it's probably not affecting contiguous data access too much). Either
way, if you don't mind the downtime (you'll have to shut down the server in
order to defrag the files), it certainly won't hurt. As for defrag tools, I
don't like Windows' built in one. I personally have gotten very good
results from O&O Software Defrag.
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:12e6801c41674$4b7a4c20$a601280a@.phx
.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy