June 12, 2014 at 8:26 am
We have an SCCM database that has a 22 GB .mdf file and a 15 GB .ldf file. The database is in Simple Recovery Mode. I tried Shrinking the Log file via SSMS. I issued the DBCC SHRINKFILLE command.... neither worked. I tried to reduce the "Initial Size" from 14377 to 1000. I then ran the shrink.... nothing changed.... the initial size went back to 14377. I read a few articles that suggested that I detach the DB and rename the .ldf and reattach. Another article recommended a server reboot..... these seem drastic. Any advice would be greatly appreciated.
Charlie
June 12, 2014 at 9:28 am
Why do you want to shrink it? It may be that that is the size it needs to be.
June 12, 2014 at 9:31 am
A 15 GB Log file seems very large for a 22 GB database... Especially in SIMPLE mode.
June 12, 2014 at 10:51 am
The log file is going to be as big as it needs to be. Lots of small transactions, a couple long-running transactions could keep it at the size you're seeing.
IIRC, not having used SCCM but having used similar MS products, you likely get a *lot* of transactions going with SCCM.
Couple things you could try:
1. Switch it to Full Recovery and run frequent backups of the Transaction Log
2. Just let it be...
As for the suggestion of detaching the DB, and renaming the log file...
DON'T.
A reboot won't shrink the log file, either.
June 12, 2014 at 10:55 am
Thanks for the information. I was actually able to get the Log to shrink back to 4,750 MB by lowering the Initial Size and issuing a CHECKPOINT. My guess is.... eventually it will grow again.... it has remained at 4,750 MB for awhile now. I will talk to the user about going to FULL and doing Tlog backups... Thanks Again.
Charlie
June 12, 2014 at 3:07 pm
- Check VLF Count using DBCC LogInfo (excessive VLF's will cause performance issues)
- Put the database in full recovery
- Backup the transaction log
BACKUP LOG @databasename TO DISK = @filename WITH COMPRESSION <--(or not)
- Put it back in simple recovery
- Shrink it
- grow it to a desired size using chunks to minimize the amount of VLF's created:
chunks less than 64MB = 4 VLFs
chunks of 64MB and less than 1GB = 8 VLFs
chunks of 1GB and larger = 16 VLFs
- Set the Transaction Log growth size to be in units like 64MB(4VLF's).
Also, If your regularly shrinking the log files that means its regularly growing causing performance issues and fragmentation.
Just wait a few weeks till the size levels out then follow these steps to optimize the VLF's for that size.
- Check VLF Count using DBCC LogInfo
- Run a Full Backup including at a minimum checkDB()
June 13, 2014 at 11:02 am
What version of SCCM are you running ?
for general information ...
we have multiple SCCM database servers, the main database is in simple recovery mode and is allocated like:
- data portion is 128 GB with 64 GB us space used
- log portion is 32 GB with, on average, 5% space used
The log portion does grow to about 30% used (11-12 GB) on rare occasion.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply