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.
Author |
Topic |
AskSQLTeam
Ask SQLTeam Question
0 Posts |
Posted - 2003-11-17 : 07:40:13
|
Samo writes "Hi guys...Occasionaly (not always!)backup on SQL server (from local disk E: to local disk H: - both RAID1) fails with Windows Application event log message:Source: MSSQL$MGRCategory: (6)Type: ErrorEvent ID: 17055User: N/A18210 :BackupIoRequest::WaitForIoCompletion: read failure on backup device 'E:\MGRDATA3\MGRDATA3.ndf'. Operating system error 1450 (error not found).SQL Server Agent Job History log reports failure in step 1 of B/U:Nonrecoverable I/O error occurred on file 'E:\MGRDATA3\MGRDATA3.ndf'. [SQLSTATE 42000] (Error 3271) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated statement is not prepared [SQLSTATE HY007] (Error 0). The step failed.My configuration is as follows:ProductNameMicrosoft SQL Server ProductVersion 524288 8.00.844 Language 1033 English (United States) Platform NT INTEL X86 Comments NT INTEL X86 CompanyName Microsoft CorporationFileDescription SQL Server Windows NT FileVersion 2000.080.0844.00 InternalName SQLSERVR LegalCopyright © 1988-2003 Microsoft Corp. All rights reserved.LegalTrademarks Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation OriginalFilename SQLSERVR.EXE PrivateBuild SpecialBuild 55312384 WindowsVersion 143851525 5.0 (2195)ProcessorCount 3 3 ProcessorActiveMask 7 00000007 ProcessorType 586 PROCESSOR_INTEL_PENTIUM PhysicalMemory 3920 3920 (4109910016)Product ID NULLI can't find reasons for these events. Can you please give me some hints....Thanks a lot...Samo" |
|
X002548
Not Just a Number
15586 Posts |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-17 : 12:22:51
|
It could be that some process has taken control of that file. Once that happens, you can't backup to it. But I think that the error message would mentioned that. Do you have a tape backup process that runs around the time of the SQL backups? You might consider putting retries into your job step. When I have received this error message, I am always able to rerun the job successfully. I never spent much time troubleshooting it though because it only has happened in our development environment.Tara |
|
|
gercr
Yak Posting Veteran
53 Posts |
Posted - 2003-11-17 : 16:06:12
|
do you have automatic task to check those files for virus?? |
|
|
khanewal
Starting Member
33 Posts |
Posted - 2010-01-28 : 20:51:35
|
"BackupIoRequest::WaitForIoCompletion: read failure on backup device 'E:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\MSDBData.mdf'. Operating system error 1450(error not found). "Hi Guys, I checked every things, but still I am not sure how to fix this error, I can't take any backup, and there is 700 GB free space in my E: Drive help me much appreciated |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-29 : 04:14:19
|
Disk full on E: ?MS Poxy installer put their maintenance database in your Program Files folder <spit>Also, there is no routine to clear down this database for you - you have to set something up.Also, the thing that MS provides runs like a dog <spit2>Its better in SQL 2005 ... but not much.Clear some space on E:Backup the TLog on MSDBRun the cleardown procedure to release some spaceIf MSDBData.mdf is massive (probably is if you've had this server a long time, have lots of databases and lots of backups per day) then do a one time SHRINK to get it back to a more reasonable size.Schedule the TLog backup of MSDB and the maintenance routine.My solution to sort out this mess is here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36201&whichpage=2#108322I recommend you read the whole thread if you have time so you understand the issue. There are people who ran my solution and then came back to say it had been running for 6 days ... and anyway, running it will make your disk space problems worse in the short termCreate the Sproc in your MSDB database.Pay careful attention to the comments in the TEST RIG section, specifically this:-- Find cutoff date - based on deleting 2,500 backupsSELECT MAX(backup_start_date)FROM ( SELECT TOP 2500 backup_start_date FROM msdb.dbo.backupset ORDER BY backup_start_date ASC) X-- Now set the BACKUP date (from above) to delete the oldest batchEXEC dbo.kk_SP_DeleteBackupHistory @intDaysToRetain = NULL, @dtCutoff = '01-Jan-2004' ,@intDebug=1ROLLBACK -- Just in case SP failed half way through -- (otherwise will leave a hanging transaction, ignore error if SProc was OK) (The ROLLBACK will give you an error if it runs OK, I have had situations where an error did NOT rollback the Sproc, so its there just for belt&braces)So:1 Backup the TLog of your MSDB database2 Install my Sproc in MSDB3 Run it over a modest range - e.g. 2,500 backups3a If that runs quickly increase the number of rows to process on subsequent runs4 After you've done a few runs take another TLog backup (otherwise it will fill up and you will run out of space again)If it is going to take a huge number of runs then after you have done a "reasonable amount" Shrink the database, so you reclaim some diskspace on E:, then resume with larger ranges.As soon as you have done your first Shrink you should be able to take backups of other DBs again.<spit=final!> I suggest you ask any further questions in a new thread, folk here may not see posts in this old thread. |
|
|
khanewal
Starting Member
33 Posts |
Posted - 2010-01-30 : 01:33:14
|
Thanks, I will test and let u know |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-30 : 02:29:01
|
I should also ahve saidDBCC CHECKDB('MSDB')to make sure it is not corrupted.I started my rant without reading that you have plenty of space on E:Either way, if MSDBData.mdf is big you need to get rid of the stale/old data, assuming you don't already have good housekeeping in place. |
|
|
|
|
|
|
|