March 12, 2018 at 3:00 pm
the log file for a database for a fairly small database is up to 46 Gig - and growing
it is threatening to fill the virtual server, bringing the web application to a complete halt
I tried to shrink - no dice
how do I safely shrink it?
shouldn't it truncate when a full backup is taken?
thanks
a newbie DBA, not by choice
March 12, 2018 at 3:30 pm
No you need to run a log backup then you will be able to shrink it, and if the database is in full or bulk recovery mode you need to be running regular log backups.
March 12, 2018 at 3:32 pm
Seggerman-675349 - Monday, March 12, 2018 3:00 PMthe log file for a database for a fairly small database is up to 46 Gig - and growing
it is threatening to fill the virtual server, bringing the web application to a complete halt
I tried to shrink - no dice
how do I safely shrink it?
shouldn't it truncate when a full backup is taken?thanks
a newbie DBA, not by choice
Usually when your log blows up like that, check the log reuse value in sys.databases - you just execute: select name, log_reuse_wait_desc, recovery_model_desc
from sys.databases
If it's in full recovery model and the the log_reuse_wait_desc is log_backup, backup the log.
Sue
March 13, 2018 at 7:24 am
thanks
how does backing up a log file free up space? doesn't it simply use up more?
please, I'm new at this
concrete steps
sample SQl
do DIFF backups still work with SIMPLE recovery?
March 13, 2018 at 7:37 am
Hi,
a backup won't shrink you logfiles. The internal LSN are marked for reuse.
Try to look the usage of your logfileDBCC sqlperf (logspace)
If there is free enough space, feel free to shrink your logfile.
March 13, 2018 at 7:46 am
If the database is in full recovery (almost certainly) then the log gets retained until a log backup is taken. Not a full backup, not a differential, a log. This is because full recovery model is an indication to SQL that you need point in time recovery on that database (the ability to restore to any point in time in the case of a disaster)
If that is the case, then you need to schedule log backups. They will, after writing the log to a backup file, the backup process will mark the log records within the file as reusable, meaning they can now be overwritten (or removed with a shrink operation)
If you don't need point in time recovery, if you're OK losing everything back to a full backup if the case of a disaster, then change the database to simple recovery model, and once you've done that you'll be able to shrink the down to a sensible size, no log backups necessary (or possible)
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
March 13, 2018 at 7:56 am
Seggerman-675349 - Tuesday, March 13, 2018 7:24 AMthanks
how does backing up a log file free up space? doesn't it simply use up more?
please, I'm new at this
concrete steps
sample SQl
do DIFF backups still work with SIMPLE recovery?
What is did that query show you? What is the recovery model and what was the log reuse wait description? Some of what you do depends on what those tell you. Check those first and let us know what it says.
For the other questions, backing up the log allows space in the file to be reused. That's what they call truncating the log. It's not the same as shrinking the log - so you don't want to confuse the two.
You can take differential backups when you're in simple recovery but they aren't likely to affect the log at all.
Sue
March 13, 2018 at 9:50 am
Sue_H - Tuesday, March 13, 2018 7:56 AMSeggerman-675349 - Tuesday, March 13, 2018 7:24 AMthanks
how does backing up a log file free up space? doesn't it simply use up more?
please, I'm new at this
concrete steps
sample SQl
do DIFF backups still work with SIMPLE recovery?What is did that query show you? What is the recovery model and what was the log reuse wait description? Some of what you do depends on what those tell you. Check those first and let us know what it says.
For the other questions, backing up the log allows space in the file to be reused. That's what they call truncating the log. It's not the same as shrinking the log - so you don't want to confuse the two.
You can take differential backups when you're in simple recovery but they aren't likely to affect the log at all.Sue
thank you people (I'd say guys but it looks like I am getting advice from wise women here)
we are going with adding more disk space, backing up the log file, shrinking it, then changing it to SIMPLE
I haven't been on the server yet - I don't normally work Tuesday
March 13, 2018 at 10:15 am
If you're going to switch to simple recovery, don't bother taking log backups first. Just switch to simple, and then shrink the log to a sensible size.
Just keep in mind that simple recovery means that if something happens to the DB (accidental delete, hardware failure), you may lose data back to the last full backup. Make sure that's acceptable before deciding on the recovery model.
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
March 13, 2018 at 1:16 pm
update - my client worked with a DBA (the real deal) at Liquid Web, the hosting company
- bought 50 G's on a cloud - and backed up the database log to that
shrunk it, then set it to SIMPLE
I need to let my client know that if we ever need to restore we will have to back one day (I set up a DIFF to run every morning)
March 13, 2018 at 6:24 pm
Seggerman-675349 - Tuesday, March 13, 2018 1:16 PMupdate - my client worked with a DBA (the real deal) at Liquid Web, the hosting company
- bought 50 G's on a cloud - and backed up the database log to that
shrunk it, then set it to SIMPLE
I need to let my client know that if we ever need to restore we will have to back one day (I set up a DIFF to run every morning)
Backing up the database log before shrinking it isn't going to buy you anything unless you also backed up the latest full backup and then that's not going to do you any good in a week or so anyway because you'd lose too much data if you did a restore. Also, you should set the database Recovery Model to SIMPLE before you try to shrink the log. If it worked the other way around, you got lucky with the backup.
Now, the real question is, how much work can you actually afford to lose? I ask because doing a Full Backup and then a DIF just every morning means that you could easily lose 24 hours of work if something ever happened.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2018 at 7:42 am
it's a fantasy football web application - the vast majority of the hundreds of active users are free
there are no more than a score of paid members
March 15, 2018 at 8:08 pm
Seggerman-675349 - Wednesday, March 14, 2018 7:42 AMit's a fantasy football web application - the vast majority of the hundreds of active users are free
there are no more than a score of paid members
How many of those score of paid members would have a real personal issue with you if you lost their data? To quote someone else... "You don't need a backup plan... you need a RESTORE plan".
If you have paid members of any kind, I'd suggest the you learn what RPO and RTO means, figure out what the tolerance of pain is (with special emphasis on how much of the pain could be yours if you lose data), and then figure out how to do Point-in-Time restores and the backups to support them correctly..
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2018 at 6:02 am
a user would only lose at most a day of updates
the restore plan would be - take down the web site (there is a HubSpot piece which would stay live)
put up a notice on the HubSpot that the system is partially down
restore from the last full backup
restore from the latest DIFF
bring everything back up
March 16, 2018 at 6:44 am
Seggerman-675349 - Friday, March 16, 2018 6:02 AMa user would only lose at most a day of updates
the restore plan would be - take down the web site (there is a HubSpot piece which would stay live)
put up a notice on the HubSpot that the system is partially down
restore from the last full backup
restore from the latest DIFF
bring everything back up
If you were to implement a better plan, there should be no reason to lose more than 0 to 15 minutes of data. I don't know how big your database is but you said its fairly small. With that understanding, there's no reason why you couldn't do the following to be able to restore to a point in time fairly easily...
1. Either do a nightly full backup or a weekly full backup. Considering the small size, a nightly full backup sounds like the best plan.
2. If you're only doing weekly full backups, then do a DIF backup at least once a day. If you're doing nightly full backups, DIFs may not be necessary.
3. Either way, do transaction log file backups every 15 minutes and learn what the "Tail of the Log" means.
4. Write code to find the latest full, dif, and related trn backups and restored them so that you don't have to figure it out during a crisis. Practice using it.
Your customers and the reputation of the site (and your company) will someday depend on how quick you can get back online and how little data you lose... even for a gaming system like this one. It'll also help your resume for future opportunities even if a crisis doesn't occur. 😉
Also, don't forget to do a DBCC CHECKDB on a regular basis. For a small database, you could do one every night.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply