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)

1
vote
1answer
60 views

SQL Server query problem when selecting data from child table based on column in parent table

I am using MS SQL Server 2008 using Management Studio. I am running a query(below) to extract dates and value based on the 'ticker' column from the parent table, the attribute column (child) , and ...
1
vote
1answer
51 views

Running select affects performance of stored procedure called from code

In what scenario would running the same select statement in SSMS as that in a stored SP which is currently timing out start making the SP (which is being called from a service every minute (30 sec ...
1
vote
1answer
49 views

sp_stop_job reports Success, but the jobs are still running

We have some jobs that run via the scheduler. We noticed today that some are still running from the weekend, and we need to kill them. We have used sp_stop_job and it reports that the job has been ...
-4
votes
1answer
42 views

Insert and Update a sql table at same time is not working [closed]

I am facing an issue with my SQL DB. I have a stored proc which inserts data into a tables [Table1],[Table2],[Table3]. But when I tried to update some data which already exists in those tables at the ...
1
vote
0answers
32 views

Determine kind of this SQL Deadlock

I have a Deadlock file & i can't determine the kind of this deadlock. Read-write Write-write Parallel query Index Other I stuck on this issue a week :( Here is: Deadlock File Note: open ...
3
votes
1answer
72 views

Fatal error 3624 : iKey < m_statBlob.GetHeader()->GetKeyCount()

I am receiving errors when I run the following query: SELECT ( SELECT 1 FROM dbo.AccountEntry AE WHERE AE.Account = P.Account ) FROM dbo.Proposal P WHERE P.id = @Id ...
1
vote
0answers
18 views

How can I attach the SSL authenticated certificate for my SQL Server 2014 if it is not on domain controller

We have Microsoft Azure with Windows Server 2012 R2 in Cloud Computer and window Server is Standalone it is not come under domain controller. We want to add SSL authentication. SQL Server is not ...
1
vote
0answers
44 views

ms/write on tempdb data files are dead slow

I don't know why writes on my tempdb data files are extremely slow. ms/write on each of the tempdb data files reaches to more than 800 ms!!! that's even after I moved the 8 equally sized (7GB) tempdb ...
-1
votes
1answer
32 views

OUTPUT clause with UPDATE FROM

In the code below, the OUTPUT Updated.* fails when executed as-is, but removing the FROM in the UPDATE and replacing it with a static value works fine. Is there a different syntax required when using ...
1
vote
1answer
22 views

Managing SQL Server logins and database users

I am getting a little further into the database administration side of things and starting to set up security access to SQL server databases for users. I have a question about server logins and ...
1
vote
1answer
32 views

SQL Server local instance user login [closed]

I am working on an Azure Mobile Apps backend service, which I am trying to connect to a local SQL Server database, as described here. I have followed all the steps, except I am using SQL Server 2016 ...
0
votes
3answers
67 views

what is difference between database developer and administrator? [closed]

i am a doing graduate degree, and i want to go in database field as i find this subject very interesting. For becoming the DBA ,is there a need of becoming developer first for graduate passout and ...
1
vote
2answers
70 views

Affect of Shrinking Database before Transaction log backup

I am newbie in the world of Database administration, so excuse my stupid question :) I have the following maintenance jobs scheduled through SQL Server Management Studio : Full DB Backup at 2 am, ...
1
vote
2answers
54 views

Log in as another SQL Server user via SSMS to check their permissions?

I am getting a little further into the database administration side of things and starting to set up security access to SQL server databases for users. I have a question: Once I have given a set of ...
1
vote
0answers
12 views

TLS 1.0 disabled causing errors when requests come from SQL Server to a Webserver

The issue that I have at the moment is that when I disable TLS 1.0 (due to PCI Compliance) on a web server that hosts our external application, a stored procedure is called from a different server ...
1
vote
1answer
50 views

SQL Server Replace not returning expected result

Shouldn't the below code return 0? SELECT REPLACE(ISNULL('',0),'',0) This doesn't return 0 either.... SELECT REPLACE('','',0) I have an nvarchar field in a table that contains numerical values ...
0
votes
1answer
29 views

SQL Server - Why does importing lots of columns with few rows takes way longer than few columns with lots of rows?

I am using the import wizard in SQL Server 2014. When I import a text file with 40 columns with 1 million row it is pretty fast. On the other side when I am importing a few rows with 268 columns it is ...
0
votes
0answers
34 views

Replication and insert error

I set up a merge replication between one Publisher and a subscriber[ the same tables on both] and everything seems to be working fine and there was not a single conflict in the initial setup and ...
-1
votes
1answer
66 views

Meaning of terms in SQL Server execution plan [closed]

I am finding difficulty in understanding the output of execution plan. Execution plan consist of terms like "Estimated sub tree cost", "Estimated I/O cost" etc. I can find the definition of those on ...
0
votes
0answers
10 views

Database patch management approach for client-hosted databases

I currently develop software (.NET WinForms) for enterprise customers. My customers host their MS-SQL database for my application internally (on-premise) and I don't have access to it. This has meant ...
1
vote
0answers
46 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 ...
3
votes
2answers
64 views

Union of results of all database query to also include database name

I'm hoping someone can help. I have a number of servers each with multiple databases on it. All of the databases have a table I want to query with a very simple 'select count(*) from section'. I ...
12
votes
1answer
256 views

Clustered index not used in a delete statement

I have a SQL Server table defined as follow CREATE TABLE [dbo].[Production_Detail] ( [Id] [bigint] NOT NULL DEFAULT (NEXT VALUE FOR [dbo].[Production_Detail_Seq]), [Meta_Data_ID] INT NOT NULL ...
2
votes
1answer
56 views

SQL Server 2008: how do I hide data from a DB Maintainance User?

Our organisation employs a third party company to do maintenance on our database. To be honest, currently they act as our DBA's since we do not have one. In order to do the maintenance work, they ...
5
votes
1answer
88 views

When you switch Table A to Table B, does the index data get switched as well?

I currently have a rather large table (5-7 million rows). This table is rebuild regularly by a procedure which builds the data in a staging table, then switches the data into the production table ...
9
votes
2answers
227 views

SQL Server database snapshots for integration testing

I am trying to define a way of working with a test database (in SQL Server) for our integration testing. My idea was to do these steps at the launch of the integration test assembly: create a ...
5
votes
1answer
254 views

Microsoft SQL Server creating an Inline function

Hey guys I'm completely new to SQL where I have to write a user-defined function (UDF) that calculates a student's GPA for a given time frame. The Inputs are StudentId int, ClassStartDateStart ...
0
votes
0answers
34 views

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed

Daily at 5:33:18 AM I am getting this alert , i checked error log and found these entry daily. what is the reason behind this and what is the fix ? Login failed for user 'NT AUTHORITY\ANONYMOUS ...
0
votes
1answer
146 views

Please explain why deadlock is detected in my example

I think Deadlock will happen when T1 lock tabA and T2 lock tabB. Then querying T1 to tabB (locked by T2) and T2 querying to tabA (locked by T1). So T1 and T2 will wait and wait till end of life! But ...
-1
votes
0answers
20 views

How to get CPU usage for the whole sql server (not single database) [duplicate]

I have collected and modified some sample codes to collect SQL CPU metrics as shown. IF OBJECT_ID('DISK.dbo.cpu_activity') IS NOT NULL DROP TABLE cpu_activity CREATE TABLE cpu_activity ( ...
1
vote
2answers
55 views

Please explain the difference between row and page compression

I'm trying to determine which to apply and when is the appropriate time to apply compression. I'm posting this question to get insight from this community. I have read several articles, but wanted to ...
-3
votes
1answer
145 views

How to achieve high performance management of a huge list of distinct strings [on hold]

I am trying to achieve high performance management of a huge list of distinct strings in SQL Server (but I'm open to suggestions of alternate platforms that might be more performant). My specific use ...
0
votes
2answers
40 views

Data recovery from system or application crashes in SQL Server 2012 [closed]

Can anyone please let me know what are the steps to recover the database after system is crashed? I would really appreciate it. I am a junior DBA with not a lot of experience started as intern. ...
0
votes
1answer
59 views

Coalesce on right hand table field with left join, or a better way?

I have two Sql Server 2014 tables one has "en-GB" cultured names and descriptions, and a second has translations for other cultures. The "en-GB" table... CREATE TABLE [dbo].[HomePages] ( [Id] ...
2
votes
4answers
77 views

Query To Return Data From MAX(ID)

I need to return only the 1 row of the MAX(ID), but my query is not returning such. This is the result set that I get returned id bbid indate st val 200817 16543 01/01/16 1 ...
0
votes
1answer
154 views

How to load entire database into memory?

I want to load my entire database into memory, but how can I do this? I have about 256 GB memory and my database is about 200 GB, so I can easily handle in memory. When I do select count(*) from ...
4
votes
0answers
71 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 ...
0
votes
1answer
38 views

Insert into matching on a WHERE clause

I'm sure this is simple but can't get it work. I have two tables, A and B, where one only A has ID and they both have Number. I'd like to copy all the ID's from A into B where their Number matches (...
5
votes
1answer
371 views

SQL Query for cumulative sum

I am experiencing problems with formulating a (relatively) simple SQL Query (SQL Server 2012 is used). We have a database which counts something up for certain users. Therefore we have a really simple ...
2
votes
1answer
63 views

How do I place an UpdateLock on a row with an index

Let's assume I have a table like this on a SQL Server 2014: CREATE TABLE Accounts ( Id int IDENTITY(1,1) NOT NULL, OwnerId int NOT NULL, Balance bigint NOT NULL CONSTRAINT PK_Account ...
0
votes
1answer
35 views

Unique key constraint violation not escalated to parent stored procedure

I have a stored procedure which calls 2 nested sub procedures. For Example, MyProc BEGIN BEGIN TRY BEGIN TRAN EXEC UpdateProc EXEC InsertProc COMMIT TRAN END TRY ...
1
vote
2answers
42 views

Determine ANSI_DEFAULTS setting ON or OFF?

How can I determine if ANSI_DEFAULTS is enabled or not on my server. My server is using IMPLICIT transactions and one way from which it can affect it based on setting of ANSI_DEFAULTS. How can I ...
3
votes
2answers
94 views

Collect disk space metrics for whole SQL Server instance

I am quite new to SQL Server. I would like to collect database disk space metrics for the SQL Server instance as a whole. I have found a code sample that can collect disk space information per ...
0
votes
1answer
75 views

How to detect bad execution plans automatically?

I want to automate a way to detect when a stored procedure plan changed for worse. I am planning to do it using a list of critical SPs and checking when their plans changed. Then compare the execution ...
1
vote
1answer
28 views

How to secure SQL Server being accessed from the DMZ

We have an internal stand alone SQL Server 2014 default instance that needs to be accessed from an application server residing in a DMZ. The application team wants to open the default 1433 port so ...
1
vote
0answers
57 views

NOLOCK vs Read Uncommitted

in our environment, reports are running on the subscriber and resulting in deadlocks with MS Replication SPs, which are doing inserts/updates. What's the best way to eliminate the deadlocks? We have ...
3
votes
1answer
66 views

Adding an ID column based on two other columns

I have a query which returns output as follows: [Location] [Reference] [Year] [Int1] [Int2] England 1 2015 13 201 England 1 2015 12 57 England ...
1
vote
0answers
18 views

Two Dimensional Partition

We have a new application that we anticipate will grow substantially and we're exploring partitioning now. If we have a database that includes information, for example, across continents, and we want ...
4
votes
1answer
84 views

Connecting a standalone Microsoft R Server to a SQL Server with R Services and a SQL Server without R Services

Let's assume I have 3 machines with me. My first machine has SQL Server 2016 with R Services installed on it. My second machine has SQL Server 2016 without R services enabled on it. My third ...
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 ...