Big Transcation Log File

gtrapp
gtrapp used Ask the Experts™
on
I still have a very large Transcation Log File, so I ran these commands, and the LDF file is still big.

BACKUP LOG 'database' TO DISK='some_file_path'

and tried this:

BACKUP LOG 'database WITH TRUNCATE_ONLY

What can I do? Nothing happened.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
>BACKUP LOG 'database WITH TRUNCATE_ONLY
no. don't do that.
instead:
DBCC SHRINKFILE (yourlogfile )
http://msdn.microsoft.com/en-us/library/ms189493.aspx

note: if might still not "work" the first time.
AneeshDatabase Consultant
Top Expert 2009
Commented:
truncation does nothing to the physical size of the allocated file on the O/S. A shrink operation actually clears space from a file and a truncate essentially frees up the used space within that file. This is why a shrinking of a log file that is using all of the space won't affect the size and why a truncationof a log file won't reduce the size. A truncation would have to happen first to make room available for the shrink to work. This is not recommended, however.

Author

Commented:
The DBCC SHRINKFILE (yourlogfile ) is not working.

I get this error, but I have the right path to the file. I have used single and double quotes, but same error:

Could not locate file 'X:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\cf_pbtf_log.ldf' for database 'cf_pbtf' in sys.database_files. The file either does not exist, or was dropped.

X: partition is where the file is and space is maxed out.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
with yourlogfile you need to specify the logical file name (or the id), not the physical path.
select * from sys.sysfiles to see the difference.

Author

Commented:
Thanks, that worked.

Author

Commented:
I need to adjust my maintenance plans to include the TRN files, so the Transcation Log file is kept small.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
>I need to adjust my maintenance plans to include the TRN files, so the Transcation Log file is kept small.
yes, exactly :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial