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?
Best Answer
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
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!
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
