Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

Questions tagged [sql-server]

All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.

Filter by
Sorted by
Tagged with
2
votes
1answer
13 views

SQL Server 2017 Distributed Availability Groups

Please suggest what are the steps/commands need to follow during below situation. Env: Configured SQL Distributed AG from DC1 to DC2. DC1 (Production): TEMPDB1 AND TEMPDB2 (HOSTED ON WSFC1) DC2 (DR)...
0
votes
0answers
5 views

Check linked server database status registered in the DBConfig Database

I am working with the Code below to check all the entries in the DBConfig Database of all the databases linked to the instance Server- LSNR_ADMINDB DB _ DBConfig However when i run the Code, it ...
0
votes
1answer
25 views

What is Accepted NTLM SPNs

In SQL Server Configuration Manager on the Advanced tab in TCP/IP Protocol properties box there is an option Accepted NTLM SPNs. What is the use of that option and how it can be used?
0
votes
1answer
30 views

Unusual deadlock occuring

I have spent a few days trying to debug a deadlock that occurs in production. This is one I don't understand however. Below is pseudo code for the proc. The proc deadlocks with another instance of ...
0
votes
0answers
8 views

update statistics in ola.hallengren maintenance solution

Is there a reason the default IndexOptimize job not include: @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y' We will have to remember to change t incude above two lines to the job. Is there ...
0
votes
0answers
22 views

Replicate data with minimal downtime using transactional replication

I have a SQL Server database with nearly 7TB of data. I want to replicate it using transaction replication. What will be its approximate downtime? Please suggest a method to implement it with minimal ...
0
votes
1answer
36 views

Do I still need to disable or drop column store index before insert and/or update on big tables?

As the title says, I thought that from MSSQL 2016 I don't really have to drop or disable ColumnStore indexes before ETL and rebuild it after. Any consensus?
-1
votes
0answers
29 views

Junction Between Multiple Database Schema's

The current plan is to internally develop new applications to automate/digitalise several processes and to replace old clunky applications. For this there is an idea to use one database for all ...
1
vote
1answer
60 views

What does “sample_ms” and “num_of_reads” means in “sys.dm_io_virtual_file_stats”. what is the interval time of stats

As per MS-Docs it returns the Number of milliseconds since the computer was started, but i'm getting different result when i compare with actual server start time in sys.dm_os_sys_info. Here are the ...
1
vote
1answer
86 views

Single Publication With Multiple Subscribers

What should I be aware of before adding multiple subscribers to single publication? Obvious ones I can think of are server capacity and network bandwidth. Should I be aware of anything else?
1
vote
1answer
90 views

Ignoring Null (optional) Variables in Stored Procedure

I am looking for what is hopefully something easily achievable without a ton of coding. I have a stored procedure that does some work and has about 20 optional parameters: CREATE PROCEDURE dowork ...
0
votes
1answer
27 views

Is there a way to figure out an executing query's plan handle by the query text?

Is there a way to determine the plan handle for the query plan of an executing query by that query's text? Perhaps in any of the DMVs? (I'm trying to find the query stats on an ad-hoc query.)
0
votes
1answer
20 views

Capturing Column Names in Dynamic SQL Statement

I'm in the process of updating some procs made for SQL 2008R2 to 2016 that are using a dynamic result set. Assuming there is a procedure that always has exactly one result set, but the result set has ...
2
votes
2answers
42 views

SQL - Leveraging an index on another column with the same ordering guarantee

I have a massive table that is only indexed by the auto-increment column, which is also populated with the current timestamp which is not indexed. CREATE TABLE MyTable ( ID BIGINT NOT NULL ...
7
votes
3answers
6k views

How to detach corrupt database in SQL Server

I have Sql server data files saved on external storage. I want to detach specific database. If external storage is not connected I want to drop database. Is this safe way to do that: DECLARE @...
5
votes
6answers
17k views

Backup database directly to FTP server

Is it possible from SQL Management Studio to create a backup directly to an FTP server? Basically, instead of choosing the destination to a disk, I sould like to be able to specify an FTP location (...
6
votes
2answers
2k views

Is there a way to have SQL Server format a number into ordinal form?

Data 1, 2, 3, 4, 11, 21, 99 I want to get 1st, 2nd, 3rd, 11th, 21st, and 99th I could roll my own, but is there any function built into SQL server that could so this for me? Corollary: I will also ...
2
votes
1answer
34 views

Imported SSIS package fails if imported with SSMS on the SQL server, but works when imported from a remote PC

I am facing a strange issue on SQL Server 2017 Enterprise edition running in AWS on EC2. When we are on the SQL server and we import an SSIS package using the locally installed SSMS connected to ...
0
votes
1answer
35 views

How to Design Name and Address with Multiple Addresses

I have a names and addresses table: Name Address AddressType ID CustID Sample: Bob Smith, 123 Main St, Corporate, 1, 1 Bob Smith, 123 Main St, Home, 2, 1 null, P.O. Box 123, Warehouse, 3, 1 ...
2
votes
2answers
185 views
+100

SQLServer Row level security - Enforce from within database

Have a multi-tenant app running on a single SQL Server 2016 db. I have a RLS (row level security ) predicate on customer_id (unique for each tenant) and the column exists in all tables. CREATE ...
0
votes
2answers
18 views

How to set AUTHORIZATION on CREATE SCHEMA

I'm looking to create a new schema for an existing database, which currently only uses the dbo schema. I'm after advice on how to set the AUTHORIZATION (owner name) and what affect this will have. 1....
28
votes
2answers
14k views

Why does SQL Server require the datatype length to be the same when using UNPIVOT?

When applying the UNPIVOT function to data that is not normalized, SQL Server requires that the datatype and length be the same. I understand why the datatype must be the same but why does UNPIVOT ...
4
votes
1answer
8k views

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' for SSIS

I have a SSIS package which is perform data migration between MySQL database and SQL server database. When I manually run it from visual studio or from SQL Server Management Studio, it ran ...
4
votes
2answers
30 views

sysmessages table issue

I'm experiencing an annoying problem with a table named SysMessages in one of our databases. When I run a select statement I don't get the contents of this table, but of the sys.sysmessages view in ...
0
votes
3answers
62 views

Can I change tempdb size to a specific value and can I shrink it during our daily working hours on our production database

Can I change tempdb size to a specific value and can I shrink it during our daily working hours on our production database ?. Please tell me if it has bad affect on production database Please be ...
10
votes
3answers
20k views

How to add a Default constraint while creating a table? SQL Server [closed]

I am trying to create a new table with columns followed by their constraint as shown below. Create tblTest( columns.. .. .. Gender int, Constraint DF_tblTest_Gender Default 3 For Gender, .. .. .. ) ...
9
votes
1answer
872 views

Minimal Logging Conditions in SQL

I have written a script to test out the claims made on this page http://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx in the table titled Summarizing Minimal Logging Conditions about ...
12
votes
2answers
46k views

Does a simple select query acquire locks?

I am very new to SQL Server, and would like to understand whether the following, very simple select statement would take any locks. Select * from Student; Please consider the case where the ...
0
votes
1answer
35 views

Use expression in window function that references columns from the current row

Suppose I have the following query that uses window function: SELECT id , var , num , SUM(var * num) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS calc FROM (VALUES ...
33
votes
2answers
9k views

Does the order of columns in a PK index matter?

I have a few very large tables with the same basic strucure. Each one has a RowNumber (bigint) and DataDate (date) column. Data is loaded using SQLBulkImport every night, and no "new" data is ever ...
2
votes
0answers
43 views

How to install Distributed Replay (dreplay.exe) for SQL Server 2017

In SQL Server 2014, dreplay.exe was available under "Management Tools – Basic" feature. However, in SQL Server 2017 that feature no longer exists (Enterprise edition) and it doesn't come with SSMS ...
8
votes
2answers
12k views

Updating a table efficiently using JOIN

I have a table that has the details of households and another that has the details of all the persons associated with the households. For the household table I have a primary key defined using two ...
1
vote
1answer
146 views

Opening Symmetric Keys

I am trying to open a symmetric key I created in a database, but it doesn't seem to be working. I am executing the query as dbo and it even says that it is completed successfully. However, the value ...
0
votes
0answers
19 views

SQL Server 2017 Machine Learning repair fails

CU17 update failed on an Enterprise 2017 box, both R and Python (we use both). I removed them both. Trying to Add Feature, and R still fails - Python is good. Removed R again, after stopping ...
-1
votes
0answers
29 views

How to set up a custom table for self calculated statistics [on hold]

I want to to do some analytics on my database and collect the results in a custom table or report so I can export the collected values afterwards using my c# tool to generate a PowerPoint presentation....
0
votes
2answers
100 views

Set who can restart/stop/start DB services

Currently we have 3 AD users that can log into a Windows Server: MRT\administrator MRT\adminJohn MRT\adminAlice They all logged into the Windows Server 2012 and then does the following: MRT\...
0
votes
3answers
59 views

Nullable smallint column usage in a CASE Statement nested in a STUFF function

I'm having issues writing a Stored Procedure that uses a CASE statement inside of a STUFF function. I thought my issue may have to do with Restricted being a nullable smallint. I tried converting it (...
0
votes
5answers
98 views

Restore DB Backup with limited amount of disk space

I want to restore a SQL Server database using a backup (.bak) of about 60GB. Is it possible to do this on a machine with only 100GB of free disk space? Or do I need at least the 60GB for the .bak ...
0
votes
2answers
136 views

msdb.dbo.sp_send_dbmail temp table does not exist

I have created a temp table and would like to email the results I am getting the following error. Query execution failed: Msg 208, Level 16, State 1, Server PRODYCHDBX3\X3V7, Line 1 Invalid ...
-1
votes
2answers
39 views

Removing multiple values using if else statements?

I have a program that downloads raw data from a sensor. Due to to some specifications made in the SQL code, there are values that are duplicated which needs to be deleted to maintain data integrity. ...
4
votes
1answer
184 views

After upgrading to SQL Server 2019, function throws “insufficient memory” error

I have moved an SQL Server 2012 Database (8GB in size) to a newly setup SQL Server 2019 virtual machine with the same memory and CPU configuration and changed the compatibility level to SQL Server ...
2
votes
1answer
83 views

Snapshot Folder Location for Replication with Clustered Instance

I am assigned to create a transactional replication with a SQL Server clustered instance for reporting purposes. I'm planning to use the Distributor and the Subscriber on the same box. Regarding the ...
8
votes
4answers
1k views

Foreign Key with a Constant

Let's say I have a table A, that has two columns: one is an ID for ThingA, and one is an ID for ThingB. The primary key is (ThingA, ThingB). Next, I have a second table, but this time it's restricted ...
7
votes
2answers
3k views

Accidently Named all tables in my database starting with a number

I've accidently named all my tables (100+) in my sql database with the prefix 2019_ Wasn't aware this was not a good idea until just now (just returned to SQL after 2 years) Is there any way to bulk ...
0
votes
0answers
36 views

Question about data type to hold large number

I have a line of data that store on Microsft SQL Server 13.0.4 in numeric(32,16). It looks like this 9642327391274.0000000000000000 I try to migrate that database table to another on Azure SQL ...
0
votes
1answer
276 views

When is dm_exec_procedure_stats updated?

I just created a new proc and ran it, but it is not showing up in my query: SELECT schema_name(schema_id) SchemaName, name ProcName, last_execution_time LastExecuted, last_elapsed_time ...
2
votes
1answer
53 views

What's the cause for an AlwaysOn AG Secondary Replica to have a high Redo Queue Size and Estimated Recovery Time with a good Redo Rate?

SQL14 is the Primary server, SQL16 is the Secondary Replica, and they're setup using synchronous commit availability mode: As of yesterday it appeared that data stopped synchronizing from the primary ...
7
votes
1answer
151 views

Is there a way to determine what kind of data is sitting in the Redo Queue for an AlwaysOn AG Secondary Replica?

Do any of the DMVs or is there another way to expose what kind of data is in the Redo Queue that's in process of being synced up to a secondary replica? (E.g. is it table data and which table, or is ...
3
votes
1answer
56 views

Skipping VM backups for high workload SQL Server?

For those who have virtualized SQL Servers with very high volume transaction workload (very high rate of data change), I am curious how you handle VM backups, or if you even do them. Of course, I ...
3
votes
1answer
106 views

SQL Server 2012 - How to Remove Partition on Publisher but not Subscriber?

Is it possible to setup partitioning on a SQL Server 2012 publisher table, so that older data can be removed without affecting the same table on a subscriber table? Table exists on Database A (SQL ...