Post reply

t-sql is bigger

  • October 2, 2013 at 7:05 am

    #283348

    Guys, I currently have a database of 80 giga bytes. did a shrink log file to 1 mega byte. after it started backup diff and log, but the log file grew unexpectedly, and even when not being used by any application, ie only test ... as a log file can grow as fast if my database is not linked to any application? every 30min i've 170mb for each log file... that is stranger...

  • October 2, 2013 at 7:20 am

    #1655207

    A log file will only grow if there's activity being logged.

    As for log maintenance, see http://www.sqlservercentral.com/articles/Administration/64582/

    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

  • October 2, 2013 at 7:33 am

    #1655211

    Hi,

    Check for any connection into that database by profiler?

    Regards,
    Kumar

  • October 2, 2013 at 10:11 am

    #1655260

    I would strongly encourage you to look at the article that Gail posted.

    I currently have a database of 80[gb]. did a shrink log file to 1 mega byte.

    For a database that size I would suggest that you start with a larger transaction log than 1mb. It is going to begin growing right away and that will impact performance.

    You can also check activity on that database using sp_who2

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • October 2, 2013 at 10:15 am

    #1655263

    I noticed that the log file is bigger when I do a full backup. is this normal?

  • October 2, 2013 at 10:19 am

    #1655264

    all right, but the big problem is backup log... may a 170mb for each file... lets suppose that I have 30 log files? 30 x 170 = 5gb backup log? in the case i have not yet application... and i not like use a shrink... so, I put my thin log ..

  • October 2, 2013 at 10:28 am

    #1655270

    Did you read the article I referenced?

    If your log is growing, there is something modifying that database. Logs won't grow without log records being added, that is changed to the database occurring and being logged.

    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

  • October 2, 2013 at 10:36 am

    #1655275

    Gila,

    I noticed that the log file is bigger when I do a full backup. is this normal? actually my database have a 80gb (mdf). and a do a shrink... all right (log file 30mb), when i do backup full... (300mb), so for my each log backup is 170mb... is not a big??

    daily -> backup full: 15GB

    every 2 hour -> backup diff: 2mb...

    every 30 minutes -> backup log: 170mb (each file)

    170mb x 20 each file, = 3.4GB log...

    so... the log is bigger then diff..

  • October 2, 2013 at 3:09 pm

    #1655345

    Don't shrink your log. Please read the article I referenced.

    If the same database pages are getting repeatedly modified, log backups can be larger than the diffs.

    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

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

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