Skip to main content
25 votes
Accepted

Disconnect from network without stopping the query

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 ...
Dan Guzman's user avatar
  • 28.3k
13 votes
Accepted

Does taking transaction log backups every 15 minutes consume more disk space than log backups every 6 hours?

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'...
Brent Ozar's user avatar
12 votes

Disconnect from network without stopping the query

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 ...
David Spillett's user avatar
10 votes
Accepted

Static DAC ports for named instances

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 ...
Hannah Vernon's user avatar
  • 70.2k
8 votes

Is network traffic encrypted when writing remote backups using SQL Server TDE?

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 ...
BradC's user avatar
  • 9,994
8 votes
Accepted

Client application disconnects from database/s it is connected to after 10 minutes of inactivity

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 ...
joanolo's user avatar
  • 13.5k
7 votes
Accepted

Always On Availability Groups Network Configuration in AWS

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 ...
Brent Ozar's user avatar
7 votes
Accepted

How to connect to sql server with dynamic IP Address

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 ...
Brent Ozar's user avatar
6 votes

Does taking transaction log backups every 15 minutes consume more disk space than log backups every 6 hours?

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), ...
CaM's user avatar
  • 2,183
6 votes

Full Back up over to Network failed due to unknown network error 59

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 ...
Aaron Bertrand's user avatar
6 votes
Accepted

Full Back up over to Network failed due to unknown network error 59

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 ...
Hannah Vernon's user avatar
  • 70.2k
6 votes
Accepted

Dynamic Ports are being set to different ports

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 ...
Hannah Vernon's user avatar
  • 70.2k
6 votes
Accepted

Are CNAME aliases for connection strings a good idea?

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 ...
Tony Hinkle's user avatar
  • 7,964
5 votes

How fast can I pull data through a SQL Server client connection?

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 ...
Brent Ozar's user avatar
5 votes

Connection to localhost dramatically slower than to network server

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" ...
mwolfe02's user avatar
  • 432
5 votes
Accepted

Which TCP port to choose for SQL Server to listen to for security reasons?

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 ...
David Spillett's user avatar
5 votes
Accepted

What ports does SQL Server use on Linux?

Assuming the defaults, there are two ports SQL Server listens on which can be verified with netstat -ln, ms-sql-s TCP 1433 for the server ms-sql-m TCP 1434 for the Dedicated Administrator Connection ...
Evan Carroll's user avatar
  • 63.6k
5 votes

Cannot give NT SERVICE\MSSQLSERVER permissions on network drive

The accepted answer is wrong. The 'NT SERVICE\MSSQLSERVER' is not a local service account, it is virtual account. Virtual Accounts are running tasks in the context of the computer they are installed ...
Helmut's user avatar
  • 151
4 votes
Accepted

SQL Server - login failure every day at same time with the service account

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. ...
SomeGuy's user avatar
  • 2,013
4 votes

Adding secondary replica from different subnet for AlwaysON

IP address for each subnet. Documented here: https://msdn.microsoft.com/en-us/library/hh213080.aspx#Prerequisites
Robert L Davis's user avatar
4 votes
Accepted

Will matching SQL Server network packet size to mtu increase performance

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 ...
Ed Elliott's user avatar
4 votes

Need SQL Server VLAN advice.. is it best practice to have all SQL Servers under 1 VLAN/network zone?

Is it a best practice to have all SQL Servers in 1 or 2 VLANs within your businesses network? This is enterprise and personal preference. Some enterprises setup areas for specific things, some don't. ...
Sean Gallardy's user avatar
4 votes
Accepted

Why changing the order of network protocols does not make any sense?

I believe the issue has to do with what client is used when establishing connectivity to Server B from Server A rather than how you order your protocol order on the server itself. The first hint comes ...
John Eisbrener's user avatar
4 votes
Accepted

Connecting to a remote SQL named instance with named pipe

Although the named instance TCP port 50002 was blocked, port 445 is used for named pipes (SMB) and is apparently allowed by the firewall. The SqlClient driver tries TCP/IP first and then falls back ...
Dan Guzman's user avatar
  • 28.3k
3 votes

Is network traffic encrypted when writing remote backups using SQL Server TDE?

... is the backup operation data encrypted as it travels from computer A to be written to disk at computer B? Yes, it is decrypted when it enters the buffer pool and encrypted when it leaves. In this ...
Sean Gallardy's user avatar
3 votes

SQL Server Express 2014: Network Access

SQL Express installs by default with a named instance of SQLEXPRESS. See https://technet.microsoft.com/en-us/library/ms143744(v=sql.90).aspx as it applies to SQL Express 2005. The settings you give ...
S M's user avatar
  • 196
3 votes

MSSQL Mirror Configuration Troubleshooting: Witness server forcibly disconnecting every 100 seconds

We reported the issue to Microsoft support and got it fixed. I have also tested SP1 CU1 and the database mirroring error messages have disappeared. So I think that all is good. https://support....
Ola Hallengren's user avatar
3 votes
Accepted

If shared memory, named pipes, TCP/IP network protocols are all enabled which has priority?

If no protocol is specified , the connection will try based on the order. This is specified in the Enabling a Protocol section in Choosing a network protocol
Bob Klimes's user avatar
  • 3,254
3 votes

IP configuration for MultiSubnet AAG setup

There are a few things going on in your question. To start I presume you are referring to an Always On Availability Group, just to be clear. You shared a link about multi-subnet failover. Multi-...
Mike Walsh's user avatar
  • 18.2k
3 votes

Multi-homed SQL Server with High Availability Groups

Is there any way to set up the Failover Cluster and High Availability Group to tell it: Send synchronous replica traffic for SQL-ATL01 <--> SQL-ATL02 over 192.168.99.x Send asynchronous replica ...
David Browne - Microsoft's user avatar

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