Database Backup Size - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Database Backup Size

Author

Topic

hitesh6221
Starting Member

28 Posts

Posted - 2008-08-09 : 03:38:54

Hi Frnds,

I am facing problem in taking backup og my live database,
I am taking backup and it comes upto 34 GB but while I am checking size from EM and then and opening property of file it has only 3 GB data,
Now I think this is problem of T-Log and it comes with this back up so any one has solution for this problem

Regards,
Hitesh Soni

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-08-09 : 04:23:00

Check the size of the transaction log file.
Are you in full or bulk logged recovery mode?
Do you have log backups running?
What's the recoverability requirements for this database should the server fail?

--
Gail Shaw
SQL Server MVP

hitesh6221
Starting Member

28 Posts

Posted - 2008-08-09 : 07:57:30

Check the size of the transaction log file. 890 MB
Are you in full or bulk logged recovery mode? Full
Do you have log backups running? No

Regards,
Hitesh Soni

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-09 : 20:35:44

Did you backup to a new file or append to existing file?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-08-10 : 03:45:38

Aside from your backups, if you are using full recovery, you need log backups running or your log file will grow without bound. Depending on the acceptable data loss in the case of a disaster, either switch the DB to simple recovery or schedule log backups.

For background info - [url]http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/[/url]

--
Gail Shaw
SQL Server MVP

hitesh6221
Starting Member

28 Posts

Posted - 2008-08-11 : 01:35:50

Ok,
So is it possible to take log backup before full backup so backup size would reduce?

Regards,
Hitesh Soni

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-08-11 : 03:44:34

Should do a bit, though the log's only 890 MB and so cannot account for the 34GB. See rmiao's earlier question.

--
Gail Shaw
SQL Server MVP

hitesh6221
Starting Member

28 Posts

Posted - 2008-08-11 : 03:52:39

I have created one plan in tis plan I have wirtten script and it will overwrite last days file
"Backup database <DBName> to disk = '<Location>'"
and I have tried that Frist taken Log back and then I am taking back up of Full DB it is comming only 3GB.

Regards,
Hitesh Soni

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-11 : 23:09:21

Do you use 'with init' option in backup statement? Log file with size of 890mb shouldn't generate 31gb logs as Gail said.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources