DBCC Shrinkfile

Question

DBCC Shrinkfile

Viewing 5 posts - 16 through 19 (of 19 total)

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

okbangas SSChampion Points: 11773 More actions · Answer 1

derekr 43208 (11/17/2011)
Ok, I though that there was a performance hit with too much extra space in the log.....

Absolutely not. There is a performance hit with shrinking the log file (since it requires disk IO) and growing it againi (since it requires IO). The free space itself in a log file, is not an issue.

However, by running DBCC LOGINFO you may in some cases (for instance when a log file is set to grow with a little amount) you may get quite a few rows in this result set. Each row returned is a VLF, or Virtual Log File. There may be some issues with having a huge amount of VLFs. Kimberly Tripp has a blog post on this

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

SQLSACT

SSC-Insane

Points: 21838

November 17, 2011 at 11:30 pm

#1409689

Thanks All

derek

fitsumkh SSChasing Mays Points: 652 More actions · Answer 3

I would suggest finding what the underline cause for your transaction log file to increase by a lot, this could be caused by:

- Full Recovery Model DB without log backups (how often it is taken)

- Long running transaction - large delete statement for example (you can use buffer to do it in chunks so your transaction log would not increase)

- Uncommitted transaction - check for open transactions that are uncommitted

- Loading bulk data - you might want to change the recovery model to bulk load if you are doing bulk loads.

Hope this helps,

Fitsum

SQLSACT SSC-Insane Points: 21838 More actions · Answer 4

fitsumkh (11/18/2011)
I would suggest finding what the underline cause for your transaction log file to increase by a lot, this could be caused by:
- Full Recovery Model DB without log backups (how often it is taken)
- Long running transaction - large delete statement for example (you can use buffer to do it in chunks so your transaction log would not increase)
- Uncommitted transaction - check for open transactions that are uncommitted
- Loading bulk data - you might want to change the recovery model to bulk load if you are doing bulk loads.
Hope this helps,
Fitsum

Thanks

jnuqui SSCommitted Points: 1585 More actions · Answer 5

its pointless trying to calculate Log Shrink when your main goal is to have it small in the first place. 😀

shrinked data are pointless anyway. if there is a percentage that will be left we wouldnt need that data anymore.

===============================================================

"lets do amazing" our company motto..