All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

13
votes
0answers
219 views

Service broker was backed up, now receiving, but doesn't seem to be processing

Having a problem with Event Notifications. On the machine/drive/database that the messages are sent to (receiver), the drive filled up when nobody was looking, so it's been backed up all day. Now ...
9
votes
0answers
247 views

ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) doesn't use index on (A,B,C)

Consider these two functions: ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) As far as I understand, they produce exactly the same result. In other ...
8
votes
0answers
159 views

SQL Server - Handling localization of strings in nested non-deterministic view stacks

While profiling a database I came across a view that is referencing some non-deterministic functions that get accessed 1000-2500 times per minute for each connection in this application's pool. A ...
7
votes
0answers
225 views

How to reload a linked server?

I am using Microsoft SQL Server 2014 Enterprise Edition. A problem occurs with the linked servers where it is necessary to restart the server, or to stop the MSSQLSERVER service. When the server is ...
7
votes
0answers
356 views

Performance inner join vs left join involving parallelism

I have a query which takes about 8 hours to finish. Simplified, the query looks something like this: SELECT [Some attributes, inconsequential] FROM RBM.Financial_Events RFE INNER JOIN ...
6
votes
0answers
109 views

SQL Server Sharding strategy optimization

I have implemented a sharding solution for our SQL Server databases as per the MSDN documentation here: https://msdn.microsoft.com/en-gb/library/dn589797.aspx This has been running for around 6 ...
6
votes
0answers
286 views

Why does resource in AlwaysOn fail?

I have two SQL Servers (12.0.4100.1) and Availability Group (AG) configured on them. Sometimes some server hangs. It responds on ping, but RDP connection are not available, SQL Server doesn't respond ...
5
votes
0answers
35 views

Excessive Prelogin Packets

Running a client/server application where the client app is powered by MS Access and the database on the server is in SQL Server. We have two locations and have active users at both. We recently moved ...
4
votes
0answers
77 views

Why different datetimes freezes a query based on indexes?

I have a table with a billion records. It has these columns: Id bigint, MobileNumber varchar(100), Date datetime, Message nvarchar(400) And it has non-clustered indexes on MobileNumber and Date ...
4
votes
0answers
59 views

Columnstore index and low selective columns

I have a table with almost 1T rows. create table bigtable ( K1 int, K2 date, -- PK columns C1 ..., C8 ...., -- columns with various data types like float, date, varchar, ... B1 bit, B2 bit, .......
4
votes
0answers
54 views

HADR high worker thread usage

Why would an availability group's number of worker threads in an HADR pool increase well beyond minimum usage of "typically, there are 3–10 shared threads" per replica? In one case we've observed ...
4
votes
0answers
51 views

Large Variation in Bulk Insert time

So I have a simple Bulk Insert process to take data from our staging table and move it into our datamart. The process is a simple data flow task with default settings for "Rows per batch" and the ...
4
votes
0answers
54 views

SQL Server logins “un-sync” when Availability Group fails over

I have 2 SQL Servers in an Availability Group. The logins are manually synced – same SID, same Password Hash, etc. on both replicas – and the user can log in via the listener to the primary replica. ...
4
votes
0answers
109 views

Restored DB has data, DBCC CHECKDB is clean, but SELECT TOP 1 returns zero rows

Having restored a client's SQL 2014 EE DB onto our Developer Edition instance, without issue, then running a DBCC CHECKDB that found zero errors, and a clustered columnstore index alleging over 1Bn ...
4
votes
0answers
61 views

VLDB is stuck in recovery state. It's a permission issue, now what should I do?

SQL Server database which is very large (4TB) is stuck in recovery state. Reason: Data center had a planned downtime and we had to turn the physical machine off and when it turned on, as I read in ...
4
votes
0answers
358 views

Interpreting sys.dm_os_sys_memory

I try to figure out a way(using only TSQL) to determine if and how much the PAGEFILE (I will capitalize this term if I refer to the windows page file from now on) is used (like pagefileusage% counter)....
4
votes
0answers
87 views

Take action from extended event?

I've seen it mentioned a few times that you can configure an event session to take an action when an event fires. Does anyone have any information on how to do this? I know that you can use the ...
4
votes
0answers
70 views

Can SQL Server use a spatial index and another index at the same time and be efficient?

Let's say we have a table that contains two terrain datasets defined by TerrainModelID like below: CREATE TABLE [dbo].[DigitalTerrain]( [DigitalTerrainID] [uniqueidentifier] NOT NULL, [...
4
votes
0answers
90 views

Why is the lease locked for a some full sql backups to Azure Blob using Ola Hallengren backup scripts?

I'm using SQL Server 2012 and working on backing up to Azure Blob using Ola Hallengren backup scripts. Some backups are lease locked (ex. model) while others are not (ex. msdb). It's inconsistent, it ...
4
votes
0answers
2k views

SQL Server intermittent error 40 Could not open a connection to SQL Server or The network path was not found

I am getting intermittent errors as per the below stack trace. I am using C# Entity Framework with SQL Server 2012 enterprise running on a separate server computer. I can connect and query the DB ...
4
votes
0answers
249 views

Conflict handling, SQL Server 2008 R2 Peer to Peer

I have a Peer to Peer replication topology set up between two SQL Server 2008 R2 servers. I was doing some testing on fault tolerance and conflict handling. I have conflict detection enabled, and ...
4
votes
0answers
746 views

INSERT BULK in SQL Profiler without values

I started SQL Profiler to catch actions that are doing on server, and among them I found one row where is stated: SQL BatchStarting: insert bulk table_name <column_list> but there is no ...
4
votes
0answers
152 views

How to Audit for Access with Cross Database Ownership Chaining

I've just joined the data team of a new company and we have a large SQL Server environment (100+ instances) and cross database ownership chaining is on by what I gather is a necessity. I'm looking for ...
4
votes
0answers
491 views

SQL Server 2008 replication scenario

First of all sorry about my english, and most importantly, my database ignorance, I'll do my best ;) Until now, the scenario that I have is as follows: In a server under our LAN, we have a database ...
3
votes
0answers
45 views

ODBC Connections locking tables in SQL Server

We have an ERP system that is a little less than modern, the backend is currently sitting on a SQL Server 2008 R2 installation. We have through mandate of management (isn't that always the case) the ...
3
votes
0answers
41 views

How much disk space do I need to add a primary key to a very large table?

I'm using Microsoft SQL Server 2014 - 12.0.4100.1 Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) I have a 491 GB table with 3.6 billion rows that does not have a primary key. The ...
3
votes
0answers
47 views

How to manage primary key value while multiple secondary server sync with primary server?

we have three secondary database server in different cities and one primary server. my question is i want asynchronous transaction commit to all three database but primary key data conflict occur ...
3
votes
0answers
90 views

Heavy I/O for Microsoft Transaction Log

We have a Microsoft SQL Server (SQL Server 2012 SP3), we've noticed that transaction log I/O is very high. We do have a busy server; we're hosted at Rackspace with SSD block storage. The Logs are are ...
3
votes
0answers
51 views

Do Large Hash Joins Contribute to Stolen Pages?

On a SQL Server 2014 SP1 Enterprise Instance, I'm performing an outer join between two partitioned tables on a numeric(16,0) + char(1) composite key. 100 GB is the max server memory setting on a 128 ...
3
votes
0answers
157 views

Best procedure to measure query optimization effects?

I could not find valuable answers or explanations to the following question: How should a procedure to test query improvements effects look like? Using SSMS I am not absolutely sure about the steps. ...
3
votes
0answers
68 views

Personal Finance database review

G'Day, I found that someone had started on this topic on personal finance, I like to go further using a good personal finance plan I found on the internet which I would model. In advance please ...
3
votes
0answers
66 views

how do i create an automated sql restore script for multiple backups?

Hello to all SQL gurus out there. I basically have 6 daily sql backups (.bak) that need a restore everyday. I want to automate this process by having the sql agent run the script daily. I have the ...
3
votes
0answers
82 views

File size when extracting from SQL Server to CSV

I am attempting to extract a table to CSV using BCP from SQL Server 2014 and the size of the file once the data is extracted is actually larger than the size of the database itself. One thing to note ...
3
votes
0answers
48 views

Case Sensitive Ordering within the CMS Server List

I've got to believe there's a reason for this, but for the life of me I cannot figure it out. It looks like ordering of Server Names within any Central Management Server is Case SENSITIVE, regardless ...
3
votes
0answers
43 views

SQL Server storing predefined value sets along with open values

I have Cars with different Characteristics, Characteristics have values, which can be restricted by predefined set of values or be "open" values (any string). Characteristics and predefined values ...
3
votes
0answers
113 views

Read/Write Deadlock on Ms-Sql Server

We are facing deadlocks when store procedure is concurrently executed. We are using Microsoft SQL Server 2008 R2, concurrent execution is done with java executing multiple request of sp. I have ...
3
votes
0answers
75 views

SQL Server 2012; Incorrect PLE buffer manager value for NUMA server

We are receiving PLE alerts from SCOM for several instances. These instances are on hardware servers, and use NUMA nodes. SQL Server 2012 SP1 CU7 Enterprise Edition is installed. Memory is fixed and ...
3
votes
0answers
592 views

Much slower SQL application performance after upgrading from Windows Server 2008 R2 to Windows Server 2012 R2

Has anyone seen much slower SQL Server application performance when upgrading from Windows Server 2008 R2 to Windows Server 2012 R2? We recently did a side-by-side upgrade of an important server ...
3
votes
0answers
87 views

Sort operation SQL Server 2012

I am trying to optimize a query and the sort operation (91% cost) is the costliest operation. Any help will be appreciated. Query is : SELECT TOP 1 ArchiveType, CONVERT(NVARCHAR(30), A....
3
votes
0answers
45 views

How to deal with Users Needing to Enter Mixed Data Types in MS SQL Server

I have a form in a C# application that I can't change with a data table bound to a query. The user enters a mark for a student's task or 'A' for absent. However, the person who designed this had the ...
3
votes
0answers
245 views

How to PUSH data between a Local DB Server to Cloud DB Server?

I have an implementation doubt regarding replicating data between SQL Server 2014 database servers (Replicating data from Local Premises SQL Server to Cloud DB Server, which are not in same domain or ...
3
votes
0answers
60 views

SQL Server Incremental Backups Split

I am trying to recover a database where the incremental (log) backups are split between two methods (Carbonite/Zmanda and SQL .bak files). When I try to use the restore wizard I get the following ...
3
votes
0answers
125 views

Options to migrate large database from Sybase ASE to SQL Server with minimum downtime

The problem: Our server database contains 1TB of data. It runs on a Sybase ASE 15.7 server and shall be migrated to SQL Server 2014. The servers will be located in the same data center during ...
3
votes
0answers
281 views

How to fix SQL Server Management Studio 2014 IntelliSense when intermittent

Our remote SQL Server was recently upgraded to 2014. I installed SQL Server Management Studio 2014 on my Windows 10 client computer. At first, everything seemed to be going great, until I noticed ...
3
votes
0answers
394 views

Restore Database wizard won't open in SSMS 2014

On a dev machine running Window Server 2012 R2, I just upgraded SQL Server from 2012 to 2014. Using SQL Server Management Studio 2014, when I right-click a database and go to Tasks -> Restore -> ...
3
votes
0answers
76 views

Unable To Log In Through Windows Authentication

See this question for details of troubleshooting I've been stepping through (I did answer 2, which didn't work - it was a corrupt master file). In configuration manager, I changed to a Built-in ...
3
votes
0answers
33 views

Incremental statistics with failover

We have two AlwaysOn availability replicas - secondary readable. We do have an incremental statistics on a large table. When a failover happens, SQL Server tries to update statistics on that table ...
3
votes
0answers
172 views

Unable to restore backup taken on one server onto another server

I am using SQL Server 2014 version 12.0.2000.8 on both systems. When I try to restore the backup taken from the first system onto the second system it says: No backup set selected to be restored ...
3
votes
0answers
43 views

Memory\Pool Nonpaged Bytes low but Memory\Pool Paged Bytes high

Can someone help identify how these values can indicate memory leak. In the system I am monitoring Memory\Pool Nonpaged Bytes are low (on average 100mb) but Memory\Pool Paged Bytes are high (on ...
3
votes
0answers
33 views

ObjID in Page Header

I am reading this article: Inside the Storage Engine: Anatomy of a page. I have a database MyDB, and there is a table MyTable in the database. I have the following questions: If I do as follows: (...