Database backup size is huge whereas there are only 10k records in all the tables.


This database was taken of a different application which we are using now by cleaning it

Database size after backup is around 10GB whereas only 3-4 tables are having records around 10,000 only.Unable to understand why is so huge,

Please help in sharing what could be the possible reasons.

Tech NoviceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Manoj SinghSoftware EngineerCommented:
Think of database as a box. If u take something out it will not shrink , instead allow more space to put new record. U can create new database of small size and import old database to see the difference.
Tech NoviceAuthor Commented:
Shrink done but still no success,i need to understand why the space is being taken... can't possible as there are over 400 tables having some reference data (100-200) records..
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
It looks like your database is in FULL recovery mode, and no log backups are being taken to clear the logs.

Every transaction is tracked in SQL Server in what is known as the transaction log. This helps in recovering a database from failure. In case of a database with FULL recovery, one needs to perform regular log backups in order for the transaction logs to "recycle".

I would recommend some reading and research on transaction logs and recovery models.

While you setup your backup and recovery strategy, you can evaluate switching to the SIMPLE recovery mode, provided this is not a mission critical database and you can afford to loose data in case of a failure.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Tech NoviceAuthor Commented:
IS there any way out by which i can delete the transaction log...

Could you please share some link wherein i can get the significant information...
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
This should be a good start:

By the way, you do not want to delete the log. Better take a log backup and then shrink the log file. Once you know which recovery model works best for you, set the appropriate one.
Vitor MontalvãoMSSQL Senior EngineerCommented:
It looks like you're taking a transaction log backup and not a full database backup.
But as stated by Nakul, you should start by creating a database backup plan to reduced the transaction log size.
Tech NoviceAuthor Commented:
Better take a log backup and then shrink the log file

Tried with the same approach and through ssms shrinked the log file ,but there was only a little difference i found.
Is there any way in which i can reduce the log file size to 80%? if yes then pls share command to do it...
John TsioumprisSoftware & Systems EngineerCommented:
if you really want to find out what is the actual size of your db ...just create a new one and copy the tables from ome database to another...this should give you the clear idea...also remember that a database is not only data but slso keys indexrs  etc
Vitor MontalvãoMSSQL Senior EngineerCommented:
You don't need to create a new database to know the size of the database.
SSMS offers Reports that can give you that information. Or run the following command to retrieve that information:
sp_helpdb 'databaseName'

Open in new window

Vikas GargAssociate Principal EngineerCommented:

If the data Bakcup size is huge there may be multiple reason for that ...

1. It contains more data in table (Record may be less but of high data type)
For this you can run query to know table size.

    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
    sys.tables t
    sys.indexes i ON t.OBJECT_ID = i.object_id
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    sys.allocation_units a ON p.partition_id = a.container_id
    sys.schemas s ON t.schema_id = s.schema_id
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
    t.Name, s.Name, p.Rows

Open in new window

2. Log file size is too large .
know the size of log and data file using this query
SELECT file_id, name, type_desc, physical_name, size, max_size  
FROM sys.database_files 

Open in new window

3. Data was removed from database but space still occupied and not freed to the File system.
For this Shrink file can be used.
Tech NoviceAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.