Backup File I/o Error

Guys,

Apologies if this is in the wrong section. I have a SQL 2000 database which I backup to a folder on the hard disk using the following command in a Server Agent:

BACKUP DATABASE [Multilog] TO DISK = N'C:\MSSQL7\BACKUP\multilog_backup' WITH INIT , NOUNLOAD , NAME = N'Multilog backup', NOSKIP , STATS = 10, NOFORMAT

Recently it has been failing with the following error message:

Nonrecoverable I/O error occurred on file 'C:\Multilog Database Files\Data\MultiLog_data.MDF'. [SQLSTATE 42000] (Error 3271) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Any idea what could be causing this?

Any help would be greatly appreciated.

Sean.

Try some of the method(s) in the following kb:

http://support.microsoft.com/default.aspx/kb/904804

Is this an SP4 server? Looks like it may have been upgraded from SQL 7.0, is that correct?

|||

Chad,

Just noticed the following error on the logs:

I/O error 23(Data error (cyclic redundancy check).) detected during read of BUF pointer = 0x14243d80, page ptr = 0x1607c000, pageid = (0x1:0x1ae), dbid = 5, status = 0x801, file = C:\Multilog Database Files\Data\MultiLog_data.MDF..

Would this indicate a fault with the actual database file? Any idea what could be done to check and fix?

|||

Yeah, that would indicate a data integrity issue within your DB file. Try running a DBCC CHECKDB on the database in question and see what you get for output. Depending on the output of that, you could try using the FIX options with the CHECKDB command, rebuilding indexes, etc.

Backup command and getting progress like Enterprise/Management Studio

Sorry if this is the wrong place for this, but I couldn't find a better place...

I have a .net application that calls a TSQL command to backup the database when the user clicks a button. I would like to give the user incremental feedback on what's going on.

So I hooked into the connection's InfoMessage event.

It gets called at the end of the command, not during. I read somewhere that you're supposed to be able to add "WITH NOWAIT" to a TSQL command to have it return immediately and then use the InfoMessage for updates, but the Backup command doesn't support it.

I also set a trace on the SQL Server and told SQL Server to backup using Enterprise manager hoping to catch what it was doing special to get these commands, and didn't see anything unique.

Does anyone have suggestions? I really need to show the progress of what's going on.

Thanks!

SqlClient unfortunately does not support retrieving of messages asynchronously during command execution. You can do this however if you use System.Data.Odbc instead. The steps are pretty much the same. This can also be done if you use C/C++ and use ODBC/OLEDB/SNAC.

Backup command and getting progress like Enterprise/Management Studio

Sorry if this is the wrong place for this, but I couldn't find a better place...

I have a .net application that calls a TSQL command to backup the database when the user clicks a button. I would like to give the user incremental feedback on what's going on.

So I hooked into the connection's InfoMessage event.

It gets called at the end of the command, not during. I read somewhere that you're supposed to be able to add "WITH NOWAIT" to a TSQL command to have it return immediately and then use the InfoMessage for updates, but the Backup command doesn't support it.

I also set a trace on the SQL Server and told SQL Server to backup using Enterprise manager hoping to catch what it was doing special to get these commands, and didn't see anything unique.

Does anyone have suggestions? I really need to show the progress of what's going on.

Thanks!

SqlClient unfortunately does not support retrieving of messages asynchronously during command execution. You can do this however if you use System.Data.Odbc instead. The steps are pretty much the same. This can also be done if you use C/C++ and use ODBC/OLEDB/SNAC.

backup

Hello,
OK. At this point, I'm almost convinced that something went wrong
during the install process for SQL 2000. No matter what I try, I can
not get SQl to listen on port 1433. I clearly see that named pipes and
tcp is set for the network setting. Port 1433 is the default.
I did a backup of all my databases (to c:\dbbackup) and am going to do
an un-install, reboot, then re-install SQL server.
Is there anything I'm forgetting?Nothing in the errorlog? Of some other service is using 1433, then you'd get an error in the
errorlog...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Won Lee" <noemail@.nospam.com> wrote in message news:ujvVGKm6DHA.2576@.TK2MSFTNGP11.phx.gbl...
> Hello,
> OK. At this point, I'm almost convinced that something went wrong
> during the install process for SQL 2000. No matter what I try, I can
> not get SQl to listen on port 1433. I clearly see that named pipes and
> tcp is set for the network setting. Port 1433 is the default.
> I did a backup of all my databases (to c:\dbbackup) and am going to do
> an un-install, reboot, then re-install SQL server.
> Is there anything I'm forgetting?
>|||Tibor,
Thanks for trying to help me out.
THis is the full cut and paste of my error log.
2004-02-03 09:28:32.10 server Microsoft SQL Server 2000 - 8.00.194
(Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
2004-02-03 09:28:32.10 server Copyright (C) 1988-2000 Microsoft
Corporation.
2004-02-03 09:28:32.10 server All rights reserved.
2004-02-03 09:28:32.10 server Server Process ID is 1688.
2004-02-03 09:28:32.10 server Logging SQL Server messages in file
'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2004-02-03 09:28:32.15 server SQL Server is starting at priority
class 'normal'(2 CPUs detected).
2004-02-03 09:28:32.95 server SQL Server configured for thread mode
processing.
2004-02-03 09:28:32.96 server Using dynamic lock allocation. [2500]
Lock Blocks, [5000] Lock Owner Blocks.
2004-02-03 09:28:33.01 server Attempting to initialize Distributed
Transaction Coordinator.
2004-02-03 09:28:35.34 spid2 Starting up database 'master'.
2004-02-03 09:28:35.82 server Using 'SSNETLIB.DLL' version '8.0.194'.
2004-02-03 09:28:35.82 spid5 Starting up database 'model'.
2004-02-03 09:28:35.87 spid2 Server name is 'WINBOX'.
2004-02-03 09:28:35.87 spid8 Starting up database 'msdb'.
2004-02-03 09:28:35.87 spid9 Starting up database 'equity'.
2004-02-03 09:28:35.87 spid10 Starting up database 'vwap'.
2004-02-03 09:28:36.48 spid5 Clearing tempdb database.
2004-02-03 09:28:38.12 server SQL server listening on Shared Memory,
Named Pipes.
2004-02-03 09:28:38.12 server SQL Server is ready for client connections
2004-02-03 09:28:40.56 spid5 Starting up database 'tempdb'.
2004-02-03 09:28:41.39 spid2 Recovery complete.
2004-02-03 09:31:19.40 spid51 Using 'xpstar.dll' version
I should see SQL server listening on Shared Memory, TCP, Named Pipes.
I'm missing the TCP in my error log though.
netstat -an shows that I'm not listening on port 1443.
C:\Documents and Settings\wlee>netstat -an
Active Connections
Proto Local Address Foreign Address State
TCP 0.0.0.0:80 0.0.0.0:0 LISTENING
TCP 0.0.0.0:135 0.0.0.0:0 LISTENING
TCP 0.0.0.0:445 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1025 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1027 0.0.0.0:0 LISTENING
TCP 0.0.0.0:3389 0.0.0.0:0 LISTENING
TCP 192.168.1.202:139 0.0.0.0:0 LISTENING
TCP 192.168.1.202:139 192.168.1.159:1934 ESTABLISHED
TCP 192.168.1.202:1031 192.168.1.201:139 ESTABLISHED
TCP 192.168.1.202:1033 192.168.1.200:139 ESTABLISHED
TCP 192.168.1.202:3389 192.168.1.159:1923 ESTABLISHED
UDP 0.0.0.0:445 *:*
UDP 0.0.0.0:500 *:*
UDP 0.0.0.0:1034 *:*
UDP 0.0.0.0:1435 *:*
UDP 0.0.0.0:4500 *:*
UDP 127.0.0.1:123 *:*
UDP 192.168.1.202:123 *:*
UDP 192.168.1.202:137 *:*
UDP 192.168.1.202:138 *:*
C:\Documents and Settings\wlee>
I tried to change it to other ports via the Network Utility but I still
can't see the TCP in the error log.
Tibor Karaszi wrote:
> Nothing in the errorlog? Of some other service is using 1433, then you'd get an error in the
> errorlog...
>|||Yes, my sever has entries such as:
2004-02-02 14:15:42.42 server SQL server listening on <IP hiddeen>
...
2004-02-02 14:16:13.95 server SQL server listening on TCP, Named Pipes.
Indeed, it seems like SQL Server doesn't even try to listen to IP. I'd check the registry so there
isn't anything strange in the registry entry where this configuration is held (compare against a
healthy SQL Server).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Won Lee" <noemail@.nospam.com> wrote in message news:uFrXSkm6DHA.696@.tk2msftngp13.phx.gbl...
> Tibor,
> Thanks for trying to help me out.
> THis is the full cut and paste of my error log.
>
> 2004-02-03 09:28:32.10 server Microsoft SQL Server 2000 - 8.00.194
> (Intel X86)
> Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> 2004-02-03 09:28:32.10 server Copyright (C) 1988-2000 Microsoft
> Corporation.
> 2004-02-03 09:28:32.10 server All rights reserved.
> 2004-02-03 09:28:32.10 server Server Process ID is 1688.
> 2004-02-03 09:28:32.10 server Logging SQL Server messages in file
> 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
> 2004-02-03 09:28:32.15 server SQL Server is starting at priority
> class 'normal'(2 CPUs detected).
> 2004-02-03 09:28:32.95 server SQL Server configured for thread mode
> processing.
> 2004-02-03 09:28:32.96 server Using dynamic lock allocation. [2500]
> Lock Blocks, [5000] Lock Owner Blocks.
> 2004-02-03 09:28:33.01 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2004-02-03 09:28:35.34 spid2 Starting up database 'master'.
> 2004-02-03 09:28:35.82 server Using 'SSNETLIB.DLL' version '8.0.194'.
> 2004-02-03 09:28:35.82 spid5 Starting up database 'model'.
> 2004-02-03 09:28:35.87 spid2 Server name is 'WINBOX'.
> 2004-02-03 09:28:35.87 spid8 Starting up database 'msdb'.
> 2004-02-03 09:28:35.87 spid9 Starting up database 'equity'.
> 2004-02-03 09:28:35.87 spid10 Starting up database 'vwap'.
> 2004-02-03 09:28:36.48 spid5 Clearing tempdb database.
> 2004-02-03 09:28:38.12 server SQL server listening on Shared Memory,
> Named Pipes.
> 2004-02-03 09:28:38.12 server SQL Server is ready for client connections
> 2004-02-03 09:28:40.56 spid5 Starting up database 'tempdb'.
> 2004-02-03 09:28:41.39 spid2 Recovery complete.
> 2004-02-03 09:31:19.40 spid51 Using 'xpstar.dll' version
> I should see SQL server listening on Shared Memory, TCP, Named Pipes.
> I'm missing the TCP in my error log though.
> netstat -an shows that I'm not listening on port 1443.
> C:\Documents and Settings\wlee>netstat -an
> Active Connections
> Proto Local Address Foreign Address State
> TCP 0.0.0.0:80 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:135 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:445 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:1025 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:1027 0.0.0.0:0 LISTENING
> TCP 0.0.0.0:3389 0.0.0.0:0 LISTENING
> TCP 192.168.1.202:139 0.0.0.0:0 LISTENING
> TCP 192.168.1.202:139 192.168.1.159:1934 ESTABLISHED
> TCP 192.168.1.202:1031 192.168.1.201:139 ESTABLISHED
> TCP 192.168.1.202:1033 192.168.1.200:139 ESTABLISHED
> TCP 192.168.1.202:3389 192.168.1.159:1923 ESTABLISHED
> UDP 0.0.0.0:445 *:*
> UDP 0.0.0.0:500 *:*
> UDP 0.0.0.0:1034 *:*
> UDP 0.0.0.0:1435 *:*
> UDP 0.0.0.0:4500 *:*
> UDP 127.0.0.1:123 *:*
> UDP 192.168.1.202:123 *:*
> UDP 192.168.1.202:137 *:*
> UDP 192.168.1.202:138 *:*
> C:\Documents and Settings\wlee>
>
> I tried to change it to other ports via the Network Utility but I still
> can't see the TCP in the error log.
>
>
>
> Tibor Karaszi wrote:
> > Nothing in the errorlog? Of some other service is using 1433, then you'd get an error in the
> > errorlog...
> >
>|||Tibor,
What entry should I search for?
Tibor Karaszi wrote:
> Yes, my sever has entries such as:
> 2004-02-02 14:15:42.42 server SQL server listening on <IP hiddeen>
> ...
> 2004-02-02 14:16:13.95 server SQL server listening on TCP, Named Pipes.
> Indeed, it seems like SQL Server doesn't even try to listen to IP. I'd check the registry so there
> isn't anything strange in the registry entry where this configuration is held (compare against a
> healthy SQL Server).
>|||Tibor,
Does this make you think that something is wrong with the SQL server and
not the OS?|||You have to do some investigation yourself and compare a healthy and the problematic machine. Here's
a place to start:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Won Lee" <noemail@.nospam.com> wrote in message news:e3BY9tm6DHA.2568@.TK2MSFTNGP10.phx.gbl...
> Tibor,
> What entry should I search for?
>
> Tibor Karaszi wrote:
> > Yes, my sever has entries such as:
> >
> > 2004-02-02 14:15:42.42 server SQL server listening on <IP hiddeen>
> > ...
> > 2004-02-02 14:16:13.95 server SQL server listening on TCP, Named Pipes.
> >
> > Indeed, it seems like SQL Server doesn't even try to listen to IP. I'd check the registry so
there
> > isn't anything strange in the registry entry where this configuration is held (compare against a
> > healthy SQL Server).
> >
>