SQL 2012 Error 823

Hello - we are seeing an error on our SQL Server 2012 that is puzzling us.  It's an Error 823 and its occurring every minute of the day, for as far back as the event log goes.  the strange thing is ity does not seem to impact the DB itself - but its causing our Arcserve SQL backup job to fail when backing up ANY of the many DB's on the server.  We called Arcserve, and they tell us "fix this SQL error, and the backup job should work".  So we have tried restoring the DB from the last good backup, and also ran a DBCC  checkDB on the DB in question, to no avail.   Here is the error:

"The operating system returned error 64(The specified network name is no longer available.) to SQL Server during a read at offset 0x0000000016a000 in file '\\lacosql2\X\AX2012R3BaselineDEV10.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online."

We have also confirmed the UNC path to the DB file (on another server - "lacosql2") that the path works and is good.  We also checked the free space on the drive, and there is 132GB free.   Also checked and verified that the MDB file is actually there.  

Out of ideas now..

Thanks for your help.
Damian GardnerIT AdminAsked:
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.

pcelbaCommented:
You should not use SQL Server this way. The power of SQL Server is based on the fact it has all data available on local drives and no network should be involved in the data access. Even the data drives virtualization is not recommended for SQL Server. It degrades the performance significantly.

Once you have data placed on another server and the network is not 100% reliable with possible lags (which SQL Server does not expect) then you may observe this unpredictable behavior.

Of course, you may define Linked Servers for remote data access but that's a different story.

So the main question is:  Why do you have data on a different server than the one where SQL Server resides?
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
Damian GardnerIT AdminAuthor Commented:
it is a temporary DB that was created, and there is not enough space on the local drives.  If we do not have enough space, should we just give up?
0
Scott PletcherSenior DBACommented:
Make sure the SQL Server account has access to the network of the drive and any other needed permissions.

You can try forcing the "bad" flag off (assuming you have sysadmin access to the instance), but it will turn itself back on if SQL detects a new error:

ALTER DATABASE your_db_name SET EMERGENCY;
ALTER DATABASE your_db_name SET ONLINE;

On occasion, I've had that clear up lingering "bad data" issues.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Damian GardnerIT AdminAuthor Commented:
Ok let me try that.  thanks and standby Scott.
0
pcelbaCommented:
No, you should not give up but you should add new disk to the computer where the SQL Server resides. Or install SQL Server on the computer having enough disk space.
0
Damian GardnerIT AdminAuthor Commented:
welp - so far, it doesn't seem to be helping.  After executing the two commands, and also opening up the permissions for the user account being used, the event viewer is still showing the error as of the last minute.  I also ran a test backup job and there's no change.  But - I hadn't noticed this before, but out of about 8 DB's I have in the backup job, only one is showing an error related to not being able to reach the DB file - wheras the others are failing with something different:

(Microsoft SQL Server Error 0 (ODBC State = "08S01"): [Microsoft][ODBC SQL Server Driver]Communication link failure).

The rest of the DB's are LOCAL to the machine.  So - on the SQL server itself, there is only the Error 823 with the one DB.  But on the Arcserve backup server, it's getting these other errors.  

This is a weird one.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Are you running the backup from a SQL Agent job? If so, the SQL Agent account should also have permissions on the network share.
0
Damian GardnerIT AdminAuthor Commented:
Ahh - yes I am, and good point.  I need to add that user....
0
Bharat BhushanSolution ManagerCommented:
The error message is related to operating system error or an IO error. Read this post: https://www.stellarinfo.com/blog/how-to-fix-sql-server-error-823/
0
pcelbaCommented:
Both the "specified network name is no longer available" and "Communication link failure" means unstable network. When you solve this problem then even the database on a network share should work for you.

BTW, you were talking about the "temporary database". So if you have database on a network share for some temporary or test purposes then you could exclude it from AcrServe backups and do these backups manually.
0
Damian GardnerIT AdminAuthor Commented:
Thank you for your help gentlemen.  It was a bad database, as it turned out.  we deleted the database and the error went away.
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
SQL

From novice to tech pro — start learning today.