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

New answers tagged

1 vote

Can we take a copy-only backup for filegroups on the SQL Server from a secondary HA replica?

The Docs state this is possible (emphasis mine): BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it's executed on secondary replicas. Copy-only backups ...
Sean Gallardy's user avatar
1 vote

SQL Server Failover Cluster Data Loss on failover

The problem is when the passive node came online, the data was a month old. It wasn't just one database, it was multiple (all six) databases that must have rolled back ~30 days. Much as others have ...
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
1 vote

Availability group failover-tempdb

Does anyone know if SQL AG will initiate automatic failover to secondary if tempdb is full in primary ? It won't fail over, there's no problem with the databases and the instance is not failing ...
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

I could not add sp_BlitzIndex store procedure to SQL Server 2014 version

I was able to fix it by changing the select statement against dm_db_index_operational_stats to do NULL as hobt_id since hobt_id isn't a valid column in that DMV for version earlier than 2016 (my ...
Kellyleia's user avatar
5 votes

Why is my SQL Server prefering to pick one plan over the other when there are 2 executions plans for the same query in the cache?

Multiple plans Multiple plans (each with a unique plan_handle) for the same batch text (sql_handle) means one or more cache keys must be different. A difference in SET options is one possibility ...
Paul White's user avatar
  • 85.7k
0 votes

Error in Login securables - SQL Server

I get this error when trying to use SSMS 19.1 (aligned with SQL Server 2019) to connect to SQL Server 2016. When I use SSMS 13.0 (aligned with SQL 2016) it works fine. When you posted this in Sept ...
Brien Malone's user avatar
1 vote

SQL join query without multiplying results

Enumerate rows (in CTEs) then join by both table column and row number. WITH cte1 AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY {...}) rn FROM table1 ), cte2 AS ( SELECT *, ROW_NUMBER() OVER (...
Akina's user avatar
  • 20k
1 vote

Using custom table definitions in a multi statment table function

No, it's not possible. The syntax grammar is quite clear: -- Syntax for Transact-SQL multi-statement table-valued functions. CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name ( [ { @...
Charlieface's user avatar
  • 13.2k
8 votes
Accepted

Indexed view - Cannot create index because "select list of the view contains an expression on result of aggregate function or grouping column."

Well, the error message is technically correct: because the select list of the view contains an expression on [...] grouping column The expression is your function, which takes Address as a ...
Paul White's user avatar
  • 85.7k
1 vote

Handling duplicated data in SQL

Here is a possible start to a solution. Providing actual table, actual data , and expected result may change answer. As it is written, it will fail if all values are legitimate duplicates or if the ...
Bob Klimes's user avatar
  • 3,254
4 votes

Sampled Statistics Percentage Internal Calculation

The sampling percentage is derived from the number of rows in the table and the number of leaf level pages in the index chosen for the statistics collection. This 'index' will most often be the base ...
Paul White's user avatar
  • 85.7k
3 votes

Indexed view - Cannot create index because "select list of the view contains an expression on result of aggregate function or grouping column."

wild guess I don't have time to build and test a repro, but I suspect that this is due to UDF inlining. Can you try re-creating your function this way, and seeing if that resolves the issue? CREATE ...
Erik Darling's user avatar
1 vote

Telling SQL Server about moving the backup files

There's a table called dbo.backupmediafamily in database msdb. It contains physical_device_name column, which can be correctly updated. Using the script below you can get list of last 100 registered ...
Andrey Samykin's user avatar
0 votes

How to Get the Sum (total) of a column in a query result set

Also: select column1 ,amount_column from dbo.tablename UNION select 'TOTAL' ,sum(amount_column) from dbo.tablename ;
johcar's user avatar
  • 1
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

How to execute complete database script without checks on objects existence

If the archiving db should have the same schema, that should include views. If the views are referencing other dbs that are not available, one solution could be to extract the view script and modify ...
MUSQL's user avatar
  • 1
0 votes

SQL Server system views VERY slow at one computer

FYI, this recently happen to me and I realized I had turned ODBC tracing on during some troubleshooting and forgot to turn it off. The log file either grew enormously huge and or got corrupted and ...
PTP's user avatar
  • 1
1 vote

Database cannot be opened. It is in the middle of a restore

You need the additional command: RESTORE DATABASE test WITH RECOVERY; It appears that RECOVERY is ignored in the RESTORE LOG command, and it needs an additional command to confirm that the restore ...
comment-answers's user avatar
3 votes

Multiples full backup from MS SQL Server all day long

It's a very expensive way of doing your backups. If they are taking full backups each x minutes that means the point of time they are taking the backup is the point they can restore to. For instance ...
RPD's user avatar
  • 31
0 votes

Using SQL Server with BitLocker

Ideally you need to check all the paths which are getting used during the process. Bitlocker is a volume based encryption and encrypts / locks every volume separately. Therefore if any other path from ...
Umar Khan's user avatar
0 votes
Accepted

Processes stuck in WAITFOR without an explanation or WAITFOR command anywhere in the code

This turned out to be some code deeper within a Stored Procedure that used a semaphore to check if a task had been completed, and the task was unexpectedly failing. When the task failed, without ...
Thomas's user avatar
  • 133
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
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
11 votes

What does SQL Server's Error Log actually log?

The name ERRORLOG is historical. It's got all the startup messages, plus the details of all the login failures (which aren't sent to the clients), plus all errors of high severity, plus any raiserror ...
David Browne - Microsoft's user avatar
9 votes
Accepted

Granting SELECT...INTO permissions

The documentation for the INTO clause says: Requires CREATE TABLE permission in the destination database. But CREATE TABLE says: Requires CREATE TABLE permission in the database and ALTER ...
Paul White's user avatar
  • 85.7k
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.7k
0 votes

SQL Pivot table splitting rows on custom column names. I need 1 row per OrderId

You will need to add a MAX and group by in order to make this work. I answered a similar question here:How to pivot on multiple columns in SQL Server? But here is an example for you using your data. ...
Jonathan Fite's user avatar
5 votes
Accepted

Number of Real Months Between Two Dates

You aren't really doing date math, you are counting the number of billable events between two dates. There are two approaches to solving this, one using a calendar table and the other using pure date ...
Jonathan Fite's user avatar
0 votes

SharePoint changing statistics options daily

from the comments I check the error log of all the instances once everyday and some times I overlook critical events in the log because of so much noise as there is no visual indication between ...
Erik Darling's user avatar
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
1 vote
Accepted

Split intervals with two tables

This solution is base on a calendar table . For this I added this piece of code(if you have in your system, just replace it and use yours) (SELECT DISTINCT DATEADD(DAY,v.number,'2020-10-01') as ...
Sabin B's user avatar
  • 4,436
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.7k
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,747
1 vote
Accepted

The debugger only stops at the breakpoints of the first Script Component. How do I debug more than one Script Component in an SSIS Data Flow Task?

Thanks for asking. With respect to SSIS versions after 2008, debugging script components is now possible. Microsoft has updated their documentation with more details, which I am providing here as well....
Donovan Bergin's user avatar
1 vote

SharePoint changing statistics options daily

In case someone else will google it. I disabled the Health Analyzer Rule Definitions with name "Databases used by SharePoint have fragmented indices." Also I disabled the following timerjob ...
Евгений ББ's user avatar
11 votes
Accepted

Extra NESTED LOOP / INNER JOIN causing NO JOIN PREDICATE warning

The optimizer decided your query was a selective star query on fact and dimension tables. The giveaway is the presence of StarJoinInfo attributes on the plan's join operators (except the cartesian ...
Paul White's user avatar
  • 85.7k
1 vote
Accepted

How to implement an "Apple or Orange but not both" constraint

either a Site or to a Technician - but not both. alter table Robots add Check (SiteID is null or TechnicianID is null)
SergeyA's user avatar
  • 692
0 votes

Database systems - data type alignment padding and record size (C/C++ related)

Oracle uses of proprietary BCD encoding for numbers, and also uses proprietary own arithmetic implementation. So results are identical on every CPU platform. Oracle's number can have up to 30 valid ...
ibre5041's user avatar
  • 1,564
1 vote

Schema compare on production database

if Schema Compare is meant to be ran against production databases. Yes. The intent is that you have a new schema version, use the schema compare with production to identify the changes that need to ...
David Browne - Microsoft's user avatar
1 vote
Accepted

SQL Job Roles: How to grant a group permission to access SQL Jobs with unique mapping?

When you add a user for the login (which is a group in AD/Windows) and somebody in that AD group creates a job, SQL will extract the SID for the AD user that created that job and records that sid as ...
Tibor Karaszi's user avatar
-2 votes

How to add new sysadmin account when no sysadmin accounts exist

I am login with sqlcmd CREATE LOGIN Dinesh WITH PASSWORD = 'Dinesh'; GO SP_ADDSRVROLEMEMBER Dinesh,'SYSADMIN'
Vishe's user avatar
  • 226
0 votes

How to fetch the columns of the synonyms for linked servers in SQL server

You can follow How to find all column names of a synonym - Stack Overflow: SELECT TOP(0) * INTO #tmp_synonym_xyz FROM synonym_xyz; SELECT name FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('...
questionto42's user avatar
0 votes

Speed up an already running insert script

The issue here is that you are doing this in one transaction. You say "only inserting 8 million rows" - but that's all happening in one transaction. And the transaction log is logging all of ...
Mike Walsh's user avatar
  • 18.2k
2 votes

Processes stuck in WAITFOR without an explanation or WAITFOR command anywhere in the code

WAITFOR can be a little tricky sometimes. You aren't really "blocked" here as you may fear. These are harmless (usually - not so much if someone is putting something like a WAITFOR in the ...
Mike Walsh's user avatar
  • 18.2k
2 votes

Could not locate statistics in the system catalogs error in SQL Server

Seems like you are bumping into a bug that sometimes happens in AGs, the process which updates the statistics on the secondary can get blocked/hampered from running. Try running this first on the ...
Mike Walsh's user avatar
  • 18.2k

Top 50 recent answers are included