Log File Growth

Question

Post reply

Log File Growth

santoshkal

SSC-Addicted

Points: 497
More actions
September 23, 2014 at 5:00 am

#296819

Hi,
One of the database log file is having only 12% space free which is in full recovery model but when i exec dbcc sqlperf(logspace) its shows 1.87% only used we are in DWH environment & don't take any log backups
what should i need to do ??
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ed Wagner SSC Guru Points: 287024 More actions · Answer 1

Here's a good series on transaction log management: http://www.sqlservercentral.com/stairway/73776/

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 2

If you database is in the full recovery mode, you need to be taking regular log backups.

Where are you getting the 12% free space figure from?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

santoshkal (9/23/2014)
One of the database log file is having only 12% space free which is in full recovery model but when i exec dbcc sqlperf(logspace) its shows 1.87% only used we are in DWH environment & don't take any log backups

There's your problem right there.

Full recovery model is for when you need point-in-time recovery and intend to take log backups to allow that. If you don't need point-in-time recovery, then the DB should be in simple 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

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

santoshkal SSC-Addicted Points: 497 More actions · Answer 4

ya its Simple recovery model ... we have a support team who monitors the size

DatabaseSizeUnallocatedSizeAllocatedSizeAvailableSpaceAvailableSpaceIn%MaxSize

2.891299 0.179775 2.711523 17.28848 86.44238 20

Num_Of_TablesLogFileSizeLogFileUsedSizeAvailableLogSizeInPercent

29 1007608 891647 12

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 5

From what you posted, the first script shows that your database has 86% space free overall (including your log file),

The second output shows that you have 12% in your log file, but you're getting a different figure? Are the scripts being run on the same database, at the same time?

santoshkal SSC-Addicted Points: 497 More actions · Answer 6

yes data base is same ...

how to check the used log space ? any script ?

Thanks

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 7

This will show you the bytes in use in each of the transaction logs in the databases on the system:-

USE [master];

GO

SELECT

DB_NAME(database_id) AS [Database],

SUM(database_transaction_log_bytes_used_system) AS [System Bytes Used],

SUM(database_transaction_log_bytes_used) AS [Bytes Used]

FROM

sys.dm_tran_database_transactions

GROUP BY

DB_NAME(database_id)

ORDER BY

DB_NAME(database_id) ASC