Post reply

database shrinking not executing from one hour and Transactional log backups .trn files increasing rapidly and causing low disk space

  • August 11, 2012 at 4:21 am

    #260088

    database shrinking not executing from one hour and Transactional log backups .trn files increasing rapidly and causing low disk space

    Thanks
    Naga.Rohitkumar

  • August 11, 2012 at 4:31 am

    #1524762

    Please give more detail.

    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

  • August 11, 2012 at 4:46 am

    #1524766

    IN one server the database size is increased for thaT iam performing SHRINKING BUT IS TAKING LONG TIME AND PARALLEY INCREASING TRANSACTIONAL LOG FILES [.TRN] RAPID INCREASE WHICH ARE IN ANOTHER DRIVE. HOW TO REDUCE THIS BOTH ISSUES

    -- THESE SCRIPTS IAM USING TO SHRINK --

    use [R2]

    DBCC SHRINKDATABASE (R2,10);

    go

    ----NEXT-----

    USE [R2]

    GO

    DBCC SHRINKFILE (N'R2' , 0, TRUNCATEONLY)

    GO

    ----NEXT-----

    USE [R2]

    GO

    DBCC SHRINKFILE (N'R21' , 0, TRUNCATEONLY)

    GO

    USE [R2]

    GO

    DBCC SHRINKFILE (N'R2_log' , 0)

    GO

    Thanks
    Naga.Rohitkumar

  • August 11, 2012 at 5:08 am

    #1524769

    Shrink is a logged operation and so growth of the log is expected.

    You probably shouldn't be shrinking the DB anyway. That's something that should only be done after some archive or deletion of data. You need to rebuild all your indexes now and regrow your log to a sensible size, what you've done will reduce database performance until you fix it.

    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 4 posts - 1 through 3 (of 3 total)

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