Skip to main content

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
1 vote
1 answer
42 views

Need to add user and assign roles automatically, using DDL Trigger, when database is created

I am trying to create an automated process that will add a user and assign them the roles of db_datareader, db_datawriter and db_ddladmin whenever a database starting with PA is created. I have ...
0 votes
2 answers
916 views

how to find backup history of SSAS database

Shockingly on Internet, I am unable to find backup history of SSAS database. Is there no way to do it? Also should we take multiple backups of SSAS database? I am not much aware of SSAS database. ...
4 votes
1 answer
138 views

MSG 14262: The specified @server_name ('<server_name>') does not exist

We have an SQL Server 2019 instance (let's call it 'sample_instance') with server collation of Turkish_CI_AS. When I try to create a job, SSMS creates the part of the script that adds the target ...
17 votes
4 answers
39k views

Get detailed output of SQL Agent job

We have an SQL Server Agent job that runs a maintenance plan to reindex all of the database on the server. Recently this has failed but the job history is not providing enough information to diagnose ...
1 vote
2 answers
2k views

How can I get the list of tables in all the stored procedure

I am trying to get the information about all the tables belongs to the stored procedure. I was able to get this information from below query. But, this query only gives information about the tables ...
0 votes
1 answer
28 views

How do I consolidate the related mappings?

I have a table which has mappings coming from different sources (one row for each source) for three different identifier types (idtype1 to idtype3) with idtype1 having the highest priority and idtype3 ...
0 votes
1 answer
23 views

Is DBCC CHECKFILEGROUP worthless if you already run DBCC CHECKDB on all databases?

DBCC CHECKDB makes many other integrity checks redundant, but I can't see anything in the documentation saying if it makes DBCC CHECKFILEGROUP redundant. Is DBCC CHECKFILEGROUP worthless if you ...
0 votes
1 answer
3k views

error 15466 An error during decryption

I have a stored procedure that runs xp_cmdshell with a user with sysadmin right.(it was created by someone else long time ago). For security purposes I need to remove the sysadmin right from this user....
-2 votes
0 answers
25 views

SQL Server: Two MERGE statements are blocking each other on a large partitioned table

Have a large table partitioned on an INT column. When I run two different MERGE statements on two different partitions of this table, they seem to be blocking each other. Example: -- Large table ...
2 votes
1 answer
1k views

"Login failed for user..." with cross-instance access on same server in SSIS package

I have 2 SQL Server instances running on the same server. Lets call the instances Instance_A and Instance_B and the server Svr0045. Both server use the same service accounts (a service account is ...
0 votes
2 answers
421 views

Is socket a synonym of NUMA, and virtual processor synonym of physical processor in a virtualized SQL server environment?

Link: https://learn.microsoft.com/en-US/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15#Recommendations I understand the ...
-1 votes
2 answers
355 views

What is the relation between renaming the computer and sp_dropserver/sp_addserver?

I have default SQL server instance (not a named instance). have changed the SQL server computer name, and not run the following commands: sp_dropserver 'oldname'; sp_addserver 'newname', 'local'; ...
0 votes
0 answers
52 views

Problem with many connections from RHEL 8.10 to SQL Server

I have simple script: for i in {1..1000} do isql -v -k "Driver={ODBC Driver 17 for SQL Server};Server=mssqlserver,1282;Database=dbname;UID=user;PWD=xxx;Encrypt=no" >> aaa.log & ...
2 votes
1 answer
2k views

A beginner question on Primary Key VS Unique Key, Not Null

I am new to Databases and while i was studying about constraints in SQL. I came across Primary Key, Unique Key, Not Null Constraints. As per my understanding a primary key is used to uniquely identify ...
0 votes
0 answers
27 views

Transactional replication is trying to update a row that does not exist yet. Why?

I've been tinkering with SQL Server 2019 and transactional replication for a little while now and I've hit a wall. Looking at the replication monitor I'm seeing the error "The row was not found ...
user avatar
3 votes
0 answers
1k views

Linked Server - Cannot create an instance of OLE DB provider "IBMDADB2.DB2COPY2" for linked server "MYDB2"

I would like to create Linked Server from SQL Server 2016 on Windows to Db2 v11.5.7 on Linux/x86. I created linked Server using Microsoft OLE DB Provider for DB2 and it works fine. Now I would like to ...
-4 votes
1 answer
54 views

Reorganize or Rebuild Spatial Indexes

Will there ever be a way to exclude or only include spatial indexes from the IndexOptimize tool?
2 votes
1 answer
125 views

Abnormal behavior in TempdB SQL Server 2019

We have recently migrated from a version of SQL Server 2014 to a SQL Server 2019 CU 26. In all our tests everything went well before performing the migration but when we made the move to production ...
6 votes
1 answer
4k views

SQL Server Network Packet Size

During the analysis of network traffic from Citrix clients to our database server, we noticed that we had a lot of RETRANSMITs occurring during the transport of TCP packets. The packets were being ...
1 vote
1 answer
5k views

ERROR 17058 COULD NOT FIND THE ERROR LOG

We just installed SQL Server on 1st node of windows cluster and it stop/start fine from configuration manager but from command line it is giving error and I checked that service account have all the ...
-2 votes
0 answers
71 views

Why would email alerts on severity 16 errors be desirable? [closed]

Errors for severities beyond 16 are standard. Such errors are signs of really serious problems. I've noticed that this script here includes alerts for severity 16. To my knowledge, severity 16 errors ...
3 votes
1 answer
9k views

Permanent count of waiting tasks (2) in Activity monitor

Now I'm observing in Activity Monitor permanent count of waiting tasks: and at "Resource waits" tab I see next: so, my question is: How to find and resolve this wait tasks? I suspect that it ...
0 votes
3 answers
2k views

SQL Server Agent & sp_send_dbmail failing on permissions

I have set up a SQL Server Agent job to call sp_send_dbmail with a very simple select on a certain table in a certain database. Within the properties > permissions of the target database, the ...
2 votes
1 answer
984 views

DPM transaction log backups of SQL Server partially contained databases

I am trying to use Microsoft System Center Data Protection Manager (DPM) 2016 (5.0.342.0) to protect SQL Server 2017 Standard CU8 (14.0.3029.16) on Windows Server Std 2016. I have a named instance ...
4 votes
1 answer
776 views

Different collation between master database and application database

I just installed SQL Server 2019. Unfortunately I didn't notice the collation while installing. Following are the collations: SELECT name, collation_name FROM sys.databases name collation_name ...
0 votes
1 answer
1k views

Efficient way to check for number of records more than a limit from a table in SQL Server

I came across queries that can most efficiently get the total sum of the records in the table. However, i want to group the records in a table by a column and check if the count of the records is ...
0 votes
0 answers
42 views

High CPU utilization on an idle SQL Server instance without user traffic

We have an SQL Server 2022 CU13 Enterprise on a Windows 2022 VM with 8 vCPU. The sqlserver.exe process shows a high CPU utilization between 10-15% even when there is no user generated load (meaning ...
1 vote
0 answers
23 views

How to audit server settings configuration changes other than view server state?

I want to capture the audit of server configuration settings changes. For this I setup the SERVER_OPERATION_GROUP audit event. This event is raised when Security Audit operations such as altering ...
-1 votes
0 answers
77 views

Tempdb is growing too fast

I execute an INSERT on a temporary table in SQL Server 2022, but after 8 minutes of execution, 500GB of disk where the TEMPDB files are located ends up. "Could not allocate a new page for ...
0 votes
1 answer
37 views

sql server client network utility - how to list the aliases?

there is something called sql server client network utility it lives on C:\Windows\System32 for 64 bits. as you can see I have many aliases there, server, instance and ip address and port used for ...
0 votes
2 answers
49 views

Yet another Replicated transactions are waiting for next Log backup or for mirroring partner to catch up

We have a partial transactional replication setup in our environment to make a data subset available to another application. Application Server 1 -> Tables1.row1/row2/row3 -> Application Server ...
0 votes
1 answer
846 views

The Windows Server Failover Clustering (WSFC) resource control API returned error code 19

What is root cause of this error. I couldn't find any related information. There are two nodes on Availability Group and if you reboot second node, database wont up.
0 votes
1 answer
52 views

Locked out of SQL Server 2005 .... User number set to "1" in Enterprise manager? [duplicate]

Whilst trying to restore a "master" backup file (on my Windows Server 2003 / SQL Server 2005) machine I've dropped a clanger and re set the number of users from "0" (unlimited) to &...
0 votes
2 answers
78 views

create_date and modify_date for sys.objects in MSSQL shows same date

I am trying to figure out when is the create date of a user defined object in the database. but when I ran a select query on sys.sysobjects, it seems create_date is the same as modify_date. Is this by ...
0 votes
1 answer
2k views

SQL Server add a linked server to postgres database

I am trying to connect a PostgreSQL to SQL Server server as a linked server. On the SQL Server, I have installed the ODBC driver for PostgreSQL (psqlodbc_09_02_0100-x64). I created a system DSN to a ...
0 votes
1 answer
336 views

How to remove unallocated space after partitioning

I've recently done partitioning on a table (postcode column) and created new filegroups for each postcode. Now that it is no longer using the PRIMARY filegroup, the file size is still as it is. How do ...
0 votes
0 answers
37 views

How do you fix parameter sniffing on an ORM / Entity Framework Query?

We have a problem with plan cache bloat due to unparameterized Entity Framework queries. In cleaning some of these up we've seen dramatic reduction in SQL CPU usage. I'm inclined to go "clean up ...
0 votes
1 answer
52 views

Sql Agent jobs, execution contexts, run as issues

We're on Sql Server 2019. We have a whole bunch of Sql Agent jobs and pretty much by default they are owned by sa. The company security team issued an edict that all sa accounts are to be disabled for ...
-1 votes
0 answers
47 views

Best Practices for Migrating 2TB MSSQL Databases with High Availability to PostgreSQL

I'm planning to migrate a large-scale MSSQL environment to PostgreSQL and would appreciate some advice and best practices. Here are the details of our current setup: Current Environment: Two servers ...
5 votes
2 answers
718 views

SQL AlwaysOn & System Center Configuration Manager TP 4 1601

Recently Microsoft released Technical Preview 4 for Configuration Manager (1601). Per TechNet, "Configuration Manager now supports using a SQL Server AlwaysOn availability groups to host the ...
8 votes
2 answers
654 views

query to determine forceencryption state

I'm trying to confirm whether or not force encryption is currently set and/or if the current connection is secure or not. Is there a SQL query I can run that will give me that information or do I ...
5 votes
3 answers
6k views

SQL table design for primary keys (best practice)

I have a User table that is made up of the the following columns: ID (Auto-generated GUID) UserName Password FirstName LastName Email AddressID (GUID) This is linked to the table Address (in a one-...
8 votes
1 answer
6k views

Statistics on temp tables

Quick and simple... why is this sp failing when I attempt to retrieve statistics for one of the columns on the temp table? CREATE PROCEDURE dbo.Demo AS BEGIN SET NOCOUNT ON -- Declare table variable ...
2 votes
2 answers
430 views

Trying to read a log file encrypted by TDE

I have a full chain of full backups and log backups. I am looking to investigate exactly what transaction was done by a certain user in a given period of time. The database (thus all backups) are ...
1 vote
1 answer
8k views

Linked server error- The table either does not exist or the current user does not have permissions on that table

There is a view that is being called from Server A (SQL 2016) and this view needs to read data from Server B (SQL 2014). There is a linked server set up between these two servers and the connection ...
1 vote
1 answer
11k views

Where is the default location for the SQL Agent Job step output file? How do I update it?

I recently migrated my system databases to a new location. I then experienced failures of my SQL Agent jobs with the following error: 2015-10-03 01:53:10 - ! [136] Job - reported: Unable to ...
2 votes
3 answers
561 views

Are unique filtered indexes considered an antipattern for enforcing constraints?

I have a table, such as the following. Let's called it Fools. FoolID FoolValue IsActiveFoolValue 1 Ultra Foolish 0 1 Super Foolish 1 2 Quite Foolish 1 A business rule is that for each FoolID, we ...
2 votes
0 answers
3k views

Unable to get output variable of Oracle procedure using Sql linked server

I have created a Heterogeneous connection between SQL Server and Oracle and tried to call a oracle procedure from SQL Server.I got below error when i tried to execute the procedure. OLE DB provider ...
1 vote
1 answer
460 views

Connection to database causes SSPI context error

I have migrated a SQL server from another server box that was decommissioned. They have the same name and also same IP address. However, when I connect to the server from an application using trusted ...
-2 votes
0 answers
49 views

SQL Server partition recreate procedure blocked by other queries

I use every night partition recreate procedure which create a new partition for 5 tables in SQL Server. The partition recreate procedure is blocked by other DML queries which eventually get timeout (...

1
2 3 4 5
689