suesh avatar image
suesh asked

can we shrink the Transaction log file when it is 100% full?

My log file is 100% full and there is no space to take log backup, here can shrink log file? and shrinking process any effect on log file? Tell me the answer.. soon... Thanks, Suresh
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
How large is the transaction log? I'd imagine there must be some space you could use temporarily to take a backup somewhere or your servers wouldn't run at all. Where would the backup go if the log weren't full? Do you ever backup the log? If not, maybe you could use simple recovery (are you already)?
3 Likes 3 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Don't leave it at simple. That's nuts. But if you're stuck, you need to get unstuck somehow. Another option would be to add another log file to the database. If you have to plug a thumb drive into a USB port on the server, you can get a little more space that way. Do you have log backups running?
1 Like 1 ·
suesh avatar image suesh commented ·
Hi.. My database is critical db and i don't want to loose even 1 sec data. and My log file occupies 70 gb space and there is no space in all drives on the server to take log backup.& Changing recovery model from full to Simple.... is fine. But after changing recovery model to simple if the db is crash then we have some data loss. but here i don't want loose data.
0 Likes 0 ·

1 Answer

Grant Fritchey avatar image
Grant Fritchey answered
Sounds like you either don't backup the log or don't back it up often enough. However, best bet now would be to try to modify the database to change it to simple recovery. That should reclaim space in the log and you can then shrink it. That might not work with the log full. If you get really stuck, what you may have to do is detach the database, delete the log file, and then reattach the database recreating the log file. If you take that approach, you have to have a clean shut down of the database before you detach it. Don't just turn off the server and start deleting files or you will end up with a corrupted database, guaranteed. Third option is to shut down the database (clean) and use the CREATE DATABASE command to rebuild the database with FOR ATTACH_REBUILD_LOG. Again, the shutdown must be clean. No options there. After you bring it back online, I strongly advise getting appropriate backups in place.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.