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)

0
votes
0answers
4 views

Multiple SQL instance install question for Allways On

I have a 2 node WSFC cluster with SQL 2014 installed on each for Allways On. This is working fine. My question is can I install another instance of SQL 2014 on these nodes for another Allways On ...
1
vote
1answer
7 views

Clustered Index Scan when using greater than in WHERE clause

I have the following table create table t1( col1 varchar(255) NOT NULL, col2 varchar(255) NULL, col3 bigint NULL, CONSTRAINT PK_t1 PRIMARY KEY CLUSTERED ( col1 asc ) ) CREATE NONCLUSTERED INDEX ...
1
vote
1answer
26 views

Transactional replication latency issue

I inherited database systems like this. Currently I have publisher database in SQL Server 2005 compatibility mode on Windows Server 2008 R2 with SQL Server 2008R2 SP2 machine. Distributor is on the ...
2
votes
1answer
13 views

Migrating from SQL Server 2008 R2 to SQL Server 2014

I'm migrating from SQL Server 2008 R2 to SQL Server 2014. Can someone tell me if my approach will work? Current Environment: SQL Server 2008 R2 Windows 2008 R2 Steps to Migrate from SQL Server ...
1
vote
2answers
37 views

How can I represent a list of words and related words in a relational structure?

I have a list of header words and words as below and I am trying to understand how best to represent these in a relational table: Header Other Words Words abandon abandoned, ...
1
vote
1answer
35 views

Syntax Error Help

I have this script that works just fine but when I change the directory paths it seems to get this syntax error "unclosed quotation mark after the character string" I have looked over this for hours ...
0
votes
1answer
318 views

Prevent gaps while using Identity/sequence object in sql server

I have a table and I am trying to insert values in it. I want to prevent gaps in the primary key column. For primary column I have tried using Identity / sequence objects. I know that this is ...
0
votes
2answers
41 views

Extracting data from SQL Server linked server

I am using MS SQL Server 2012, and I am trying to extract data from a Linked Server database. In SQL Server management studio, I can see the linked server. I can browse the database on the server. I ...
1
vote
2answers
44 views

Query to extract all SQL database roles with INSERT and/or UPDATE privileges

I've taken over the support for a SQL 2008 database and there are a multitude of custom roles (25+) associated within. I'm trying to figure out which of the custom roles have the ability to perform ...
2
votes
2answers
69 views

Composite index

Assuming i have a table X with 12 columns . in my queries i am filtering on these conditions select * from x where a=@a and b=@b select * from x where a=@b and b=@b and c=@c select * from x where ...
1
vote
2answers
35 views

Any different guidance for tempdb with Availability Groups?

I have an Availability Group set up on SQL Server 2014 running on a two-node Windows Server Failover Cluster. The setup consists of two stand-alone instances + synchronous with automatic failover. ...
3
votes
2answers
32 views

Extended events vs SQL Audit - performance implications

I would like to setup a audit trial kind of system on my database to monitor for UPDATE/INSERT statements on a specific table. I have two options infront of me i.e. Using SQL Server Built in audit ...
4
votes
2answers
196 views

Should I add an auto-increment / IDENTITY field to a cross-reference table just for PK purposes?

I'm adding the following cross-reference table to my SQL Server-hosted DB: company_id bigint not null (FK) org_path nvarchar (2048) not null The company_id field refers to the id field in another ...
14
votes
3answers
158 views

Transaction Log Backups Serial or Parallel?

We happen to be using SQL Server 2012 Standard Edition. I also happen to use Ola Hallengren's scripts to provide an easy, more flexible framework for doing backups and maintenance. This question ...
3
votes
1answer
86 views

MEMORYCLERK_XE Memory Usage Will not Clear

We've been having an issue where memory usage from Extended Events grows to over 1 GB and then will not clear - even when we stop all XEvent session on the instance. This hasn't been an issue yet on ...
0
votes
2answers
43 views

A tail-log backup of the source database will be taken ? Why I am seeing this warning

I took a backup of a database from 1st server (which runs SQL Server 2014). When I try to restore the backup to a 2nd server (which runs SQL Server 2012) I get this warning message: Warning ...
1
vote
1answer
16 views

Is there a way to associate a description with database snapshot?

Is there a way to associate a description with database snapshot? I usually name snapshots using original database name and timestamp: DBName-05-27-2016_1018 I would like to be able to associate a ...
0
votes
1answer
39 views

Index Reorganize/ Rebuild Time

I have maintenance job on SQL Server 2012 Enterprise Edition that runs daily to check index fragmentation and reorganize or rebuild the index based on the percentage of fragmentation. I used this ...
3
votes
3answers
59 views

Get last two IDs from rows for last two days

I have a table that looks like this: +------------+------------+--------+----------+--------------+ | terminalid | date | toolid | toolname | workcenterid | ...
4
votes
1answer
78 views

How do I set a SQL Server Unicode / NVARCHAR string to an emoji or Supplementary Character?

I want to set a Unicode string variable to particular character based on its Unicode code point. I want to use a code point beyond 65535, but the SQL Server 2008 R2 database has a collation of ...
1
vote
1answer
17 views

The call to LOADLIBRARY for the XA resource manager DLL failed (PostgreSQL & SQL Server)

I am attempting to setup a Linked Server from MS SQL Server 2012 to PostgreSQL 9.3 via Linked Servers & ODBC driver from PostgreSQL. Everything works, until a given query invokes MSDTC, at which ...
3
votes
1answer
18 views

Multiple Filters for Databases on SQL Server Server Level Audit

I am attempting to create a Server Level Audit to track Database Level events in SQL Server 2014. I know I can create them at the database level but I want the audits all databases at the server ...
0
votes
2answers
57 views

Splitting a large table

My scenario is I look after an old system with a VB6 based front end and a SQL back end (good news, the replacement is being developed but in the meantime I need to keep this up). Now the application ...
6
votes
1answer
116 views

How do I move or redistribute FILESTREAM files between containers?

We have a FILESTREAM container with a few million files, and we believe it's the cause of our performance issues we're experiencing (abundant timeouts). According to this blog about FILESTREAM best ...
1
vote
3answers
120 views

When to use DISTINCT [on hold]

When is it good to use SELECT DISTINCT .. and when not? Basically whenever I see DISTINCT keyword I start to get cautious as most of the cases it is ends up finding that actual query has some ...
0
votes
0answers
73 views

Dropping PLE on query

In company we are working in project on very big database. It uses 100GB RAM. What's weird before first running a query PLE is 11k~, after running it drops to about 70, anyway when after 15 mins I ...
0
votes
2answers
31 views

SQL Server auto restore only log where not restored before

i want to make an auto restore script for the transaction log only i have found the script to restore log in directory which have named with dbname_timestamp.trn the problem is this script will ...
1
vote
1answer
28 views

Login in Always On Availability Groups

I am new to Always On Availability Groups and I am little bit confused with creation of logins. Let's assume an environment NODE1(Primary) & NODE2(secondary) & NODE3(Stand by) & ...
0
votes
0answers
21 views

is it possible to use SQL Server listener IP to the application?

I have 2 application servers and I have SQL Server clusters with always on high availability. I need to point my application server database to the SQL Server listener IP. Is it possible ? if so ...
1
vote
1answer
59 views

Trace flag 2861 and what a 'zero-cost' plan actually means

I'm trying to get my head around trace flag 2861 and what it actually does with trivial queries? The blurb says: SQL Server typically won't cache plans for these trivial queries because the cost ...
0
votes
1answer
28 views

Upgrading SQL Server 2008 failover cluster node to SQL Server 2012 failover cluster node [on hold]

Please I want someone to help me with step by step method of upgrading SQL Server 2008 with failover cluster node on two different machines to SQL Server 2012 without losing the existing ...
2
votes
0answers
43 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 ...
0
votes
2answers
38 views

Update Grouped records

I have the following query that returns duplicate values: SELECT [t].[Field1], [t].[Field2], COUNT([t].[Field2]) FROM [dbo].[Table1] AS [t] WHERE [t].[Field2] <> '' GROUP BY ...
3
votes
0answers
101 views
+150

Plan cache size and reserved memory

When running a query including the Actual Execution Plan, the root operator (SELECT) tells me that the Cached Plan Size is 32KB. A query that joins sys.dm_exec_cached_plans and ...
-1
votes
0answers
41 views

Unable to remove the database name [on hold]

One of the databases is getting to suspect mode. Since I have backup, I restored the same database with different name and then take the SQL SERVER and remove the data and the log file of the ...
4
votes
1answer
53 views

What are the “GDR” vs “QFE” hotfixes for SQL 2008 R2 SP3?

I'd always assumed that higher patch numbers were necessarily later releases, but now I'm not so sure. Looking at the SQL Server Build List for SQL 2008 R2 SP3, there are two possible ways to apply ...
0
votes
1answer
115 views

Communication Link Failure in SQL

I have an SSIS package that runs daily in SQL agent, there are days that the job is failing due to "Communication Link Failure" but sometimes successful. I search the net for some solutions and there ...
2
votes
1answer
21 views

PowerShell Pipeline Binding examples on SQL server?

I am learning PowerShell, my training machine (VM) is a Windows Server 2012 R2, with SQL 2014 and PS 4.0. I am try get a handle on the two Pipeline Binding methods ByValue & ByPropertyName. I am ...
4
votes
1answer
59 views

Count instances of value in multiple tables

I'm attempting to write a query that returns the count of a given value across multiple tables. Right now, I'm using the following query SELECT COUNT(*) AS SL, ( SELECT COUNT(*) FROM [Sales Line ...
0
votes
1answer
38 views

Querying a date range

i query this SQL query: SELECT * FROM [DB].[dbo].[Table] WHERE [DATE] BETWEEN '01-01-2016' AND '31-03-2016' AND ([TIME] >= '00:00:00' OR [DATE] > '01-01-2016') AND ([TIME] <= '00:00:00' OR ...
1
vote
1answer
75 views

Troubleshooting extremely long running query (SQL Server 2012)

We have a purchased reporting application that executes a query that just recently is taking an extremely long time (>2 hours). A week ago, this same query completed in less than a minute. I've ...
1
vote
2answers
363 views

Estimation of creating index in SQL Server

We create new index on our production database, with a few fields, our database size is around 300GB and it works for one hour and do not finished yet. How can you estimate this operation? If we ...
2
votes
1answer
24 views

Fail to start sql cluster role

I have installed SQL Server using Failover Clustering on 2 nodes, running Windows Server 2012 R2 Enterprise Edition and SQL Server 2008R2 Standard SP1. Shared storage is provided by an iSCSI NAS ...
1
vote
1answer
66 views

Scheduling index rebuild jobs

I have a MS-SQL 2014 SP1 - two node AlwaysOn AG (One Primary and an read-only secondary) with about 10 databases sizing 20-30GB in total. I have some queries regarding scheduling index rebuild and ...
0
votes
0answers
17 views

How to set up repl_distributor for data access

I need to run queries on the distributor server from the publisher during deployment in a transactional replication setup. I see repl_distributor is set up as a linked server, but am having trouble ...
2
votes
1answer
1k views

Fastest way to migrate database to AlwaysOn cluster

I want to make the fastest migration plan to move a SQL Server database in simple recovery from a standalone server (SQL 2005) to a two node AlwaysOn cluster in full recovery. Some databases are ...
1
vote
1answer
383 views

Why does SSAS keep restarting every few seconds?

We have a SQL Server 2005 Analysis Server which began restarting this weekend, seemingly without any external changes. The current state of the server is that as long as the SSAS service is enabled, ...
1
vote
2answers
29 views

Dynamic script to Drop Indices of a specific column

I´m currently writing an SQL script to update our database and for that I need a script to drop indices that rely on a specific column (because I also want to drop the column). In my script I have ...
0
votes
1answer
33 views

Accessing computed column in a nested query

I am using nested sets to represent tree-type date in SQL-Server and basing what I'm doing on this article: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ by Mike Hillyer. In ...
-4
votes
0answers
23 views

Get the output of a stored procedure into a table [on hold]

Can anyone help me with a table that takes the output from a stored procedure. It should not be a temp table or table valued parameter. I am able to insert values to the table, but when I use select ...