xp_delete

xp_delete_file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Heer - 23 Jan 2008 01:44 GMT

As per my manager's directive, I am attempting to forcibly shrink the log
files of multiple SQL Server 2005 databases with the following process:
USE [master]
GO
sp_detach_db 'myDB'
GO
DECLARE @dteNow [datetime]
SET @dteNow = GETDATE()
EXECUTE [master].[dbo].[xp_delete_file] 0,'E:\SQLData\myDB','LDF',@dteNow,0
GO
sp_attach_db 'NDE','E:\SQLData\myDB\myDB.mdf'
GO

The detach works fine, as does the attach whether the .ldf file exists or
not. However, xp_delete_file does not delete the .ldf and does not error.
Errors I can deal with. Non-functioning and non-erroring leaves me at a
loss...

Any suggestions...?

Signature

Cheers!

David W. T. Heer

Reply to this Message

Aaron Bertrand [SQL Server] - 23 Jan 2008 02:07 GMT

Yikes, you are expecting to detach the database file, then delete the log
file, and then re-attach the data file only, with no consequences? I hope
you have current backups when doing this!!!

Your manager needs to read a book. Or a blog. Or something.

Anyway, have you thought about looking at the return value from
xp_delete_file? I don't think I would ever try to do this kind of thing
from within T-SQL (even if I thought it were a good idea) but you should be
able to say:

DECLARE @hr INT;
EXEC @hr = master..xp_delete_file ...
IF @hr <> 0
BEGIN
PRINT 'Error';
PRINT @hr;
END

> As per my manager's directive, I am attempting to forcibly shrink the log
> files of multiple SQL Server 2005 databases with the following process:
[quoted text clipped - 16 lines]
>
> Any suggestions...?

Reply to this Message

David Heer - 23 Jan 2008 15:21 GMT

Thanks for answering, Aaron! I ended up sending a DEL through xp_cmdshell,
rather than beating my head up against xp_delete_file not wanting to return
an error when it clearly didn't do that which it was told to do.

I found a comment, after many hours of Googling, that indicated that
xp_delete_file will only delete .trn and .bak files, but, even if that's the
case, it doesn't explain the lack of an error...

Regardless, thanks, again!

Signature

Cheers!

David W. T. Heer

Reply to this Message

Linchi Shea - 23 Jan 2008 04:41 GMT

For shrinking the log file, you may want to read up on DBCC SHRINKFILE in the
Books Online.

Linchi

> As per my manager's directive, I am attempting to forcibly shrink the log
> files of multiple SQL Server 2005 databases with the following process:
[quoted text clipped - 15 lines]
>
> Any suggestions...?

Reply to this Message

David Heer - 23 Jan 2008 15:22 GMT

Have done, thanks, but my manager has indicated a lack of confidence in DBCC
SHRINKFILE due to past experience.

I appreciate the response!

Signature

Cheers!

David W. T. Heer

> For shrinking the log file, you may want to read up on DBCC SHRINKFILE in the
> Books Online.
>
> Linchi

Reply to this Message

Greg D. Moore (Strider) - 24 Jan 2008 13:12 GMT

> Have done, thanks, but my manager has indicated a lack of confidence in
> DBCC
> SHRINKFILE due to past experience.

So let me get this straight, he wants to ignore the MS recommended and
SUPPORTED method for shrinking logfiles and use an completely non-supported
method that is in fact recommended against?

Like Aaron said, I really hope oyu have good backups. This will work 99 out
of 100 times, and the 100th time will kill you.

And of course you completely invalidate your back-up chain, limiting your
recovery options.

And drop a copy of http://www.karaszi.com/SQLServer/info_dont_shrink.asp on
his desk.

> I appreciate the response!
>
[quoted text clipped - 3 lines]
>>
>> Linchi

Signature

Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Reply to this Message

Harry Hay - 31 Jan 2008 20:33 GMT

Sounds to me like it's time you questioned your manager's technical judgment.

Many years a production DBA on SQL Server, and I certainly wouldn't be
taking such seemingly dubious advice...

In my experience DBCC SHRINKFILE is a solid and reliable utility.

Harry.

> > Have done, thanks, but my manager has indicated a lack of confidence in
> > DBCC
[quoted text clipped - 20 lines]
> >>
> >> Linchi

Reply to this Message

Andrew J. Kelly - 31 Jan 2008 23:26 GMT

I agree as well. If he had a bad experience it was probably due to lack of
proper execution or hardware issues. DBCC SHRINKFILE is a safe and supported
operation and your attempts to hack a different solution are what will get
you into real trouble.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Sounds to me like it's time you questioned your manager's technical
> judgment.
[quoted text clipped - 34 lines]
>> >>
>> >> Linchi

Reply to this Message