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
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
204 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
31 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
437 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
19 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
103 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
121 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
18 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
733 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
67 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
52 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
40 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
39 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
58 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
76 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
44 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
74 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 ...
-3
votes
1answer
53 views

Script out spaceused stored procedure to edit it

Is it possible to script out the sp_spaceused stored procedure so I can edit it to include log space information and sort of create my own custom stored procedure? I would like to put the results in ...
1
vote
1answer
29 views

Pre-checking execute privs for SPs

We have a number of DBs where EXECUTE privs are required on a SP by SP basic. We have other DBs where we don't need to do this at all. In the latter case, I understand that this is a blanket GRANT, ...
3
votes
2answers
42 views

Changing Database Transaction isolation level to SNAPSHOT for SQL Server replication “distribution” database

I have a couple transactional replication publications that make large changes (nearly 1 billion records) to a publication article. These transactions are captured by the log reader and stored in the ...
4
votes
2answers
129 views

Will a DBCC CHECKDB with repair have to rescan?

I've run a DBCC CHECKDB and it found allocation errors. When I run it with REPAIR_ALLOW_DATA_LOSS - will it need to take the same amount of time to scan for errors, or will it attempt to use the last ...
4
votes
1answer
62 views

Log backup vs differential backup if latter is broken

Under full recovery mode, will a differential backup "break" the previous log backups? Let me give an example: Let's say we have the following backups: FULL backup 1 Log backup 1 LogBackup ...
1
vote
0answers
48 views

SQL Server 2016 - row level security by AD group membership

I'm wondering if it is possible to use row level security based on a users AD group membership. I would love to have something that could support users in different groups and some super users who ...
3
votes
1answer
55 views

Auto created Statistics vs Manually created Statistics

Is there any difference between the Statistics that got created automatically by Query optimizer and the one that created with CREATE STATISTICS statement. Will the manually created statistics get ...
1
vote
0answers
13 views

What settings / statements are pushed to subscribers in transactional replication?

Version: SQL Server 2014 (Web edition) Replication: Transactional Are the following pushed to subscribers in transactional replication? SET_QUOTED_IDENTIFIER statement; SET ANSI_NULLS statement; ...
5
votes
3answers
562 views

use of coalesce to check for all variables being null

How can I use coalesce to check for all variables with different type precedence to all be NULL without encountering errors. DECLARE @StartDate DATETIME = NULL, @EndDate DATETIME = NULL, @StateCode ...
15
votes
4answers
668 views

Why can't a differential backup specify its base?

This is my first DBA.SE post, so please inform me of any mistakes, thanks! I am a new DBA (not an IT pro, just no one else in the company to do it), so the more basic the explanation the better. I ...
2
votes
1answer
73 views

Microsoft SQL Server AG Synchronous Mirroring is not synchronous

We were upgrading to SQL Server 2012 and decided we wanted to divorce ourselves from replication we were using for Reporting services on Node 2. AG and mirroring looked like a good solution -- and we ...
4
votes
1answer
158 views

TDE - Encryption on a large database

I'm in the process of researching TDE implementation on a VLDB and would like to know if anyone has real world experience with enabling encryption on a production server. What kind of problems (if ...
17
votes
1answer
215 views

Why does this MERGE statement cause the session to be killed?

I have the below MERGE statement which is issued against the database: MERGE "MySchema"."Point" AS t USING ( SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region" ...