• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 48
  • Last Modified:

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

Hi,

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.

Thanks
0
java novice
Asked:
java novice
  • 4
  • 2
  • 2
  • +3
4 Solutions
 
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.
0
 
java 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..
0
 
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.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
java 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...
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
This should be a good start: https://www.red-gate.com/simple-talk/sql/learn-sql-server/managing-transaction-logs-in-sql-server/

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.
0
 
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.
0
 
java 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...
0
 
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
0
 
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

0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

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.

SELECT 
    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
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

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 
GO 

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.
0
 
java noviceAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now