none
Reducing the capacity of SQL Server RRS feed

  • Question

  • Hi,

    I am using SQL Server to store images in MyIdentityBinary table. I have been storing images in this table for several years but now facing a problem to increase the capacity of MS SQL database(MyIdentityBinary  table). Please let me know a way to reduce its capacity by doing any maintenance work such as running any script to archive data more than one year or some specific date.

    Please let me know if there are some other alternatives too.

    Thanks and Regards,

    Pratham 

    Monday, January 22, 2018 4:54 AM

All replies

  • Hi Pratham,

    Capacity - Do you mean compute (Memory and CPU) or Storage here?

    If it is storage

    1.You can delete older data based on your company data retention policy using delete from query.

    2.If you have Enterprise edition you can use table compression to compress the table data and get some space.

    3.If you have stringent backup policy created you can delete older data monthly/quarterly from current table in this way you can save save some compute as well.  

    If it is compute

    1.Keep the required data in current table say of 1 month and move the rest of data to other table as archive data.

    2.It is always better to create cluster index on table for better performance and afterwords index maintenance is required based on fragmentation of indexes.

    Regards,

    AKash Pawar

    Monday, January 22, 2018 6:39 AM
  • Hi Akash,

    Thanks for your response.

    This is increasing Database storage capacity which has crossed 150 GB (running out of server capacity). Also processing time to save one image is very high which is around 4 mins now. So basically I need a housekeeping query to reduce the storage capacity of the database by archiving the data older than some constraint of time period. Also I need a way to reduce the processing time to store one image. I am using MS SQL SERVER 2008 R2.

    Also please let me know if there is some other way to achieve this with same version of SQL Server.

    Thanks and Regards,

    Pratham Jain

    Monday, January 22, 2018 6:05 PM
  • Hi Pratham,

     

    Method 1.

    You can delete older data based on your company data retention policy using delete from below query.
    Note: Below query will delete the data permanently so be careful or take backup before executing.

    DELETE FROM table WHERE columnname='deleteCriteria' 

    Method 2.

    Create the archive table and move the data from current table to archive using insert query or export/import option in sql. Once data moved to archive table you can delete from current table; while deleting make sure you delete data in batches else it will create a lock on table for longer and application wont be able to access table.

    --Insert Query
    INSERT INTO table2
    SELECT * FROM table1
    WHERE condition;

    --Delete query
    DELETE FROM table WHERE columnname='deleteCriteria'

    --Script to delete rows in batch to avoid long term lock on table
    --Start of script
    DECLARE @continue INT
    DECLARE @rowcount INT

    SET @continue = 1

    WHILE @continue = 1
    BEGIN
        PRINT GETDATE()
        SET ROWCOUNT 100000 --Try not to increase beyond 1000000
        BEGIN TRANSACTION
        DELETE FROM some_big_table WHERE  ... --query to delete rows from table
        SET @rowcount = @@rowcount 
        COMMIT
        PRINT GETDATE()
        IF @rowcount = 0
        BEGIN
            SET @continue = 0
        END
    END

    --End of script


    If your query is answered kindly click "Mark as Answer" and click "Unmark as Answer" if not.

    Thanks,
    AKash Pawar

    Tuesday, January 23, 2018 5:43 AM