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)

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
0answers
48 views

how to insert data from a tableA(ID,Items) into tableB(c1,c2,c3…c15) [closed]

Suppose tableA has data like this: I have to insert into table B and also 1=Y and 2=N like this SELECT * FROM #temp1 PIVOT(max(ID) FOR Items IN([897455X7X35] ,[897455X7X39] ,"897455X7X43" ,"...
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
728 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
21 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
52 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
128 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
47 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
155 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" ...
2
votes
3answers
86 views

Full Outer Join shows NULL when Group By is used

I have 4 subqueries and each of the subquery is grouped by 'Group name'. The attempt is to have each subquery as a column all grouped by the 'Group Name'. Here is the query: select coalesce(co....
2
votes
0answers
50 views

“Access is denied.” for xp_cmdshell for network share

On a SQL 2014 SP 1 instance a SQL Agent job I'm using xp_cmdshell to return a list of files from a network share. This has recently stopped working and returns "Access is denied." The command is ...
11
votes
1answer
152 views

Will running a large query on a secondary database in an availability group affect transaction performance in the primary database?

I need to provide real-time, or almost real-time, data for SSRS and Tableau reporting. I don't want the production OLTP system to be negatively impacted by long running queries. Will running a large ...
0
votes
0answers
7 views

Transient Log Shipping Restore Error - Could not redo log record (172980:741440:22), for transaction ID (0:1889746485), on page (1:28106052)

I'm running SQL Server 2008 r2 sp3 and am taking transaction log backups every 5 minutes from my primary server in a log shipping scenario. I have multiple standby servers that are log shipped to. On ...
1
vote
1answer
50 views

SSIS package succesfully completes but does not import 100% of records from flat file into table [closed]

I have a fixed width text file. I have created an SSIS package that imports the file into a table in a SQL 2014 server. When I open the file (with notepad++) i see that it contains 259,735 records. ...
5
votes
0answers
34 views

Excessive Prelogin Packets

Running a client/server application where the client app is powered by MS Access and the database on the server is in SQL Server. We have two locations and have active users at both. We recently moved ...
0
votes
1answer
37 views

SQL Server 2014 - AlwaysOn HA - Always Synchronizing

I have a Windows Server 2012 R2 cluster with SQL Server 2014 configured in a HA configuration. The secondary databases always say Synchronizing when the data movement is happening. Is this normal? ...