Potential Production issue

  • I have noticed my production database was around 60 GB night before and now around 107GB also Full backup was around 47GB before and now 106GB.

    I think it was not a user / developer issue. If I go back and see my jobs history, I suspect one job that I run on weekly basis.

    USE XXXXXX

    GO

    EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 40)"

    GO

    Now how could I shrink the database back to <50GB so I could have some space for the busy week ahead ?

    and also appreciate if you guys could throw some more light on alternate way of reindexing without killing all the space.

    thanks for your time in advance.

    apps dba

  • forgot to add the following :

    Clustered(single node) SQL Server 2005 SP2 64-bit on a Windows 2003 SP1.

  • Not totally sure why you are "suspicious" of that reindexing job; perhaps you have some information you haven't shared yet?

    Could you please post space usage as follows?...

    Data space

    -- Data

    -- Indexes

    -- Unused

    -- Unreserved

    Transaction log

    -- Used

    -- Unused

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Hope this helps :

    total_space: 106764.88

    total_db_space: 106725.06

    db_can_grow: Yes

    total_log_space: 39.82

    log_can_grow: Yes

    total_space_used: 102041.31

    total_db_space_used: 102040.30

    total_log_space_used: 1.01

    total_free_space: 4723.57

    total_free_db_space: 4684.76

    total_free_log_space: 38.81

    percent_db_used: 95.61

    percent_log_used: 2.54

    Database Usage

    Data 93921.02 MB (88.00 %)

    Indexes 9346.75 MB (8.76 %)

    Unused 142.45 MB (0.13 %)

    Unreserved 3314.84 MB (3.11 %)

    Total Database Size 106725.06 MB

    Transaction Log Usage

    Transaction Log Used 0.00 (0.00 %)

    Transaction Log Unused 39.82 (100.00 %)

    Total Transaction Log Size 39.82 MB

  • I am suspecting my job as all my research pointing to that one job that was running around that time. Also my hourly log backups around the time of this job were 30mb, 42mb, 6mb, 7mb, 1mb, 22gb, 24gb, 3mb, 6mb.

    job started around 1mb and ended just before 3mb.

    as always, thanks for your time and help.

  • Hope i read it correctly but the Log size didnt grew it only 39 mg so one of the tables grew 50 GIG...?

    Oh the reindex

    EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 40)" isn't this making

    every table 40% fill so that each table has 40% of space before it page splits isn't this rather a big amount.

  • Tracey,

    Thanks for your response. Yes, you are right.

    Now I only have 4.5gb space left for data files. if I run it again with fill factor let's say 100, would it free up some space ? I am not sure whether 4.5gb free space is sufficient enough even to finish this job again.

    how could I bring it back to 50gb ball park ? I could add another data file as a work around as long as if you guys can suggest me some thing to bring it back. I think I am out of ideas but still exploring the best way to do it.

    appreciate all your time and help.

  • Reindex again with a higher fill factor. Do you have a reason for wanting the data pages to be only 40% full after the reindex?

    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
  • I would do it for say 80.....not the 100.

    Then once you have done this you can do the shrink on the data files. (not the log as it not the log that filled up).

    The only thing now is that the reindex will not do the tables you did the other day at 40% as they will not be considered fragmented so you need to do the tables one by one using the command with the table names.

  • thanks guys.

    appreciate all your help.

    I will probably try for big 20 tables one by one and then try to generate some script for the rest of the tables.

    will post the outcome tonight.

  • tried for one big table and it failed as the rest of the space 4.5gb also occupied. I have added another data file on a different drive with 10gb more and tried again and that one too filled and statement failed again.

    Now I am db at 120gb 🙁

  • BOL is an important resource.

    reference

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/6e929d09-ccb5-4855-a6af-b616022bc8f6.htm

    This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead.

    also,

    FILLFACTOR

    Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration. fillfactor must be an integer value from 1 to 100. The default is 0.

    Note: [SQL2005]

    Fill factor values 0 and 100 are the same in all respects. [There is a subtle difference in SQL 2000 you might like to research]

    CodeOn

    😛

  • Thanks a bunch to all of you guys for your help and time.

    issue has been resolved finally and now every thing back to normal. Learned the lesson.

  • Curious, and others may have a similiar problem, but what was done to resolve the problem?

Viewing 14 posts - 1 through 13 (of 13 total)

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