SQL Server Transaction Log is Full
A really useful tip when a database transaction log file has
completely used up all your physical drive space and you've nowhere to
expand into...
Note: You really need to ask why the transaction log is filling up. The transaction log should only fill up if the transaction log is not being backed up so check transaction log backups first! If the transaction log does fill up then the transaction log should be backed up straight away. The log file should then auto shrink at it's next checkpoint if auto shrink is switched on, else manual shrink is required if disk space needs to be freed. Shrinking manually can cause fragmentation so is not recommended.
When I run into this common issue, I try the following 3 methods in order and they seem to work fine.
1. Fire up a Query on the database in question in SQL Management Studio and enter "BACKUP LOG <Enter your DATABASE NAME HERE> WITH NO_LOG;"
2. Using Enterprise Manager,
select your database, all tasks, shrink database
you will get a new dialog box, click on Files
under database file, select the Log file
click Ok
3. use this TSQL command
DBCC SHRINKFILE ('<Enter your DATABASE NAME HERE>',TRUNCATEONLY)
Hope that helps
Note: You really need to ask why the transaction log is filling up. The transaction log should only fill up if the transaction log is not being backed up so check transaction log backups first! If the transaction log does fill up then the transaction log should be backed up straight away. The log file should then auto shrink at it's next checkpoint if auto shrink is switched on, else manual shrink is required if disk space needs to be freed. Shrinking manually can cause fragmentation so is not recommended.
re: transaction log file is full
When I run into this common issue, I try the following 3 methods in order and they seem to work fine.
1. Fire up a Query on the database in question in SQL Management Studio and enter "BACKUP LOG <Enter your DATABASE NAME HERE> WITH NO_LOG;"
2. Using Enterprise Manager,
select your database, all tasks, shrink database
you will get a new dialog box, click on Files
under database file, select the Log file
click Ok
3. use this TSQL command
DBCC SHRINKFILE ('<Enter your DATABASE NAME HERE>',TRUNCATEONLY)
Hope that helps
Comments