NETWORK IO Causing Database Hang And Blocking
Aug 12, 2015
I've been put in charge of a SQL server recently and I'm completely new to maintaining SQL. For some reason several clients when they are doing their database processing I get network_io hangs and everything comes to a halt. I have to go in there and manually kill the SPID and everything continues where it left off.
SQL Setup
Server 2012 R2
SQL 2014 SP1
Dell 850
VM running in Hyper-V
50GB RAM
FusionIO Card
10GB Ethernet between clients and server
All workstations are pretty much
Windows 7/8.1
10GB
64+GB of RAM
Several CPUs
SSD and SATA hard drives
It's very hit or miss when this occurs as well, however, it's always the same NETWORK_IO cause.
I've read on the Internet about how the client keep up with what SQL is sending. Could this be poor programming or do I need to investigate my network setup?
View 2 Replies
ADVERTISEMENT
Mar 10, 2003
Recently a production server suffered a critical blocking period and I wanted to know if I could solicit some input. It seems that a stored procedure was in the middle of recompiling while and auto update statistics started. This caused blocking for like an hour on the
single object (stored procedure) that was originally called. The table that the update occurred on and that the
stored procedure is reading form is quite large. It is 2 mil rows and about 140 columns wide. Some info from
sysprocesses is below. The table alone takes up almost 4GB of space, when looking at sp_spaceused. I have some
questions.
1. Can the update statistics for a '_WA%' stats cause
blocking on a table?
2. Does an update stats on an index survive a restart of
SQL server? We tried restarting, but the blocking did not
end.
3. If the stored procedure is running under a compile, can
the server automatically start an update stats and cause
the stored procedure to wait?
4. Can the server automatically start an update stats on
more than one column stats at a time, causing one to be
blocked by the other?
5. We had never seen this issue before going to SQL2K
clustering. Is this something specific to SQL2K and not
SQL7 ?
Thanks for your input.
John Lee
This is the lock info for the blocking processes.
spid dbid ObjId IndId Type Resource Mode Status name
------ ------ ----------- ------ ---- ---------------- -------- ------ -------------------------
142 7 2 1 KEY (6f00035ef42b) S GRANT sysindexes
142 7 2 1 KEY (6f00035ef42b) S GRANT sysindexes
142 7 421576540 0 TAB Sch-S GRANT tJob
142 7 1141579105 0 TAB Sch-S GRANT tPatient_info
142 7 1141579105 0 TAB [UPD-STATS] Sch-M GRANT tPatient_info
142 7 1659921035 0 TAB [COMPILE] X GRANT iDBGetPatInfoRecord
142 7 1659921035 0 TAB Sch-S GRANT iDBGetPatInfoRecord
These are the processes that are being blocked:
spid
------
137
140
Below this is a snapshot of all the SQL processes on the server being blocked.
Save the report and send to the whole database group.
spid kpid blocked waittype waittime lastwaittype waitresource
------ ------ ------- -------- ----------- -------------------------------- -----------------------------
140 4292 142 0x0005 68609 LCK_M_X TAB: 7:1659921035 [[COMPILE]]
137 2576 140 0x0005 64671 LCK_M_X TAB: 7:1659921035 [[COMPILE]]
View 1 Replies
View Related
Feb 11, 2015
I have a sql snippet from a 3rd party application that will not complete its transaction. The SELECT statement executes but does not finish. Instead the statement just sits in AWAITING COMMAND for 1000 seconds then dies, thus killing the UPDATE statement that is supposed to follow.
The CROSS JOIN and CROSS APPLY seem suspect.
(
@p0 DATETIME,
@p1 INT,
@p2 INT,
@p3 NVARCHAR(4000),
@p4 INT,
[code]....
View 9 Replies
View Related
Oct 13, 2015
SQL server DB server is in hang state. Is this is due to high memory utilization?
View 9 Replies
View Related
Feb 28, 2007
Hi All,
I tried to create a new project in BCM and I get an error indicating that there is already a project with that name. There is no project with that name in my list of projects! However, I think during one of the frequent installs and uninstalls before I was able to install a somewhat stable BCM, i might have created a project with that name prior to one of the many crashes (it still hangs and has to be killed in task manager periodically). I suspect I might have a rogue database that was created during one of the reinstalls which included installing and uninstalling small business accounting, which I have since also uninstalled. I have tried to use studio manager express to look at the tables in the databases, of which there are two, "mssmallbusiness" and "contctmgr 16022007" , in order to find an instance of a record with the name in question, no joy yet. Do I need the "mssmallbusiness" database to run BCM and if not can I delete it? I suspect that the mssmallbusiness database could be interfering somehow with the "contctmgr 16022007".
Since installing BCM, my outlook has slowed down considerably and hangs often on launch, I would abandon BCM, but I think it's a really cool tool and I would like to install small business accounting too! My machine is a centrino duo with 1 GB RAM and plenty HD space.
I know this message is somwhat convoluted, but if you can make sense of my issues, pls help.
Regards,
EdincoT
View 1 Replies
View Related
Dec 7, 2006
Hey,
I hope someone can quickly tell me what I am obviously missing for this weird problem.
To give a general picture, I have an ASP.net webpage that allows users to select values from several dropdown menus and click an add button which formats and concatenates the items together into a listbox. After the listbox has been populated the users have the option to save the items via a save button.
The save button parses each item in the listbox to basically de-code the concantenated values and subsequently inserts them into a table residing on a backend MSSQL 2005 database.
PROBLEM:
In the process of testing the application, I noted this strange behavior. If I use the webpage to insert the values, go to the table where the values are stored and delete the rows; Upon a refresh of the web page the same actions seem to be getting replayed and the items are again inserted into the table.
Naturally, what I'd really like would be for the page to refresh and show that the items aren't any longer there and not the other way around.
If the code that performed the insert was residing in a component that was set for postback I'd expect this type of behavior but its in the Save buttons on_click event. I have tried practically everything in effort of targeting the problem but not having much luck with it.
Is this behavior practical and expected in ASP.net or has anyone ever heard of anything similar? I have never encountered this type of problem before and was hoping someone could provide some clues for resolving it. If more information is required I'd be happy to supply it. Hopefully, there's a simple explanation that I am simply unaware since I haven't experienced anything like this before.
Anybody got any ideas???
Thanks.
View 6 Replies
View Related
Nov 26, 2002
I am in the process of writing a stored procedure that takes data from a table, within a cursor, and puts that data to the network server via the xp_cmdshell procedure. The xp_cmdshell command is at the end of the cursor in the SP.
I have worked with the admins on getting the proper permissions to execute this procedure and have tested it by running the procedure directly in Query Analyzer and by calling it in Query Analyzer successfully.
However when I try to call this procedure from another programming envirnoment, once the xp_cmdshell process is called, the procedure locks up and blocks the rest of the process, including the other software programming environment. When I look at the error log, it indicates that correct command, as a matter of fact I have copied the log file command into Query Analyzer and ran the procedure successfully. So for some reason, the statement is not being committed or there is some kind of threading issue I do not know how to address.
If anyone out there has any suggestions or has a simular problem in the past and knows how to address it, it would be greatly appreciated.
Thanks!
View 4 Replies
View Related
Jan 27, 2007
I am trying to deploy a SQL Server Express database to a remote SQL Server 2005 host. I was planning to copy and paste the data from my local tables to the remote tables using Management Studio Express but my ISP (NTL) appears to be blocking port 1433 and preventing me from getting a connection to the remote database.
Will an upgrade to SQL Server 2005 Developer Edition help me? I understand that it has a Backup & Restore Wizard but how does this work? Does it rely on a remote connection and will it fall foul of the NTL port blocking? Or, can the file that the Backup stage produces be copied into the web-based MSSQL Manager, that my web host provides, to restore to the remote database?
Any help would be much appreciated.
View 11 Replies
View Related
Jan 26, 2008
I am running MS SQL 2000 server. The table involved is only about10,000 records. But this is the behavior I am seeing.The local machine is querying the table looking for a particularrecord. Everything works fine.The remote amchine goes to clear the table and then insert all 10,000records, into the table the following happens.1) the local machines queries do not compilete until the remotemachine is done.2) the remote machine can take up to 6 minutes to do these 10,000insert operations. So nothing on the local machine works right forthese 6 minutes.I do not have access to the remote machines source to see what isrunning but I am told it is simply a for loop with a insert query init. Nothing of a locking natture.Any idea the types of things I should look for to track this down? Ifound this by doing SQL profiler profiling and finding the remoteoperations. Turn these operatiiosn off and the local machine worksfine again, with no other action.Thanks,David
View 4 Replies
View Related
Oct 12, 2015
We are getting frequently blocking in Report server database.
Is it ok to change isolation level to RCSI for report server database?
View 1 Replies
View Related
Mar 31, 2007
I created the db with the attached script and I am able to access ituntil I reboot the server. I've tried enabling flag 1807 via the SQLserver service and the startup parameters of the instance. In allcases the database always come up suspect after a reboot. There wasone instance where I was able to recover, but I am not sure how thathappened.Does anyone have an idea of how I can reboot the server without thedatabase becomming suspect?USE MASTERGODBCC TRACEON(1807)GO--DBCC TRACEOFF(1807)--DBCC TRACESTATUS(1807)GOCREATE DATABASE ReadyNAS ON( NAME = ReadyNAS_Data,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Data.mdf',SIZE = 100MB,MAXSIZE = 20GB,FILEGROWTH = 20MB)LOG ON ( NAME = ReadyNAS_Log,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Log.ldf',SIZE = 20MB,MAXSIZE = 100MB,FILEGROWTH = 10MB)
View 5 Replies
View Related
Dec 23, 2006
Hi
Scenario: In general user will take database backup from database server to local system. User perform some changes to his local database based on his requirement. Again user connects to database server and click on update, local database should update to server database by informing each record status to the user. ex: record 1 updated / call closed do you want to update?
Here local database is going to be Pocket pc database and server database is on SQLDatabase. Can any one suggest best way to implement this feature in Windows Pocket pc, c#. I would appreciate your reply.
Thanks,
View 2 Replies
View Related
Sep 30, 2004
Hello all,
We have a Windows 2003 Web Edition server serving a site through IIS. It connects to a Windows 2003 Standard Edition server running SQL 2000 SP3a.
This site receives the following error as seen in the topic:
[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.
We've configured the server network utility to only allow TCP/IP connections. The connection string for the site is as follows:
<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
MM_connSpankMSSQL_STRING = "Provider=sqloledb;Data Source=SERVER_IP,1433;Network Library=DBMSSOCN;Initial Catalog=ourmaindb_1;User ID=ourmaindb_1;Password=hotcookies;"
%>
Any ideas or tips on solving this issue? We've noticed it is due to larger queries as smaller ones do work with no problems.
The servers are behind a BSD box running iptables, has 1433,1434 along with standard web ports wide open. Anything outbound is allowed.
Some further testing via ODBC on the IIS server improved things. No more connection pooling for the SQL Server driver allows for 1/3 of the query to run. Still 2/3s of it doesn't show up, and that general network error message appears.
Should have 330 rows if it works right.
View 2 Replies
View Related
May 19, 2004
Hi
While running a DTS, many times the error message: "[DBNETLIB][ConnetionWrite (WrapperWrite()).]General network error. Check your network documentation." appears.
Does somebody knows why?
I am running the DTS from a computer (not the server where the DTS is stored) and it is connected without any problem when this happens.
Thanks!!
Regards
Lautaro - Argentina.
View 4 Replies
View Related
Sep 29, 2000
I am trying to backup database on a network disk using isql
dump database <DBNAME> to disk = 'servernamepathfile.ext' with INIT
but this does not work and gives an error device off line.
How can I do this??
Thanks
View 1 Replies
View Related
Oct 4, 2002
I tying to test my backup to a network drive but it fails.
Please help ..thanks
this is the script -- example from BOL:
BACKUP DATABASE pubs
TO DISK '\radrisMSSQLBACKUPpubtest.bak'
this is the error:
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\radrisMSSQLBACKUPpubtest.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
View 1 Replies
View Related
Oct 26, 2006
Hello, everyone:
Every time to restore database, I copy the database backup file to local hard drive. Is is possible to restore database over network? Is other tools need to install? Thanks
YTZ
View 1 Replies
View Related
Jul 23, 2005
Hi,its newbie question.I have one production server(A) ,one backup server(B),additionalserver(C).my database backup are moved from A to C in regular basis.My problem is that when ever I have to restore my database in BackupServer i.e B,I have to first copy that particular Backup File on to my Backup Serverfrom Cthen i have to perform my restoration process.Even if I mapped the drive in which my bakup file is lying ,I amnot able to see that drive in SQL Entriprise Manager and when i amrestoring through Query Analyzerit shows the following error -Server: Msg 3201, Level 16, State 2, Line 1Cannot open backup device 'G:pharmacypharmacy_db_200505210330.BAK'.Device error or device off-line. See the SQL Server error log for moredetails.Server: Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.What i think is it a waste a time ,first copy the bak file in localdrive and perform the restoration process.SO please can any one guide me how should i do the network restorationif possible or put me to right path for doing it so.I am using SQL SERVER 2000,OS WINDOWS 2000 Adv SERVER.cheersT.V
View 3 Replies
View Related
Nov 18, 2006
Hi,
I want to take backup of my database over a network drive, the network drive is on a box which is on different domain,
Primary Server: IMM01
Database: DDR
Domain: PNR
User: PNRkpuser
Backup Drive: BMM02
Shared Folder: \BMM02BackupFolder
Domain: BNR
Thanks,
Imran.
View 4 Replies
View Related
Sep 1, 2006
Hi,
I am running the following TSQL command and it hangs. These commands are running from SQL 2000 server and it is querying information for SQL20005 database.
Running simple EXEC command work fine but running using Insert hangs.
Set @Cmd = @ServerName + '.' + @dbname + '.dbo.sp_helpntgroup'
Print @Cmd
Insert into dbo.DBSecurity ( NTGroupName, NTGroupID, SID, HasDbAccess)
Exec (@Cmd)
Any idea,
View 2 Replies
View Related
Oct 4, 2007
Hi Every one,
I am using .Net 1.1 for my web application.
And my database server is SQL Server 2005.
My application is running fine, as i can login to it and also able to view pages. But when i open Order management(having 3K records) its give me error,
General Network Error. Check your network documentation
I have also searched many articles and tried following solutions but nothing working
- connectiontimeout = 0, max pool size = 7500/100, pooling = false
- SSL disabling enforce security false as mentioned in microsoft kb article.
And there's nothing any issue with hardware/firewall as my application's login and other forms are working fine(which use same database with same connection string)
Can any one please help me to solve this error?
With Regards,
View 3 Replies
View Related
May 26, 2007
Hi We have a written a error log in Global.asax. which capture below mentioned error in the prodcution server. Same applications is working fine in some locations and some loactions we are getting the error.Any body knows why this error is occurs.Any body knows why this error is occurs. Error Message:General network error. Check your network documentation.Stack Trace: at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin() at System.Data.SqlClient.SqlInternalConnection..ctor(SqlConnection connection, SqlConnectionString connectionOptions) at System.Data.SqlClient.SqlConnection.Open() at datalayer.sqldb.Execute(String ProcedureName, SqlParameter[] Parameters) at lms.User.GetUserDetails(String strUserid)ITs at LMS.Login.w_btn_Login_Click(Object sender, EventArgs e) at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
View 2 Replies
View Related
Oct 4, 2007
Hi,I am using .Net 1.1 for my web application.And my database server is SQL Server 2005. My application is running fine, as i can login to it and also able to view pages. But when i open Order management(having 3K records) its give me error,General Network Error. Check your network documentation I have also searched many articles and tried following solutions but nothing working- connectiontimeout = 0, max pool size = 7500/100, pooling = false- SSL disabling enforce security false as mentioned in microsoft kb article. And there's nothing any issue with hardware/firewall as my application's login and other forms are working fine(which use same database with same connection string) Can any one please help me to solve this error?
View 2 Replies
View Related
Feb 8, 2004
When I try to update the site setting, I get this Error and I don't know how to fix.
Server Error in '/DotNetNuke' Application.
--------------------------------------------------------------------------------
General network error. Check your network documentation.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: General network error. Check your network documentation.
Source Error:
Line 281:
Line 282: myConnection.Open()
Line 283: myCommand.ExecuteNonQuery()
Line 284: myConnection.Close()
Line 285: End Sub
Source File: C:DotNetNukeComponentsAdminDB.vb Line: 283
Stack Trace:
[SqlException: General network error. Check your network documentation.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +721
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +196
DotNetNuke.AdminDB.UpdatePortalInfo(Int32 PortalId, String PortalName, String PortalAlias, String LogoFile, String FooterText, Int32 UserRegistration, Int32 BannerAdvertising, String Currency, Int32 AdministratorId, String ExpiryDate, Double HostFee, Double HostSpace, String PaymentProcessor, String ProcessorUserId, String ProcessorPassword, String Description, String KeyWords, String BackgroundFile, Int32 SiteLogHistory) in C:DotNetNukeComponentsAdminDB.vb:283
DotNetNuke.SiteSettings.Update_Click(Object sender, EventArgs e) in C:DotNetNukeadminPortalSiteSettings.ascx.vb:287
System.Web.UI.WebControls.LinkButton.OnClick(EventArgs e)
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
Please Help!
Thank you,
dlouanis
View 2 Replies
View Related
Jan 9, 2006
I use sqlclient to connect directly from my mobile 5 pocketpc to a sql server 2005 database on my desktop.
I use a sqldatareader to traverse the results of a query that delivers 2 fields: (1) the filespec, and (2) the image field.
I use a filestream with a binary writer to write the contents of the image data to the pocketpc's storage card.
I have a test table with 10 images, not one more than 4 MB. I cannot complete a test run prior to receiving the general network error.
The pocketPC is connected via activesync to the desktop, and I connect to the database with:
Integrated Security=false;
user id=sa
password=admin.
Any help with this strange error would be greatly appreciated.
Wil
View 5 Replies
View Related
Oct 11, 2006
Hi I get a "General Network Error Check network documentation" error sometimes, but its only while insert or update statements are fired while selects keep working perfectly.
The Error details are as follows:
General network error. Check your network documentation.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: General network error. Check your network documentation.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException: General network error. Check your network documentation.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
View 1 Replies
View Related
Jan 15, 2007
Hello,
I need help with the setup of my sql server express 2005 and more specifically, with the location of the database files. Normally, database files are located on the c: drive. In my situation, being in a high school environnement, I need to have the database files on the network drive ... lets call it g:drive. The reason why it cannot be on c:drive is because this drive is protected using DeepFreeze. Now, I was able to change the location of the new drive as I went into the properties window of the sql server instance but when I try to create this new database, sql gives me grief ;-|
I am from the group of people that thinks that to anything, there is a solution and am hoping that to this problem, there is hope of a solution.
Can anyone help?
Thank you
View 1 Replies
View Related
Jul 31, 2004
Hi,
I want a few tips on how to go about designing a schema for multi parent hierarchical tree structure.
Will this work in large scale, like Social Networking
how do we generally go about designing for Social Networks?
What could be the optimal architecture?
aravind
View 2 Replies
View Related
Mar 16, 2004
The database server is on computer P4.
Computer P3 has a shared folder with full access.
Computer P5 on which I have my Backup Device and will execute the Backup Database Command using Query Analyzer.
On P5 I created the Backup Device:
USE master
EXEC sp_addumpdevice 'disk', 'myDevice',
'\P3 emp est.bak'
Then I executed the following on P5 Query Analyzer:
Backup Database myDatabase TO myDevice
But I get this error:
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'myDevice'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Any idea? Thanks and regards.
View 4 Replies
View Related
Apr 25, 2007
Hello,
I have Sql Server in one pc and I want to attach one databse that is in another computer that doesnt have SQL Server. Sql Server doesnt permit UNC path when attaching database files. Is there anyway to workarounf this. Is Trace Flag 1807 of any use?
Thanks
View 3 Replies
View Related
Jul 31, 2007
Hello!
I have a Java application that gains access to a SQL database using:
connection="jdbc:jtds:sqlserver://localhost:1433/databasename;user="+user+";password="+password;
//Load and register SQL Server driver
Class.forName("net.sourceforge.jtds.jdbc.Driver");
//Establish the connection
conn=DriverManager.getConnection(temp);
When I distribute this application throughout the local network, what would the jdbc url be for the application to find the SQL server?
Should I replace "localhost:1433" with something else?
The instance of SQL server is called SQLExpress.
I have my computer name, computer domain, ip address etc.
Is the port 1433 still the same?
How could the SQL database be located throughout the network?
Any advice appreciated.
View 8 Replies
View Related
Jul 6, 2007
I am here in a peculiar situtation i am not very sure the problem which i am facing as of know is a Database or Network.
One of our organisation application is connected to SQLServer 2005 (clustered) database was working fine before few days back we gone though a windows 2003 server patch update activity and server was restarted and a failover occured.
The day patch activity is done we are facing data loss problem over the network.
Checked the SQLServer2005 ErrorLog but no error related to database.
Few user are facing problem as mentioned below
Error : Checknetwork Documentation
Search the net but no satisfactory answer.
Please help me with a resolution or work around.
One more Issue :
While pinging the server some time we get request Time out.This Request time out is for 1 ms.
Do this also effects the thick client application connectivity with the Database.
regards
Sufian
View 1 Replies
View Related
Aug 9, 2006
When I try to create a new database on a network path, I get the following error:
"\server est estdatabase.mdf" is on a network path that is not supported for database files.
Am I trying something that isn't possible? Should databases always be stored on the server PC running SQL (Express)?
I'm using Visual Basic Express and SQL Express (installed on a local PC not the server, so the connectionstring is ".SQLEXPRESS"), TCP/IP is enabled.
Regards, EBA
View 4 Replies
View Related
