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
0 votes
1 answer
2k views

Unable to load user-specified certificate in event log although SSL certificate is installed

Unable to load user-specified certificate [Cert Hash(sha1) "SOMERANDOMHASH"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See &...
0 votes
2 answers
60 views

Economical options for long term storage of MS SQL Reporting Data (Currently in Azure SQL)

We have a reporting database in Azure SQL that is passing 3TB and we need to decide what to do before we reach the 4TB limit. Azure Hyperscale SQL is not feasible as the storage cost is more than ...
0 votes
1 answer
738 views

Importing a .bacpac from SQL Azure into a local SQL Server 2016 server gives errors

I'm trying to import a .bacpac created from SQL Azure into a SQL Server 2016 server that has no existing databases beforehand. It will run but run into issues such as: Unhandled Exception: ...
2 votes
1 answer
499 views

SSIS: How to export more than 4000 characters to Excel

I have the following basic setup in SQL 2008 R2. This does not work however as I always get the errors Error 1 Validation error. SQL to XLS: SQL to XLS: Columns "Original" and "...
1 vote
1 answer
55 views

QueryStore plan forcing errors

We have a weekly stored procedure that ocassionaly picks up wrong execution plan that causes much longer execution times. I've tried to force correct execution plan but still every few weeks the ...
12 votes
2 answers
2k views

Should the filtering column(s) always be in the keys / includes?

I'm considering creating a filtered index in my copy of the Stack Overflow database. Something like this, for example: CREATE UNIQUE NONCLUSTERED INDEX IX_DisplayName_Filtered ON dbo.Users (...
1 vote
0 answers
97 views

SSMS User Mapping Slow to Load, or Not Responding

I have been experiencing an issue in SSMS versions 14, 18, and 19, where loading the User Mapping of any given Login takes an age to load, sometimes up to an hour, or just never loads at all and hits ...
0 votes
0 answers
48 views

Efficiently get next value after range and previous value before range

I have a series of measurements ordered ascending by a Coordinate (in millimeter), but it might as well be a timestamp. The distance between 2 measurements is irregular. I need all measurements in a ...
0 votes
1 answer
3k views

Require distributed transactions for server-to-server communication

I want to know limitations or disadvantages of enabling 'Require distributed transactions for server-to-server communication' on SQL SERVER 2012 please? My SQL database Server 2012 have a subscriber ...
0 votes
1 answer
479 views

SSRS Encryption errors post reinstall services

Initially, I installed and configured SSRS and backed up Encryption Keys, able to run reports successfully. Then I uninstalled the SSRS from the VM, dropped report databases, renamed the SSRS folder ...
0 votes
1 answer
35 views

Ideal VLF size - SQL Server transaction log

Is there an ideal size for the VLFs in a transaction log. This is a system with AlwaysON and Transactional replication. I was wondering if there is any cons to creating large VLF's. Eg, lets say ...
0 votes
1 answer
381 views

Does polybase bring entire external table data onto sql server?

I'm exploring sql polybase using Azure blob storage and other data sources. For blob storage sql server brings the entire blob onto the server and then performs where/join. What is the behaviour when ...
0 votes
0 answers
34 views

Need help to get a server to login to SQL Server database

I created a virtual machine (VM) on Azure to host Sparx Enterprise Architect cloud-based repositories using Pro Cloud Server (PCS) https://sparxsystems.com/products/procloudserver/30-min-tour/index....
1 vote
1 answer
283 views

Problem with unmasking a database user during a sproc

I am having an issue with a stored procedure that refreshes dynamic data masking on a database. This sproc is run as a job against all databases on this server. The problem that I have is that I have ...
0 votes
0 answers
28 views

Do stolen pages (as in sys.dm_os_performance_counters) report anything that sys.dm_os_memory_clerks does not?

I have been under the misconception that SELECT * FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Stolen Server Memory%'; shows how much memory the OS is stealing from SQL Server. The ...
-1 votes
2 answers
51 views

I want to find out how much is my database using and how much CPU are the function on that database is using

As I stated in the title, I want to find out how much cpu time my database is using VS how much cpu time all of the functions on that database are using. I want to show that the functions that are ...
3 votes
1 answer
874 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 ...
2 votes
1 answer
540 views

DB_CHAINING lost on on planned Availability Group failover for SQL Server 2019 cumulative update

I'm looking for ideas on how to determine a plausible root cause for DB_CHAINNG problems encountered when we recently performed a planned AG failover. We're using SQL Server 2019 Standard Edition with ...
0 votes
1 answer
18 views

Routing List Empty, and cant add using SQL either

Question: Why is my routing list empty? I have removed the secondary from the AG and re-added back, everything is healthy and synchronized. lets assume for the example that server1 is currently the ...
1 vote
1 answer
130 views

Performance degradation after migrating from SQL Server 2016 to SQL Server 2022

I have migrated our load test environment from SQL Server 2016 to SQL Server 2022 (Developer edition). Here are some findings: Changed compatibility level of databases to 160 (for SQL Server 2022) - ...
0 votes
0 answers
37 views

Can my database LDF and MDF files go into this directory?

Is there a way to determine, given a drive and a directory, if a CREATE DATABASE command would succeed? There are basically two questions here. 1. How do you determine if a simple command like this ...
0 votes
1 answer
18 views

Is there a way to verify if a SSIS instance was configured as a cluster resource or not?

We had 2 servers in a cluster ( no aoag ) with SQL + SSIS. one of the cluster nodes died and we removed it. we are in the plan to create a new cluster node and configure SQL engine normally on it as a ...
0 votes
1 answer
26 views

Changes to MSSQL System databases from Cumulative Updates

What sort of changes are made to System tables (master / model / msdb) by Cumulative Updates? For context, I have SQL servers that have AlwaysOn Groups, and am looking at a way to roll back updates if ...
1 vote
1 answer
63 views

how can I see how many records have been affected by a transaction before it is committed? or how costly would be the rollback if I have to kill it?

I am using sqlwatch to monitor some of my servers and it has a job that deletes in batch from a table called dbo.sqlwatch_logger_snapshot_header I had lots of problems of locking and blocking as this ...
3 votes
2 answers
3k views

Not able to Create Database or Restore Database on the Azure SQL Managed Instance

I am trying to restore database on the Azure SQL Managed Instance using Azure blob container URL. Reference CREATE CREDENTIAL [https://zzdevbackup.blob.core.windows.net/dbbackup] WITH IDENTITY = '...
1 vote
1 answer
488 views

Aggregate values over time in temporal table

Recently we started using temporal tables feature in MSSQL. Now our analytics department wants to write query aggregating data on per month basis. Let's say we have table +----+-------+----------...
0 votes
1 answer
2k views

Transfer data from MS SQL to PostgreSQL with SSIS ODBC destination

I try to sync data from MS SQL to postgres with SSIS. I configure ODBC destination using psqlodbc_12_01 driver downloaded from here My SSIS package has a single data flow task which just gets data ...
1 vote
1 answer
46 views

Can the plan cache be used to diagnose a lack of RAM?

I have learned a few good ways to diagnose a lack of RAM on a SQL Server and I am wondering if I should add the plan cache to this toolbox. A sign of lacking RAM is that the plan cache doesn't live ...
0 votes
0 answers
29 views

log_reuse_time_desc is ACTIVE_BACKUP_OR_RESTORE

After dropping database replication and subscriber, the database's option log_resue_wait_desc turned from replication to ACTIVE_BACKUP_OR_RESTORE. I should I do to return it into NOTHING value
-1 votes
1 answer
48 views

What context does the agent service account use (logon account or the NT SERVICE\SQLSERVERAGENT)?

My SQL server agent service account is running under the following domain user account: mydomain\svc_svr1_sql_agent I have a ssis package that must be able to read/write files in a folder. The ssis ...
0 votes
0 answers
24 views

Unresponsive restore process for log shipped db having memory optimised tables

Log shipping documentation has a caution: https://learn.microsoft.com/en-us/sql/database-engine/log-shipping/about-log-shipping-sql-server?view=sql-server-ver16 Known issue: For databases with memory-...
-1 votes
2 answers
82 views

Why does restoring a database on a different server bring in the backup info into the msdb db of new server?

I restored a db to a different SQL server but interestingly the msdb backupset tables on this server are showing the db backup history from the source server. Is this expected? Note: I have not ...
-1 votes
0 answers
22 views

SQLServer and memory usage [duplicate]

I have a Windows Server 2019 with SQLServer 2019 Standard Edition. The server has 48Gb Ram + 7 Gb in swap. I saw that SQLServer uses over than 50Gb. There is something to set to force it to act on how ...
-1 votes
1 answer
49 views

When patching SQL FCI, how to ensure there will not be a failover to the node being patched?

This is about patching a 2 node SQL FCI. Say node1 is the current active node. When I am patching the node2, how do I ensure that there won't be a automatic failover to node2 even if node1 crashes ...
0 votes
1 answer
69 views

Subsystem ID for TSQL when creating a Proxy for a SQLAgent job step

I have users who have the SQLAgentOperator role but not the SYSADMIN role. They can create SQLAgent jobs and set the job owner as 'sa.' These users can only modify their own jobs, but we want to give ...
2 votes
2 answers
95 views

Finding encrypted SQL database instances in our SQL Server Estate

Is there a quick way to find SQL Server instances across our whole SQL Estate that hold encrypted (SQL TDE) whole databases or database table columns?
-4 votes
0 answers
42 views

MSSQLSERVER service stops randomly [closed]

We have a Windows server running SQL Server 2019 Developer Edition. Randomly it is happening that the MSSQLSERVER service is stopping; it has been set to run automatically; we have to manually start ...
3 votes
2 answers
765 views

Permission hierarchy vs. WITH GRANT OPTION

I have an Azure SQL database with this security setup: SchemaUpdater user with db_ddladmin, db_datawriter, and db_datareader roles. The user is used to execute database change scripts during ...
1 vote
0 answers
64 views

SQL Server not using index in aggregates [closed]

We have a large table called 'TransactionTable' with around 20 million records and there's a index cover all columns used in below query. We also have a temporary table called '#TempTable' with around ...
0 votes
3 answers
10k views

Using Cursor to loop through a Table variable in SQL Server

I have a parameter in the Stored Prod which gets some data in the format 1/1/2018-2/1/2018,2/1/2018-3/1/2018,3/1/2018-4/1/2018,4/1/2018-5/1/2018,5/1/2018-6/1/2018,6/1/2018-7/1/2018,7/1/2018-8/1/2018,...
0 votes
0 answers
30 views

How automate backup and restore in Azure SQL Managed Instance?

I'm the DBA of one Azure SQL Managed Instance and one of our clients asked us if is possible automate a backup and restore in order to have a copy of one of the database before one of our ETL procces ...
0 votes
1 answer
425 views

Can I change database compatibility level from 80 to 90 or higher with log shipping?

I have a database on SQL 2008 R2 with compatibility level 80 and I want to change it to 90 or 100. I assumed that after changing, I may find a regression in our application's performance. Many backups ...
0 votes
2 answers
55 views

SQL Developer edtion as a new node within an Enterprise Availability Group

We currently have a 2 node Availability Group in our test environment running SQL Server 2017 Enterprise Edition. I need to downgrade these both to Developer edition, since we are not using this in a ...
0 votes
1 answer
44 views

Cannot disable Query store due to QUERY STORE ASYNC LOAD

I'm using Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64). One of our databases seem to have a run away Query Store with over 90 gb stored. I want to disable QS, but cannot do so ...
3 votes
2 answers
3k views

Are views logically redundant?

Earlier today, I realised that I had made a very stupid mistake. Rather than write a view, I wrote a parameterless inline table-valued function. This got me thinking: Interface aside, is there any ...
2 votes
1 answer
515 views

Handling new fields when merging in data using hashbytes?

We load in data from stage into our ODS and check for differences using hashbytes. We calculate hashbytes from the stage tables and insert/update into the destination table and also store the ...
0 votes
1 answer
288 views

SQL Server Agent log is showing error [298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01] (connexecutecacheableOP)

Can You please suggest me why it Happens and how to resolve this issue
-1 votes
0 answers
69 views

Using R and Python with SQL Server 2022 not possible

I want to use R and Python with SQL Server 2022, but I get an error if I try to exec the Python syntax. I did everything that Microsoft recommends: https://learn.microsoft.com/en-us/sql/machine-...
1 vote
2 answers
104 views

Prevent users from sending an email attachment in sql server

In SQL Server, I want to restrict all users (except a few specific users) to send an email with attachment. For example, someone import data into excel file and send it via mail using the ...
18 votes
6 answers
65k views

SQL Server 2016 DB Mail Not Sending

I'm on SQL Server 2016 and having a blast... my DB Mail isn't sending and I'm running out of places to look. I double checked the SQL account permissions to the DBmail executable - it has read and ...

1
2 3 4 5
680