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)

5
votes
3answers
245 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 ...
0
votes
2answers
41 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 ...
0
votes
2answers
80 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 ...
0
votes
0answers
27 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
3answers
134 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 ...
3
votes
1answer
22 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 ...
-1
votes
0answers
44 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 ...
1
vote
1answer
65 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 ...
5
votes
1answer
64 views

What are the “GDR” vs “QFE” hotfixes for SQL Server 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 Server 2008 R2 SP3, there are two possible ways to ...
4
votes
1answer
65 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
48 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
0answers
35 views

Quorum Questions For An FCI And AlwaysOn in a WSFC

I was reading the white paper on FCI + AlwaysOn as well as several other resources on quorum voting differences in Windows Server 2008 and 2014 and had 3 questions. 1- Will giving a node in the ...
2
votes
1answer
26 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 ...
0
votes
0answers
20 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 ...
1
vote
1answer
71 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
2answers
41 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 ...
1
vote
1answer
36 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) & ...
1
vote
1answer
79 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 ...
3
votes
3answers
69 views

Get last two IDs from rows for last two days

I have a table that looks like this: +------------+------------+--------+----------+--------------+ | terminalid | date | toolid | toolname | workcenterid | ...
0
votes
1answer
36 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
26 views

Get the output of a stored procedure into a table [closed]

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 ...
0
votes
1answer
30 views

Database Log 3 times Data File [duplicate]

I have a set of database on full recovery model, my backup schedule looks like this: 1 Weekly Full Backup 1 Daily Log Backup Full backups are about 10GB, log backups are about 500mb per day. What ...
0
votes
1answer
23 views

SQL Server Msg 2812, level 16 state 62 line 30

i want to make automatic backup and restore my database on SQL Server 2012. i have made automatic backup with script in my windows server. now, i want to make auto restore with script, i have found ...
0
votes
1answer
29 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 ...
3
votes
2answers
47 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 ...
2
votes
1answer
27 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 ...
-3
votes
1answer
42 views

How to make a DBADB? [closed]

I recently seen a database server and it had a DBADB database. I asked the dba what it was for and he said he uses it to hold scripts so he has a central spot for them instead of using a network share ...
0
votes
0answers
16 views

DBCC DBINFO and linked servers [closed]

I have 2 servers A, B. A has a linked server connection to B. B has a stored procedure that runs DBCC DBINFO WITH TABLERESULTS for a database name passed into it. From server A I can execute the ...
3
votes
0answers
49 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 ...
1
vote
1answer
18 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 ...
0
votes
1answer
12 views

shipping data without impacting performance

I am new to DBA - actually deputising for a colleague on maternity, Im a sql developer. My company, an on-line retailing coy wants to move copies of our databases from our prod server to a back up ...
4
votes
1answer
84 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 ...
0
votes
0answers
35 views

SQL Server Join the same table twice with different filters [closed]

I'm working on a query right now that requires me to filter joined data differently for multiple aggregate columns, so I'm joining the same table twice and applying a filter only one one of the joins. ...
5
votes
1answer
163 views

sp_who returns more rows than the value of “max degree of parallelism”

sp_configure returns the following values on a server. name minimum maximum config_value run_value max degree of parallelism 0 32767 8 8 However, ...
0
votes
1answer
26 views

sql auth vs nt auth in SQL server

Ok, so everything I read says best practice is to us NT authentication in SQL server. And I can see some advantages to that but using NT auth how do I force users to access the data using only my ...
1
vote
0answers
35 views

Is there a cheap way to bulk select/insert data between SQL Server instances?

In my office, our employees use applications that are consistently writing transaction-type data to a number of databases on a SQL Server 2012. The traffic consists mainly of inserts and updates but ...
3
votes
1answer
48 views

Create a hierarchy chain (reverse path) for each node in a tree

Given a typical closure table, how would I write a query to get a list of ids back to the root? Is there a way to do that for all the [unique] ids? I'm using Microsoft SQL Server. Given: parent ...
1
vote
0answers
37 views

Why is my server with memory pressure when I have 4.3GB available memory? [duplicate]

I'm doing a couple of tests in my local computer and I noticed that the plan cache was being wiped every couple of minutes. After researching a bit I noticed on my error log that my instance was with ...
0
votes
1answer
28 views

dropping SYNONYM is blocking in sql server?

I have synonyms which points to a TestDB1, by the next refresh synonyms point to TestDB2. while re-pointing from TestDB1 to TestDB2 it will drop all synonyms and re-creates them for TestDB2. Now my ...
0
votes
1answer
45 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 ...
6
votes
1answer
657 views

Simulate deadlock with more than 2 processes

How to Simulate deadlock with more than 2 processes in SQL Server test environment? Can someone help me with the code? Just to clarify my question again, I don't want deadlock simulation with only 2 ...
1
vote
0answers
12 views

How to prevent SSDT publishing from dropping columns

I want to create a publishing profile that DOES a complete schema comparison and publishing BUT does not drop any tables or any columns that have been removed between the old and new version. I am ...
8
votes
1answer
294 views

Is the WHERE-JOIN-ORDER-(SELECT) rule for index column order wrong?

I am trying to improve this (sub-) query being part of a larger query: select SUM(isnull(IP.Q, 0)) as Q, IP.OPID from IP inner join I on I.ID = IP.IID where ...
1
vote
1answer
117 views
+100

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 ...
3
votes
0answers
117 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. ...
-1
votes
0answers
15 views

Installing a Failover Cluster Instance on a Cluster already in use for Instances hosting Always On

I have been asked to install a SQL Server FCI on a 3 nodes cluster already hosting 4 different instances with Always On Availability Groups. While I am building the lab on my machine, I am wondering ...
0
votes
1answer
40 views

How to delete duplicate records in my database? [duplicate]

I joined about 12 databases into 1 db and in each database I have some duplicate records. How can I delete all duplicate records in my joined database? For example, I want to delete duplicate ...
0
votes
3answers
62 views

Problem generating restore scripts for multiple ola backups

I am using Ola for maintenance solution. I have 300 databases I want restore those databases using scripts rather than going through one by one. I prepare the following script to generate restore ...
1
vote
2answers
45 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 ...
1
vote
1answer
29 views

Clearing Log Shipping config Secondary server inaccessible [duplicate]

We are using SQL 2012 Our Secondary server in a log shipping configuration went offline never to come back (the VM was permanently deleted before log shipping configuration was updated). On the ...