none
SQL db size reduces all of a sudden

    Question

  •   In my SQL 2000 database when I have checked the backup size it was around 1.6 GB but the next week suddenly the db size got reduced to around 500 MB ,not able to analyse as my data got loss or not and why it got reduced?

    Need help


    • Edited by Rapunzel-P Wednesday, January 01, 2014 8:15 AM
    Wednesday, January 01, 2014 8:15 AM

All replies

  • 1) Was the database shrank?

    2) Massive deletion was  performed


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 01, 2014 8:25 AM
  •   In my SQL 2000 database when I have checked the backup size it was around 1.6 GB but the next week suddenly the db size got reduced to around 500 MB ,not able to analyse as my data got loss or not and why it got reduced?

    Need help


    Hello,

    I expect it to be a normal behavior. Backup does not includes any space and only includes data and some transaction log if necessary.Did you performed any delete operation ,truncated any table ,moved data from your database.If so there must been empty space created which will not be included in backup but if you restore the backup it will take space equal to database for which backup was taken.

    SQL 2000 is not supported by Microsoft now I suggest you to upgrade your database to latest version


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, January 01, 2014 8:25 AM
  • No not performed any thing.

    There was sudden shrunk in db

    Wednesday, January 01, 2014 8:28 AM
  • k but need to know whether the data is lost or not not able to identify and why the size got reduced does it have any other specific reason.
    Wednesday, January 01, 2014 8:45 AM
  • k but need to know whether the data is lost or not not able to identify and why the size got reduced does it have any other specific reason.

    Hello,

    Can you paste output for below.I need to check if backup compression is enabled or not

    sp_configure 'show advanced options',1
    go
    reconfigure with override
    go
    sp_configure

    Nobody can tell whether there is data loss  unless one looks into your system.Did your user complained did you saw and data mismatch.I think you can best check it.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, January 01, 2014 8:59 AM
  • affinity mask -2.1E+09 2.15E+09 0 0
    allow updates 0 1 0 0
    awe enabled 0 1 0 0
    c2 audit mode 0 1 0 0
    cost threshold for parallelism 0 32767 5 5
    Cross DB Ownership Chaining 0 1 0 0
    cursor threshold -1 2.15E+09 -1 -1
    default full-text language 0 2.15E+09 1033 1033
    default language 0 9999 0 0
    fill factor (%) 0 100 0 0
    index create memory (KB) 704 2.15E+09 0 0
    lightweight pooling 0 1 0 0
    locks 5000 2.15E+09 0 0
    max degree of parallelism 0 32 0 0
    max server memory (MB) 4 2.15E+09 2.15E+09 2.15E+09
    max text repl size (B) 0 2.15E+09 65536 65536
    max worker threads 32 32767 255 255
    media retention 0 365 0 0
    min memory per query (KB) 512 2.15E+09 1024 1024
    min server memory (MB) 0 2.15E+09 0 0
    nested triggers 0 1 1 1
    network packet size (B) 512 32767 4096 4096
    open objects 0 2.15E+09 0 0
    priority boost 0 1 0 0
    query governor cost limit 0 2.15E+09 0 0
    query wait (s) -1 2.15E+09 -1 -1
    recovery interval (min) 0 32767 0 0
    remote access 0 1 1 1
    remote login timeout (s) 0 2.15E+09 20 20
    remote proc trans 0 1 0 0
    remote query timeout (s) 0 2.15E+09 0 0
    scan for startup procs 0 1 0 0
    set working set size 0 1 0 0
    show advanced options 0 1 1 1
    two digit year cutoff 1753 9999 2049 2049
    user connections 0 32767 0 0
    user options 0 32767 0 0

    Wednesday, January 01, 2014 10:23 AM
  • I am sorry I missed a point.Your SQl server is 2000 and compression was not introduced then.Apologies.

    What is current size of you database.how much free space is there in database.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, January 01, 2014 10:40 AM
  • The current db size is 653 MB

    Wednesday, January 01, 2014 11:00 AM
  • The current db size is 653 MB

    If your current Db size is 653 MB and backup is 500 MB i dont find any issue in this .If you are asking how my DB changed from 1.6 G to 653 GB No one can answer but you.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, January 01, 2014 11:41 AM