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)

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 ...
2
votes
1answer
36 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
40 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
231 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
95 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
54 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
28 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
768 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
35 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
90 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
93 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
146 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
44 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
25 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
53 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
51 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
280 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
215 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 ...
0
votes
0answers
19 views

Schema Locks blocking during Alter Table Add Constraint?

I am dealing with a customer using MS SQL Server with Entity Framework to generate their database upgrades. EF generates a "Migration Script", which in this case is a fairly straightforward collection ...
3
votes
1answer
32 views

TempDB's Size and the Buffer Pool

Source: Next, if you can give tempdb its own disk, then configure it to almost fill the drive. If nothing else will ever be on the drive, then you’re better off setting it to be larger than ...
0
votes
0answers
54 views

select * into taking too long

I have an SP where we preselect some data into temp tables using select * into. From my understanding it doesnt copy over index,constraints. For last week i have seeing performance difference for ...
0
votes
0answers
30 views

SQL Server 2012 Missing Index

I am trying to optimize the database by creating missing indexes, but dm_db_missing_index_details table have not any data. I am having same data base on different server and there are many records in ...
0
votes
1answer
14 views

Visual Studio 2013 and up - Database Project Schema Compare File

Is there any reason to save the schema compare file once you are done comparing, creating scripts or just simply updating?
4
votes
2answers
440 views

Clustered vs Nonclustered indexes, similar to arrays vs linked lists?

I'm just starting out as a DBA, and have recently been interested in indexes and how they speed up queries. I was reading about indexes here: https://msdn.microsoft.com/en-us/library/ms190457.aspx ...
3
votes
2answers
173 views

Partitioning a large table has not improved performance, why?

In SQL Server 2014 I have partitioned one of my large tables weekly and defined a sliding window scenario to switch the oldest week's data to the archive DB and create a new partition for the next ...
0
votes
0answers
20 views

Replicate SCCM2012 DB to secondary read only server

We have a SCCM2012 DB that we're looking to replicate out to a SQL2012 or above server. The reason for the replication is for a new ITSM system to use ODBC to extract information about devices without ...
3
votes
3answers
104 views

Query Spatial Data for Nearest Neighbor for each group

I want to query spatial data for nearest neighbor. I am using this article and the following query works perfectly: SELECT TOP 1 [Location].STDistance(@Location) FROM [DS1] WHERE [Location]....
-3
votes
1answer
124 views

Downgrade backup from SQL Server 2014 to SQL Server 2012 [duplicate]

Im trying to restore my database on my server, but i got the following error: The database was backed up on a server running version 12.00.2269. That version is incompatible with this server, ...
1
vote
0answers
81 views

same float data sum - return different result sql server [closed]

I summed the same float data it returns different result. Any help? The column Value is float. SQL command used to sum: Select sum(Value) from myTable There is no index in this table. My ...
-6
votes
0answers
30 views

SQL Database managment studio [duplicate]

i want to dump my database from one machine to another machine automatically by using SQL managment studio .Suppose i have two servers on server1 i have database1 and on server2 i have database2 i ...
0
votes
0answers
19 views

Merge replication snapshot folder cleanup

I have setup merge replication and facing some issues with the snapshot files cleanup. I understand the distribution agent is responsible for this cleanup but it does not seem to purge old files. It ...
-1
votes
1answer
66 views

How much data in one table before it hurts performance

I'm about to develop a database for a community. The database is MS SQL Server. Each user can create their own shop where they can advertise about their products. So let's say I create a table to ...
0
votes
0answers
113 views

Which is preferable With clause or formulas in from clause

Query with with clause with temp_table(fName,sCode,sDesc) as( select case left(shipping_info.ship_description, (len(shipping_info.ship_description) - 11)) when 'John Doe' then 'Johnathan'...
0
votes
0answers
27 views

How to troubleshoot the “lock request timeout exceeded” error?

Scenario: Usually I received "lock request time-out exceeded" message on client's server. This is not fixed by time and other things. I am getting details of executed SQLs by running query with sys....
22
votes
2answers
757 views

Methods for finding new Trace Flags in SQL Server

There are a lot of Trace Flags out there. Some are well-documented, some are not, and others found their way to default behavior status in the 2016 release. Aside from official support channels, ...
3
votes
1answer
70 views

Changing SQL account password in specific database

I am trying to change the password for userId in a specific database called LionsDB. If I am changing the password manually, I would login into SQL Management Studio using that user account and update ...
1
vote
3answers
54 views

Backup throws permission error

I'm trying to backup SQL Server database to upload it to my database server. I am using Microsoft SQL Server Management Studio, Database -> Tasks -> Back Up... And I got the following error: An ...
1
vote
0answers
41 views

Different sporadic SQL Server errors

First question on this SA but I feel my problem is more Database related than code related. I have multiple Delphi Datasnap servers connecting to SQL Server 2012. The SQL server has multiple ...
-3
votes
2answers
76 views

Case statement by using IsNull and IsNotNull [closed]

ALTER FUNCTION [dbo].[addC]() RETURNS varchar(255) AS BEGIN DECLARE @desc varchar(255); DECLARE @desc1 varchar(255); SET @desc = '' SET @desc1 = '' Declare c1 Cursor for ...
1
vote
0answers
22 views

McAfee antivirus and File stream SQL Server 2008 R2 Issue

I am facing file stream issues in SQL Server 2008 R2 and I am not able to correlate things and how to fix them. Below are the event logs. Please let me know if they both the errors (One is Mccafee ...
1
vote
1answer
40 views

News Database Design for a Desktop Application

I am making an application that will process a big chunk of information. This information was retrieved using some web crawlers and is about news, containing data such as News Title, URL, Publication ...
1
vote
1answer
62 views

Convert hierarchy in an ordered list of rows

I tried to make a simple list that represents hierarchy of items. Tags have simple structure like [Id] ,[Name] ,[ParentTagId] I try to make the follwing result table: ID, ParentID, Name, Lev,...
6
votes
1answer
79 views

Some SQL Server geospatial queries take much, much longer than others

We have a simple SQL Server table with geospatial data that looks like this: CREATE TABLE [dbo].[Factors]( [Id] [int] IDENTITY(1,1) NOT NULL, [StateCode] [nvarchar](2) NOT NULL, [...
1
vote
1answer
46 views

Restore database from script created by the “Generate and Publish Scripts” wizard

I have this database I need to move to a test server. The only way I can do this according to my searches is to create a script. The database is on SQL Server 2014, I am using 2012). I can get the ...
3
votes
1answer
29 views

Maintenance Plan on Replication Subscriber

I have seven subscribers to a replicated publication using transactional replication in SQL Server 2012. Do I treat the subscribers just like a normal database and perform maintenance via a ...
2
votes
1answer
41 views

Query 2 tables one with 1 row and the other with multiple rows

I have a part table: ID, Description ABC-123 ABC DEF-456 DEF I have a User Defined Fields table that stores information in a single row. ID, DOCUMENTID, STRING_VAL, DATE_VAL ...
1
vote
1answer
76 views

Are Database duplication and Mirroring one and the same? [closed]

I am going over the concepts of Distributed database management system. I read about duplication and replication. Then there is also something called Mirroring. I googled to find if duplication is the ...