April 29, 2011 at 6:35 am
Hi,
I am using SQL server 2008 Enterprise edition with SP2 on production environment.
Database files are restricted file growth up to 5 GB, and simple recovery model.
Now database size is 4GB and free space 1GB,
Backup size 1 GB, by using backup compression features for taking database backup size.
Daily backup size has been increased by 1 MB, what could be reason daily 1 MB size increased even database setting restricted file growth option? daily data inserting more than 48 records for 6 tables only (each table having 500 column getting inserted), all are numeric data. (data example like 1456.45)
Thanks
ananda
April 29, 2011 at 6:42 am
The backup only contains the data pages that are used in the db. So if you had more data, you have more pages and the backup gets bigger. The backup size has nothing to do with the restrictions you put on the data and log files.
April 29, 2011 at 6:56 am
To add to the above, you can run this query to see exactly how much data is being backed up for your database.
SELECT database_name, backup_start_date, backup_size, compressed_backup_size
FROM msdb..backupset
WHERE type = 'D'
ORDER BY database_name, backup_start_date DESC
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
April 29, 2011 at 10:28 am
Database filegroup and files structure is ?
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 29, 2011 at 10:33 am
Syed Jahanzaib Bin hassan (4/29/2011)
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
http://www.aureus-salah.com[/quote%5D
How could that possibly have any effect?
April 29, 2011 at 11:14 am
Yes,because he doesnt mention how many files and he mentioned files are 5GB restricted growth its mean each file have restriction of 5GB ,database size is cumulative size of files in a filegroups and this question is not for you,this is for the initiator
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 29, 2011 at 11:18 am
Syed Jahanzaib Bin hassan (4/29/2011)
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
http://www.aureus-salah.com[/quote%5D
Thanks for the B**ch slap. It's really hard to keep awake today.
BTW the question is about backup size, which AFAIK has nothing do to with the files in the DB but the data in those files.
I expected you knew something new I could have learned, but my hopes are crushed once again. :hehe:
May 1, 2011 at 10:34 pm
Syed Jahanzaib Bin hassan (4/29/2011)
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
http://www.aureus-salah.com[/quote%5D
I don;t understand what additional input you could provide if you know the file and filegroups structure..
Because the user is not concerned about database size rather he is more concerned about database backup size.
Database backup contains all used pages and not the free pages with in the database. Even if i have 10 file of 10 GB each and database size is nearly 110 GB, it has nothing to do with the size of my database backup. The backup might only a few GB. So, it is all about how much is your used space within your database.
So, i totally agree with Ninja, that the backup size will grow over a period of time if your used space within the files increase. And it is always directly proportional to the used space within your files.
Regards..
May 2, 2011 at 2:44 am
Database backup size depend on the total files size,he has mentioned each file have 5GB restricted size,how many files are there,do you people know this, if 1 data file and 5GB restricted size and if size increase from 5GB question is correct,if 2 files are there and size increase from 5GB then total size can be 10GB thats why i was asking
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 2, 2011 at 3:19 am
Syed Jahanzaib Bin hassan (5/2/2011)
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
http://www.aureus-salah.com[/quote%5D
Hi,
Database have only one mdf file (5GB size), ldf file ( 6MB) in simple recovery mode.
As you told datafile size is 5GB then backup size also 5GB, that is correct.
what I am asking backup size increased on daily upto 1 MB.? what could be reason?
Thanks
ananda
May 2, 2011 at 4:04 am
As stated by the other guys, a backup file only contains data pages that are actually used. If you run the following script:
CREATE DATABASE sizetest ON PRIMARY(NAME = 'sizetest_data', SIZE = 100MB, FILENAME = 'e:\temp\sizetest.mdf')
LOG ON (NAME = 'sizetest_log', SIZE = 1MB, FILENAME = 'e:\temp\sizetest.ldf')
GO
BACKUP DATABASE sizetest TO DISK = 'e:\temp\sizetest.bak'
GO
this creates a database 100 MB in size. However, the backup file is only 2MB in size, since the database contains only metadata at this point.
Likewise, in your database, if it's currently not full, the full backup will only contain data pages that are used. As you add data into the tables, more data pages get used, and the backup file increases in size accordingly.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
May 2, 2011 at 5:21 am
Ray Mond (5/2/2011)
As stated by the other guys, a backup file only contains data pages that are actually used. If you run the following script:
CREATE DATABASE sizetest ON PRIMARY(NAME = 'sizetest_data', SIZE = 100MB, FILENAME = 'e:\temp\sizetest.mdf')LOG ON (NAME = 'sizetest_log', SIZE = 1MB, FILENAME = 'e:\temp\sizetest.ldf')
GO
BACKUP DATABASE sizetest TO DISK = 'e:\temp\sizetest.bak'
GO
this creates a database 100 MB in size. However, the backup file is only 2MB in size, since the database contains only metadata at this point.
Likewise, in your database, if it's currently not full, the full backup will only contain data pages that are used. As you add data into the tables, more data pages get used, and the backup file increases in size accordingly.
+1.
May 2, 2011 at 6:27 am
this creates a database 100 MB in size. However, the backup file is only 2MB in size, since the database contains only metadata at this point
Backup option perform with compression thats why
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 2, 2011 at 6:32 am
Did you READ THE SCRIPT? There's no compression in it.
For the 10TH TIME already, stop blabbering answers just for the sake of getting points and links to your useless blog, which BTW Microsoft shoud surely sue you over since it's their content for the vast majority of you post.
May 2, 2011 at 7:24 am
Syed Jahanzaib Bin hassan (5/2/2011)
this creates a database 100 MB in size. However, the backup file is only 2MB in size, since the database contains only metadata at this point
Backup option perform with compression thats why
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
http://www.aureus-salah.com[/quote%5D
At least talk sensibly, why are you writing comments without validating your answers. It is not going to help the community neither it will be of any help for you. At least don't mislead the community users. I would sincerely request Gail and steve to strongly respond on his comments.
Regards
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply