Why is my log file so massive? 22gb. I am running log backups The 2019 Stack Overflow Developer Survey Results Are InWhy Does the Transaction Log Keep Growing or Run Out of Space?Why Can't I shrink log file in full recovery modeSQL Server 2008 - Log file out of control & cant shrink itSql Server 2008 R2: Simple recovery model with transaction log backupsShrink log file after configuring backups properlyWhy is the transaction log file not truncated though its simple recovery model?Shrinking a physical transaction log file on a mirrored databasebackup log larger than log fileUnexpected Transaction Log Flush events and LOG_BACKUP errorsWhy Won't a Database Backup if the Log File is Full?LDF file size is very huge even after doing Transaction log backupLog file is growing up and is not releasing space

Is this app Icon Browser Safe/Legit?

Should I use my personal e-mail address, or my workplace one, when registering to external websites for work purposes?

How come people say “Would of”?

Why not take a picture of a closer black hole?

Does a dangling wire really electrocute me if I'm standing in water?

Write faster on AT24C32

Are there incongruent pythagorean triangles with the same perimeter and same area?

Right tool to dig six foot holes?

What is the closest word meaning "respect for time / mindful"

Time travel alters history but people keep saying nothing's changed

Why didn't the Event Horizon Telescope team mention Sagittarius A*?

Worn-tile Scrabble

Which Sci-Fi work first showed weapon of galactic-scale mass destruction?

Did Section 31 appear in Star Trek: The Next Generation?

Output the Arecibo Message

FPGA - DIY Programming

Did 3000BC Egyptians use meteoric iron weapons?

What is the meaning of Triage in Cybersec world?

Have you ever entered Singapore using a different passport or name?

During Temple times, who can butcher a kosher animal?

Why was M87 targetted for the Event Horizon Telescope instead of Sagittarius A*?

Why can Shazam fly?

How to support a colleague who finds meetings extremely tiring?

How technical should a Scrum Master be to effectively remove impediments?



Why is my log file so massive? 22gb. I am running log backups



The 2019 Stack Overflow Developer Survey Results Are InWhy Does the Transaction Log Keep Growing or Run Out of Space?Why Can't I shrink log file in full recovery modeSQL Server 2008 - Log file out of control & cant shrink itSql Server 2008 R2: Simple recovery model with transaction log backupsShrink log file after configuring backups properlyWhy is the transaction log file not truncated though its simple recovery model?Shrinking a physical transaction log file on a mirrored databasebackup log larger than log fileUnexpected Transaction Log Flush events and LOG_BACKUP errorsWhy Won't a Database Backup if the Log File is Full?LDF file size is very huge even after doing Transaction log backupLog file is growing up and is not releasing space



.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;








7















I can't seem to figure out the answer. I've seen multiple answers like this:
Why Does the Transaction Log Keep Growing or Run Out of Space?



and everyone talks about running back ups on your log file so it shrinks down. I am doing that, but it doesn't shrink anything! I also don't believe I am running any super long transactions.



Server: SQL Server 2008



Recovery Mode: Full



I have a maintenance plan to store 5 days worth of backups. Task 1 backups up the databases with Backup Type Full, Task 2 backs up Transaction logs. Verify backup integrity is checked on both tasks.



My DB's normal .ldf file is 22gb. When I run the above task, the .bak file is 435mb, but the .trn. file is 22gb, same as the ldf. And after successfully running the .ldf doesn't shrink at all, despite everything I've read telling me it should?



What is going on here and why doesn't the log file ever shrink?



I've also tried running this command as mentioned in another answer:



select name, log_reuse_wait_desc
from sys.databases



And it says LOG_BACKUP for the db with the huge log file.



Based on an answer below I am confusing allocated with used space. These are my stats for:



enter image description here



For reasons I have no clue why, the initial size was set to 22gb...










share|improve this question









New contributor




SventoryMang is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.




















  • Transaction logs do not shrink automatically. Have you tried to shrink it?

    – Tony Hinkle
    2 days ago






  • 1





    I found the Shrink Database task. Added it to my maintenance plan, and re-ran it, still didn't shrink it anyway.

    – SventoryMang
    2 days ago






  • 5





    everyone talks about running back ups on your log file so it shrinks down - no, nobody says that, and log backups will never shrink a file. They say running backups frequently should help prevent it from growing, since the space inside can be reused once it has been backed up. Sometimes the log space gets used faster than your backups run. If this happens often, shrinking just to grow again is pointless, just leave them large. If this is due to a known, abnormal event and you've put something in place to prevent it happening again, that is about the only time I would advocate any shrinking.

    – Aaron Bertrand
    2 days ago






  • 2





    Be aware that everyone helping you is under the assumption that you have a legitimate reason to want full recovery mode. If your recovery point objective is at least 1 day, consider switching to simple recovery mode and omitting transaction log backups entirely (then shrinking your log backups once).

    – Brian
    2 days ago







  • 1





    I do need the full.

    – SventoryMang
    yesterday

















7















I can't seem to figure out the answer. I've seen multiple answers like this:
Why Does the Transaction Log Keep Growing or Run Out of Space?



and everyone talks about running back ups on your log file so it shrinks down. I am doing that, but it doesn't shrink anything! I also don't believe I am running any super long transactions.



Server: SQL Server 2008



Recovery Mode: Full



I have a maintenance plan to store 5 days worth of backups. Task 1 backups up the databases with Backup Type Full, Task 2 backs up Transaction logs. Verify backup integrity is checked on both tasks.



My DB's normal .ldf file is 22gb. When I run the above task, the .bak file is 435mb, but the .trn. file is 22gb, same as the ldf. And after successfully running the .ldf doesn't shrink at all, despite everything I've read telling me it should?



What is going on here and why doesn't the log file ever shrink?



I've also tried running this command as mentioned in another answer:



select name, log_reuse_wait_desc
from sys.databases



And it says LOG_BACKUP for the db with the huge log file.



Based on an answer below I am confusing allocated with used space. These are my stats for:



enter image description here



For reasons I have no clue why, the initial size was set to 22gb...










share|improve this question









New contributor




SventoryMang is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.




















  • Transaction logs do not shrink automatically. Have you tried to shrink it?

    – Tony Hinkle
    2 days ago






  • 1





    I found the Shrink Database task. Added it to my maintenance plan, and re-ran it, still didn't shrink it anyway.

    – SventoryMang
    2 days ago






  • 5





    everyone talks about running back ups on your log file so it shrinks down - no, nobody says that, and log backups will never shrink a file. They say running backups frequently should help prevent it from growing, since the space inside can be reused once it has been backed up. Sometimes the log space gets used faster than your backups run. If this happens often, shrinking just to grow again is pointless, just leave them large. If this is due to a known, abnormal event and you've put something in place to prevent it happening again, that is about the only time I would advocate any shrinking.

    – Aaron Bertrand
    2 days ago






  • 2





    Be aware that everyone helping you is under the assumption that you have a legitimate reason to want full recovery mode. If your recovery point objective is at least 1 day, consider switching to simple recovery mode and omitting transaction log backups entirely (then shrinking your log backups once).

    – Brian
    2 days ago







  • 1





    I do need the full.

    – SventoryMang
    yesterday













7












7








7


1






I can't seem to figure out the answer. I've seen multiple answers like this:
Why Does the Transaction Log Keep Growing or Run Out of Space?



and everyone talks about running back ups on your log file so it shrinks down. I am doing that, but it doesn't shrink anything! I also don't believe I am running any super long transactions.



Server: SQL Server 2008



Recovery Mode: Full



I have a maintenance plan to store 5 days worth of backups. Task 1 backups up the databases with Backup Type Full, Task 2 backs up Transaction logs. Verify backup integrity is checked on both tasks.



My DB's normal .ldf file is 22gb. When I run the above task, the .bak file is 435mb, but the .trn. file is 22gb, same as the ldf. And after successfully running the .ldf doesn't shrink at all, despite everything I've read telling me it should?



What is going on here and why doesn't the log file ever shrink?



I've also tried running this command as mentioned in another answer:



select name, log_reuse_wait_desc
from sys.databases



And it says LOG_BACKUP for the db with the huge log file.



Based on an answer below I am confusing allocated with used space. These are my stats for:



enter image description here



For reasons I have no clue why, the initial size was set to 22gb...










share|improve this question









New contributor




SventoryMang is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.












I can't seem to figure out the answer. I've seen multiple answers like this:
Why Does the Transaction Log Keep Growing or Run Out of Space?



and everyone talks about running back ups on your log file so it shrinks down. I am doing that, but it doesn't shrink anything! I also don't believe I am running any super long transactions.



Server: SQL Server 2008



Recovery Mode: Full



I have a maintenance plan to store 5 days worth of backups. Task 1 backups up the databases with Backup Type Full, Task 2 backs up Transaction logs. Verify backup integrity is checked on both tasks.



My DB's normal .ldf file is 22gb. When I run the above task, the .bak file is 435mb, but the .trn. file is 22gb, same as the ldf. And after successfully running the .ldf doesn't shrink at all, despite everything I've read telling me it should?



What is going on here and why doesn't the log file ever shrink?



I've also tried running this command as mentioned in another answer:



select name, log_reuse_wait_desc
from sys.databases



And it says LOG_BACKUP for the db with the huge log file.



Based on an answer below I am confusing allocated with used space. These are my stats for:



enter image description here



For reasons I have no clue why, the initial size was set to 22gb...







sql-server sql-server-2008 backup transaction-log log






share|improve this question









New contributor




SventoryMang is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




SventoryMang is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited 2 days ago







SventoryMang













New contributor




SventoryMang is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked 2 days ago









SventoryMangSventoryMang

1416




1416




New contributor




SventoryMang is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





SventoryMang is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






SventoryMang is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.












  • Transaction logs do not shrink automatically. Have you tried to shrink it?

    – Tony Hinkle
    2 days ago






  • 1





    I found the Shrink Database task. Added it to my maintenance plan, and re-ran it, still didn't shrink it anyway.

    – SventoryMang
    2 days ago






  • 5





    everyone talks about running back ups on your log file so it shrinks down - no, nobody says that, and log backups will never shrink a file. They say running backups frequently should help prevent it from growing, since the space inside can be reused once it has been backed up. Sometimes the log space gets used faster than your backups run. If this happens often, shrinking just to grow again is pointless, just leave them large. If this is due to a known, abnormal event and you've put something in place to prevent it happening again, that is about the only time I would advocate any shrinking.

    – Aaron Bertrand
    2 days ago






  • 2





    Be aware that everyone helping you is under the assumption that you have a legitimate reason to want full recovery mode. If your recovery point objective is at least 1 day, consider switching to simple recovery mode and omitting transaction log backups entirely (then shrinking your log backups once).

    – Brian
    2 days ago







  • 1





    I do need the full.

    – SventoryMang
    yesterday

















  • Transaction logs do not shrink automatically. Have you tried to shrink it?

    – Tony Hinkle
    2 days ago






  • 1





    I found the Shrink Database task. Added it to my maintenance plan, and re-ran it, still didn't shrink it anyway.

    – SventoryMang
    2 days ago






  • 5





    everyone talks about running back ups on your log file so it shrinks down - no, nobody says that, and log backups will never shrink a file. They say running backups frequently should help prevent it from growing, since the space inside can be reused once it has been backed up. Sometimes the log space gets used faster than your backups run. If this happens often, shrinking just to grow again is pointless, just leave them large. If this is due to a known, abnormal event and you've put something in place to prevent it happening again, that is about the only time I would advocate any shrinking.

    – Aaron Bertrand
    2 days ago






  • 2





    Be aware that everyone helping you is under the assumption that you have a legitimate reason to want full recovery mode. If your recovery point objective is at least 1 day, consider switching to simple recovery mode and omitting transaction log backups entirely (then shrinking your log backups once).

    – Brian
    2 days ago







  • 1





    I do need the full.

    – SventoryMang
    yesterday
















Transaction logs do not shrink automatically. Have you tried to shrink it?

– Tony Hinkle
2 days ago





Transaction logs do not shrink automatically. Have you tried to shrink it?

– Tony Hinkle
2 days ago




1




1





I found the Shrink Database task. Added it to my maintenance plan, and re-ran it, still didn't shrink it anyway.

– SventoryMang
2 days ago





I found the Shrink Database task. Added it to my maintenance plan, and re-ran it, still didn't shrink it anyway.

– SventoryMang
2 days ago




5




5





everyone talks about running back ups on your log file so it shrinks down - no, nobody says that, and log backups will never shrink a file. They say running backups frequently should help prevent it from growing, since the space inside can be reused once it has been backed up. Sometimes the log space gets used faster than your backups run. If this happens often, shrinking just to grow again is pointless, just leave them large. If this is due to a known, abnormal event and you've put something in place to prevent it happening again, that is about the only time I would advocate any shrinking.

– Aaron Bertrand
2 days ago





everyone talks about running back ups on your log file so it shrinks down - no, nobody says that, and log backups will never shrink a file. They say running backups frequently should help prevent it from growing, since the space inside can be reused once it has been backed up. Sometimes the log space gets used faster than your backups run. If this happens often, shrinking just to grow again is pointless, just leave them large. If this is due to a known, abnormal event and you've put something in place to prevent it happening again, that is about the only time I would advocate any shrinking.

– Aaron Bertrand
2 days ago




2




2





Be aware that everyone helping you is under the assumption that you have a legitimate reason to want full recovery mode. If your recovery point objective is at least 1 day, consider switching to simple recovery mode and omitting transaction log backups entirely (then shrinking your log backups once).

– Brian
2 days ago






Be aware that everyone helping you is under the assumption that you have a legitimate reason to want full recovery mode. If your recovery point objective is at least 1 day, consider switching to simple recovery mode and omitting transaction log backups entirely (then shrinking your log backups once).

– Brian
2 days ago





1




1





I do need the full.

– SventoryMang
yesterday





I do need the full.

– SventoryMang
yesterday










2 Answers
2






active

oldest

votes


















7














You are confusing allocated space with used space. After running the backup use this query to see the difference between allocated and used space.



select file_id
, type_desc
, name
, substring([physical_name],1,3) AS [Drive]
, physical_name
, state_desc
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB' --Addapted from https://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
, growth / 128 as 'GrowthSettingMB'

from sys.database_files
order by type_desc Desc, name


You can use the GUI to shrink the log file by changing the 'Initial size'



enter image description here



If you are having troubles shrinking the log, even when it looks mostly empty see my post here






share|improve this answer

























  • Wow the initial size was set to 21gb. I couldn't possibly imagine why. Is it possible for a log file to be "full" when it reaches the max size? Since I added the shrink task to my maintenance plan, it should presumably never be able to get to max size if I am running backups and shrinking often?

    – SventoryMang
    2 days ago











  • Actaully I am trying to change it to 500mb and clicking okay and it's reverting back to 21gb.

    – SventoryMang
    2 days ago











  • @SventoryMang Read the link at the last line of my answer.

    – James Jenkins
    2 days ago






  • 6





    Please don't add shrink to your maintenance plan. If your log file hits a certain size, it will hit that size again under the same circumstances. Thus, shrinking introduces a performance cost (for the shrink and the regrow), but offers no long-term benefit. A one-time manual shrink to after increasing log file back-up frequency is OK, but shrinking log files as a maintenance task is not.

    – Brian
    2 days ago


















6














Taking this backup will just backup the data and clear the log. The actual size of the log will need to be shrunk via a DBCC command if you really need to shrink the log. Depending on how often you are backing up your log file it will likely just grow again.



Try running this to see how much actual space on your log is taken up.



SELECT 
[TYPE] = A.TYPE_DESC
,[FILE_Name] = A.name
,[FILEGROUP_NAME] = fg.name
,[File_Location] = A.PHYSICAL_NAME
,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END
+ CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END
+ CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id
order by A.TYPE desc, A.NAME;


If you actually have loads of free space available you can run the DBCC SHRINKFILE command in order to get your log file down to whichever size you think it should be.



Edit: You may also want to check DBCC LOGINFO; then you can see any items that are in use by your transaction log file as they will have a status of two.



HOWEVER whatever activity caused you log file to grow in the first place is likely to continue to happen. From the sounds of thinks you're only taking one log backup a day.



What you should be doing is taking multiple log backups throughout the day in between your full database backups. I'd likely recommend starting with hourly and adjust to see ultimately what works best for you. You can either continue doing this via maintenance plans if that's what's comfortable for you. Other wise you could use Ola Hallengren's scripts to set up a maintenance plan. There are a lot of different options to go with and for the most part they're all pretty great as long as you're taking frequent backups.






share|improve this answer

























  • +1 for CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]

    – James Jenkins
    2 days ago











Your Answer








StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);






SventoryMang is a new contributor. Be nice, and check out our Code of Conduct.









draft saved

draft discarded


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234221%2fwhy-is-my-log-file-so-massive-22gb-i-am-running-log-backups%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown

























2 Answers
2






active

oldest

votes








2 Answers
2






active

oldest

votes









active

oldest

votes






active

oldest

votes









7














You are confusing allocated space with used space. After running the backup use this query to see the difference between allocated and used space.



select file_id
, type_desc
, name
, substring([physical_name],1,3) AS [Drive]
, physical_name
, state_desc
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB' --Addapted from https://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
, growth / 128 as 'GrowthSettingMB'

from sys.database_files
order by type_desc Desc, name


You can use the GUI to shrink the log file by changing the 'Initial size'



enter image description here



If you are having troubles shrinking the log, even when it looks mostly empty see my post here






share|improve this answer

























  • Wow the initial size was set to 21gb. I couldn't possibly imagine why. Is it possible for a log file to be "full" when it reaches the max size? Since I added the shrink task to my maintenance plan, it should presumably never be able to get to max size if I am running backups and shrinking often?

    – SventoryMang
    2 days ago











  • Actaully I am trying to change it to 500mb and clicking okay and it's reverting back to 21gb.

    – SventoryMang
    2 days ago











  • @SventoryMang Read the link at the last line of my answer.

    – James Jenkins
    2 days ago






  • 6





    Please don't add shrink to your maintenance plan. If your log file hits a certain size, it will hit that size again under the same circumstances. Thus, shrinking introduces a performance cost (for the shrink and the regrow), but offers no long-term benefit. A one-time manual shrink to after increasing log file back-up frequency is OK, but shrinking log files as a maintenance task is not.

    – Brian
    2 days ago















7














You are confusing allocated space with used space. After running the backup use this query to see the difference between allocated and used space.



select file_id
, type_desc
, name
, substring([physical_name],1,3) AS [Drive]
, physical_name
, state_desc
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB' --Addapted from https://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
, growth / 128 as 'GrowthSettingMB'

from sys.database_files
order by type_desc Desc, name


You can use the GUI to shrink the log file by changing the 'Initial size'



enter image description here



If you are having troubles shrinking the log, even when it looks mostly empty see my post here






share|improve this answer

























  • Wow the initial size was set to 21gb. I couldn't possibly imagine why. Is it possible for a log file to be "full" when it reaches the max size? Since I added the shrink task to my maintenance plan, it should presumably never be able to get to max size if I am running backups and shrinking often?

    – SventoryMang
    2 days ago











  • Actaully I am trying to change it to 500mb and clicking okay and it's reverting back to 21gb.

    – SventoryMang
    2 days ago











  • @SventoryMang Read the link at the last line of my answer.

    – James Jenkins
    2 days ago






  • 6





    Please don't add shrink to your maintenance plan. If your log file hits a certain size, it will hit that size again under the same circumstances. Thus, shrinking introduces a performance cost (for the shrink and the regrow), but offers no long-term benefit. A one-time manual shrink to after increasing log file back-up frequency is OK, but shrinking log files as a maintenance task is not.

    – Brian
    2 days ago













7












7








7







You are confusing allocated space with used space. After running the backup use this query to see the difference between allocated and used space.



select file_id
, type_desc
, name
, substring([physical_name],1,3) AS [Drive]
, physical_name
, state_desc
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB' --Addapted from https://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
, growth / 128 as 'GrowthSettingMB'

from sys.database_files
order by type_desc Desc, name


You can use the GUI to shrink the log file by changing the 'Initial size'



enter image description here



If you are having troubles shrinking the log, even when it looks mostly empty see my post here






share|improve this answer















You are confusing allocated space with used space. After running the backup use this query to see the difference between allocated and used space.



select file_id
, type_desc
, name
, substring([physical_name],1,3) AS [Drive]
, physical_name
, state_desc
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB' --Addapted from https://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
, growth / 128 as 'GrowthSettingMB'

from sys.database_files
order by type_desc Desc, name


You can use the GUI to shrink the log file by changing the 'Initial size'



enter image description here



If you are having troubles shrinking the log, even when it looks mostly empty see my post here







share|improve this answer














share|improve this answer



share|improve this answer








edited 2 days ago

























answered 2 days ago









James JenkinsJames Jenkins

2,14022045




2,14022045












  • Wow the initial size was set to 21gb. I couldn't possibly imagine why. Is it possible for a log file to be "full" when it reaches the max size? Since I added the shrink task to my maintenance plan, it should presumably never be able to get to max size if I am running backups and shrinking often?

    – SventoryMang
    2 days ago











  • Actaully I am trying to change it to 500mb and clicking okay and it's reverting back to 21gb.

    – SventoryMang
    2 days ago











  • @SventoryMang Read the link at the last line of my answer.

    – James Jenkins
    2 days ago






  • 6





    Please don't add shrink to your maintenance plan. If your log file hits a certain size, it will hit that size again under the same circumstances. Thus, shrinking introduces a performance cost (for the shrink and the regrow), but offers no long-term benefit. A one-time manual shrink to after increasing log file back-up frequency is OK, but shrinking log files as a maintenance task is not.

    – Brian
    2 days ago

















  • Wow the initial size was set to 21gb. I couldn't possibly imagine why. Is it possible for a log file to be "full" when it reaches the max size? Since I added the shrink task to my maintenance plan, it should presumably never be able to get to max size if I am running backups and shrinking often?

    – SventoryMang
    2 days ago











  • Actaully I am trying to change it to 500mb and clicking okay and it's reverting back to 21gb.

    – SventoryMang
    2 days ago











  • @SventoryMang Read the link at the last line of my answer.

    – James Jenkins
    2 days ago






  • 6





    Please don't add shrink to your maintenance plan. If your log file hits a certain size, it will hit that size again under the same circumstances. Thus, shrinking introduces a performance cost (for the shrink and the regrow), but offers no long-term benefit. A one-time manual shrink to after increasing log file back-up frequency is OK, but shrinking log files as a maintenance task is not.

    – Brian
    2 days ago
















Wow the initial size was set to 21gb. I couldn't possibly imagine why. Is it possible for a log file to be "full" when it reaches the max size? Since I added the shrink task to my maintenance plan, it should presumably never be able to get to max size if I am running backups and shrinking often?

– SventoryMang
2 days ago





Wow the initial size was set to 21gb. I couldn't possibly imagine why. Is it possible for a log file to be "full" when it reaches the max size? Since I added the shrink task to my maintenance plan, it should presumably never be able to get to max size if I am running backups and shrinking often?

– SventoryMang
2 days ago













Actaully I am trying to change it to 500mb and clicking okay and it's reverting back to 21gb.

– SventoryMang
2 days ago





Actaully I am trying to change it to 500mb and clicking okay and it's reverting back to 21gb.

– SventoryMang
2 days ago













@SventoryMang Read the link at the last line of my answer.

– James Jenkins
2 days ago





@SventoryMang Read the link at the last line of my answer.

– James Jenkins
2 days ago




6




6





Please don't add shrink to your maintenance plan. If your log file hits a certain size, it will hit that size again under the same circumstances. Thus, shrinking introduces a performance cost (for the shrink and the regrow), but offers no long-term benefit. A one-time manual shrink to after increasing log file back-up frequency is OK, but shrinking log files as a maintenance task is not.

– Brian
2 days ago





Please don't add shrink to your maintenance plan. If your log file hits a certain size, it will hit that size again under the same circumstances. Thus, shrinking introduces a performance cost (for the shrink and the regrow), but offers no long-term benefit. A one-time manual shrink to after increasing log file back-up frequency is OK, but shrinking log files as a maintenance task is not.

– Brian
2 days ago













6














Taking this backup will just backup the data and clear the log. The actual size of the log will need to be shrunk via a DBCC command if you really need to shrink the log. Depending on how often you are backing up your log file it will likely just grow again.



Try running this to see how much actual space on your log is taken up.



SELECT 
[TYPE] = A.TYPE_DESC
,[FILE_Name] = A.name
,[FILEGROUP_NAME] = fg.name
,[File_Location] = A.PHYSICAL_NAME
,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END
+ CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END
+ CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id
order by A.TYPE desc, A.NAME;


If you actually have loads of free space available you can run the DBCC SHRINKFILE command in order to get your log file down to whichever size you think it should be.



Edit: You may also want to check DBCC LOGINFO; then you can see any items that are in use by your transaction log file as they will have a status of two.



HOWEVER whatever activity caused you log file to grow in the first place is likely to continue to happen. From the sounds of thinks you're only taking one log backup a day.



What you should be doing is taking multiple log backups throughout the day in between your full database backups. I'd likely recommend starting with hourly and adjust to see ultimately what works best for you. You can either continue doing this via maintenance plans if that's what's comfortable for you. Other wise you could use Ola Hallengren's scripts to set up a maintenance plan. There are a lot of different options to go with and for the most part they're all pretty great as long as you're taking frequent backups.






share|improve this answer

























  • +1 for CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]

    – James Jenkins
    2 days ago















6














Taking this backup will just backup the data and clear the log. The actual size of the log will need to be shrunk via a DBCC command if you really need to shrink the log. Depending on how often you are backing up your log file it will likely just grow again.



Try running this to see how much actual space on your log is taken up.



SELECT 
[TYPE] = A.TYPE_DESC
,[FILE_Name] = A.name
,[FILEGROUP_NAME] = fg.name
,[File_Location] = A.PHYSICAL_NAME
,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END
+ CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END
+ CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id
order by A.TYPE desc, A.NAME;


If you actually have loads of free space available you can run the DBCC SHRINKFILE command in order to get your log file down to whichever size you think it should be.



Edit: You may also want to check DBCC LOGINFO; then you can see any items that are in use by your transaction log file as they will have a status of two.



HOWEVER whatever activity caused you log file to grow in the first place is likely to continue to happen. From the sounds of thinks you're only taking one log backup a day.



What you should be doing is taking multiple log backups throughout the day in between your full database backups. I'd likely recommend starting with hourly and adjust to see ultimately what works best for you. You can either continue doing this via maintenance plans if that's what's comfortable for you. Other wise you could use Ola Hallengren's scripts to set up a maintenance plan. There are a lot of different options to go with and for the most part they're all pretty great as long as you're taking frequent backups.






share|improve this answer

























  • +1 for CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]

    – James Jenkins
    2 days ago













6












6








6







Taking this backup will just backup the data and clear the log. The actual size of the log will need to be shrunk via a DBCC command if you really need to shrink the log. Depending on how often you are backing up your log file it will likely just grow again.



Try running this to see how much actual space on your log is taken up.



SELECT 
[TYPE] = A.TYPE_DESC
,[FILE_Name] = A.name
,[FILEGROUP_NAME] = fg.name
,[File_Location] = A.PHYSICAL_NAME
,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END
+ CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END
+ CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id
order by A.TYPE desc, A.NAME;


If you actually have loads of free space available you can run the DBCC SHRINKFILE command in order to get your log file down to whichever size you think it should be.



Edit: You may also want to check DBCC LOGINFO; then you can see any items that are in use by your transaction log file as they will have a status of two.



HOWEVER whatever activity caused you log file to grow in the first place is likely to continue to happen. From the sounds of thinks you're only taking one log backup a day.



What you should be doing is taking multiple log backups throughout the day in between your full database backups. I'd likely recommend starting with hourly and adjust to see ultimately what works best for you. You can either continue doing this via maintenance plans if that's what's comfortable for you. Other wise you could use Ola Hallengren's scripts to set up a maintenance plan. There are a lot of different options to go with and for the most part they're all pretty great as long as you're taking frequent backups.






share|improve this answer















Taking this backup will just backup the data and clear the log. The actual size of the log will need to be shrunk via a DBCC command if you really need to shrink the log. Depending on how often you are backing up your log file it will likely just grow again.



Try running this to see how much actual space on your log is taken up.



SELECT 
[TYPE] = A.TYPE_DESC
,[FILE_Name] = A.name
,[FILEGROUP_NAME] = fg.name
,[File_Location] = A.PHYSICAL_NAME
,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END
+ CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END
+ CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id
order by A.TYPE desc, A.NAME;


If you actually have loads of free space available you can run the DBCC SHRINKFILE command in order to get your log file down to whichever size you think it should be.



Edit: You may also want to check DBCC LOGINFO; then you can see any items that are in use by your transaction log file as they will have a status of two.



HOWEVER whatever activity caused you log file to grow in the first place is likely to continue to happen. From the sounds of thinks you're only taking one log backup a day.



What you should be doing is taking multiple log backups throughout the day in between your full database backups. I'd likely recommend starting with hourly and adjust to see ultimately what works best for you. You can either continue doing this via maintenance plans if that's what's comfortable for you. Other wise you could use Ola Hallengren's scripts to set up a maintenance plan. There are a lot of different options to go with and for the most part they're all pretty great as long as you're taking frequent backups.







share|improve this answer














share|improve this answer



share|improve this answer








edited 2 days ago

























answered 2 days ago









ZaneZane

2,77221842




2,77221842












  • +1 for CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]

    – James Jenkins
    2 days ago

















  • +1 for CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]

    – James Jenkins
    2 days ago
















+1 for CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]

– James Jenkins
2 days ago





+1 for CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]

– James Jenkins
2 days ago










SventoryMang is a new contributor. Be nice, and check out our Code of Conduct.









draft saved

draft discarded


















SventoryMang is a new contributor. Be nice, and check out our Code of Conduct.












SventoryMang is a new contributor. Be nice, and check out our Code of Conduct.











SventoryMang is a new contributor. Be nice, and check out our Code of Conduct.














Thanks for contributing an answer to Database Administrators Stack Exchange!


  • Please be sure to answer the question. Provide details and share your research!

But avoid


  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234221%2fwhy-is-my-log-file-so-massive-22gb-i-am-running-log-backups%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







-backup, log, sql-server, sql-server-2008, transaction-log

Popular posts from this blog

Frič See also Navigation menuinternal link

Identify plant with long narrow paired leaves and reddish stems Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern) Announcing the arrival of Valued Associate #679: Cesar Manara Unicorn Meta Zoo #1: Why another podcast?What is this plant with long sharp leaves? Is it a weed?What is this 3ft high, stalky plant, with mid sized narrow leaves?What is this young shrub with opposite ovate, crenate leaves and reddish stems?What is this plant with large broad serrated leaves?Identify this upright branching weed with long leaves and reddish stemsPlease help me identify this bulbous plant with long, broad leaves and white flowersWhat is this small annual with narrow gray/green leaves and rust colored daisy-type flowers?What is this chilli plant?Does anyone know what type of chilli plant this is?Help identify this plant

fontconfig warning: “/etc/fonts/fonts.conf”, line 100: unknown “element blank” The 2019 Stack Overflow Developer Survey Results Are In“tar: unrecognized option --warning” during 'apt-get install'How to fix Fontconfig errorHow do I figure out which font file is chosen for a system generic font alias?Why are some apt-get-installed fonts being ignored by fc-list, xfontsel, etc?Reload settings in /etc/fonts/conf.dTaking 30 seconds longer to boot after upgrade from jessie to stretchHow to match multiple font names with a single <match> element?Adding a custom font to fontconfigRemoving fonts from fontconfig <match> resultsBroken fonts after upgrading Firefox ESR to latest Firefox