Error ruunning log backup

Hi,

We're using sql server 2008 and are getting the following error when backing up our log

A nonrecoverable I/O error occurred on file "B:\Backup Log\Smyths_Toys_Log_Backup.fbk:" 112(failed to retrieve text for this error.

Our database is 970 GB, log file 150 GB with 28% free.

The drive we're trying to save the log backup to has 230 Gb free

We've deleted the old log backup but it still errors

DBCC showed no errors in the db and we're able to run full db backups. Just erroring on the log.

Would anyone know how we can get our log file backed up?

Thanks
smythsitAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rajkumar GsSoftware EngineerCommented:
Are you sure backup path you specified exists ?

Check whether this folder exists
B:\Backup Log

Raj
0
smythsitAuthor Commented:
Hi Raj,

Yes this folder exists. The log backup was running fine to that location and just started erroring a few days ago.

Thanks.
0
Rajkumar GsSoftware EngineerCommented:
What's that B drive ? Is it network mappath location ?

Raj
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

smythsitAuthor Commented:
It's a local drive on a SAN
0
RiteshShahCommented:
what is the recovery model of your database? is it SIMPLE?
0
smythsitAuthor Commented:
No it's Full
0
RiteshShahCommented:
are you doing it from any other third party software? as I can see the extension .FBK
0
r_panosCommented:
Repeat the backup and:

1. post the SQL Server error log file
2. check your system event log for IDs 29, 30, 31, 34, 35, 37
0
smythsitAuthor Commented:
No we're just running a sql agent job with the following T-SQL

backup LOG [Smyths Toys] to Smyths_toys_Log_Backup with init
0
r_panosCommented:
And repost the full event ID (it seems the is truncated, there must be some text after (failed to retrieve text for this error.)
0
smythsitAuthor Commented:
Here's the full error message

xecuted as user: SMYTHS\sqlserver. A nonrecoverable I/O error occurred on file "B:\Backup Log\Smyths_Toys_Log_Backup.fbk:" 112(failed to retrieve text for this error. Reason: 15105). [SQLSTATE 42000] (Error 3271)  BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.
0
Rajkumar GsSoftware EngineerCommented:
Are you sure the backup device is accessible now. ?

Raj
0
Rajkumar GsSoftware EngineerCommented:
http://www.sqlcoffee.com/Troubleshooting041.htm

May be issue with non-availability of freespace ?

Raj
0
smythsitAuthor Commented:
It's not a lack of free space anyway. Tryed backing up the log to another local drive with 547 GB free space and got the same error.
0
smythsitAuthor Commented:
I've tried the backup a few times and have attached the log
Log.txt
0
r_panosCommented:
On which volume do the SQL Server DBs reside? What is the overal size and free space of that volume?
0
smythsitAuthor Commented:
There are 16 files for the db. ! file on each drive below from F to U



Drive      Drive Size      Space Used      Space Free      % Free
A:\      716798.62      669690.62      47108.00      6.57
B:\      425557.74      190581.74      234976.00      55.22
C:\      139234.00      70000.00      69234.00      49.72
F:\      102398.65      73262.65      29136.00      28.45
G:\      102398.65      49245.65      53153.00      51.91
H:\      102398.65      49245.65      53153.00      51.91
I:\      102398.65      49245.65      53153.00      51.91
J:\      102398.65      49245.65      53153.00      51.91
K:\      102398.65      49245.65      53153.00      51.91
L:\      102398.65      49245.65      53153.00      51.91
M:\      102398.65      49245.65      53153.00      51.91
N:\      102398.65      49245.65      53153.00      51.91
O:\      102398.65      49245.65      53153.00      51.91
P:\      102398.65      49245.65      53153.00      51.91
Q:\      102398.65      56413.65      45985.00      44.91
R:\      102398.65      56413.65      45985.00      44.91
S:\      102398.65      56413.65      45985.00      44.91
T:\      102398.65      56413.65      45985.00      44.91
U:\      102398.65      56413.65      45985.00      44.91
0
r_panosCommented:
The important thing is to verify the free space available on the volume that the temb db resides.

If the free space is low you can't back up big db and log files. Check how big the temp db is now. If it's really big try to restart sql server in order to reset temp db and try  immediately the backup of the db for which you get the error.
0
smythsitAuthor Commented:
Here are the details for the temp db. Shouldn't this be enough space?

tempdb size: 13GB
Space Available: 12GB
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Are you using named pipes when doing your backup?
Could you change to only use TCP-IP for the server if this is the case?

Check Under SQL Server Configuration manager/ protocols.

//Marten
0
smythsitAuthor Commented:
Named Pipes is disabled and TCP/IP is enabled.
0
r_panosCommented:
I'm afraid not. Butt you can find it easily: extend temb db with a second file on a volume that has enough free space although I don't see such a volume (considering that your db data & log files are several hundrends Gb!)
0
smythsitAuthor Commented:
I expanded the tempdb. So it has two files now each 25 GB.

Tried backing up the log but same error.

Would i need a restart for the new changes to take affect?
0
smythsitAuthor Commented:
Hi,

Would anyone have anymore idea's on how the get this log backup back running?

Thanks
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Hi there

Your errorlog indicates no problem growing the tempdb.

I would do a simple test:

tsql code below:
observer it's written straight out of my head. But this would probably give us a hint on whats going on.
Please do this test. (all code presumes there is no database foo, if so use another name i e foo2).

/Marten

-- Code starts here
create database foo
go

use foo
go
create table fooTable(
textdata nvarchar(max)
)
backup database foo to disk 'B:\foodb.bak'
go
insert into fooTable(textdata)
select 'inserting into db'
go
backup log to disk  'B:\footranslog.trn'
go

--drop db
use master
go
drop database foo
go

-- Code ends here
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Whats your OS hosting the SQL, and SP/hotfix level for thesame?

Could it be a simple permissionsproblem. Can you try the following:

This code is verified!

-- Code starts here
Create database FOO  --(assuming foo doesnt exist)
GO
use FOO
GO
create table fooTable(
id int identity,
strData nVarchar(max)
)
GO
insert into foo..fooTable (strData)
select 'My first insert of data AAA'
GO

-- Check that FOO is in full recovery mode
-- Now do a full backup to B:
-- I e
BACKUP DATABASE [FOO] TO DISK = 'B:\Backup Log\FOO.fbk'
--Insert another row
insert into foo..fooTable (strData)
select 'My second insert of data BBB'

-- Now do a log backup
BACKUP LOG [FOO] TO DISK = 'B:\Backup Log\FOOLog.trn'

-- Drop the database
use master
GO

Drop database FOO
GO
-- Code ends here

Now report result to us, will tell us all we need to know about permissions and functionality on your server.

//Marten
0
smythsitAuthor Commented:
Thanks for the code Marten. I ran it and all was successfull.


(1 row(s) affected)
Processed 176 pages for database 'FOO', file 'FOO' on file 1.
Processed 5 pages for database 'FOO', file 'FOO_log' on file 1.
BACKUP DATABASE successfully processed 181 pages in 0.914 seconds (1.543 MB/sec).

(1 row(s) affected)
Processed 6 pages for database 'FOO', file 'FOO_log' on file 1.
BACKUP LOG successfully processed 6 pages in 0.015 seconds (2.669 MB/sec).
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
And if you change the code to:

-- Now do a log backup
BACKUP LOG [FOO] TO DISK = 'B:\Backup Log\FOOLog.fbk'

(other extension not trn but fbk)

Do you have a antivirus on the machine. Is fbk excluded, so it cant lock this file?!?

Is the logfile data important, or is it more important to get the process working again?

/Marten
0
smythsitAuthor Commented:
Yes we've McAfee version 8.7

Changed the code to use .fbk and it ran successfully again.

No the log file data isn't important because we've run full backups since it stopped working.

Yes we're just trying to get the process working again.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Good. Im examining your errorlog and...

04/19/2010 10:35:19,Backup,Unknown,Error: 3041<c/> Severity: 16<c/> State: 1.
Lookat:
http://support.microsoft.com/kb/928317
(serch for "Error: 3041, Severity: 16, State: 1")
This clearly states that you must do a fullbackup before a transbackup.

So I gather something breaks your transaktion chain, perhaps a badly written application using backup log with truncate only?

I would propose a full backup, and then immediatly after (perhaps alter one row or something) a log backup.

Is this something you can provide, I know it's going to take a while, but nevertheless, this is the first good clue I found.
(and my testcode working fits in this scenario)
Actually you could use my code, and just skip the backup database line and see if you get the exact same errorcodes. This vould strongly indicate were on the right track. This only works if you dropped the foo database ofcourse

//Marten

//Marten
0
smythsitAuthor Commented:
Great thanks Marten, i'll try that tonight and let you know how it goes. I can't do a full backup during working hours as it will affect performance.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Quote: ' can't do a full backup during working hours as it will affect performance.'
Dont I know it!

Be hearing from you in the morning then. Good luck, may this be the reason.

If you want to you can do another small test:
This implies that the foo database was previously dropped.

-- Code starts here
Create database FOO  --(assuming foo doesnt exist)
GO
use FOO
GO
create table fooTable(
id int identity,
strData nVarchar(max)
)
GO
insert into foo..fooTable (strData)
select 'My first insert of data AAA'
GO

-- Check that FOO is in full recovery mode
-- Now we don't do a full backup
-- instead do a log backup.
-- Check if the errorlog somewhat identical with your smyths... log?
BACKUP LOG [FOO] TO DISK = 'B:\Backup Log\FOOLog.trn'

-- Drop the database
use master
GO

Drop database FOO
GO
-- Code ends here

This will indicate weather were on the right track, i e if it gives error 3041 but does not look similar otherwise, were probably not on the right track. But with 3041 and a high similarity in the errorlog, then this could be it.

Bye the way, you did check backupmode = full, you didn't answer that posting out of your head because you know this setting? Just checking, Not trying to offend you!

//Marten
0
smythsitAuthor Commented:
Oh no problem at all Marten. I ran the above code and got the error below

(1 row(s) affected)
Msg 4214, Level 16, State 1, Line 6
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 6
BACKUP LOG is terminating abnormally.

Yes i checked that the recovery model is set to Full

So i'm going to run a full db backup tonight and try and log backup immediatly after.

If this doesn't work then i'm going to set the recovery model = Simple and use DBCC to shrink the log file.

Then set the recovery model back to Full.

How does that sound?

I've also attached the log file.

Thanks for all the help
Log.txt
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Sounds like a plan!

This should work, and would be my next proposal given the fullbackup/logbackup after works as expected.

//Marten
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Hi Smythsit

Do you have any updates. Have you had time to run your fullbackup, and then the logbackup?

//Marten
0
smythsitAuthor Commented:
Hi Marten,

Yes we did this lastnight.

We ran a full backup and then straight after a log backup but got the same error.

Then we shrank the log file, ran a full backup and backed up the log and this worked.

Hopefully the process will be back to normal tonight.

Thanks for all the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.