Database Growing Suddenly

  • HEY, RTFM.

    If you don't want our help stop asking for it.

  • thanks for everyone reply on this post

  • naresh.talla (5/16/2011)


    in my job

    step 1-- re indexing

    EXEC sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ( "?")'

    DBCC DBREINDEX is deprecated, should not be used for new development, will be removed in a future version of SQL.

    DECLARE @table_name varchar(1000),@sql nvarchar(4000) declare c1 cursor for SELECT name FROM sysobjects WHERE xtype = 'U' and name not in ('tbl_check')

    sysobjects is deprecated, should not be used for new development, will be removed in a future version of SQL.

    open c1

    fetch next from c1 into @table_name

    while @@Fetch_Status = 0

    begin

    Select @sql = 'UPDATE STATISTICS '+ '[' + @table_name + ']' +' WITH FULLSCAN'

    --print @sql

    exec sp_executesql @sql

    fetch next from c1 into @table_name

    end

    close c1

    deallocate c1

    GO

    Waste of time as you're updating stats that the reindex just updated.

    step 3-- update usage

    DBCC UpdateUsage (DatbaseName)

    Does not need to be run on a regular basis on SQL 2005 and above. There were bugs in the page space algorithms in SQL 2000, they were fixed in 2005.

    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
  • thanks for your post.....

  • Ninja's_RGR'us (5/16/2011)


    #1 culprit is reindex job

    I had the impression that reindex jobs increase the size of log file (ldf) not the data file(mdf)

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • I take transaction log backups very frequently inbetween reindexing steps to prevent big .ldf growth.

  • SKYBVI (5/16/2011)


    Ninja's_RGR'us (5/16/2011)


    #1 culprit is reindex job

    I had the impression that reindex jobs increase the size of log file (ldf) not the data file(mdf)

    Regards,

    Sushant

    I was the reindex job that was doubling the data size... it's that 99.99% of the time.

  • SKYBVI (5/16/2011)


    Ninja's_RGR'us (5/16/2011)


    #1 culprit is reindex job

    I had the impression that reindex jobs increase the size of log file (ldf) not the data file(mdf)

    Regards,

    Sushant

    Both actually. During the reindex a new index is created during the sorting. It takes up to 1.5 X the size of the original index.

    If you happen to have 1 very large table then the db data files need to grow to accomodate it.

  • SKYBVI (5/16/2011)


    Ninja's_RGR'us (5/16/2011)


    #1 culprit is reindex job

    I had the impression that reindex jobs increase the size of log file (ldf) not the data file(mdf)

    Rebuild can increase both. The log because the rebuild is logged (fully in full recovery, minimally in bulk-logged and simple). The data because SQL needs somewhere to put the new index, preferably somewhere contiguous.

    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
  • homebrew01 (5/16/2011)


    I take transaction log backups very frequently inbetween reindexing steps to prevent big .ldf growth.

    Can full backups, transaction log backups and rebuilding index go at the same time?

    Isnt there any interference between them.

    Regards,

    SKYBVI

    Regards
    Sushant Kumar
    MCTS,MCP

  • Probably could go at the same time, but why ?

    I run in series, not parallel:

    Rebuild index

    Rebuild index

    backup t-log

    Rebuild index

    Rebuild index

    backup t-log

Viewing 11 posts - 16 through 25 (of 25 total)

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