none
HOW TO REDUCE THE SIZE OF SQL SERVER DATABASE. RRS feed

  • Question

  • I have patch server on which the database is SQL server 2000.

     

    The Database size is 27GB and I want to reduce the size of DB by deleting all the records and keeping only year 2007 records.

    Please advise me how to reduce the size of the DB.

     

    If I need to delete any record, please let me know how to delete it.

    If their is any Query for it, please send the Query.

     

    The DB contains total of 11 tables.

     

    Thanks to All,

     

    Mohammed Muzaffar.

     

     

    Sunday, November 25, 2007 6:15 AM

Answers

  • You may first check the log file size. If logfile is consuming more space shrink the file and take full backup. Also run sp_spaceused and check the dataabse is using space properly...

     

    Run

    USE yourdb;
    GO
    EXEC sp_spaceused @updateusage = N'TRUE';
    GO
     
    Note : Before deleting keep a full backup.
     
    Madhu
    Tuesday, November 27, 2007 2:52 AM
    Moderator
  • This is really a pretty open question.  I mean, if you have a date column on all tables, then you could just:

     

    delete from table

    where dateValue < '20070101'

     

    But (hopefully) you don't have a date value like this, but rather on one table, and you have relational integrity constraints set up between tables, so you will need to create a chain of statements:

     

    delete from otherTable

    where  not exists (select *

                               from   table

                               where otherTable.tableKey = table.tableKey

                                  and  table.dateValue  < '20070101')

     

    And this might take multiple layers of correlated subqueries, depending on your structures.  Once you have deleted the data, dump the logs, and look into the DBCC SHRINKDATABASE statement.  That will be the command you will likely need to use to shrink the files and reclaim some space. 

     

    But, don't reclaim all of the space, becasue then you wll have to keep readding the space back, which is costly.  You want the size of your database to be the largest the data could logically get in a reasonable period of time.

    Tuesday, November 27, 2007 6:17 AM
    Moderator

All replies

  • It would be helpful if you could provide table CREATE scripts, a description of the relationships between tables, and an indication as to which columns should be used to determine which rows should be deleted (or archiving - have you considered this?).

     

    Sample data (in the form of INSERT scripts) would also be welcome.

     

    Chris

    Monday, November 26, 2007 10:51 PM
  • You may first check the log file size. If logfile is consuming more space shrink the file and take full backup. Also run sp_spaceused and check the dataabse is using space properly...

     

    Run

    USE yourdb;
    GO
    EXEC sp_spaceused @updateusage = N'TRUE';
    GO
     
    Note : Before deleting keep a full backup.
     
    Madhu
    Tuesday, November 27, 2007 2:52 AM
    Moderator
  • This is really a pretty open question.  I mean, if you have a date column on all tables, then you could just:

     

    delete from table

    where dateValue < '20070101'

     

    But (hopefully) you don't have a date value like this, but rather on one table, and you have relational integrity constraints set up between tables, so you will need to create a chain of statements:

     

    delete from otherTable

    where  not exists (select *

                               from   table

                               where otherTable.tableKey = table.tableKey

                                  and  table.dateValue  < '20070101')

     

    And this might take multiple layers of correlated subqueries, depending on your structures.  Once you have deleted the data, dump the logs, and look into the DBCC SHRINKDATABASE statement.  That will be the command you will likely need to use to shrink the files and reclaim some space. 

     

    But, don't reclaim all of the space, becasue then you wll have to keep readding the space back, which is costly.  You want the size of your database to be the largest the data could logically get in a reasonable period of time.

    Tuesday, November 27, 2007 6:17 AM
    Moderator