Can't shrink transaction log

Hi,
A SQL Server2000 database we have has ended up with a
massive Transaction Log (basically because the maintenance
schedule needs looking at).
Anyway to cut a long story short, I backed up the
transaction log which truncated it, but I can't seem to
shrink the logfile to free up the space.
I tried:
DBCC SHRINKFILE (2, 200, TRUNCATEONLY)
[where 2 is the filenumber of the logfile]
This came back with:
'Cannot shrink log file 2 (Main_Log) because all logical
log files are in use.'
Can anyone tell me how I can shrink this log file please?
Regards,
VinceVince Beaney wrote:

> Can anyone tell me how I can shrink this log file please?
Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||I have had a similar problem. The only thing that worked was running these
commands:
First, backup the log with truncate_only:
BACKUP Log DBNAME with TRUNCATE_ONLY
Then you can run the SHRINKFILE DBCC Command:
DBCC SHRINKFILE (LOGFILE, 10)|||Excellent!
Thankyou, the "INF: Shrinking the Transaction Log in SQL
Server 2000 with DBCC SHRINKFILE" is what I was looking
for.
Thanks,
Vince

>--Original Message--
>Vince Beaney wrote:
>
please?
>Check out below KB articles:
>INF: How to Shrink the SQL Server 7.0 Transaction Log
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;256650
>INF: Shrinking the Transaction Log in SQL Server 2000
with DBCC SHRINKFILE
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;272318
>Log File Grows too big
>http://www.support.microsoft.com/?id=317375
>Log file filling up
>http://www.support.microsoft.com/?id=110139
>Considerations for Autogrow and AutoShrink
>http://www.support.microsoft.com/?id=315512
>http://www.mssqlserver.com/faq/logs-shrinklog.asp
>sincerely,
>--
>Sebastian K. Zaklada
>Skilled Software
>http://www.skilledsoftware.com
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>.
>sql

Can't launch executable from SQL Server Agent Job

I'm trying to launch an .exe from SQL Server Agent Job and basically nothing happens. I created a job, with 1 step, type is Operating System (CmdExec), run as sql agent service account, and in the command box I typed "start notepad" (no quotes). The job owner is set to administrator. I manually start the job, it processes successfully yet notepad does not launch. I've tried with other .exe's and result is the same. Any advice? Thanks!

maybe what you need to type is notepad.exe alone

anyway you can also

place a jobstep containing

exec xp_cmdshell 'notepad.exe'

for more details on xp_cmdshell please consult BOL

or see this link

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

thanks

joey

|||

Thanks joeydj but I've tried xp_cmdshell method and that does not work either. I've also tried launching a batch file (.bat) that calls the .exe and that does not work. The batch call (job) is successfull but again the .exe does not launch. I would like to know if anyone else has had this work for them (launch an .exe directly from a job). I'm starting to think there may be a security setting not allowing executables to be launched. My last option would be an SSIS(DTS) package but since just typing "start xxx.exe" is so straight forward I was trying to avoid the SSIS(DTS).

Note: Gosh I forgot to mention I'm using SQL Server 2005 Standard version.

|||

xp_cmdshell is disabled by default in sql server 2005.

Permissions

Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.

|||thanks joeydj, but I don't think the permissions for xp_cmdshell is an issue. My login has sysadmin permissions.|||

http://msdn2.microsoft.com/en-us/library/ms190693.aspx

hi

please make sure you enable this by runningsurface area configuration

start>programs>micrsoft sql server 2005>configuration tools>

surface area configuration>surface area configuration for FEATURES>

>xp_cmdshell> check this option

regards,

joey

|||Thanks joeydj but xp_cmdshell is/was enabled and a proxy account with sysadmin permissions is setup for it as well. Can you answer the question: Should I be able to launch an .exe directly from SQL Server Agent Job?|||

hi odalys,

Yes. You can. you scenario and encountered the same error

anyway

here's the work around create an operating system(cmdexec) step

directly invoke command.com and pass the command as parameter

so the script should look like this

Command.com /c dir/w>mydoc.txt

regards,

joey

|||I tried the command.com /c notepad and the job just hangs, it never finishes executing.|||

same here.

|||

why do you want to do this? anyway..

is this a maintenance task or something

|||It's part of an I/O process. I create a data file from a db and have to launch the .exe to import the data. I've decided to do it through an SSIS package...But thanks for your suggestions!|||aaarrrrggghh! The package executes from SSIS but when I schedule it as a job it just hangs...it just says executing and it should not take more than a few seconds to run...|||

why not use the windows scheduler instead of a sql job?

To open Scheduled Tasks, click Start, point to Programs, point to Accessories, point to System Tools, and then click Scheduled Tasks.

|||Actually, that's how I have it running now, using Scheduled Tasks :)