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
26 views

MAXDOP setting as uneven number or number not divisible by 4

One always sees maxdop as 4, 8 or 16. Is it recommended that maxdop is always divisible by 4? Is there e.g. a problem with setting maxdop as 12? And what about uneven numbers, e.g. 5 or 9?
user avatar
  • 637
2 votes
1 answer
45 views

Restore over dns alias fails with Operating system error 1326(The user name or password is incorrect.)

following configuration: SQL Server is running on server.domain.intra with an gMSA. DNS alias: server-db-dev.domain.intra gMSA Permissions on backup share: Full Control SPNs set for gMSA: MSSQLSvc/...
user avatar
-1 votes
1 answer
54 views

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value when running T-SQL query

Please note I am not a coder by any means, I am simply trying to assist with a migration from Server 2008 R2 and SQL Server 2008 to Server 2022 and SQL server 2019. My situation is as follows When I ...
user avatar
0 votes
0 answers
23 views

When rebuilding sql server on a new machine, should SMK be restored prior to or after restoring system dbs?

Service Master key is at top of sql server encryption hierarchy. Linked server, credentials, database master key (if any) are encrypted using the service master key. When rebuilding a sql server using ...
user avatar
  • 2,407
0 votes
0 answers
15 views

Where is the service master key (SMK) and database master key stored?

Sql server encryption has got the service master key at the top of the hierarchy. The database master key is encrypted with the service master key. The database master key is stored in the respective ...
user avatar
  • 2,407
-2 votes
0 answers
24 views

Long running sp_server_diagnostics

sp_server_diagnostics is running from past 28 days, i.e., from last server reboot. This is going on every month. It never stops and gets notified as long running sessions often. It starts again after ...
user avatar
  • 320
0 votes
2 answers
49 views

The Best Recovery Model to Rebuilt Indexes

I'm looking for some advice. I have to rebuild my database indexes (because of shrinking) but due to having low disk space, I am not sure which recovery model is better to avoid getting disk space ...
user avatar
2 votes
2 answers
166 views

How to connect to remote SQL server instance by IP

I have a remote development box with SQL 2016 installed. Remotely, I can access that server by IP just fine. Let's assume for my question that IP address is 1.1.1.1. Now I have installed a new SQL ...
user avatar
1 vote
0 answers
27 views

Formula for memory grant for DBCC CHECKDB

How is the memory grant for a DBCC checkdb determined (other than the max size for memory grants which is from resource governor, possibly the default pool)? I'm not trying to solve a specific problem....
user avatar
  • 637
-3 votes
1 answer
21 views

cant attach mdf file because of suspect mode

Please I need Help i cant attach Mdf Database , As last time it was suspect , i made detach and i cant now attache it again . and i recieve this message An exception occurred while executing a ...
user avatar
-1 votes
1 answer
30 views

sql server developer throttled?

I know SQL Server Developer is functionally equivalent to SQL Server Enterprise. But surely it's less performant in some way (can't leverage as much memory, only works against one core, etc)? Is SQL ...
user avatar
1 vote
1 answer
60 views

Reclaiming allocated free space from partitioned tables

We have a data warehouse with about 4TB worth of datafiles. A couple of these databases consist of partitioned tables. Since we're running out of disk space (on premise environment), we are looking to ...
user avatar
-4 votes
0 answers
31 views

How can i backup mssql to microsoft azure sql?

I have MSSQL sql backup and I want to use this backup of this file with .bak extension on microsoft azure server, how can I do it?
user avatar
4 votes
1 answer
82 views

SQL: finding unique multi-column value triplet matching multiple rows?

TL;DR: using Transact-SQL/SQL Server, how can I find the rows of a table which match a single "solution" triplet of column values (ColA, ColB, ColC) for some set of values in another column? ...
user avatar
2 votes
0 answers
35 views

SQL Server data file creates 50% unallocated bloat when I create clustered index

My company has relatively large datasets with clustered indexes currently being built on a char(40) field. In order to maintain disk space and streamlining backups, I currently have a "one table ...
user avatar
0 votes
0 answers
8 views

Executing SSIS in Powershell

I am trying to execute an SSIS package from Powershell where th,e package has several configured environment references. $null = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer....
user avatar
  • 101
-3 votes
1 answer
102 views

After mass deletion of data, what should be done first: Index rebuilding or shrinking filegroups?

When we delete huge amounts data from a table, what should be the best thing to follow: Shrink the file group first and rebuild indexes, or Rebuild indexes then shrink the file group?
user avatar
0 votes
0 answers
19 views

Trigger agent jobs on failover

I currently have a 2 node AG with automatic failover enabled. Some of the databases in the AG are also using change data capture, as such we have CDC_capture and CDC_cleanup jobs. The jobs are ...
user avatar
  • 388
0 votes
0 answers
29 views

Sql Server optimizer trees: CONVERTED and TREE_COPIED_OUT

Using the trace flags 8605, 8606, and 8607 in a Sql Server query, we can have a look at the logical trees that are built. Trying to associate each tree with the optimization phases of the First Step I'...
user avatar
0 votes
0 answers
20 views

PostgreSQL as an external data source

I'm trying to get an external data source to work with PostgreSQL. This is what I've done so far: Downloaded and installed the latest 64bit PostgreSQL ODBC drivers Created a System DSN using the ANSI ...
user avatar
2 votes
1 answer
203 views

Why SERIALIZABLE level does not lock the table for other session in SQL Server

I want to Prevent concurrency in my table for Delete, Insert, Update, and Select so I tried to use SERIALIZABLE for the table before transation, but that didn't lock the table for other processes! how ...
user avatar
0 votes
1 answer
40 views

Allow external users to access my DB through Linked server

What do i need to configure to allow external users from different domain to access a few tables in my SQL DB? I've created in the past a connection to external linked server by one of our clients who ...
user avatar
  • 78
-1 votes
0 answers
41 views

Best way to capture all the instance and DB settings on an instance

What is the best way to capture all the instance and database settings in SQL Server so one can check if anything has been changed? And what about the same question but for the OS (Windows). I have ...
user avatar
  • 637
0 votes
1 answer
34 views

SQL Server 2019 how can I create an alert for when service broker messages are aggregating in the queue and not being processed?

How do I create an alert in SQL Agent, to notify me once service broker message queue exceeds 300 messages? I am trying to get a notification to an operator in SQL Agent when this occurs.
user avatar
  • 25
0 votes
1 answer
50 views

How to map the newly created records in parent table with the child table?

have a table Employees Id Name EmployeeType_ID 1 John 1 2. Mathw 2 EmployeeTypes Id TypeName 1 Perm 2. Temp 3. Regular Now I am copying data from one server to ...
user avatar
0 votes
0 answers
22 views

SSIS package part breaks whole SSIS solution due to .designer files

I have troubles with usage of Package Parts in SSIS. My environment consists of: VisualStudio 2019 16.11.18 Add-on SSIS 4.1 to VisualStudio Windows 10 21H2 SQL 2019 CU16 - Standard edition The ...
user avatar
  • 105
0 votes
3 answers
37 views

MySQL: For every Column, create if not exists?

I'm a student and am trying to create a column for my table, but only if it does not exist. I did come across a solution: SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA="...
user avatar
0 votes
1 answer
45 views

SQL Agent Job fails after upgrade to SQL 2017

I have upgraded a database to SQL 2017. A SQL Agent Job that previously ran now fails with this error: Unable to start execution of step 1 (reason: JobOwner DOMAIN\USERNAME doesn't have permissions ...
user avatar
3 votes
1 answer
152 views

Logical reads and LOB logical reads

I have a query app scanning a whole table with a text field. The query is doing this many reads: Scan count 1, logical reads 170586, physical reads 3, read-ahead reads 174716, lob logical reads ...
user avatar
1 vote
0 answers
24 views

Enable Shared Memory Protocol (SQL Server PowerShell)

I`m trying to modify and use powershell script from docs.microsoft.com in order to enable SQL Shared Memory protocol on a remote server. But my issue is instead of connecting to provided ...
user avatar
1 vote
3 answers
67 views

Find which IP added a specific record to a table in SQL Server

Let's say I have an SQL Server running on Azure and multiple applications add records to it. The problem is that some records come from an unknown application. Is there a nice way of finding which IP ...
user avatar
0 votes
0 answers
15 views

SQL Server Edition upgrade to import new license key stuck - over console

I currently rue the day when I decided to install SQL Server on a Windows Server without the desktop experience. Microsoft really did such a good job with SQL Server on Linux, in comparison, SQL ...
user avatar
  • 133
0 votes
0 answers
12 views

SSIS package fails sometimes and works fine

We have a scheduled SQL Job which runs the SSIS package and process the files (Hourly Job, and 24 execution's daily). From past few weeks we are seeing minimum 2 failures in a day randomly, post ...
user avatar
1 vote
1 answer
27 views

Reinstalling server and SQL Server database

Due to some Windows issue, can't clean-up the WinSxS folder and the Disk Space process (cleanmgr.exe) makes the CPU going to 100% meaning that I am at 97% of disk usage, I am planning to reinstall the ...
user avatar
0 votes
0 answers
40 views

Connect query from sp_whoisactive and Query Store

We have application which is returning result of sp_whoisactive every 1 minute. Is that any option to connect (join) results from sp_whoisactive and QueryStore ? I would like to add some stats from ...
user avatar
  • 307
-2 votes
1 answer
40 views

What does suspended, with wait time of 450 seconds on CXPACKET indicate?

I am monitoring the requests and sessions using dmv's. I am seeing a session that is having suspended status, which means it is waiting for something like IO. Yet the wait time is showing as 450000 ms ...
user avatar
  • 2,407
-1 votes
2 answers
67 views

What does a record in the sys.dm_exec_requests with corresponding blocking session id is not showing in the same result set mean?

sys.dm_exec_requests - these are the active requests sys.dm_exec_sessions - these are the active sessions A session can have 1 or more requests. From perspective of detecting and resolving blocking, I ...
user avatar
  • 2,407
-1 votes
1 answer
28 views

Does differential backups and log backups contain users, roles and permission changes?

Full backup contains database users, roles and permissions. Does differential backups and log backups contain the above changes?
user avatar
  • 2,407
2 votes
2 answers
43 views

Is it possible to query total CPU load on system that is hosting SQL Server 2014+ Express?

I can get the available memory by querying the [master].[sys].[dm_os_sys_memory] view, but I haven't found anything similar for getting info about the CPU load. I only find info about querying CPU ...
user avatar
0 votes
0 answers
17 views

Developing SQL Server DML Audit Trail for Heavily Denormalized Staging Table?

Put simply, we (are forced to) rely upon a very large, heavily denormalized staging table that's typically about 20k records, 80+ fields that's overwritten at least 4-5x each business day for updates, ...
user avatar
0 votes
1 answer
62 views

The store schema does not contain the Element class SqlXmlCompressionOption

I hope you are all good I am doing or importing a bacpac file from azure SQL to SQL on-premisse with SSMS and I am receiving this error. Somebody get me help or give me a north Details Could not ...
user avatar
-3 votes
1 answer
46 views

Retrieving range of values from a lookup table using SQL

I am using SQL Server. I have two tables Result and Grading. Using T-SQL select statement, from the lookup table (Grading),I want to fill Grade column in Result table with the knowledge of range of ...
user avatar
0 votes
1 answer
36 views

Performance considerations when adding a new column to a big table in Sql Server

I have a table with 100 million rows and 70 columns. I'm planning on adding a new DateTime column to this table but I'm concerned that it might affect its performance. Would it be better to create a ...
user avatar
0 votes
2 answers
105 views

Downsides of too much memory

What are the downsides of a SQL Server with a lot of memory, e.g. several TB. In SQL Server 2022 there is the new feature called parallel buffer pool scan. Will that solve all the downsides of several ...
user avatar
  • 637
1 vote
1 answer
46 views

New DMVs related to Availability Groups in SQL Server 2019 CU9

In SQL Server 2019 since Cumulative Update 9, there are two new DMVs related to Availability Groups: sys.dm_hadr_cached_replica_states sys.dm_hadr_cached_database_replica_states I found out about ...
user avatar
5 votes
3 answers
613 views

NULL values appear when taking the sum of two columns taken from Lookup tables

I am using SQL Server and taking data from two lookup tables and want to take the sum of these two columns. The problem arises when the sum of these two columns (lookup values) gives NULL values. ...
user avatar
0 votes
1 answer
38 views

How to reduce internal fragmentation while deleting rows in bulk

Context I have a table in SQL Server with millions of rows and a primary key (by default, clustered key). I got the requirement to do housekeeping for that table, so I need to delete rows in bulk ...
user avatar
0 votes
1 answer
42 views

Find an open transaction across any session and any database in SQL Server

I know that we can use select @@trancount and select * from sys.sysprocesses where open_tran = 1 to find if a session has an open transaction in a given database. The problem with these statements is ...
user avatar
1 vote
0 answers
15 views

Transactional replication deadlock during setup

I'm encountering a problem setting up transactional replication between SQL Server 2017 and an Azure SQL database on a high throughput system. When trying to add any of the bigger, higher traffic ...
user avatar
  • 11
2 votes
3 answers
64 views

How do I restore multiple transaction log backups after restoring a full backup SQLSERVER 2019

I am creating my DR strategy, and my full back-up recovers fine. The full backups are taken with a modified version of the Ola Hallengren scripts. The file timestamp of the full backup shows 07/26/...
user avatar
  • 25

1
2 3 4 5
642