December 12, 2017 at 8:40 am
Hi I have a database with 140GB log file and 6 GB data file If I do a transaction log backup will it shrink my transaction log files?
December 12, 2017 at 8:43 am
We do a full backup every night for this database, no we do not backup the transaction logs for this database.
December 12, 2017 at 8:47 am
iimohiuddin - Tuesday, December 12, 2017 8:43 AMWe do a full backup every night for this database, no we do not backup the transaction logs for this database.
And your recovery model is..? 🙂 (Full..?)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 12, 2017 at 9:10 am
A backup of the transaction log does not shrink the log. The presumption is that the log got the size it is because it needed to be that big, and will likely need to be that big again in the future. So you would want to look into *why* the log got this big.
Was it a one-off process, or is it a process that runs regularly.
If it was a one-off, than you could comparatively safely shrink the log down (do it during off-hours, though.) If it's a regular process, then it's just going to grow again (which also will impact performance during the grow operation, among other things,) in which case it would be wiser to leave it as-is.
More importantly, is what Thom asked, which is what is your recovery model set to for this database? If you're in Simple, you can't take a log backup and all of the above is (mostly) moot.
December 12, 2017 at 9:13 am
jasona.work - Tuesday, December 12, 2017 9:10 AMMore importantly, is what Thom asked, which is what is your recovery model set to for this database? If you're in Simple, you can't take a log backup and all of the above is (mostly) moot.
I suspect that the OP's database is in FULL and only creating Database back ups, and no transaction log back ups. Thus, the log has been growing since the DB was created. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 12, 2017 at 9:48 am
December 12, 2017 at 9:53 am
iimohiuddin - Tuesday, December 12, 2017 9:48 AMThanks
For..? Could you please post what recovery model you're using? We can't help you unless you give us all the details; otherwise it's pure guesswork.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 12, 2017 at 10:18 am
The recovery model is Full
December 12, 2017 at 10:56 am
iimohiuddin - Tuesday, December 12, 2017 10:18 AMThe recovery model is Full
From your previous answer, you've not been backing up the transaction log, so that's the first thing you need to fix.
Find out what your Recover Point Objective is (how much data the business is willing to lose, none is not an option,) then schedule transaction log backups for at least that frequently.
IE, up to 15 minutes of data can be lost, run log backups every 15 minutes.
First, that will allow SQL to start flagging the portions of the log that have been backed up, as backed up and re-usable. Once that's happening, then you could start monitoring the log file usage, and see how much of the log the application uses over a couple weeks and either shrink the log down to just above that size, or just leave it where it's at now.
Or, not the preferred option, make sure your log growth settings are "reasonable" (IE not a percentage, not something silly-small like 1MB,) and shrink the log down, then let it grow to however big it needs to be.
All of the above while continuing to take log backups.
December 12, 2017 at 11:04 am
iimohiuddin - Tuesday, December 12, 2017 10:18 AMThe recovery model is Full
OK, if the recovery model is Full then you need to do regular Transaction backups. When I say regular, I'm not talking about daily or weekly, I mean hourly or less; what ever your business decides is a permissible loss of data (it could be as frequent as every 30 seconds! (much more frequent than that, and you might want to start looking at high availability options)).
If your company has decided that up to 24 hours of data loss is permissible, then doing a daily full backup is fine, however, the database has no need to be in Full Recovery mode. Firstly, just to be on the safe side, I'd perform a Transaction Log Back up. Then, change your recovery model to Simple and perform a Database Backup.
After either of those processes, your log file is still (probably) going to be 140GB in size, but it'll have a load (probably about 140GB) of free space. You'll therefore, like Jasona mentioned, need to shrink it; however, just like he also mentioned, do it out-of hours. If you go with using the Full Back up strategy and Transaction Back ups, I'd suggest changing to Simple first (using the instructions above), shrinking the file, and then changing back. It'll be much cleaner (just make sure you switch the transaction back ups off while it's Simple, and then switch them back on when you change back to Full).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 12, 2017 at 11:55 am
And I just realized I'd been reading it as a 140GB *data* file, not log file...
Yeah, it'll need shrinking once you've resolved your lack of log backups.
December 12, 2017 at 12:11 pm
will it automatically shrink or do I have to run a command to shrink it?
December 12, 2017 at 12:27 pm
iimohiuddin - Tuesday, December 12, 2017 12:11 PMwill it automatically shrink or do I have to run a command to shrink it?
You will need to shrink it.
Either via SSMS (right-click DB->Properties->Files and change the initial size) or by DBCC SHRINKFILE()
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply