Skip to main content
Share Your Experience: Take the 2024 Developer Survey

New answers tagged

2 votes

2x SQL Server 2019 in AlwayOn group - how to remove transaction log

There are supposed to be some way how to purge the log by doing some kind of backup, but never found real help on that. They're called Transaction Log backups. For a database that's set to the Full ...
J.D.'s user avatar
  • 37.9k
2 votes

2x SQL Server 2019 in AlwayOn group - how to remove transaction log

Simply put, we don’t need the log, we don’t want the log, how to get rid of that? You don't. SQL Server requires it to run and Always On requires it to function properly.
Sean Gallardy's user avatar
1 vote

SQL Agent Job failing after uninstall of SQLPS and install of the new module

'Cannot find drive. A drive with the name 'SQLSERVER' does not exist. This happens because it's reliant upon the SQLPS module to setup the SQLSERVER psuedo drive in PowerShell. How can I make it ...
Sean Gallardy's user avatar
0 votes

CDC Capture job fails with 'Cannot insert duplicate key row in object cdc.Table_CT' with unique index 'CT Clustered Index'

Eventually, applied traceflag 8291. Will monitor. https://support.microsoft.com/en-au/topic/kb4521739-fix-error-occurs-when-cdc-capture-process-tries-to-insert-duplicate-key-in-table-cdc-lsn-time-...
Pam Moodley's user avatar
0 votes

SSIS Package hanging with two Constraints pointed at one Task: Can you have multiple constraints pointing to one Task?

The problem is not that you have two arrows pointing to the same task. You absolutely can have two arrows pointing to the same task. The problem is that your setup requires a logical self-...
ImperviousInclemency's user avatar
0 votes

Automatic Failover for Basic Availability Group

So you want free HA\DR? There is only one way and that is to use developer Edition which will allow you to set up 2 servers with SQL instances on them and play around with availability groups. Of ...
RPD's user avatar
  • 31
0 votes

SSIS Package hanging with two Constraints pointed at one Task: Can you have multiple constraints pointing to one Task?

https://learn.microsoft.com/en-us/sql/integration-services/control-flow/integration-services-tasks?view=sql-server-ver16#types-of-tasks Type of task: Custom Tasks. Configuration of Task section ...
Brooke Metoxen-Smith's user avatar
2 votes
Accepted

Non-yielding Scheduler Error in SQL Server 2019 on Docker with CDC Enabled

I applied the suggestion from AlwaysLearning for my MS SQL Server 2019 instance: Does enabling Forced Unit Access (FUA) help? See Log reader agent may raise a Non-yielding Scheduler dump in SQL ...
Макс Самшит's user avatar
1 vote

Alternate T-SQL Approach for Creating a PATINDEX Funtion to Find Multiple Occurrences of a Pattern

Performance considerations and other alternatives such as CLR aside, maybe something like this?:- CREATE FUNCTION dbo.fcnPATINDEX2 ( @TargetStr NVARCHAR(MAX), @SearchedStr VARCHAR(MAX), @...
Rob Dalzell's user avatar
2 votes

Is it possible to register two SSL certificates on SQLServer?

I haven’t tested with sqlserver yet, but a chance you may be able to work around your dual cert issue here using haproxy to MITM the connection. This would terminate the incoming certificate with the “...
simon coleman's user avatar
4 votes
Accepted

Is it possible to register two SSL certificates on SQLServer?

No. The certificate setting is stored in the registry, at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Your Instance Name Here\MSSQLServer\SuperSocketNetLib in a key called Certificate. ...
Charlieface's user avatar
  • 13.2k
1 vote

Preferred seconday backup in AG - what about the primary ? Shows no backups taken

The most recent backup time is pulled from msdb, which is not joined to the Availability Group (each replica has it's own copy of system databases). To get accurate information about the last backup ...
Dan's user avatar
  • 646
3 votes
Accepted

Index seeks and predicate push down

Solution You can use an expanded FORCESEEK hint with the seek keys needed: FROM dbo.MyTable AS M WITH (FORCESEEK(IDX(WTID, OETID, SRID))) For example: IF EXISTS ( SELECT T.* FROM ( ...
Paul White's user avatar
  • 85.8k
1 vote

Index seeks and predicate push down

For some unclear reason, the compiler seems to be getting confused by the UNION and not pushing the NOT EXISTS anti-join predicates all the way, and therefore not matching the index fully. The ...
Charlieface's user avatar
  • 13.2k
5 votes
Accepted

How do I fully qualify event predicates for [backup_restore_progress_trace] Extended Events?

You can qualify the fields with the event name to distinguish them. e.g. [backup_restore_progress_trace].[database_name] versus [sqlserver].[database_name]. Full session: CREATE EVENT SESSION [...
Paul White's user avatar
  • 85.8k
3 votes

How do I fully qualify event predicates for [backup_restore_progress_trace] Extended Events?

Recap To simplify the question: There is a conflicting column named database_name (one is from global action and the other is the event's column). You want to be deterministic, so it makes sense you ...
Zikato's user avatar
  • 4,797
0 votes

How to Increase The maximum number of allowed pending SQLServerAgent notifications

A few details are lacking here, but it's quite likely that you are either e-mailing too many folks in the notifications or using multiple jobs to run the notifications simultaneously. You can't ...
Mike Walsh's user avatar
  • 18.2k
2 votes

How is Memory Grant Desired Memory calculated

The sort operation will have storage overheads. Pure in-place sort algorithms exist but they may costume more CPU especially if operating in parallel. Those row number and row size values are ...
David Spillett's user avatar
1 vote
Accepted

Invoke-PolicyEvaluation with the new SqlServer module generates security error

Follow the example in the docs to use a custom connection string. Set-Location "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Policies\DatabaseEngine\1033" $Connection = New-Object ...
David Browne - Microsoft's user avatar
5 votes
Accepted

Running sp_repldone on a secondary node of an Availability Group with @reset = 1 not working

For background, I'm assuming this is a custom CDC solution, and the regular CDC jobs aren't running to populate the change tables and run sp_repldone, and so you must run sp_repldone to enable the log ...
David Browne - Microsoft's user avatar
2 votes

High free memory and Lazy Writer triggered by IndicatorsPool

details Since you're on Enterprise Edition, SQL Server isn't limited to a certain amount of memory, or a certain number of processors like it is in Standard Edition, unless you installed it with goofy ...
Erik Darling's user avatar
  • 41.1k
3 votes

Can using UPDLOCK and ROWLOCK help me fight of deadlocks in my situation?

Change the clustered index so the concurrent transactions aren't reading each other's rows. CREATE TABLE [dbo].[tblImportData] ( [ImportDataID] INT IDENTITY(1,1) NOT NULL, [...
David Browne - Microsoft's user avatar
4 votes

Can using UPDLOCK and ROWLOCK help me fight of deadlocks in my situation?

So would it help me if I put WITH (UPDLOCK, ROWLOCK) in every select on tblImportData in the stored procedures ? Hard to say without knowing exactly what is causing the deadlocks and more about how ...
Paul White's user avatar
  • 85.8k
3 votes

Can using UPDLOCK and ROWLOCK help me fight of deadlocks in my situation?

Table Improvement Your table really should look like this, with a computed column to help the non-sargable predicate shown in one of your queries. CREATE TABLE dbo.tblImportData ( ...
Erik Darling's user avatar
  • 41.1k
0 votes

What is best practice when performing manual failover in a 2 node FCI

Should we manually stop the SQL Server Agent before failover? I don't see why. When a failover occurs, SQL Agent is going to be asked to shut down due to the engine shutting down and also from a ...
Sean Gallardy's user avatar
1 vote

Getting sum for each day based on most recent timestamp entry for each entity

One way to achieve this is: First, creates the tables (#tableA with your source , #tmpDateSequence with a list of days = 2024-02-03 ,2024-02-04 ,2024-02-05...) drop table if exists #tmpDateSequence,#...
Sabin B's user avatar
  • 4,436

Top 50 recent answers are included