Hot answers tagged

25

As the answer referenced in your question indicates, SQL Server will cancel the executing query and rollback the transaction when it detects the network connection has terminated. A SQL Server session needs a connected client to query return results and messages (informational, warning, and error messages). You have a few options to maintain the connection/...


15

If client takes long time to receive data and in turn send acknowledgement to SQL Server that it has received the data SQL Server has to wait, due to this wait SQL Server will not release the locks held by the query unless acknowledgement is received from client. This is not accurate, it is dependent on the isolation level. At the default READ ...


13

No, but it may make your transaction log smaller - so your SQL Server will use less space. To keep things simple, say you generate 1MB of transaction log activity every minute. After 15 minutes, you've generated 15MB of log activity - but that also means that your transaction log will need to be at least 15MB large (assuming that you're in full recovery ...


12

Not directly using normal tools (SSMS etc.) and access libraries - if you disconnect progress should stop and any active transactions will get rolled back. You could run the statements via an agent job set to start immediately, assuming that this is configured and your login has sufficient privileges.


10

http://dev.mysql.com/doc/refman/5.6/en/innodb-implicit-commit.html says: If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction. That means if your session disconnects for any reason, either by choice, or else because an error occurs like the network connection fails, etc., ...


10

Taken from the Microsoft Support site: To configure a static port for the specialized Dedicated Administrator Connection (DAC), you must update the registry key that corresponds to your instance. For example, the registry key may be the following: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\SuperSocketNetLib\...


9

Mark's answer cleared up a lot of my confusion, but I wanted to post my findings after I tested this using NetBalancer to emulate latency. I had my local machine call a remote SQL server and execute both SELECTs and INSERTs on a table within a small transaction. On the remote machine, I connected to the local SQL instance and used a WHILE loop to ...


7

have fixed sqldwdev01 the reason I couldn't connect to UAT instance is because of a couple of reasons there was a dynamic port specified for the instance under SSNetworkConfig\Protocols for UAT, so had to remove that and specify 1435 (as 1434 is in use) then restart the service still cant connect remotely to the instance name but can connect using the port ...


7

In AWS EC2, you don't get the ability to add additional network ADAPTERS to your VM. You can add additional network INTERFACES, which are just different IPs/subnets/routes/etc, but they're using the same underlying network adapters (cards). You can choose to use instance types with faster networking ports, though. The EC2 instance configuration page lists ...


7

Yes, backups are encrypted while moving over the network because TDE data is encrypted on disk, and the backup operation never decrypts it. Paul Randal's Backup Myths: Myth 30-09) backups read data through the buffer pool No. The backup subsystem opens its own channels to the database files to avoid the performance hit of having to read everything into SQL ...


7

Instead of connecting via IP address, connect to your machine's name. If you're connecting locally, you can use LOCALHOST as the name, or just a period ("."), which connects you to your local default instance.


6

The /128 in the address is the NUMBER OF BITS to match... not a range. So you would want 10.8.101.0/24 (match the first 24 bits). As an additional example, if you wanted to restrict it to 10.8.101.128 to 10.8.101.255 then you'd do 10.8.101.128/25 Please see the address subsection in the documentation


6

Changing the IP Address for a machine running SQL Server is not a problem at all. You'll need to restart the SQL Server after changing the IP Address so that the SQL Server will start listening on the new IP address. But that's it. If no users actually connect to the machine then there's nothing else to do. If users did connect to the machine they'd need ...


6

pgAdmin keeps throwing up a connection timeout every 10 minutes or so. I would think that the problem might be related to a router (or, in some cases, a stateful firewall) between your pgAdmin client and the PostgreSQL server. This is quite common when you are at home (or in the office) and your router is performing NAT (Network Address Translation). After ...


6

There's a little bit of overhead in each transaction log file written out to disk, even if no changes occurred in the DB. In some databases I have (SQL Server 2014, compression turned on for backups), transaction logs written during periods of inactivity are generally 9.5 kb per TRN file. But other than that overhead, the transaction logs are going to ...


6

The point of dynamic ports is that you don't care what port the server runs on. SQL Server Browser directs clients to the dynamically chosen port. If you don't want dynamic ports, just assign a port at the outset, then it will never change.


6

One minor drawback would be that you would need to add a Subject Alternative Name (SAN) to the certificate that is bound to SQL Server if you are using encrypted connections. This could be a significant pain if you have a "shared" server that hosts a number of smaller applications and you want to set up an alias for each application. If you are using a ...


5

Most likely, it will finish a DDL command behind the scenes, but I wouldn't bet my job on it. What if your CTAS action runs out of tablespace? You would never see the error. If you have an unrealiable Internet connection, then the best solution is probably Simple/basic solution Use VNC or RDP to "jump" to a desktop or server that is in your datacenter. ...


5

Try enabling TCP/IP on your local instance: Open SQL Server Configuration Manager (mmc.exe -> [Ctl] + [M] -> Add "SQL Server Configuration Manager" snap-in) Expand "SQL Server Network Configuration" Click on "Protocols for MSSQLSERVER" (or whatever your local instance name is) Set "TCP/IP" to Enabled I just ran into this problem tonight. Contrary to @...


5

If you are having network reliability issues: speak to your network/SAN team (and/or use a different file share that doesn't exhibit the problem) Back up the file, temporarily, to the local SQL Server system, assuming there is adequate space. Then copy the backup to the network share, typically using a tool that can resume interrupted transfers, and delete ...


5

Aaron's answer provides great advice on how to work around this issue. I thought I'd add an answer to provide some more technical details. According to Microsoft's err.exe utility, error 59 is defined in winerror.h as: An unexpected network error occurred. If this was a problem with the session timing out, you would almost certainly be seeing ...


5

This question may be better suited to https://security.stackexchange.com/ as the idea and implications are not at all specific to SQL Server, they apply to any other internal service. Changing the SQL Server instance port generally adds very little to security - it is a "theatre" action rather than a real security benefit, something to put on a checklist to ...


4

I certainly wouldn't want to include the server name in the TNS alias. It would seem highly probably that this would change over time as databases move from one server to another and as organizations move to things like RAC where there would be multiple servers. Assuming that your service names are chosen meaningfully, I would expect that the service name ...


4

I'm not sure I would advocate it as a good solution, neither performance wise nor as a best practice, but there actually is a possibility. In short, the steps would be Make a backup on a swap file on the server Create a temporary table with a varbinary field Insert the backup into the table Fetch column data using ADO.NET Save the stream in a file The ...


4

Databases require certain guarantees from the storage it runs on to implement ACID. Unfortunately, it is not always possible for the database to determine if those guarantees are met. Here are some requirements that the storage (no matter if it is NAS, direct attached or SAN) must meet: Write Ordering: Writes my happen in the order they are confirmed to ...


4

UDP 1434 is used for Named SQL Server instances and SQL Browser service listens on this port for any incoming requests to a named sql server instance. The browser service will respond to the client with TCP port no. for the requested named instance. From BOL : The SQL Server Browser service lets users connect to instances of the Database Engine that are ...


4

IP address for each subnet. Documented here: https://msdn.microsoft.com/en-us/library/hh213080.aspx#Prerequisites


4

Using an Extended Events session I was able to track down the issue and see that it came from the syspolicy_purge_history job that SQL Server creates by default to clean up Policy Management records. Step 3 is "Erase Phantom System Health Records". This contains a PowerShell script that tries to connect to OTHER instances on the machine, causing the login ...


4

Some things to try: Wider data sets - if you're using a narrow table (just a few fields), SQL Server may be waiting for the client app to ack the data and request the next set of rows. Since you're doing experimentation, try building a table with just two fields: an identity, and a CHAR(7000) filled with a string. Pull data directly from cache - size your ...


4

TLDR: set it lower if it helps you, monitor your packet sizes to see if you are sending more packets than you need. The MTU controls how large each network segment can be on all the points between each server, think traceroute hops - the MTU controls how large the segments are between 2 hops roughly. The smaller the MTU on a particular segment the more ...


Only top voted, non community-wiki answers of a minimum length are eligible