Is this normal Log file size?

Question

Post reply

Is this normal Log file size?

Tac11

SSCertifiable

Points: 6980
More actions
April 24, 2014 at 2:26 pm

#284818

DB size 775 GB
Data file space usage 585 GB
log size 185 GB - according to SQLPERF
My question is this a normal logfile size of 585GB Datafile? any suggestions to reduce log file size besides SHRINKFILE? If I do SHRINKFILE , should I make LOGFILE to '0' and give the right size again? if so, what right size should I give after SHRINKING log file?
Thank you!!!

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 1

Depends on the transactions you are doing and on the recovery model.

What is the recovery model?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

smtzac (4/24/2014)
My question is this a normal logfile size of 585GB Datafile? any suggestions to reduce log file size besides SHRINKFILE?

Why do you want to shrink the file? What problems are you seeing?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tac11 SSCertifiable Points: 6980 More actions · Answer 3

Tac11

SSCertifiable

Points: 6980

April 25, 2014 at 6:56 am

#1708760

full

Tac11 SSCertifiable Points: 6980 More actions · Answer 4

@Gila, I like to get your opinion is it normal log size? it's ok to shrink Logfile size? have 520 VLF. should I shrink log file and reset logsize to zero? if I do that what's the initial size should I give to logfile size after shrink Log?

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 5

smtzac (4/25/2014)
full

Did you ever take transaction log backups?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Tac11 SSCertifiable Points: 6980 More actions · Answer 6

Tac11

SSCertifiable

Points: 6980

April 25, 2014 at 7:01 am

#1708765

yes, every 30 min.

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 7

smtzac (4/25/2014)
yes, every 30 min.

If you take a transaction log backup, the log is truncated.

So you need to check if there is a lot of space available right before and after a transaction log backup.

This can give an indication how much space you actually need for your transactions.

Any way, if your log is that big, it means it needed that space to store transactions at some point in time.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

smtzac (4/25/2014)
@Gila, I like to get your opinion is it normal log size?

There's no such thing. A log file size is dependant on transactional workload, recovery model and log backup frequency. There isn't a 'normal'

it's ok to shrink Logfile size?

Generally no. If it got to that size it usually means that it needs to be that size.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 9

smtzac (4/24/2014)
DB size 775 GB
Data file space usage 585 GB
log size 185 GB - according to SQLPERF
My question is this a normal logfile size of 585GB Datafile? any suggestions to reduce log file size besides SHRINKFILE? If I do SHRINKFILE , should I make LOGFILE to '0' and give the right size again? if so, what right size should I give after SHRINKING log file?
Thank you!!!

What is the size of the data for the largest table (clustered index)?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

homebrew01 SSC Guru Points: 55553 More actions · Answer 10

It might not be a "normal" size for that database if it grew large due to a lack of t-log backups some time in the past. But now that you are doing regular backups, it might not need all that space.