June 27, 2011 at 3:41 am
Hi All,
I wonder is anyone would be able to help me out here please, I have the following message "The log file for database 'Databasename' is full. Back up the transaction log for the database to free up some log space. I was wondering where do I go to try and free-up some space because I went to SQL Data/mssql/log and also BackUp and tried to delete some of the older files but it does not seem to be freeing up any space. Would you please let me know what I'm doing wrong.
Thank you
June 27, 2011 at 3:49 am
you need to backup the t-log.
you should reallly be backing up the t-log regulary to aviod this happening
June 27, 2011 at 9:06 am
Go look up my two articles on transaction log management and read them please (no, I don't have the links handy)
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
June 27, 2011 at 9:11 am
GilaMonster (6/27/2011)
Go look up my two articles on transaction log management and read them please (no, I don't have the links handy)
You meant these?
http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
June 27, 2011 at 2:49 pm
No. I meant these:
http://www.sqlservercentral.com/articles/64582/
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Hard to copy-paste links when posting from an iPad (which I was earlier)
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
June 27, 2011 at 2:54 pm
GilaMonster (6/27/2011)
No. I meant these:http://www.sqlservercentral.com/articles/64582/
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Hard to copy-paste links when posting from an iPad (which I was earlier)
Cool, tho all 4 are good reads :w00t:.
June 29, 2011 at 4:01 am
Thank you for your help.
I first Backed up the Transaction Log files using
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
Then I shrunk the Database using the following
DBCC SHRINKDATABASE (DatabaseName, TRUNCATEONLY)
and it works. Thanks everyone for your help.
June 29, 2011 at 4:57 am
#1 You need to take another full backup NOW. Doing what you did cost you the possibility to restore to a point in time.
#2 Stop shrinking the DB from now on. If it needs more room, it'll take it and there's nothing [usefull] you can do about that.
#3 Start scheduling regular REAL log backups (like every 15 mintues 24/7/365).
What you did is put a bandaid on a full blown hemorrhage. It might look like you solved it but you didn't.
June 29, 2011 at 5:10 am
Ninja's_RGR'us (6/29/2011)
#1 You need to take another full backup NOW. Doing what you did cost you the possibility to restore to a point in time.#2 Stop shrinking the DB from now on. If it needs more room, it'll take it and there's nothing [usefull] you can do about that.
#3 Start scheduling regular REAL log backups (like every 15 mintues 24/7/365).
What you did is put a bandaid on a full blown hemorrhage. It might look like you solved it but you didn't.
Thank you for your advice, just one more question, what if the Database was changed to be set to Simple Recovery mode from Full or Bulk?
June 29, 2011 at 5:31 am
That would imply that you don't care about the data changes between the full backups. So in the event of a disaster you lose ALL the data since the last full backup. So if you backup only once a week then be prepared to lose 5 full days of work and make sure your boss signs on that.
June 29, 2011 at 5:33 am
Then you lose any ability to do point in time recovery, if there's any disaster you will be able to restore to the last full/diff backup and no further.
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
June 29, 2011 at 5:34 am
tt-615680 (6/29/2011)
I first Backed up the Transaction Log files usingBACKUP LOG DatabaseName WITH TRUNCATE_ONLY
That did not back up the transaction log. That just discarded log records without backing them up.
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
June 29, 2011 at 6:00 am
Ninja's_RGR'us (6/29/2011)
That would imply that you don't care about the data changes between the full backups. So in the event of a disaster you lose ALL the data since the last full backup. So if you backup only once a week then be prepared to lose 5 full days of work and make sure your boss signs on that.
No, we Backup every night through "Database Maintenance Plans" (which is set to delete any backup files that are older then 2 days). So if there should not be any issues or if there are any disasters that might occur we should fine.
Do you suggest other ways that I should change or do things please.
Thank you so much for all your help.
June 29, 2011 at 6:01 am
Ninja's_RGR'us (6/29/2011)
That would imply that you don't care about the data changes between the full backups. So in the event of a disaster you lose ALL the data since the last full backup. So if you backup only once a week then be prepared to lose 5 full days of work and make sure your boss signs on that.
No, we Backup every night through "Database Maintenance Plans" (which is set to delete any backup files that are older then 2 days). So if there should not be any issues or if there are any disasters that might occur we should fine.
Do you suggest other ways that I should change or do things please.
Thank you so much for all your help.
June 29, 2011 at 6:02 am
Ninja's_RGR'us (6/29/2011)
That would imply that you don't care about the data changes between the full backups. So in the event of a disaster you lose ALL the data since the last full backup. So if you backup only once a week then be prepared to lose 5 full days of work and make sure your boss signs on that.
No, we Backup every night through "Database Maintenance Plans" (which is set to delete any backup files that are older then 2 days). So if there should not be any issues or if there are any disasters that might occur we should fine.
Do you suggest other ways that I should change or do things please.
Thank you so much for all your help.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply