Post reply

Release unused space after drop table in SQL 2008 Enterprise Edition

  • October 6, 2011 at 8:57 am

    #1391244

    Hi Gail,

    Thank you so much for your respond.

    Finally I got the technical answer I was expecting. The free space is in a data file. 40% space is unused.

    I'm guessing nothing I can do about it, right? Any thoughts?

  • October 6, 2011 at 9:06 am

    #1391252

    How did you measure the free space? What command did you run to try and shrink?

    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

  • October 6, 2011 at 9:16 am

    #1391262

    To get the free space, I right click on the database, go to Tasks > Shrink > Files

    I'm using the DBCC SHRINKFILE command.

    When I'm putting the Initial Size as a target_size of the database it shrinks right away.

    Anything lower (even 5 mg) would freeze the execution forever.

  • October 6, 2011 at 9:24 am

    #1391267

    Put a lower value (that causes it to 'freeze') then go to management studio and see what the shrink is waiting for.

    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

  • October 6, 2011 at 9:28 am

    #1391270

    How would I do that in MS Gail?

    Should I set a trace in Profiler?

  • October 6, 2011 at 9:34 am

    #1391274

    No, query sys.dm_exec_requests and look at the wait_* columns.

    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

  • October 6, 2011 at 10:26 am

    #1391306

    That is the only difference Gail

    PAGEIOLATCH_SH79PAGEIOLATCH_SH5:1:3760981

  • October 6, 2011 at 10:34 am

    #1391314

    That's IO-related. It means SQL's requesting a page from the data file the shrink is waiting for that page.

    How long did you wait before deciding the shrink was 'hung'?

    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

  • October 6, 2011 at 10:38 am

    #1391316

    I'm trtying not to wait more than 5 minutes

  • October 6, 2011 at 10:41 am

    #1391321

    Shrinks can take hours, especially if there are LOB columns or a slow IO subsystem. Be patient.

    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

  • October 6, 2011 at 10:43 am

    #1391323

    This is a Live database. Is it not going to affect performance?

  • October 6, 2011 at 10:44 am

    #1391324

    Also, is there anything I can do about the 'Latch'?

  • October 6, 2011 at 11:48 am

    #1391357

    AER (10/6/2011)

    This is a Live database. Is it not going to affect performance?

    Yes, it will affect performance because it needs to read the database and move pages around. It will not block users, but it will probably slow things down and might be noticable for your end users.

    What noone has asked yet - is do you really need to shrink the data file? Are you running out of disk space and need it for something else? Do you expect the system to grow much in the next 6 months and reuse a good portion of that space?

    If you are not pressed for space and the system will grow over time to use that space - I would recommend not shrinking the files. If you know for sure that this unused space will never be used again - then I could see shrinking the file.

    When you use the second parameter of SHRINKFILE - it will shrink to that point unless it requires more space than your are trying to shrink to.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • October 6, 2011 at 12:00 pm

    #1391369

    AER (10/6/2011)

    This is a Live database. Is it not going to affect performance?

    Of course it will, badly. Both in terms of the IO that it's doing and in terms of the resultant fragmentation that it will cause.

    Also, is there anything I can do about the 'Latch'?

    Improve the throughput and latency of your IO subsystem. Other than that, not much.

    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

  • October 6, 2011 at 12:43 pm

    #1391411

    Unfortunately I cannot do it because this is a client's machine and even more; it is outside US.

    And yes, Jeffrey, there is a space limitation issue with this Server.

    I can only advise the client on the space and system improvements; but cannot do much more.

    I really appreciate everybody's help and cooperation. It was a real pleausure talking to all of you.

    I guess I will hear from you in the next blogs.

    Thanks a lot

    Alex

Viewing 15 posts - 16 through 30 (of 34 total)

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