Shrink Transaction Log

[eggy168]

Hi,

Can anyone help??

I have the maintenance plan for backing up the Transaction Log every 3 hrs/day. It works perfect til this morning. For some reasons, it is not working, and I found that it already maxizie the size of the allocation space. I would like to know is there anyway I can shrink the log after the backup or just shrink it every other days? I am using the Enterprise Manager to set up the maintenance plan.

Thank You.

eggy168

 

[nigelrivett]

Right click on the database, alltasks, shrink database.

also see dbcc shrinkfile in bol

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLBill]

You also need to find out why your maintenance plan failed.

Check the SQL Server Error Logs (Enterprise Manager, expand Management, then expand SQL Server Logs.)

Also check out the Windows Event Viewer logs.

_SQLBill

 

[eggy168]

I don't find any errors in the logs, I wonder is it because the Transaction Log file meets the maxizium size? Is there anyway I can shrink only the Transaction Log? How can I do it without doing it in the Maintenance Plan?
Thanks

 

[SQLBill]

Check out these two FAQs from the MS SQL Server Programming forum:

FAQ183-345
FAQ183-1534

-SQLBill

 

[jawan]

Do this

Make full backup of your Database


USE master
EXEC sp_detach_db 'your_DB-name', 'true'

then move yoy log file some other location in hard drive and then

EXEC sp_attach_single_file_db @dbname = 'your _DB_name',
@physname = 'path\???.mdf'

the above command will creat a new log file with minimum size.

Then again execute following command

USE master
EXEC sp_detach_db 'your_DB_name', 'true'

Now you move your data and log file in specific locations (directorie or sub directorie) and then execute following command

EXEC sp_attach_db @dbname = 'your_DB_name',
@filename1 = 'd:\Path????\Data_filename.mdf',
@filename2 = 'd:\Path????\Log_filename.ldf'

Make sure you make full backup before above all commands