Database shrinking - Can't release unused space!! (SQLSERVER2000)

Discussion in 'Databases' started by entrytestsc, Sep 24, 2006.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. I can't seem to be able to release the space being used in my DB. For example, I have a table,
    Table: Member_Test_Question
    Rows:467220
    reserved: 224464 KB
    data: 49096 KB
    index_size: 22000 KB
    unused:153368 KB

    Backup and Shrink on the DiscASP control panel don't seem to lessen this unused space at all.

    Any help will be GREATLY appreciated!!
     
  2. JorgeR

    JorgeR DiscountASP.NET Staff

    entrytestsc

    Can you please give more details on how the table is setup. do you have a cluster index or proper indexes on the table? if so, have you tried reindexing the tables and then shrinking the database. what is the database name so that we can see what you have for the table as well


    junior

    DiscountASP.NET

    www.DiscountASP.NET
     
  3. The Database Name isDB_161362_q


    I can't take a look at the physical structure right now, but can send that through later. If you can see our DB, please take a look.


    Thanks in advance.
     
  4. Bruce

    Bruce DiscountASP.NET Staff

    wecan't see your database content. please post the configuration when you have it.

    Bruce

    DiscountASP.NET
    www.DiscountASP.NET
     
  5. Here is the script for the table and indexes:



    CREATE TABLE [dbo].[Member_Test_Question] (
    [member_test_id] [int] NOT NULL ,
    [member_id] [int] NOT NULL ,
    [test_id] [int] NOT NULL ,
    [question_id] [int] NOT NULL ,
    [display_question_num] [int] NULL ,
    [question_num] [int] NULL ,
    [question_response_ind] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [question_response_code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [marked_for_review_ind] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [record_entry_date] [smalldatetime] NULL ,
    [last_update_date] [smalldatetime] NULL
    ) ON [PRIMARY]
    GO


    ALTER TABLE [dbo].[Member_Test_Question] WITH NOCHECK ADD
    PRIMARY KEY CLUSTERED
    (
    [member_test_id],
    [member_id],
    [test_id],
    [question_id]
    ) ON [PRIMARY]
    GO


    ALTER TABLE [dbo].[Member_Test_Question] ADD
    CONSTRAINT [DF__Member_Te__recor__29221CFB] DEFAULT (getutcdate()) FOR [record_entry_date],
    CONSTRAINT [DF__Member_Te__last___2A164134] DEFAULT (null) FOR [last_update_date]
    GO


    CREATE UNIQUE INDEX [XAK1Member_Test_Question] ON [dbo].[Member_Test_Question]([member_test_id], [question_id]) ON [PRIMARY]
    GO


    ALTER TABLE [dbo].[Member_Test_Question] ADD
    FOREIGN KEY
    (
    [question_id]
    ) REFERENCES [dbo].[Question] (
    [question_id]
    ),
    FOREIGN KEY
    (
    [member_test_id],
    [member_id],
    [test_id]
    ) REFERENCES [dbo].[Member_Test] (
    [member_test_id],
    [member_id],
    [test_id]
    )
    GO
     
  6. JorgeR

    JorgeR DiscountASP.NET Staff

    we checked the database and see that your database was defragmented. We went I ahead and defragmented the database to show on how much of a difference optmization of the database is needed. if you notice, you went from 290 MB of used space to 80 MB. You can see if your DB is defragmented by running dbcc showcontig (BOL explains further )and then run dbcc dbreindex to reindex your database if it is defragmetned


    junior

    DiscountASP.NET

    www.DiscountASP.NET
     
  7. THANK YOU VERY MUCH!!! I really appreciate your assistance.


    Cheers.
     
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page