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
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 ...
-7
votes
0answers
31 views

SQL Rebuild/Re indexing [closed]

I got a problem... We have almost 1 TB DB in size and it's really a risk attempt if I will for for Rebuild/Re indexing bcoz it takes huge time even DB server performance gets deteriorated. Please ...
5
votes
1answer
87 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
223 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
56 views

What is the purpose of sequence? [closed]

Is it good practice to use the object "sequence" for GENERETING unique primary key values. What is the purpose of sequence in both Oracle Sql and Sql Server?
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
33 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
145 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
144 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
39 views

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

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] ...
-3
votes
1answer
71 views

Sql join multiple tables into one [closed]

Table 1 - item_code, allocation_qty Table 2 - item_code, issue_qty Table 3 - item_code, return_qty Table 4 - item_code, po_pending_Qty Table 5 - item_code, stock_qty I wish to combine ...
2
votes
4answers
76 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
149 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
370 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
62 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
34 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
91 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
73 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
56 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
65 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
79 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 ...
2
votes
1answer
34 views

SQL Server 2005 still running after in place upgrade to sql 2012

We upgraded a default instance from SQL Server 2005 to 2012 today. But Components of SQL Server 2005 are still on the server. These includes the MSQL1. folder (it has all the data and log files), Mgmt ...
1
vote
0answers
39 views

Where can I find information on what resources are consumed when connections are established with MSSQL 2012?

I'm trying to troubleshoot the following intermittent sql connection error: "DESCRIPTION: SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. ...
2
votes
1answer
227 views

Is there a better alternative than LIKE for this query?

I am trying to retrieve data from a table where there are codes for projects which are identified by the first three numbers, the rest are unique. So, for example, the query is: SELECT * FROM TableA ...
4
votes
1answer
94 views

Should we really turn on trace flag 1118 for every SQL instance?

It has been recommended that every SQL instance have TF 1118 turned on What is Paul’s recommendation for using trace flag 1118? Everyone should turn it on, on all instances of SQL Server, from SQL ...
3
votes
1answer
54 views

sp_BlitzIndex - “Cannot Insert the value null into column 'index_sanity_id'…”

Hi I'm getting this error message from the latest version of sp_BlitzIndex on one of my test servers: database failed to process. Cannot insert the value NULL into column 'index_sanity_id', ...
3
votes
1answer
51 views

How to script XML Schema collection in SQL Server 2012?

I need to make script of XML schema collections in my Database and then insert each xml inside a table field as string. I need to do this to compare current schema collections with the new schema ...
0
votes
0answers
26 views

Custom SQL Server replication

I want to have bidirectional replication and I don't want to reinitialize subscriptions after adding filtered tables because it's a pain (merge and transactional replication force to reinitialize ...
4
votes
1answer
765 views

How can I handle unwanted, non-erroneous situations better?

When writing procedures, I occasionally encounter situations in which I want to abort the procedure, even if the situation didn't necessarily trigger an error. Let's say, if I don't want John to be ...
3
votes
1answer
34 views

Why does creating database snapshot fail unless user is db_owner or sysadmin? MSDN suggests it should work

MSDN suggests that CREATE DATABASE permissions are all that is necessary to create a snapshot. Any user who can create a database can create a database snapshot; however, to create a snapshot of ...
0
votes
2answers
89 views

Can I convert date format for this value '19920000'?

I have a value 19920000. This is date format yyyyMMdd. I want to save by date field in the table. You may see it is invalid data because day and month value is zero. Can I convert date format to this ...
6
votes
2answers
92 views

SQL perf issue - configure server correctly first or troubleshoot issues one by one?

This is for SQL Server 2012. I am troubleshooting overall slow performance on a Dynamics AX environment. Everything is just slow - app itself, reports, ad-hoc, etc. Right off the bat, I can see ...
6
votes
1answer
138 views

Failed allocate pages: FAIL_PAGE_ALLOCATION 1

We are on SQL Server 2012 SP3 Enterprise Edition on Windows 2012 R2. I saw these errors in the sql logs: Failed allocate pages: FAIL_PAGE_ALLOCATION 1 2016-06-14 04:28:27.44 spid175 ...
0
votes
0answers
42 views

DB2 While Loops

I have written some SQL for MS SQL Server that does what I want it to do. DECLARE @UpdateCount INT = 1; DECLARE @BatchSize INT = 1000; WHILE @UpdateCount > 0 BEGIN UPDATE tb SET tb....
0
votes
1answer
23 views

Log Shipping Disk Sizes

Do the drives that contain the database have to be the exact same size on the primary server as secondary? I currently have a primary database for OLTP and a standby database used for reporting. I'm ...
2
votes
1answer
51 views

Race condition on incremental backups

I am planing to automate to take incremental backups on a range of sql servers from 2000 to 2012, on a variety of windows too. This will be done with a vbs script that runs on task scheduler. I want ...
0
votes
3answers
68 views

Clustered Index and PK - Two different Objects?

I have a table which has a Clustered PK as follows: CREATE TABLE [dbo].[PanelCcmRawData] ADD CONSTRAINT [PK_CcmRawData] PRIMARY KEY CLUSTERED ( [CcmRawDataId] ASC, [LineageId] ASC ) I ...
2
votes
1answer
47 views

SQL Server 2014 Reporting Services issues

I have set up SSRS on MS SQL 2014 Dev Edition using the ReportServer built in account as the Service Account. My machine name is: POUYASAGER My local login is pyous I have given the ReportServer ...
20
votes
1answer
268 views

SQL Server 2014: any explanation for inconsistent self join cardinality estimate?

Consider the following query plan in SQL Server 2014: In the query plan, a self-join ar.fId = ar.fId yields an estimate of 1 row. However, this is a logically inconsistent estimate: ar has 20,608 ...
4
votes
1answer
201 views

SQL Server 2016 Query Crashes Server

SQL Server 2016 continually crashes when running an ETL source query. Memory starts consuming at a fantastic rate then hits max and the service shuts off. This seems ridiculous, especially since the ...