problem with log backup

Question

problem with log backup

Ganeshkumar005

Ten Centuries

Points: 1140
More actions
June 19, 2012 at 11:42 pm

#392560

I have logshipping here in 2005. After reindexing i took full backup. After that the very first schedule of logshipping transaction log backup job ceased to finish properly, it was running endlessly. Although log file size was 22 gb only on drive (and 10 gb data internally), the backup job consumed 73 gb space on backup drive and failed with error insufficient space.
Later, i am taking the log bakup manually by using the below command to a folder where full backup file exists, it is consuming all 31.5 space there and failing with insufficient error space.
This is very strange to me. Can you please help?
I am trying to shrink the log file now so as to release the internal 12 GB free space to drive.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

William Melcher SSC-Addicted Points: 425 More actions · Answer 1

Offhand, it sounds like you either have an open transaction running somewhere or you have a maintenance plan that is trying to do maintenace of your database in the background in addition to what you are doing. You could try running the following to see if there are any open transactions:

DBCC OPENTRAN;

You can also try shrinking the log file using:

DBCC SHRINKFILE ({log file name}, {target size in MB});

Reindexing your database can make your log file grow a lot and taking log backups inbetween some of your reindexing can help keep its size under control.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

ganeshkumar005 (6/19/2012)
Although log file size was 22 gb only on drive (and 10 gb data internally), the backup job consumed 73 gb space on backup drive and failed with error insufficient space.

Are you sure writing to a new backup file, or an existing backup file using WITH INIT, or an existing file without using WITH INIT? If it's the latter, you're filling up your disk with old backups and not overwriting them. Run RESTORE HEADERONLY FROM DISK = 'MyDrive\MyFolder\MyFile.TRN' to see what other backups you have in the same backup file.

John

Edit - got the quote codes wrong

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 3

.

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

Ganeshkumar005 Ten Centuries Points: 1140 More actions · Answer 4

Thanks guys. I was busy in other things but this is still there and need to fix it. See, logshipping log backup job uses below command and it fails with the unsufficient space error:

BACKUP LOG [DBNAME] TO DISK = N'D:\DBNAME_TRN\XYZ\DBNAME_20120803042046.trn' WITH NOFORMAT, INIT, NOSKIP, REWIND, NOUNLOAD, STATS = 10

I try to do it manually by issuing below command but its still the same:

backup log DBNAME to disk = 'path\xyz' with init

This could be a bug, who knows. I will try again to get the backup anyhow by adding\removing various options quoted above.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Well, if this hasn't been working for two months, chances are that you really do have a log file that's so large that you can't fit the backup on your disk. If you run DBCC SQLPERF(logspace), what information is returned for the database in question?

John

Paul Randal One Orange Chip Points: 29544 More actions · Answer 6

This sounds like you've done a minimally-logged set of index rebuilds of large indexes in the bulk_logged recovery model.

After this, the next log backup will back up all the log and then all the data extents changed by the minimally-logged rebuilds. The resulting log backup will essentially be the same size as if you'd performed the operations in the full recovery model - i.e. the size of the indexes involved.

If this is NOT the case, then you've hit a bug - as what I describe above is the only time a log backup can be larger than the log itself.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Paul, there's also the possibility that he's backing up to the same file without using WITH INIT, which is why I asked him to run RESTORE HEADERONLY. This is from two months ago now, so I don't know whether that original file even still exists on his disk.

John

Paul Randal One Orange Chip Points: 29544 More actions · Answer 8

But he said the backup job used 73GB when his log file was only 22GB - even not using WITH INIT wouldn't cause that - unless his backup job took multiple backups - so worth checking.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

Definitely worth checking, yes. When he said "the job consumed 73GB", I wanted to check that it wasn't just the case that the file ended up at 73GB due to several NOINT backups having been made to the same file.

John

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Just a couple of questions:

Recovery model of database while index rebuild was running.

Size of database (not t-log).