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.
32,085
questions
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?
- 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/...
- 23
-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 ...
- 13
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 ...
- 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 ...
- 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 ...
- 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 ...
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 ...
- 135
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....
- 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 ...
-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 ...
- 135
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 ...
- 19
-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?
TheSylar
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?
...
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 ...
- 21
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....
- 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?
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 ...
- 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'...
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 ...
- 189
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 ...
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 ...
- 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 ...
- 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.
- 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 ...
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 ...
- 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="...
- 101
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 ...
- 101
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 ...
- 592
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 ...
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 ...
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 ...
- 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 ...
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 ...
- 11
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 ...
- 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 ...
- 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 ...
- 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?
- 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 ...
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, ...
- 21
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 ...
-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 ...
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 ...
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 ...
- 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 ...
- 338
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. ...
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 ...
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 ...
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 ...
- 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/...
- 25