SQL Server 2005 backup doubles the size bak file

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)

SQL Server 2005 backup doubles the size bak file

Author

Topic

kimy_cool
Starting Member

5 Posts

Posted - 2010-07-14 : 02:47:44

Hi,

My db size on sql server 2005 is about 65gb and when i backup to disk, the bak size is usually 62gb until last Saturday when the bak size suddenly doubled to 134gb as well as the bak file. The schedule is set not to append on an existing file but creates a new backup file daily of the database. It looks like the backup has doubled my database size. How can i revert back the changes and remove/delete the extra 60+ gb from my live database without affecting its integrity? Thank you in advance

-Kim

----------
Custom web applications
http://www.imediamx.com

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-07-14 : 03:36:51

I think you should check the number of backup sets contained in the file to confirm that it has not been appended to. You can do this by using the RESTORE HEADERONLY command e.g.

RESTORE HEADERONLY FROM DISK = 'h:\backups\mybackupfile.bak'

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images and binary data from SQL Server, Oracle, DB2, PostgreSQL, SQLite, and Firebird databases.
SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily

kimy_cool
Starting Member

5 Posts

Posted - 2010-07-14 : 04:24:24

Thx ray....let me try out the headeronly restore then i revert back. in the meantime, is there anything wrong with my tsql backup code below which i used to do the backup?

The TSQL code below...kindly advise

BACKUP DATABASE [dbname] TO DISK = N'F:\Backup\dbname_201007141108.bak' WITH NOFORMAT, NOINIT, NAME = N'dbname_backup_20100714110838', SKIP, REWIND, NOUNLOAD, STATS = 10

----------
Custom web applications
http://www.imediamx.com

Kristen
Test

22859 Posts

Posted - 2010-07-14 : 05:03:00

Well ... if you happen to use the same name, twice, that command string will APPEND.

personally I don't bother with all those Tape-related commands

Here's what we use - the commented out bits were just what I cut & pasted from the DOCs when I first set it up, so you can see what I choose to leave out too


		BACKUP DATABASE @strDB 
			TO DISK = @BackupFile
		WITH	
--			BLOCKSIZE = { blocksize | @blocksize_variable },
			DESCRIPTION = @Description,
--			DIFFERENTIAL,
--			EXPIREDATE = { date | @date_var } | RETAINDAYS = { days | @days_var },
--			PASSWORD = { password | @password_variable }
--			FORMAT | NOFORMAT,
--			{ INIT | NOINIT },
			MEDIADESCRIPTION = @Description, 
			MEDIANAME = @MediaName,
--			MEDIAPASSWORD = { mediapassword | @mediapassword_variable },
			NAME = @Name,
--			{ NOSKIP | SKIP },
--			{ NOREWIND | REWIND },
--			{ NOUNLOAD | UNLOAD },
--			RESTART,
			STATS = 10

I don't know of any way of preventing APPENDS (we generate filename from current date/time, so its very unlikely that two backups could be generated the same second)

"the bak size suddenly doubled" sounds awfully like appended backup to me

kimy_cool
Starting Member

5 Posts

Posted - 2010-07-14 : 05:49:01

Ray,
I tried the database headeronly command and it executed successfully. I had dropped the database earlier on. What should i look out for?

Kristen,
Thx for the code but now how do i get back my original file size which should be 62gb instead of 132gb for both the mdf and bak files? Is there a way i can 'cut' the latest header from which looks like it has been appended on the bak file?

----------
Custom web applications
http://www.imediamx.com

Kristen
Test

22859 Posts

Posted - 2010-07-14 : 05:59:46

Ah ... the MDF file has doubled too? That is definitely not a double-appended backup file, something has grown the database.

Did you do an Index Rebuild? Large data import? data reorganise? (or something like that) which you have not done regularly before?

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-07-14 : 08:03:09

RESTORE HEADERONLY returns one row for each backup set stored in the backup file. How many rows were returned?

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images and binary data from SQL Server, Oracle, DB2, PostgreSQL, SQLite, and Firebird databases.
SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily

kimy_cool
Starting Member

5 Posts

Posted - 2010-07-14 : 09:20:52

Ray,
Only 1row was returned

Kristen,
Append also doubles the mdf file not only the bak file

----------
Custom web applications
http://www.imediamx.com

Kristen
Test

22859 Posts

Posted - 2010-07-14 : 10:23:31

"Append also doubles the mdf file not only the bak file"

I'm doubting that. If you backup to an existing file then either that file will be overwritten (e.g. if you use INIT) or that backup file will "double" in size (i.e. backup is Appended).

Backup will make no substantive difference to the size of the MDF.

For the MDF to double something has to have extended it. Either a massive import, or someone copying table(s) into new / temporary tables, or Index rebuild using lots of "elbow" room to tidy up the indexes.

In SSMS right click the database, choose properties, whats the "SIZE" and "AVAILABLE SPACE"?

If available space is 50% of the total size then something expanded the database, and you could shrink it back to its original size - but there is no point doing that if whatever expanded it is going to run again in a couple of days time ...

Maybe something has ONLY expanded the LDF file? e.g. a massive delete.

What's the size of the MDF and LDF files?

kimy_cool
Starting Member

5 Posts

Posted - 2010-07-15 : 00:52:46

Kristen, you are actually right. The append on the bak file did not double the mdf. The size is 257,452.52mb and space available is 894.76mb slightly less than 50%. The MDF is 133gb and LDF is 130gb

----------
Custom web applications
http://www.imediamx.com

Kristen
Test

22859 Posts

Posted - 2010-07-15 : 02:23:49

OK ...is your MDF file full? or half full? If its full then the database grew (spectacularly!), if its half full there was probably some work-in-progress (and index rebuild maybe, or some other data reorganisation?)

If it was a one-off accidental growth you may want to shrink the files back to their "normal maximum" size, if not - or if you have plenty of disk space - leave them alone as repeated shrink/grow just fragments the files, table indexes, etc.

Find free space:

In SSMS right click database, Tasks, Shrink, Files - do NOT actually shrink the file unless you mean to!

Toggling the FILE TYPE between DATA and LOG will show the used/free space in each. If you have recently done a Log Backup then the LOG should be "all" free space.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources