Post reply

Backup

  • May 23, 2016 at 8:13 am

    #326601

    I have a DB that is 1.81TB. Normally when a FULL backup is done, the .bak file is over 1TB. However, when I do a Full now it is just @530GB which is the size of the .mdf file. It seems it's not backing up the .NDF file (1.3TB). I have ran a DBCC CHECKDB and it was successful.

    Any idea?

  • May 23, 2016 at 8:18 am

    #1880779

    Shelley Allen (5/23/2016)

    I have a DB that is 1.81TB. Normally when a FULL backup is done, the .bak file is over 1TB. However, when I do a Full now it is just @530GB which is the size of the .mdf file. It seems it's not backing up the .NDF file (1.3TB). I have ran a DBCC CHECKDB and it was successful.

    Any idea?

    1) Someone enabled backup compression and that is being done now.

    2) You can see what is in the backup using RESTORE FILELISTONLY

    3) Perhaps you normally have a lot of stuff in the tlog that is also being backed up with the FULL and for the smaller one the tlog was mostly empty?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • May 23, 2016 at 8:37 am

    #1880785

    Thank you for your reply. The compression is set to 0 so it is not enabled.

  • May 23, 2016 at 9:01 pm

    #1880936

    Shelley Allen (5/23/2016)

    I have a DB that is 1.81TB. Normally when a FULL backup is done, the .bak file is over 1TB. However, when I do a Full now it is just @530GB which is the size of the .mdf file. It seems it's not backing up the .NDF file (1.3TB). I have ran a DBCC CHECKDB and it was successful.

    Any idea?

    The size of the mdf is not necessary the size of the internal usage.

    Have you run the SSMS graphical report for internal space utilization? Maybe someone deleted bunch of stuff and you ended with lot of free space.

  • May 24, 2016 at 1:17 am

    #1880950

    Hi,

    you can use this query to look into your database, how much storage is really needed:

    USE yourDatabase

    GO

    sp_spaceused

    The value "unallocated space" is the free space in the database, and is not "backuped".

    Or just right click on the database in the SSMS and take a look at "Space Available" on the General Tab.

    Kind regards,

    Andreas

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply