question

sqlqa avatar image
sqlqa asked

Drive space is full due to log file

Hi In My server C Drive has 50 GB and D Drive has 30 GB Recently When I checked Drive space is full due to log File of DB. Error desc: The log file for database 'dbname' is full. Back up the transaction log for the database to free up some log space So when I run shrink file command see below ALTER DATABASE dbname SET RECOVERY SIMPLE; GO DBCC SHRINKFILE (dblogfilename_log, 1); GO ALTER DATABASE dbname SET RECOVERY FULL; It throws Message like backup the log file first So I try to backup the log file again it shows there is no available space at drive BACKUP LOG CSWebUsers TO DISK = 'C:\dbname_Log.bak' OR BACKUP LOG CSWebUsers TO DISK = 'D:\dbname_Log.bak' So How to free up some space in Machine? How to run shrink db or transaction log backup command when there is no space available?
transaction-logspace
1 comment
10 |1200 characters needed characters left characters exceeded

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

Both drives are full? Do you have any other drives available?
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Do you have scheduled log backups? If not, you should either get those in place, or change your database to Simple recovery and leave it there. Simple recovery does mean that you won't be able to recover to a point in time, so I would strongly suggest you check with the business prior to doing that. To recover from this, I would follow some of what you have done. First, change the database to Simple recovery. Then, run a [checkpoint][1]. Then, try running the shrink file command. You might then try running the checkpoint once more and shrinking again. From there, you need to decide if you are switching back to FULL recovery or are staying in SIMPLE. [1]: http://technet.microsoft.com/en-us/library/ms188748.aspx
10 |1200 characters needed characters left characters exceeded

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

DenisT avatar image
DenisT answered
First of all, if this is a production database, you need to figure out why the transaction log is growing because constantly shrinking it is not something you should be doing. Second of all, BACKUP LOG won't release any space. Please read this post -- http://stackoverflow.com/questions/56628/how-do-you-clear-the-sql-server-transaction-log with a detailed explanation. Hope it helps!
10 |1200 characters needed characters left characters exceeded

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

Rahimaahmad avatar image
Rahimaahmad answered

If this is not part of Always on, then I would suggest that you change the recovery model to SIMPLE (after checking with Application team) shrink the log file and then change the recovery model again to FULL. A temporary solution would be to add a secondary log file but that would not help with performance

10 |1200 characters needed characters left characters exceeded

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.