January 31, 2017 at 7:47 pm
Hi guys
I have an issue related to log file, it keeps growing until today and I can’t shrink it as the database is in mirroring state
I checked the backup history , log backup run every 2 hour which I guess more than enough BUT last full backup is 2 weeks ago which is supposed to run every night . Could it be the cause of growing log file ?
At the end I executed this statement “BACKUP LOG DBname TO DISK = 'NUL'” and I could shrink the file .
The thing is I still need to find out the root cause
Any feedback is highly appreciated
Thanks
January 31, 2017 at 8:51 pm
WhiteLotus - Tuesday, January 31, 2017 7:47 PMHi guysI have an issue related to log file, it keeps growing until today and I can’t shrink it as the database is in mirroring state
I checked the backup history , log backup run every 2 hour which I guess more than enough BUT last full backup is 2 weeks ago which is supposed to run every night . Could it be the cause of growing log file ?
At the end I executed this statement “BACKUP LOG DBname TO DISK = 'NUL'†and I could shrink the file .
The thing is I still need to find out the root causeAny feedback is highly appreciated
Thanks
You would typically check the log_reuse_wait_desc value for the database in sys.databases. But you can't do that now as it's after the fact. Could be mirroring, could be a transaction hung up, the log_resuse_desc_value would tell you.
But you just wiped out the log with backup to NUL and broke the log chain. And before that you said the last full backup was two weeks ago. So I hope you took a full backup ASAP.
Not having the full backups working would not cause the log to grow. Not doing log backups frequent enough can cause the log to grow more than expected.
I really hope you took a full backup.
But you want to figure out why the full backups are not working. And look into backing up the log more frequently. If you see the log continuing to grow as it did now, just execute:
SELECT name, log_reuse_wait_desc
FROM sys.databases
That's how you can start troubleshooting the log growth. And don't backup to NUL. It's not a problem solving tool. It's just a way to shoot yourself in the foot.
Sue
February 1, 2017 at 8:47 am
WhiteLotus - Tuesday, January 31, 2017 7:47 PMAt the end I executed this statement “BACKUP LOG DBname TO DISK = 'NUL'†and I could shrink the file .
Congratulations, you've just broken your log chain. If there's a disaster now you won't be able to restore past that backup to NUL.
Take a full backup immediately.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2017 at 9:46 am
After you've done that, consider increasing the frequency of your log backups to see if it helps.
February 1, 2017 at 9:48 am
Beatrix Kiddo - Wednesday, February 1, 2017 9:46 AMAfter you've done that, consider increasing the frequency of your log backups to see if it helps.
Or at very least diagnose the cause of the full log before doing random things to it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2017 at 7:38 pm
Sue_H - Tuesday, January 31, 2017 8:51 PMWhiteLotus - Tuesday, January 31, 2017 7:47 PMHi guysI have an issue related to log file, it keeps growing until today and I can’t shrink it as the database is in mirroring state
I checked the backup history , log backup run every 2 hour which I guess more than enough BUT last full backup is 2 weeks ago which is supposed to run every night . Could it be the cause of growing log file ?
At the end I executed this statement “BACKUP LOG DBname TO DISK = 'NUL'†and I could shrink the file .
The thing is I still need to find out the root causeAny feedback is highly appreciated
Thanks
You would typically check the log_reuse_wait_desc value for the database in sys.databases. But you can't do that now as it's after the fact. Could be mirroring, could be a transaction hung up, the log_resuse_desc_value would tell you.
But you just wiped out the log with backup to NUL and broke the log chain. And before that you said the last full backup was two weeks ago. So I hope you took a full backup ASAP.
Not having the full backups working would not cause the log to grow. Not doing log backups frequent enough can cause the log to grow more than expected.
I really hope you took a full backup.
But you want to figure out why the full backups are not working. And look into backing up the log more frequently. If you see the log continuing to grow as it did now, just execute:
SELECT name, log_reuse_wait_desc
FROM sys.databases
That's how you can start troubleshooting the log growth. And don't backup to NUL. It's not a problem solving tool. It's just a way to shoot yourself in the foot.Sue
Hi Sue
Thanks for the reply …
After executing the script :
SELECT name, log_reuse_wait_desc
FROM sys.databases
The result is :
Name : DB 1
Log_reuse_wait_dec : LOG_BACKUP
Name : DB2
Log_reuse_wait_dec :REPLICATION
Nah actually we don’t use the replication anymore . So I am thinking to remove DB2 from replication by executing the script :
sp_removedbreplication '<Database name>'
All replication jobs have been disabled
But for DB1 the full backup keeps failing and I really have no idea what happens
Any feedback is much appreciated
February 1, 2017 at 7:43 pm
GilaMonster - Wednesday, February 1, 2017 8:47 AMWhiteLotus - Tuesday, January 31, 2017 7:47 PMAt the end I executed this statement “BACKUP LOG DBname TO DISK = 'NUL'†and I could shrink the file .Congratulations, you've just broken your log chain. If there's a disaster now you won't be able to restore past that backup to NUL.
Take a full backup immediately.
The thing is log backup is still running every 2 hours . So it doesn’t break the log chain
Feb 2 2017 12:00PM
Feb 2 2017 10:00AM
Feb 2 2017 9:50AM -> the time I Executed : BACKUP LOG DBname TO DISK = 'NUL'
Feb 2 2017 8:00AM
If it is really needed Usually I run this statement between last log backup and next Full backup ( at around 5 PM ) but today I need to execute that statement this morning as we are running out of space
cheers
February 1, 2017 at 7:55 pm
GilaMonster - Wednesday, February 1, 2017 8:47 AMWhiteLotus - Tuesday, January 31, 2017 7:47 PMAt the end I executed this statement “BACKUP LOG DBname TO DISK = 'NUL'†and I could shrink the file .Congratulations, you've just broken your log chain. If there's a disaster now you won't be able to restore past that backup to NUL.
Take a full backup immediately.
Thanks for the reply
The thing is log backup is still running every 2 hours . So it doesn’t break the log chain
Feb 2 2017 12:00PM
Feb 2 2017 10:00AM
Feb 2 2017 9:50AM Ã the time I Executed : BACKUP LOG DBname TO DISK = 'NUL'
Feb 2 2017 8:00AM
If it is really needed Usually I run this statement between last log backup and next Full backup ( at around 5 PM ) but today I need to execute that statement this morning as we are running out of space
February 1, 2017 at 8:23 pm
WhiteLotus - Wednesday, February 1, 2017 7:38 PMHi SueThanks for the reply …
After executing the script :
SELECT name, log_reuse_wait_desc
FROM sys.databasesThe result is :
Name : DB 1
Log_reuse_wait_dec : LOG_BACKUPName : DB2
Log_reuse_wait_dec :REPLICATIONNah actually we don’t use the replication anymore . So I am thinking to remove DB2 from replication by executing the script :
sp_removedbreplication '<Database name>'
All replication jobs have been disabled
But for DB1 the full backup keeps failing and I really have no idea what happens
Any feedback is much appreciated
For DB2, yes if you aren't using replication you need to clean it all up. These Microsoft docs go through the steps to clean it out:
How to: Disable Publishing and Distribution
How to cleanup Replication Bits
For DB1 it tells you right there. You probably need to increase the frequency of the log backups.
You always want to use that script to see why the log is growing.
And also - you absolutely 100% broke the log chain. Just because you did log backups afterwards means absolutely nothing. You basically create an empty log backup, have no full backups since so you put the company at risk and they are still at risk, At many companies, if they were to realize what all you have done with these backups, you would be fired.
You really need to work on understanding backup and recovery. And you have got to get the full backups working. Take one manually and do it every day until you can get the job working.
Sue
February 1, 2017 at 9:51 pm
This was removed by the editor as SPAM
February 1, 2017 at 10:51 pm
JasonClark - Wednesday, February 1, 2017 9:51 PMIn mirroring if the server of mirror instance falls behind the server principal of instance then, the amount of active log space will grow. In this case there is need to stop the database mirroring and take a long backup of truncates log by applying that log backup to database of mirror and start mirroring again. Have a look here to go in detail:
http://www.sqlmvp.org/transaction-log-is-too-big-or-growing-unexpectedly/Hope! this might resolve your issue.
Thanks Jason. Very beneficial !
I just checked the MIRROR Server and last log backup was in nov last year. It doesn’t sound normal to you ?
As it is only mirror server so I thought we don’t need a backup in the databases
Is it correct ?
February 1, 2017 at 10:57 pm
Sue_H - Wednesday, February 1, 2017 8:23 PMWhiteLotus - Wednesday, February 1, 2017 7:38 PMHi SueThanks for the reply …
After executing the script :
SELECT name, log_reuse_wait_desc
FROM sys.databasesThe result is :
Name : DB 1
Log_reuse_wait_dec : LOG_BACKUPName : DB2
Log_reuse_wait_dec :REPLICATIONNah actually we don’t use the replication anymore . So I am thinking to remove DB2 from replication by executing the script :
sp_removedbreplication '<Database name>'
All replication jobs have been disabled
But for DB1 the full backup keeps failing and I really have no idea what happens
Any feedback is much appreciated
For DB2, yes if you aren't using replication you need to clean it all up. These Microsoft docs go through the steps to clean it out:
How to: Disable Publishing and Distribution
How to cleanup Replication BitsFor DB1 it tells you right there. You probably need to increase the frequency of the log backups.
You always want to use that script to see why the log is growing.And also - you absolutely 100% broke the log chain. Just because you did log backups afterwards means absolutely nothing. You basically create an empty log backup, have no full backups since so you put the company at risk and they are still at risk, At many companies, if they were to realize what all you have done with these backups, you would be fired.
You really need to work on understanding backup and recovery. And you have got to get the full backups working. Take one manually and do it every day until you can get the job working.Sue
I just took a full backup . Thanks for the valuable advice.
We are using Netbackup as the third party tool ..I am still really curious how to find out the cause of failed FULL BACKUP
February 1, 2017 at 11:35 pm
WhiteLotus - Wednesday, February 1, 2017 7:55 PMGilaMonster - Wednesday, February 1, 2017 8:47 AMWhiteLotus - Tuesday, January 31, 2017 7:47 PMAt the end I executed this statement “BACKUP LOG DBname TO DISK = 'NUL'†and I could shrink the file .Congratulations, you've just broken your log chain. If there's a disaster now you won't be able to restore past that backup to NUL.
Take a full backup immediately.Thanks for the reply
The thing is log backup is still running every 2 hours . So it doesn’t break the log chain
And that's why what you did is so dangerous. It looks like the log chain is still intact.
It's not!
To restore to a point in time, you need ALL log backups taken since the last full. Including the one that you wrote to NUL. Since writing to NUL is essentially a delete, you don't have that log backup file.
If you need to restore, it'll all look fine, until you get to the first log backup taken after that one that was written to NUL, which will throw an error saying that it can't be restored because it's too new. And there will be no way to get past that point in the log chain.
You do not ever need to take backups to NUL, and every time you do so you're risking your company's data and business.
I just checked the MIRROR Server and last log backup was in nov last year. It doesn’t sound normal to you ?
That's fine. A mirror database is not accessible and hence can't be backed up.
Note that the rest of Jason's comment is applicable for a case where the log reuse wait desc is MIRRORING, which you don't have and hence isn't applicable
I am still really curious how to find out the cause of failed FULL BACKUP
What's the error?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply