November 6, 2017 at 8:02 pm
Good Morning Experts,
We have data and log files in seperate drives. The transaction log of all databases in the instance is full. Shrinking the log file is not releasing any space. There are no active transactions on the instance. Could you please let me know how to fix this.
November 6, 2017 at 11:10 pm
Well, I assume all Database have set to Recovery model 'Full' and you don't take periodic Transaction Log file backup. It is necessary to take transaction log backup so that your transaction log file don't get to big.
Better check Stairway to Transaction Log Management in SQL Server, Level 7: Dealing with Excessive Log Growth
I would recommend to read the complete Stairway to avoid the issues you are getting into.
(Not recommended) To make shrinking work you need to set the Recovery model to 'Simple' and then use Shrink for Log files to reclaim your disk space.
November 6, 2017 at 11:25 pm
Questions:
What are the recovery models for the databases?
What are the file growth settings for the log files?
Is there any space free on the log drive?
Is there an alternative space you could use whilst resolving the issue?
😎
Post the output of this querySELECT
SDB.name
,SDB.recovery_model_desc
,SDB.log_reuse_wait_desc
,SMF.size
,SMF.max_size
,SMF.growth
,SMF.is_percent_growth
FROM sys.databases SDB
INNER JOIN sys.master_files SMF
ON SDB.database_id = SMF.database_id
WHERE SMF.type_desc = N'LOG'
;
November 6, 2017 at 11:56 pm
twin.devil - Monday, November 6, 2017 11:10 PMWell, I assume all Database have set to Recovery model 'Full' and you don't take periodic Transaction Log file backup. It is necessary to take transaction log backup so that your transaction log file don't get to big.Better check Stairway to Transaction Log Management in SQL Server, Level 7: Dealing with Excessive Log Growth
I would recommend to read the complete Stairway to avoid the issues you are getting into.
(Not recommended) To make shrinking work you need to set the Recovery model to 'Simple' and then use Shrink for Log files to reclaim your disk space.
There is no space to take log backup also.
November 7, 2017 at 12:44 am
Eirikur Eiriksson - Monday, November 6, 2017 11:25 PMQuestions:
What are the recovery models for the databases?
What are the file growth settings for the log files?
Is there any space free on the log drive?
Is there an alternative space you could use whilst resolving the issue?
😎Post the output of this query
SELECT
SDB.name
,SDB.recovery_model_desc
,SDB.log_reuse_wait_desc
,SMF.size
,SMF.max_size
,SMF.growth
,SMF.is_percent_growth
FROM sys.databases SDB
INNER JOIN sys.master_files SMF
ON SDB.database_id = SMF.database_id
WHERE SMF.type_desc = N'LOG'
;
can you also share the answers of Eirikur Eiriksson's queries as well.
November 7, 2017 at 2:22 am
coolchaitu - Monday, November 6, 2017 11:56 PMtwin.devil - Monday, November 6, 2017 11:10 PMWell, I assume all Database have set to Recovery model 'Full' and you don't take periodic Transaction Log file backup. It is necessary to take transaction log backup so that your transaction log file don't get to big.Better check Stairway to Transaction Log Management in SQL Server, Level 7: Dealing with Excessive Log Growth
I would recommend to read the complete Stairway to avoid the issues you are getting into.
(Not recommended) To make shrinking work you need to set the Recovery model to 'Simple' and then use Shrink for Log files to reclaim your disk space.
There is no space to take log backup also.
You have to either have a place to perform log backups, or you have to set the recovery to Simple. You have no options here. You can't leave the recovery on full and then not do the backups. If you intend to have point in time recovery for these databases, the standard for the vast majority of businesses, then you should solve the space problem now.
You also must talk to your business and understand their Recovery Point Objective (RPO) and the Recovery Time Objective (RTO). The RPO defines how much data they're willing to lose. The first answer is always zero. However, the cost of that is extremely high, involving multiple servers, etc (and if you can't get an extra disk, I assume this is off the table). So you figure out that they can live with 10 minutes or 1/2 an hour. That then becomes the time that you need to schedule for your log backups. Then, you have to look at the RTO, that's the time it takes you to do a restore, during which they'll be offline. The first answer here is always zero. Again, expensive. Instead you can define how long a restore takes and you can then determine if you can run daily, or weekly full backups, differentials, etc.
However, immediately, you need a disk.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply