• beejug1983 (10/17/2010)


    First : - The query that is being run by user will be stopped or what ?

    No, but if your tran log runs out of space no other data changes will be allowed. In essence, your system will come to a complete halt

    Second : - If I shrink the .ldf file what will happen?

    Absolutely nothing. That active transaction is preventing the log space from being reused. There is no free space in your log. Shrink releases unused space to the OS.

    You can shrink as many times as you like, until that open transaction is either committed (by the user who began it) or rolled back nothing will happen at all.

    You really don't have many options here.

    1) Contact the user and get that transaction committed

    2) Kill the process and it will roll back, once rollback is complete the space in the log will be reusable (after next log backup in full recovery)

    3) Do nothing. As soon as your log fills every single transaction that occurs will cause an error because there's no free space in the log. The system will remain like this (unusable) until either option 1 or 2 is followed.

    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