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)

3
votes
0answers
226 views

SQL Server fulltext_semantic_languages

When trying to install a semantic search example, I'm running into a problem. There are some steps in the example to check if the 'new' Semantic Search and Full Text Search are installed. ...
3
votes
0answers
288 views

SQL Server 2008 R2 - Merge Replication - Can the Distribution Database size cause replication problems?

Setup: We have a SQL Server 2008 R2 instance running on a central server. The server is a merge-replication publisher. We have 'N' subsribers running SQL Server 2008 Express. 'N' can grow to any ...
3
votes
0answers
96 views

Merge Replication Optimization

I am running Merge Replication on Microsoft SQL Server 2012 through web sync. Is there a way to recognize when regenerating a new snapshot and applying it would be more efficient than running a merge?...
3
votes
0answers
498 views

Auditing - parameter values not being written to log

I am using auditing to track usage of some procs in the database. In the log I can see a record for the Procedure being run and, immediately afterwards,the SQL statement that is being run. The ...
3
votes
0answers
909 views

SQL Server Database Mirroring : strange client application behavior upon failover

I am putting a HA SQL server environment up based on three SQL Server 2008 R2 machines and database mirroring. I'll name them : principal.company.intra mirror.company.intra witness.company.intra ...
3
votes
0answers
450 views

Upgrading Remote Distributor in SQL Server Replication

I'm upgrading my remote distributor from 2005 to 2008r2, it is performing transactional replication only. this is a prerequisite to completing an upgrade of the Publisher to 2008r2. Script ...
3
votes
0answers
617 views

some SQL Server MSX targets show as Normal, Blocked

I've centralized my farm's jobs in MSX, covering about 70 instances of SQL Server. Seemingly randomly, at least once a week, several instances will stop accepting instructions. Within the MSX window ...
3
votes
0answers
251 views

Merge replication sync fails after ALTER TABLE… NOT NULL

We have a SQL Server 2005 database that uses merge replication to sync to SQL Compact Edition subscriptions. We added a column to a table using: ALTER TABLE Shifts ADD COLUMN StateOrProvince NOT NULL ...
2
votes
0answers
48 views

Constraint violation in a trusted constraint

I have a table with a constraint on it that has an existing record that is in violation of that constraint. While this is possible if someone applied the constraint without a check and after ...
2
votes
0answers
49 views

Select Query with S lock wins on Insert with IX lock in deadlock

Select Query with S lock wins on Insert with IX lock in deadlock in Sql Server 2008R2. Attaching the deadlock xml file.Please help! http://pastebin.com/nY9EXbS8 This is from the error log by ...
2
votes
0answers
51 views

“Access is denied.” for xp_cmdshell for network share

On a SQL 2014 SP 1 instance a SQL Agent job I'm using xp_cmdshell to return a list of files from a network share. This has recently stopped working and returns "Access is denied." The command is ...
2
votes
0answers
47 views

SQL Agent parsing actually executes instead of just validating

I'm seeing some really odd behavior with SSMS 2014/2016 that I stumbled accross by pure accident. This was run on SQL2014 SP1+CU6 (the fixed one) in a non-prod environment. Here's the backstory. I ...
2
votes
0answers
54 views

SSDT Syntax Error 80001 on every Select statement starting with “(”

Visual Studio / SSDT marks the following script line as wrong giving "SQL80001 Wrong Syntax next to SELECT" and also "SQL80001 Wrong syntax next to ')'" error. (select a from [tab1]) union (Select a ...
2
votes
0answers
69 views

After upgrade to 2016, can't view SQL Server Logs

Just upgraded to SQL Server 2016 from SQL Server 2014 SP1 CU4. This is a clustered instance I am no longer able to use xp_readerrorlog and xp_enumerrorlog Msg 22004, Level 16, State 1, Line 10 ...
2
votes
0answers
52 views

Procedure runs slower over time; fixed by cache clear or reboot

We are experiencing a strange issue where an application which was fixed by creating missing index requests, updating stale stats, and removing lookups and other scans, was running fine once fixed, ...
2
votes
0answers
40 views

Loads of parallel inserts (OLTP) into heap table - Operation stops every now and then for 2-3 seconds

We got an analytics database and as new events are coming in we are adding them into our tempdb. From there we have backend processors that pick up the events and merge them into the right tables, i.e....
2
votes
0answers
100 views

Advice on how to improve this stored procedure

Edited: Added table column datatype I have 2 tables ReportItem id (int, primary key clustered index) report_item (nvarchar(100)) flow_name (nvarchar(50)) source (nvarchar(50)) Tech_Details id (...
2
votes
0answers
51 views

Does Trace Flag 8295 applies to SQL Server 2012?

I've been searching about the undocumented Trace Flag 8295. It's been introduced with KB 2476322 in SQL Server 2008 and "'Creates a secondary index on the identifying columns on the change tracking ...
2
votes
0answers
31 views

How is Index rebuild affected by IN_ROW_DATA vs LOB DATA

I am using Ola Hallengren's scripts to maintain my databases. On a few of them there are indices that don't seem to be benefiting from the maintenance. I know that his scripts have a lower threshold ...
2
votes
0answers
47 views

Clustered index on a fact table

I'm trying to work out what field(s) to use for the clustered index of my fact tables (which all have a similar structure). I'll use FactSales as an example: CREATE TABLE dbo.FactSales ( ...
2
votes
0answers
33 views

Index on a highly updated field causing excessive locks in SQL server

I'm in a bit of a conundrum about what the best course of action on this (bad?) index is. I have a table which i use as a notification holder that i watch for database changes with. CREATE TABLE [dbo]...
2
votes
0answers
31 views

SAN Admin vs. DB Admin free allocated tempdb

I really want to understand. My SAN-Admin told me I have to make sure my TempDB does not grow to max hard disk space. I have 380 Gig on a dedicated disk, on a dedicated LUN, on a dedicated machine. My ...
2
votes
0answers
49 views

SQL Server 2014 sequence got reset on server restart

We have a sequence that got reset when we did SQL Server failover to the secondary node in the availability group which caused lot of issues with duplicate sequence being generated unexpectedly. I ...
2
votes
0answers
40 views

Does HOBT shared lock prevent writes

I am attempting to execute long running reads against a table which is also the target of insert bulk queries from sqlbulkcopy. In order to achieve this I have added the with(nolock) to the query ...
2
votes
0answers
44 views

ERD to SQL Server

I am working on an assignment and am having one heck of a time getting my head wrapped around these questions. I need to be able to write in SQL Server 2014 certain select statements based solely off ...
2
votes
0answers
51 views

DBCC failure due to sparse files on Windows Cluster

We have a SQL Server 2014 SP1 cluster on Windows Server 2012 R2, and came across a problem with DBCC jobs failing and issuing the following messages: Msg 1823, Level 16, State 2, Line 3 A ...
2
votes
0answers
67 views

Trouble setting up mirroring on SQL Server 2016

I've setup a test SQL Server 2016 server. I've installed 2 instances, restored a backup on the primary. Then restored a backup on the secondary with NO RECOVERY, then restored the transactional log ...
2
votes
0answers
49 views

where to add a column with low cardinality, decimal type into an index?

I got a question regarding the best way to put a bad column for an index in an index, so that I can satisfy my query below, and similar others that follow the same pattern. on the query below, all ...
2
votes
0answers
41 views

Classification of Subqueries?

Just trying to wrap my head around the use of subqueries. Is it correct to say that they come in three main categories: Scalar: returns a single value Table-valued: Returns a table, to compare or ...
2
votes
0answers
40 views

how to get column name from cross reference query

I used a script from here: http://stackoverflow.com/questions/13757387/getting-sql-server-cross-database-dependencies that Milica Medic posted and it works GREAT thank you :) my question is how do I ...
2
votes
0answers
59 views

security concerns over enabling data access for the current server

Included in the code of the creation of some stored procedures, I have the enabling of data access as you can see below. BEGIN TRY PRINT @@SERVERNAME PRINT DB_NAME() PRINT 'enable the ...
2
votes
0answers
42 views

AlwaysOn Multi Subnet Configuration

Environment Two Nodes A & B A - Site/Subnet 1 B - Site/Subnet 2 File Share - at Site 3 We have an AG listener that has two IP addresses spanning the two subnets (1 up, the other down ...
2
votes
0answers
60 views

TSQL Generic Row number theoretical question

To start, We have a pretty complicated system that's been a compilation of over 20 years of code... And the code needs to work with SQL2005 all the way to SQL2014 (can't use the offset Fetch Next) ...
2
votes
0answers
84 views

What is the correct way to rename/swap a table in production

I have two tables, tblCode and tblCodeAlternative tblCode has about 4100 rows and is used in production. tblCodeAlternative has about 4200 rows and is in the production database but not used ...
2
votes
0answers
53 views

Cross database - Table valued Stored Procedure

I have the same table type in two databases (DB1 and DB2), DB1 has an stored proc (sp1) which has the above type as an input parameter. Now, I want invoke this SP from DB2. But, I am getting this ...
2
votes
0answers
69 views

SQL Server Check Constraint Lock Order

I'm having problems with SQL Server 2012 filetable. Filetable defines an check constraint on a path_locator field with definition as: /*ParentsAreDirectories*/ (filetable_parents_are_directories((...
2
votes
0answers
75 views

Unable to use select top N shortcut on Azure SQL Database via SSMS

Yesterday morning I was able to query my Azure databases via SSMS and the Select Top N menu shortcut. That afternoon I received a couple of alerts that my web applications couldn't connect to the ...
2
votes
0answers
49 views

Max Degree of Parallelism for SQL Server 2008

Microsoft have recommendations around setting MAXDOP on various versions of SQL Server, but this (old) article raises some issues and recommends setting MAXDOP = 1 generally for SQL Server 2005 and ...
2
votes
0answers
14 views

Installing SQL Server 2012 Standard on a machine with more than 64GB RAM

I’m in the process of doing the specification for a new server that will host SQL Server 2012 including SSIS, SSRS and SSAS I’m aware SQL Server 2012 Standard has a limit of 64GB. However from what ...
2
votes
0answers
34 views

Is there impact on query execution as more partitions are added to a table?

We are looking to heavily use partitioned tables in our data warehouse environment. We are looking to take advantage of fast partition switching as well as partition elimination during analytical ...
2
votes
0answers
53 views

SQL Server Trace Flag 834 on VMware

It's recomended to enable trace flag 834 to use large-page on buffer pool for SQL Server running on VMware? The tuning options kb of Microsoft (https://support.microsoft.com/en-us/kb/920093) don't ...
2
votes
0answers
265 views

SQL Server 2012 Installation Error on Windows 2008 Server

I am facing an error while trying to install SQL Server 2012 EXPRESS ADVANCED EDITION(32bit) on Windows Server 2008 Enterprise(64bit). After passing Rule Check, at install updates section, error ...
2
votes
0answers
53 views

Shrinking a physical transaction log file on a mirrored database

I have 2 SQL Server 2008 Standard Edition instances that are mirrored. Both instances are running Service Pack 1. I run Full backups daily and transaction log backups every half hour (through ...
2
votes
0answers
107 views

DB snapshot goes into SUSPECT mode (SQL 2014 on WIN2K8R2)

I was hoping someone here might have encountered this sort of issue and if so what did you do to fix it. Here's some details: DB server: SQL 2014 Enterprise (12.0.4100.1) OS: Windows 2008R2 ...
2
votes
0answers
32 views

SQL Server - Debugging - How to show parametervalues as HEX

I am writing a script which sets Parameters in the Registry for SQL Server. The following works on some of my servers but not all. DECLARE @Parameters AS VARCHAR(MAX) SET @Parameters = '-T1118' ...
2
votes
0answers
69 views

How to avoid Network issues for SQL Server 2012 AlwaysOn

I have SQL Server 2012 and have setup AlwaysOn for High Availability. The infrastructure is on Azure VM's. After moving to this setup, we are frequently observing Network issues which either causes ...
2
votes
0answers
72 views

CPU utilization reached 100% after changing OS time on SQL Server with Mirroring configured for a database

We have configured mirroring on SQL database server. Suddenly principal database server reached 100% CPU, windows log events shows following 2 logs This event is generated when the system time ...
2
votes
0answers
93 views

How to setup SQL Server 2014 replication for a demo site

Background: We have been looking at replication of our SQL Server 2014 databases for a couple of scenarios.One which just works and its hunky dory and the other which just doesn't seem to work, ...
2
votes
0answers
60 views

SQL Server 2014 Log shipping - column error

I'm trying to set up log shipping between two instances of SQL Server 2014 Dev edition. Backup and copy jobs work flawless, but the restore one keep failing with the following error: An error ...
2
votes
0answers
2k views

How to resolve error “object reference not set to an instance of an object” in SQL Server 2014

I installed SQL Server 2014 Management Studio on my system few months age. It was working fine until yesterday. But today, when I started SSMS and clicked on "New Query" option, I got an error ...