locked
May be due to database Snapshot disk space is full in each sunday RRS feed

  • Question

  • Hi Team,

    We have a database snapshot at secondary side of mirror database.(SQL Server 2008 (SP4) - 10.0.6000.29)

    On each Sunday (Maintenance activities are scheduled at primary server) when at primary site rebuild index start(which took time only 1.5 hours in completion) after half an hour we get the alert that disk is full (293 GB space is consumed).

    Snapshot daily created through a job  in night 12 AM on secondary server.

    We are suspecting is it due to maintenance job or something else where we should check? 


    Thanks

    Thursday, May 12, 2016 9:03 AM

Answers

  • any solution except recreate snapshot?

    Thanks

    Are you using MP to rebuild indexes ? If so this might be the cause. You would have a solution in terms of using your own script to ONLY rebuild index which is fragmented >30 % and reorganize with 10 to 30 % fragmentation. If you can apply any logic which would decrease the amount of indexes that needs to be rebuild you might not get disk alerts.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Thursday, May 12, 2016 11:54 AM

All replies

  • You are correct whatever changes you make on Primary, snapshot would be synchronized with it. Index rebuild is huge activity

    The snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of the file grows. When a snapshot is taken, the sparse file takes up little disk space.

    Initially snapshot is just and image but when changes are made it keeps updating itself and growing. When change is made original page is get written to snapshot


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP



    Thursday, May 12, 2016 9:49 AM
  • any solution except recreate snapshot?

    Thanks

    Thursday, May 12, 2016 11:30 AM
  • any solution except recreate snapshot?

    Thanks

    Are you using MP to rebuild indexes ? If so this might be the cause. You would have a solution in terms of using your own script to ONLY rebuild index which is fragmented >30 % and reorganize with 10 to 30 % fragmentation. If you can apply any logic which would decrease the amount of indexes that needs to be rebuild you might not get disk alerts.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Thursday, May 12, 2016 11:54 AM