June 14, 2016 at 6:58 pm
Is it possible? I'm in full recovery mode and didn't realize I had to back up the log in addition to the database, so the log file kept on growing for the past year. It's huge now...
Is there a way to delete everything over a month old from the log file, before doing a backup of the log? We don't need the whole past year in there. But I don't want to truncate the whole log either by switching to simple recovery, if I don't have to.
I've been backing up the database weekly. I don't understand why Sql Server doesn't automatically back up the log for me when I back up the database. I mean, is there any reason someone would want to purposely do what I've been accidentally doing? 🙂
June 15, 2016 at 8:51 am
If you want to be able to restore to a point in time then you need to backup the entire log. If doing this will cause an issue - lack of space - and you can live with only being able to restore to the last full backup then switch to simple recovery mode. The file size of the log won't change so you will need to shrink it to a more manageable size.
You can then switch back to full recovery and take log backups as well as full.
June 15, 2016 at 9:02 am
There really isn't a way to go back in time and truncate everything older than 30 days from the tran log.
And, yes, for companies using passive-audit techniques, there are times when keeping the tran log intact for long periods of time is desirable.
If you need the ability to do point-in-time restores, keep the log in full mode and start doing backups of it regularly. If you don't need that, put it in simple and keep it that way.
To recover from this, you might need to break the log by truncating now (simple recovery) and then turning full recovery back on and starting to run log backups. You won't be able to do point-in-time restores to before the reset to full, but that might be the price you have to pay for saving all that backup space.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 15, 2016 at 12:07 pm
Thanks y'all... so now I have backed up the transaction log, and it shows in the disk space report that the trans log has 99% space free, but when I try to shrink the trans log using SSMS, it doesn't work. It closes the window without giving any error message, but the file size is still the same.
:crazy:
In fact, it looks like there have been auto-shrink events every hour during the day and every half hour at night, ever since at least June 1st, but the file size has only been growing! The shrinks are definitely not working.
When I run this, I get this :
USE [Lobby_queue]
GO
DBCC SHRINKFILE (N'Lobby_queue_log' , 0, TRUNCATEONLY)
GO
DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages
---------------------------------------------------------------------------------------------------------------------
824058891212840588912128
So according to that, it still thinks all the space in the log is still being used??
But the Trans Log Space Usage pie chart shows 99% unused!! What gives??
June 15, 2016 at 12:21 pm
What does running this query return
select log_reuse_wait_desc from sys.databases where name = 'YourDbName
June 15, 2016 at 12:30 pm
The shrink will attempt to reclaim space starting at the end of the file, but will not shrink past the part of the log that is active.
Most likely the active portion of the log is at the end of the file, so essentially no shrinking can be done.
Incidentally, in that case, you won't necessarily see anything for the log_reuse_wait. That concerns whether or not portions of the log can be reused.
Most likely in this case, the log can be reused just fine.
What's preventing the shrink is a different thing altogether.
You'll just need to wait for the active portion of the log to wrap around to the beginning of the file (you can also sometimes give it a little help by running some transactions of your own, so long as you're not impacting performance), take a new log backup, and try again.
You can use DBCC LOGINFO to check if the VLF(s) at the end of the file are indeed active.
Cheers!
June 15, 2016 at 12:31 pm
It shows :
select log_reuse_wait_desc from sys.databases where name = 'Lobby_queue'
LOG_BACKUP
So it still thinks the trans log backup is still happening? I left that backup running last night and it showed backup successful this morning and I clicked OK on it. How can I tell SQL Server it's done now?
June 15, 2016 at 12:55 pm
ztoddw (6/15/2016)
So it still thinks the trans log backup is still happening? I left that backup running last night and it showed backup successful this morning and I clicked OK on it. How can I tell SQL Server it's done now?
No it doesn't think the log backup is still running.
The log_reuse_wait_desc is the reason why, last time SQL attempted to mark the log reusable, it couldn't. It's perfectly normal to see that when a DB is in full recovery, because log backups are needed before the log can be marked reusable.
The reason it wouldn't shrink right after you took the log backup is that the active portion of the log is (or was) at the end of the log file, and hence there was no free space after that. You can try again now, it'll probably shrink a lot.
Oh, and turn autoshrink off. It's a really, really bad thing to have on. The reason it never reduced the size of the log before was because the log was always full, and all shrink does is remove unused space. No unused space, no shrink.
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 15, 2016 at 2:48 pm
GilaMonster (6/15/2016)
ztoddw (6/15/2016)
So it still thinks the trans log backup is still happening? I left that backup running last night and it showed backup successful this morning and I clicked OK on it. How can I tell SQL Server it's done now?No it doesn't think the log backup is still running.
The log_reuse_wait_desc is the reason why, last time SQL attempted to mark the log reusable, it couldn't. It's perfectly normal to see that when a DB is in full recovery, because log backups are needed before the log can be marked reusable.
The reason it wouldn't shrink right after you took the log backup is that the active portion of the log is (or was) at the end of the log file, and hence there was no free space after that. You can try again now, it'll probably shrink a lot.
Oh, and turn autoshrink off. It's a really, really bad thing to have on. The reason it never reduced the size of the log before was because the log was always full, and all shrink does is remove unused space. No unused space, no shrink.
But there is a ton of unused space now, and it still won't shrink! (I just barely tried it again.) Even when I check the box for "Reorganize pages before releasing unused space".
Are you saying I have to back up the trans log again?? Is Sql Server really that dumb??
Does this not work very well because there are constantly new transactions being made? Do I have to wait until after hours and then put the db in single user mode? or take it offline?
June 15, 2016 at 3:26 pm
ztoddw (6/15/2016)
But there is a ton of unused space now, and it still won't shrink! (I just barely tried it again.) Even when I check the box for "Reorganize pages before releasing unused space".
Reorganise pages only applies to the data file. It has no effect when backing up a log.
The active portion of the log is probably still at the end of the file. You need to wait until normal activity (and regular log backups) have resulted in the active portion of the log being at the start of the file. After than you can shrink the file easily
Are you saying I have to back up the trans log again??
You need to be backing it up on a regular basis, how regular is determined by your recovery requirements. If you need to restore with less than an hours data loss then you need to be backing up the log every hour. The frequency of log backups is determined by your data loss limitations. After all, that's the reason to have a DB in full recovery model, to allow for restores to exact points in time.
Does this not work very well because there are constantly new transactions being made? Do I have to wait until after hours and then put the db in single user mode? or take it offline?
Constant transactions is pretty much normal, that's the kind of thing that usually happens with SQL Server DBs. No, you definitely don't need to put the DB in single user mode or take it offline or anything such.
I would recommend that you read the Stairway to Log management on this site, as you seem to be new to SQL Server admin.
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 15, 2016 at 4:08 pm
GilaMoster, I'm not talking about regular backups. All I'm trying to do right now is get rid of all the unused space in the trans log.
When I do DBCC LOGINFO, it shows 989 rows-- the first 66 rows have status=2, then all remaining rows have status=0 except for two rows, row 415 and the last row 989.
It seems SQL Server has no way to get rid of all the unused space if it's all in the middle, with one little used VLF at the end. If that's true, that's a tremendous waste of space if I have to keep all that in a trn backup.
I already did one 332 GB trn backup. That's what made all those statuses set to zero. Now do I really have to do another 332 GB backup to be able to not break our point in time recovery? Is there really no way to remove all the unused space first, or some way to exclude all that unused space from the backup?
June 15, 2016 at 4:29 pm
I'm reading about Tail-Log Backups now-- looks like that's probably what would help out here...
June 15, 2016 at 5:01 pm
No the next transaction log backup won't be 332gb - it will be a lot smaller. Take another log backup and then try shrinking the log file again. If you want to keep the database in full recovery mode then as Gilamonster mentioned you should be taking regular log backups. Use this as an opportunity to set these up.
Take a normal log backup a tail log backup can be used prior to restoring a database to recover transactions prior to the previous log backup- this doesn't apply here.
June 15, 2016 at 5:20 pm
Ah, I see. I thought a trans log backup just basically copies your entire trans log file. But I guess it actually loops through all the VLFs inside it and discards all that are unused rather than putting them in the backup. That's the info I was missing. 🙂
Thanks again y'all 😀
June 16, 2016 at 2:05 am
ztoddw (6/15/2016)
I'm reading about Tail-Log Backups now-- looks like that's probably what would help out here...
Nope, those are for when you're moving a database without data loss or a database has failed and you're trying to recover the last few transactions.
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 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply