Large Transaction Files.

Question

Large Transaction Files.

Duran

SSChampion

Points: 13259
More actions
March 4, 2008 at 10:16 am

#121046

Hello,
I wonder if someone can help. I have large amount of Transaction log files on my drive that got the free space down to 10mb, so I ran a full transaction log back up onto a separate drive in order to clear them down, but after it completed, it made no difference. What have I done wrong? I am running SQL 2000.
Thanks for any help,
Regards,
D.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

I am not quite sure I understand. Are you saying that you ran a transaction log backup and expected it to delete old transaction log files from disk?

If you need to delete old backup files, best practice suggests that you create a maintenance plan that cleans up the drive where the logs reside. You should set the time interval based on your business requirements.

My blog: http://jahaines.blogspot.com

Adam Haines SSC-Insane Points: 23217 More actions · Answer 2

In the itermin, you need to manually remove any transaction logs you do not need.

My blog: http://jahaines.blogspot.com

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

You did nothing wrong. But you need to do one more task to shrink your log file, such as

DBCC SHRINKDATABASE('DBName')

to release the disk space.

SanjayAttray SSChampion Points: 13157 More actions · Answer 4

SQL ORACLE (3/4/2008)
You did nothing wrong. But you need to do one more task to shrink your log file, such as
DBCC SHRINKDATABASE('DBName')
to release the disk space.

I think it should be DBCC SHRINKFILE ('Logfilename') to get the log file minimize to its size..

SQL DBA.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 5

I think it should be DBCC SHRINKFILE ('Logfilename') to get the log file minimize to its size..

Yes it is.

Maybe I am missing something here but this sounds like he needs to delete some stuff to me.

I have large amount of Transaction log files on my drive that got the free space down to 10mb

My blog: http://jahaines.blogspot.com

Duran SSChampion Points: 13259 More actions · Answer 6

Thanks for your replys, I shall have to do the extra steps tomorrow. I did have maintenance plans in place but reorganized them, I have obviously missed something somewhere.

Kind Regards,

D.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 7

Do any of your maintenance plans have a cleanup task? This is what removes outdated backups from the file system.

My blog: http://jahaines.blogspot.com

Duran SSChampion Points: 13259 More actions · Answer 8

Hello, I tried to run the DBCC command, but got the following error.

Server: Msg 8985, Level 16, State 1, Line 1

Could not locate file 'D:\foldername\DB\filename_log.ldf' in sysfiles.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I did SELECT* from sysfiles to get the actual name of the file as SQL see's it, but it still didnt work. Im must be making some really basic mistake. Both the database and and logs are on the same logical drive.

I have the correct path to the folder in there I am sure. And I have also just tried it with the filename on its own, with and without quotes.

Regards,

D.

Duran SSChampion Points: 13259 More actions · Answer 9

Hello,

I worked it out in the end, I was leaving the .ldf on the end. As it turned out I got a much better result shrinking the database instead, but if anyone has any good maintenance tips, I would love to hear them.

Thank you all again,

D.