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

Can I password protect a specific ms sql database view?

I have an MS SQL database with a View that contains sensitive data in one column. Typically, users will consume this View in Power BI. As additional security, is it possible to password protect the ...
jbyrne's user avatar
  • 1
2 votes
2 answers
187 views

What SQL Server server-level roles allow you to add users to database-level roles?

In SQL Server 2022 (on-premise), I have a login (call it 'UserManager') which I have added to the ##MS_DatabaseConnector##, ##MS_DatabaseManager## and ##MS_LoginManager## fixed server-level roles. The ...
DGreen's user avatar
  • 121
1 vote
2 answers
106 views

Prevent users from sending an email attachment in sql server

In SQL Server, I want to restrict all users (except a few specific users) to send an email with attachment. For example, someone import data into excel file and send it via mail using the ...
Farid Zahidov's user avatar
0 votes
2 answers
83 views

What is taking backups of my servers?

I am working in virtualized environment. All my SQL instances are hosted on Windows Server machines. My infrastructure team is using Veeam tools for backups and replication to the secondary data ...
Radek Gąska's user avatar
1 vote
1 answer
85 views

how to find out the free space inside a database file when the database is not online?

Normally when the database is online I use the following query and get how much space is free inside each file: SELECT CONVERT (decimal(12,2) , (CONVERT(...
Marcello Miorelli's user avatar
0 votes
1 answer
78 views

Does application need to do anything to handle database failover?

I'm trying understand database failover (mainly in SQL Server) and what handling is required from application side to have zero downtime (no failures) to handle DB failover? Assuming that DBAs have ...
Hitesh's user avatar
  • 3
0 votes
1 answer
55 views

How to Identify the Most Up-to-Date Secondary Replica in a SQL Server Always On Availability Group when the primary replica is unavailable?

In a SQL Server Always On Availability Groups setup, I'm seeking guidance on determining the most up-to-date secondary replica when the primary is unavailable, to perform a failover to minimize data ...
Neaj Morshad's user avatar
0 votes
1 answer
44 views

How to delete "SQL Server Agent" logs from "Log File Viewer"?

On "SQL Server 2019" on Windows 2019: I have opened Microsoft SQL Server Management Studio. I logged into database server. I clicked on "SQL Server Agent" and clicked on "...
folow's user avatar
  • 339
0 votes
0 answers
75 views

Incorrect Next Run on Monthly Agent Job

Users have reported that a job on a recurring schedule has not been firing. This is also evident in the execution history. After some experimentation, it looks like it might be a bug in the SQL Agent. ...
Peter Godwin's user avatar
0 votes
1 answer
64 views

what sql server is holding in the used memory?

I have a query that shows me how much memory sql server is using, the settings like max memory and min memory, cpu, etc. here is the query: --===========================================================...
Marcello Miorelli's user avatar
1 vote
1 answer
58 views

How to Filter and Restore Specific SQL Database Backups Using dbatools in PowerShell

I'm working with a directory of mixed SQL Server backup files and need to restore a specific database using dbatools in PowerShell. Below is the directory structure of my backup folder: PS C:\Users\...
Just a learner's user avatar
0 votes
0 answers
49 views

combining multiple sum case statements

I have a situation where charge groups are made up a multiple of charge codes. So, for example, a Destination Charge Group consists of charges codes like PortCharges, Cartage, Insurance etc. And a ...
Chris Lane's user avatar
0 votes
1 answer
70 views

When do physical reads indicate a lack of RAM?

With Query Store, I can track how big my physical reads are and how often they occur. How can I tell if these numbers are so big that they indicate that I need more RAM? For that it's worth, my other ...
J. Mini's user avatar
  • 89
0 votes
1 answer
43 views

Is Monitoring Buffer Pages Too Noisy To Be Useful?

I quite like dbatools, so I gave Get-DbaDbMemoryUsage (docs here) a try on my production SQL Server 2019 server. Specifically, I ran this Get-DbaDbMemoryUsage -SqlInstance [MyProdServer] -...
J. Mini's user avatar
  • 89
0 votes
1 answer
51 views

76% of the RAM usage on my server is TEXT_MIX_PAGE in tempdb. What does this indicate?

I quite like dbatools, so I gave Get-DbaDbMemoryUsage (docs here) a try on my test SQL Server 2019 server. Specifically, I ran this Get-DbaDbMemoryUsage -SqlInstance [MyServer] -IncludeSystemDbs | ...
J. Mini's user avatar
  • 89
0 votes
0 answers
60 views

Do Primary Keys On Table Variables Have No Statistics At All? [duplicate]

My confusion is down to an apparent contradiction in principles. Primary keys are indexes and those always have statistics, but table variables notoriously always don't have statistics. What follows ...
J. Mini's user avatar
  • 89
-1 votes
1 answer
69 views

Can Query Store Show Spills To Tempdb?

I know that Query Store can show tempdb usage as of SQL Server 2017, but can it show spills to tempdb? I'm highly confident that the plan cache can.
J. Mini's user avatar
  • 89
0 votes
1 answer
36 views

INFORMATION_SCHEMA.COLUMNS - how to distinguish between table columns and view columns

My database has lots of tables and lots of views. For one of my tasks, I'm using this query. When I run following query it returns tons of columns - many of which belong to tables and many belong to ...
nam's user avatar
  • 291
0 votes
0 answers
26 views

How do I change the service accounts multiple SQL services run under, using PowerShell, and preserve dependencies?

I am in the process of converting all of our user-based service accounts on all MSSQL servers to gMSA and I have used PowerShell to script most of the required changes. I have hit a rather large ...
Dana H's user avatar
  • 1
0 votes
0 answers
16 views

How to find a deleted SSRS subscription that still runs

In troubleshooting an 'SSRS won't email subscriptions' problem I created a test subscription. I then found the SQL Agent job and changed the owner (I read the disclaimer in the job description, ...
dogyog's user avatar
  • 101
-1 votes
2 answers
84 views

Why does restoring a database on a different server bring in the backup info into the msdb db of new server?

I restored a db to a different SQL server but interestingly the msdb backupset tables on this server are showing the db backup history from the source server. Is this expected? Note: I have not ...
variable's user avatar
  • 3,561
-1 votes
1 answer
48 views

What context does the agent service account use (logon account or the NT SERVICE\SQLSERVERAGENT)?

My SQL server agent service account is running under the following domain user account: mydomain\svc_svr1_sql_agent I have a ssis package that must be able to read/write files in a folder. The ssis ...
variable's user avatar
  • 3,561
2 votes
1 answer
86 views

RESTORE DATABASE - The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time

While doing a restore of a big database 1.8 TB into a different server I came across this error message when the restore was about 90% completed: 90 percent processed. Msg 3013, Level 16, State 1, ...
Marcello Miorelli's user avatar
1 vote
0 answers
43 views

sp_BlitzQueryStore reports "multiple plans", but I can only find one execution plan. Why?

sp_BlitzQueryStore reports a particular query on several of my rows. In each of these, it reports Unparameterized Query, Parallel, Multiple Plans, Row estimate mismatch However, each of these rows ...
J. Mini's user avatar
  • 89
0 votes
0 answers
20 views

Can the SQL credentials packet ever be in an unencrypted state?

I understand that SQL credentials packet is always encrypted, even in the absence of SSL. However when I research about this I'm findings the following examples when the credentials packet may be ...
variable's user avatar
  • 3,561
0 votes
1 answer
22 views

How can I restore everything except stored procedures, functions, users from SQL Server 2019 native backup into another DB Server.?

How can I restore everything except stored procedures, functions, users from SQL Server 2019 native backup into another DB Server? We are planning to setup a QA environment from clone of PROD SQL ...
Sunil Bopanna's user avatar
1 vote
1 answer
63 views

How to correctly enforce referential integrity in a update/insert trigger?

Database: SQL Server, 2017. I need to enforce certain business rules in a trigger. Now, before you tell me to use a foreign key, please know that the rules are too complicated to be enforced by ...
user23278799's user avatar
1 vote
1 answer
16 views

SQL Server Linked Server PAGEIOLATCH_SH / CXPACKET

I have an agent job that runs a very simple "select max(date) from table" against a linked sql server. The linked server is part of an AG and the link from remote uses a listener to get to ...
user avatar
7 votes
1 answer
768 views

SQL Server: Max memory same as Min memory?

I am currently skimming through Pro SQL Server 2019 Administration: A Guide for the Modern DBA and I have found one thing that somewhat confuses me. In Chapter 5: CONFIGURING THE INSTANCE, the section ...
Yano_of_Queenscastle's user avatar
0 votes
1 answer
27 views

Changes to MSSQL System databases from Cumulative Updates

What sort of changes are made to System tables (master / model / msdb) by Cumulative Updates? For context, I have SQL servers that have AlwaysOn Groups, and am looking at a way to roll back updates if ...
Brett's user avatar
  • 101
1 vote
1 answer
21 views

SSAS Configuration Question

With SSIS and SSRS (and Power BI Report Server), you can install the service on one machine and the catalog database (SSISDB or ReportServer) on a different machine or clustered AOAG instance. Is the ...
Alex Pixley's user avatar
-2 votes
2 answers
54 views

Replace "INSERT" by "UPDATE"

I'm working with some tables in an SQL-Server database, and I'm doing some crucial modifications. In order not to loose everything when I mess up, I decided to do a "Generate script" (data ...
Dominique's user avatar
  • 521
-2 votes
0 answers
36 views

Best query for monitoring connection limit of 32,767

After reading the below blog the author makes it seem like their isn't much of a difference between sys.dm_os_performance_counters and sys.dm_exec_connections aside from the fact that the performance ...
Data Dill's user avatar
  • 231
-1 votes
2 answers
52 views

I want to find out how much is my database using and how much CPU are the function on that database is using

As I stated in the title, I want to find out how much cpu time my database is using VS how much cpu time all of the functions on that database are using. I want to show that the functions that are ...
Golosie Emanuel's user avatar
1 vote
1 answer
81 views

Module signing on a trigger which accesses system views. Base table is on a custom schema

Some background: Question is follow up of this question: Is there a way in SQL Server to make a table only able to insert by trigger? I'm trying to achieve what the answer to the original question ...
Stackoverflowuser's user avatar
3 votes
2 answers
377 views

What is the collation used while comparing Unicode string literals in SQL Server 2019?

My understanding is that the collation for comparing Unicode string literals is determined by the database collation. My database is using SQL_Latin1_General_CP1_CI_AS collation. When I compare N'ß' ...
QFirstLast's user avatar
0 votes
0 answers
24 views

Sqlcmd: The -W and the -y/-Y options are mutually exclusive

I am working on SQL server task. I need a sqlcmd result file with complete records with headers. Here is the command and error message below sqlcmd -S [Server_Name] -U [User_Name] -P [Password] -d [...
Yuvaraj's user avatar
3 votes
1 answer
138 views

Does automatic tuning revise the plan decision?

When automatic tuning is enabled, using SET AUTOMATIC_TUNING(FORCE_LAST_GOOD_PLAN), then based on query regression suppose SQL server auto decides to force a plan, then when does it revisit it's ...
variable's user avatar
  • 3,561
0 votes
0 answers
29 views

SQL Server database migration issues

I have a system with two servers and three client workstations. Servers were configured to work as principal/mirror servers (mirroring was configured with certificates). I have two databases in SQL ...
Lamija37's user avatar
0 votes
1 answer
28 views

Is It Normal to See Warnings in the Availability Group Dashboard for an Empty SQL Server Availability Group?

I've recently set up a new SQL Server Availability Group using T-SQL scripts. The setup was slightly unconventional as I initiated the availability group without including any databases initially. ...
Just a learner's user avatar
0 votes
0 answers
34 views

In a scalar function on SQL Server, is the only form of flow control via If...Else?

In a scalar valued user-defined function on SQL Server 2019, is the only form of flow control the If...Else block? Ideally I would rather use CASE...END, but everything I've found indicates this is ...
spinjector's user avatar
1 vote
3 answers
81 views

What is the Postgres Equivalent of SQL Server's Query Store?

I consider Query Store to be the best idea that Microsoft's SQL Server has had in the past ten years. I worry that I will miss it very dearly when I move to Postgres. What equivalents does Postgres ...
J. Mini's user avatar
  • 89
0 votes
0 answers
76 views

Properly DELETE 500k+ Rows from Multiple Tables in a Single Script

There are several examples all over the Internet and StackOverflow on how to properly delete 500k+ rows from a single table and COMMIT those DELETES in Batches. I am unsure of how to incorporate these ...
Code Novice's user avatar
0 votes
1 answer
22 views

SQL Server replication: Need to remove articles from the publication

I am replicating the data from the Database1 on Server1 to respectively, Database2 on Server2. Now, the requirement is to remove certain tables form being replicated - so I will remove them from the ...
Dmitriy Ryabin's user avatar
0 votes
0 answers
19 views

Local SQL to Managed Cloud SQL via ADF. Copy Data throughput fell off a cliff

I have an on-site mini PC acting as a data extraction and replication SQL server that copies data nightly from the ERP production database that's running some legacy linux architecture. The copying is ...
funkyman50's user avatar
1 vote
0 answers
46 views

SQL script to run from batch jobs

I have a sql script that truncates the table and insert into database from a linked server.Since the server is SQL express I will need to use batch jobs to schedule the jobs. Below is a snippet of the ...
SQL_NoExpert's user avatar
  • 1,049
0 votes
1 answer
28 views

what to do when database is not synchronizing in availability groups?

I am using Jonathan Kehayias - Monitoring Availability Group Replica Synchronization SELECT ar.replica_server_name, adc.database_name, ag.name AS ag_name, drs.is_local, drs....
Marcello Miorelli's user avatar
-1 votes
0 answers
43 views

What are some ways to manage High cxpacket waits followed by minimal latch_ex, sos_scheduler yield?

I have SQL server 2019 standard edition with 16 CPUs, 128GB RAM (max SQL memory), overall 176GB RAM, and SSD disks. CTFP is 50, and maxdop is 8. There is no cpu pressure in terms of worker threads. ...
variable's user avatar
  • 3,561
-1 votes
0 answers
48 views

Restore of database overwriting existing database even though `with replace` isn't set

SQL Server 2022, Standard Edition I have a database on one server (Server 1) called TestDb. I restored it on another server (Server 2) as TestDb_CORS. Users did some testing on the restored database, ...
variable's user avatar
  • 3,561
3 votes
2 answers
554 views

Is self signed cert the standard practice for SQL Server Always Encrypted?

We're implementing SQL Server Always Encrypted in our 2019 environment. We've done several successful POC's over the last few months, but in moving the solution to Prod, I was expecting to use a ...
Emo's user avatar
  • 143