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)

2
votes
0answers
307 views

Case Statements, subqueries, and datediff

First time posting on this website so please let me know if you require any other information. This first section of code worked perfectly and resulted in correct information. [Field 1] = (CASE ...
1
vote
1answer
2k views

Applying Quotes Across Multiple Lines

In SQL Server Management Studio you can use the shortcut CTRL + K > C or CTRL + K > U to comment/uncomment lines in bulk. Is there a similar keyboard function that applies single quotes across ...
0
votes
1answer
4k views

Excel Data Connection to SQL Server using Windows Auth from untrusted machine

We have an Excel spreadsheet that uses a Data Connection to a SQL Server database. The database server is joined to our domain, and Windows Authentication is enabled. Here is a sample of the ...
5
votes
1answer
168 views

Is it possible to get seek based parallel plan for distinct/group by?

An example from this question shows that SQL Server will choose a full index scan to solve a query like this: select distinct [typeName] from [types] Where [typeName] has a non-clustered, non-...
1
vote
2answers
2k views

How can I map a login to a database using T-SQL (not SSMS)

I'm writing a program that requires me to assign all permissions and everything in code. I have gotten stuck in this part: I just want to do the equivalent of clicking the little box under "Map" ...
1
vote
1answer
95 views

Restore Database SET SINGLE_USER

I am in the process of writing a restore database script. I want to be able to connect as sa and run the restore script. When I run SET SINGLE_USER does this mean that my login is the only user ...
3
votes
3answers
518 views

Script to drop all databases except one not working

I'm trying to drop all databases but one on my test server. For this, I'm using the below script but it gives error: EXEC sp_MSforeachdb ' USE [?] IF (DB_ID(''?'') > 4 AND DB_NAME()!=''ABC'') ...
3
votes
2answers
2k views

CXPACKET SUSPENDED and NULL wait type

I was was running the query in this article: http://sqlity.net/en/708/why-cxpacket-waits-are-not-your-performance-problem/ To see what my threads were waiting on in regards to a suspended query with ...
0
votes
0answers
484 views

Timeout occurred while waiting for latch: class 'ACCESS_METHODS_HOBT_COUNT'

We have a big reporting database on production server, and we have several SSIS jobs running against it. Yesterday almost all this-database-related SSIS jobs stuck and running forever. Also the ...
3
votes
1answer
3k views

How to resolve RESOURCE_SEMAPHORE and RESOURCE_SEMAPHORE_QUERY_COMPILE wait types

We are trying to figure out root cause of slow running sql server queries hitting/fetching data from one of the database , size 300 GB, hosted on server with below configuration: Windows server ...
2
votes
2answers
289 views

How to handle too many inserts?

I have a database that is accessed from remote locations in the world with very long delays and poor transfer speeds. I currently transfer the data using a transaction since either all or no data ...
0
votes
2answers
52 views

How can I decide Rebuilding Index with DETAILED or SAMPLED on SQL Server

I want to rebuild my indexes and statistics on DB. How can I select this option on production maintenance. SQL Server Version : Microsoft SQL Server 2012 - 11.0.2100.60 (X64) (Build 7601: Service ...
1
vote
2answers
162 views

SQLOS Resource Monitor and QueryMemoryResourceNotification Win API

Here's some info about SQLOS and the Resource Monitor I found from Jonathan Kehayias: As a general rule, SQL Server will use as much memory as you can give it, and it will not release the memory ...
3
votes
2answers
2k views

RAID10 vs. RAID5 for data files

We are trying to evaluate the potential performance improvements that could be gained by switching an existing applications .mdf files to a RAID10 LUN vs. the existing RAID5 LUN they are living on ...
3
votes
3answers
393 views

How can I get SQL Server restore start and end time information?

SQL Server error log reports only the end of the restore (ah doc restore) How can I get the start time? Is it stored somewhere?
0
votes
2answers
500 views

Is there a faster way of moving rows of data between SQL Server databases than using CTE's?

I use the following query on SQL Server 2012 Express to move data that is older than 4 days old out of one [primary] database and into another [secondary database] for archiving purposes. The only ...
0
votes
1answer
300 views

Prevent record from being inserted in the table in After Trigger

I have a scenario where I have to insert a record in TableA only if the RecordId exists in TableB. I have written an Instead of Trigger on TableA for Insert, in which I am checking if the RecordId ...
0
votes
0answers
29 views

Replicated databases across locations; delay in application

I have merge replication setup on a publisher SQL Server with subscriber instances of the database on different locations with different SANs. This is used for running a vendor-supported application. ...
2
votes
1answer
97 views

Implications of setting READ_Write

I am looking to update several users on READ_ONLY databases. As the databases are used 24/7 globally, and maintenance windows have just past, I would like to do this during a low-activity moment (to ...
0
votes
1answer
677 views

Search for first and lastname without order

maybe there is a simple answer to my problem: I have a mssql table ('Person') with a field for first and one for last name. I am coding a Program, where the User should be able to search for persons. ...
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)....
0
votes
1answer
190 views

Recommended number of index for a table

I've a table with many columns (~70) which contains computer's configuration. It contains approximately 50 000 rows. This table is very requested and all these column too. I use to run the following ...
0
votes
0answers
100 views

Grant permissions on stored procedure

I created a user in SQL Server 2012 database and revoked all permissions given by the public role. Then i granted exec permission on a stored procedure. The user can execute the procedure but cannot ...
0
votes
0answers
23 views

Load data into central database

We have 150 databases at different locations. These databases need to be move into central database. currently, i am using transactional replication, but facing lot of issues. are there any other ...
0
votes
2answers
2k views

connect to server with IP address running sql-express instance through sql-management studio

I have my VPS server in a remote location with ip address 172.16.11.2 (for example) having two instances of sql-server express running. one instance is Microsoft SQL Server 2008 R2 (SP2) where I ...
1
vote
0answers
126 views

sys.dm_os_volume_stats problem

Today I have been having quite an interesting issue, which I can't seem to fix. I am getting drive information for a Failover Cluster Instance SQL Server 2012. These drives are Volume Mount Points. ...
0
votes
3answers
4k views

SQL Server Configuration Manager returns remote procedure call failed

I open the SQL Server Configuration Manager and it returns remote procedure call failed. This is Windows 7 Ultimate version, SQL Server 2008 R2 Express version with advanced services. Previously I ...
2
votes
1answer
377 views

Differentiate SQL Server Express from SQL Server installed in a server

I own a VPS server and I was told SQL Server 2008 R2 Express edition was installed on Windows OS 2008R2. When I opened the SQL Server configuration manager I saw this: I thought I should see only ...
0
votes
1answer
64 views

check if found specific record then find another record with the same group

I have a table Master table PROBLEM_ID PROBLEM PGROUP 1 Other Hardware Hardware 2 Error On hardware Hardware 3 PM Hardware 4 ...
1
vote
0answers
217 views

Efficient way to transpose large categorical column to one-hot table row

I have a varchar column containing ~ 8000 unique values and 500k rows. I need the column transposed so that the column headers are the unique values and the values in the rows are 1 where the row has ...
0
votes
1answer
78 views

Delayed transactional replication setup?

I will need to setup transactional replication for a big database. I use backup/restore to initialize the database because it's too big to use snapshot over slow network. So the following steps are ...
0
votes
2answers
204 views

What causes disk space growth from Informatica load onto SQL Server?

We've been dealing with huge disk growth on SQL Server from Informatica. After the load, the database grows to 2.4TB. After the database shrink, it goes to 1.05TB. What could likely cause this to ...
0
votes
1answer
597 views

SQL Server Full Text Search Engine is not returning consistent results

I'm working on an application that makes use of the Full Text Search Engine from SQL Server. In order to convert the user input into readable parameters for SQL, i'm using This very useful FTS query ...
1
vote
0answers
322 views

Inserts/updates are extremely slow with Merge Replication in SQL Server 2008

I got hired into a new company and they had merge replication already in place. They have had issues with it to the point I have to evaluate whether to keep it or replace it. The main issue is it ...
0
votes
0answers
16 views

maximum server memory setting in SQL Server [duplicate]

Just had a memory issue on a 64GB of RAM machine with SQL Server 2012 SP2. 3 running db engine instances, each limited to 16GB (16384). But noticed in Task manager that sqlservr.exe was at over 30GB ...
1
vote
1answer
209 views

SQL DATEDIFF coercion differences between databases on same SQL instance?

I have a data coercion mystery. I am seeing two different behaviors from DATEDIFF for the same query, and I cannot understand why. This is an extract of the relevant part of the query in question, ...
2
votes
1answer
41 views

Finding the end of a Relationship chain optimally

I have a table that contains an id column, a parent column, and it's child column. So for every record, I know what record comes before it and what will come after it in a chain relationship, but from ...
3
votes
1answer
176 views

SQL Server Differential Backup Bloat

I perform daily full backups of all databases. Differential backups are performed every hour. The issue I am having is that around the same time each morning, a few hours after the full backup, the ...
0
votes
1answer
38 views

Database in transition

I have a database that has been locked in transition for over 24 hours. I found a similar thread that discusses how to find the commands that are running but I do not know how to unlock the database. ...
2
votes
1answer
428 views

SQL server not using the memory allocated

We have noticed that for one of our SQL server with below configuration ,memory is not getting availed as allocated. SQL server 2005 SP4, 32 bit Windows server 2003 R2 SP2, 32 bit with 16 GB ...
2
votes
3answers
256 views

SQL Server 2012 enlarge database space

I tried to enlarge a database size of database instance on my SQL Server 2012. I manually added a new .MDF file under database instance After restart on shrink database dialog I can see available ...
1
vote
1answer
51 views

One Non-Clustered Index that covers every possible combination of predicates?

Say we have a table against which a number of different queries can be executed and each query has its own combination of predicates. So, for example, if we have a table with columns A, B, C, etc we ...
1
vote
1answer
265 views

How to set IN values on an update (SQL Server)? [closed]

Quick question, I did try to google but couldn't find an answer. I have 3 separate queries here, I want to do SET IN ('price1', 'price2', 'price3') but when I tried I got an error. How would I do ...
3
votes
1answer
40 views

Determine the server that sent a query

For the distributed queries, is it possible to determine the server that sent a query? For example, I have stats: SELECT DB_NAME(st.dbid), text, request_time, query_plan, ...
2
votes
1answer
114 views

What is the most efficient way to concatenate strings in SQL Server?

I have this code: DECLARE @MyTable AS TABLE ( [Month] INT, Salary INT ); INSERT INTO @MyTable VALUES (1,2000), (1,3100); SELECT [Month], Salary FROM @MyTable; Output: I want to concat ...
1
vote
1answer
395 views

What to do when Sql Server Agent refuses to run a Job Step that references a missing database snapshot

I have a SQL Server Agent Job creating a snapshot of my main database every minute: -- delete snapshot if it exists if exists (select * from sys.databases where name = 'mydb_snapshot' ) begin ...
2
votes
1answer
308 views

SQL Server 2014: missing replication option

This article details exact steps to set up basic database replication with SQL Server 2014. It starts with this: Right-click the Replication folder, and then click Configure Distribution. ...
3
votes
1answer
284 views

Manual Failover with AlwaysOn availability group

We are planning to upgrade our SQL 2012 to SQL 2014. We currently use a mirror for our production databases but are planning to move to availability groups. We are going to be running 2 SQL Servers on ...
0
votes
1answer
204 views

Processors: E5-4620 v2 vs E5-2620 v3 [closed]

We're debating between: R820 - E5-4620 v2 - 2.6GHz - 8 cores, 20MB Cache, 1333MHz max memory speed - max of 768GB ram - 8 core R730 - E5-2620 v3 - 2.4GHz - 6 cores, 15MB Cache, 1866MHz max memory ...
2
votes
2answers
2k views

How to query readonly replica in Always On cluster

We have an Always On "cluster" that consists of 2 servers (there will be more), so one is PRIMARY and other(s) is(are) SECONDARY. The idea was to dedicate SECONDARY as a readonly replica, so it would ...