Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Posts
    5

    Unanswered: Too much table space reservation

    OS: Windows 2000 Advance Server
    DB: SQL 7 Enterprise Version

    Hi,

    I'm facing some weird problem, my goal was to clean old database records and free some space to the operating system, this is what append:

    1
    After delete the records from the database, execute again the sp_spaceused, the Data for one table was 1.2Gb and the unused space was about 4Gb.
    2
    I've export all the records (with DTS) to another table, delete the original and renamed the new, which reduce the unused space to Kbs. Everything look fine...
    3
    After some hours the of activity i execute again the sp_spaceused and the reservation parameter was about 6Gb to that table! The unused parameter looked fine.

    Question:
    How can i free that alocated space? Is it normal for this kind of behavior?

    Thanks for all the help!
    Diogo Santos

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Run SP_SPACEUSED @UPDATEUSAGE='TRUE'
    And also rebuild the indexes on that table which will correct the issues.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Apr 2003
    Posts
    5
    Hi, thanks for the replay!

    We don't have any indexes in this tables, the table is only used store historical information, for example for 1000 inserts we have 1 read.

    Is there any advantage creating indexes for this case ?

    Regarding the update command, i've also tried yesterday but nothing append.

    Thanks!
    Diogo Santos

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Even though the table have 1 read, index will help to fetch results and for better performance. Its upto you on the current performance for that table.

    But for space information SP_SPACEUSED is the best bet and use updateusage to get optimum information from this SP.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    Did you look into using DBCC SHRINKDATABASE? Look it up in BOL or read about it here, it might help you ->

    http://msdn.microsoft.com/library/de..._dbcc_3pd1.asp
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •