Post reply

index file size got changed

  • May 2, 2011 at 9:39 am

    #237441

    I have a database for a software called Semantics.

    The database data file has a file group called FG_INDEX, for the logical file name sem5_index. I got some warnings that the file is 93% full.

    The file is setup to autogrowth, by 64 mb.

    I went to database property- files- find the Sem5_index file and change the initial size to a big number. And save it.

    The strange thing is after some time, the initial size changed back to original number, and then I got those warnings again.

    Why the size got changed, how can I fix this?

    Thanks

  • May 2, 2011 at 9:49 am

    #1318500

    Autoshrink on?

    Manual shrink job?

    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

  • May 2, 2011 at 10:21 am

    #1318526

    Thanks,

    I did shrink the file manually.

    Thank you

  • May 2, 2011 at 10:27 am

    #1318531

    Why?

    You just grew the file, so why shrink it again?

    Have you rebuild all the indexes that the shrink fragmented?

    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

  • May 2, 2011 at 10:32 am

    #1318537

    I think I am not so clear about DBCC shrink file do.

    I thought by doing that will clean and reorganize the page,and leave some unused space at the end of the file and it won't reduce the size of the file I just expand.

    Maybe I mixed up with transaction log backup.

  • May 2, 2011 at 11:04 am

    #1318565

    Depends on the settings. Unless you run it with NoTruncate is will reduce the size of the file. Still badly fragmenting all your indexes though.

    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

  • May 2, 2011 at 11:34 am

    #1318596

    Thanks, I will not do it later in most cases.

  • May 2, 2011 at 12:24 pm

    #1318656

    I just got another alert that the database index file 96% full.

    I checked the file, it changed back to the original size again.

    I repeated these several times, I remember once I did a shrink file, so I told you earlier. But most time i didn't do the shrink file, for the last time after you told me, I didn't do it.

    But why the file size changed back again?

    I see the auto-shrink is setup as false.

    Thanks

  • May 2, 2011 at 12:38 pm

    #1318661

    Check the jobs for one that shrinks the DB. Nothing other than a shrink will reduce the size of the file.

    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

  • May 2, 2011 at 1:29 pm

    #1318695

    Thanks, this is an index file, does it make difference?

    I checked the jobs, I cannot see the one that shrink files.

    The only thing I suspect is we do transaction log backup, our next run is 3:00 pm, I will see if it changed after that run.

    Thanks

  • May 2, 2011 at 1:54 pm

    #1318705

    SQLMyFriend (5/2/2011)

    Thanks, this is an index file, does it make difference?

    Nope. The only thing that shrinks database files is a shrinkdatabase or a shrinkfile

    The only thing I suspect is we do transaction log backup, our next run is 3:00 pm, I will see if it changed after that run.

    Log backups don't shrink files. Check maintenance plans, it's not uncommon for someone who doesn't realise how bad a shrink is to put shrink into the full backup or index rebuild maintenance plan.

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

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