2022 Developer Survey is open! Take survey.

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 votes
0 answers
10 views

Why must the error log path be changed if the system databases are moved from C: to a different drive?

Link: https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?view=sql-server-ver15#Follow If you have moved all of the system databases to a new drive or volume or ...
user avatar
  • 2,243
0 votes
0 answers
6 views

Report (log) of each transaction on SQL Server Replication?

I've configured a transactional type of Replication between two SQL Servers. Since it's a project that it's still under review on my side I have only selected a couple of articles for the replication. ...
user avatar
0 votes
0 answers
8 views

Can transactions on main table be delayed/deadlock when maintenance transaction on temporal table runs?

I am going to run maintenance procedure similar to described in Cannot delete rows from a temporal history table . The stored procedure will SET SYSTEM_VERSIONING = OFF , delete 3 million rows on ...
user avatar
0 votes
1 answer
33 views

NULL Storage space in SQL Server

According to below links, NULL values take some storage space: https://stackoverflow.com/questions/3731172/how-much-size-null-value-takes-in-sql-server https://www.sqlservercentral.com/forums/topic/...
user avatar
0 votes
0 answers
15 views

Backup on Distributed AG setup

I have setup a distibruted availability group and trying to understand behaviour of backups on it. While I can take backup from primary replica of Primary AG, I can not take backup from primary ...
user avatar
0 votes
0 answers
18 views

Is there any option to have change tracking partition table?

We have one partitioned table which has a lot of changes during week. This are stats for table: table_rows table_size_mb ct_rows ct_size_mb 175 857 125 18 762 2 610 849 197 467 859 Is there any ...
user avatar
  • 307
-1 votes
0 answers
40 views

How can I move data from .mdf to .ndf?

My new filegroup doesn't work. I limited .mdf file and I changed the filegroups of all indexes but the data didn't go to other filegroup and .ndf files. I got 'PRIMARY' filegroup full error and I ...
user avatar
-3 votes
1 answer
42 views

How to get `dbcc checkdb` report when run via agent job?

When I run dbcc checkdb via management studio, then I get the full report as output. However when I run dbcc checkdb via sql agent job, then I think that the job status will either show as success if ...
user avatar
  • 2,243
0 votes
1 answer
43 views

Logging Which Columns Have Been Read Or Written To

I have a Microsoft SQL Server database with dozens of tables and perhaps thousands of columns. Most of the code that ran queries against the database (read and write) is now out of service (like 80%). ...
user avatar
  • 109
0 votes
1 answer
48 views

GRANT ADMINISTER Database Bulk Operations - Incorrect syntax near ADMINISTER

I am trying to import a bacpac file to my SQL Server Express 2016. An exception is thrown during the import with GRANT ADMINISTER Database Bulk Operations TO [MyUser]; Returns: Incorrect syntax near ...
user avatar
  • 103
-2 votes
1 answer
54 views

How to convert data separated pipe to rows?

I work on sql server 2017 I have table name #countriesData data as below my issue here How to convert countries column data separated pipe to rows . meaning every rev and company and year will have ...
user avatar
0 votes
1 answer
29 views

SQL Server 2014 Service Pack (SP3-CU4-GDR - KB4583462) install faliure

I ran into an error during the install of Microsoft SQL Server 2014 (SP3-CU4-GDR) (KB4583462) - 12.0.6433.1 During the script upgrades to the system database the upgrade ran into: This is from the SQL ...
user avatar
0 votes
0 answers
28 views

Database for dynamic number of hierarchy levels

My client wants to support dozens of cities where each city has its own graph-like data structure to store location hierarchy, for example: City 1: Region -> District -> Street -> Shop City ...
user avatar
  • 11
1 vote
1 answer
38 views

CREATE LOGIN for AD user in master not allowing connection

I am struggling to figure out the best method to set up security on an Azure SQL Managed instance. First, I tried to set up SQL logins, but since MUST_CHANGE isn't supported, I would then have access ...
user avatar
1 vote
2 answers
51 views

Temporarily turn on Cascade Delete

This has probably been asked before, but I couldn't find the answer. I have a complex datamodel in sql-server-20XX (more likely to be something old) in which cascade delete has been turned off (by ...
user avatar
  • 13
3 votes
0 answers
36 views

Always On Availability Groups connection with secondary database established for primary database

I'm seeing this messages almost everyday in one of the production server. I don't feel comfortable seeing this..is there anything we can do to suppress this messages ? though it's showing No user ...
user avatar
2 votes
2 answers
60 views

How to get the specific queries that are contributing to high lock wait times?

I am looking at SQL lock wait time perf mon counter and it is indicating high value ranging from 5 to 20 seconds during certain times of the day. I have setup extended events which gives me the T-SQL ...
user avatar
  • 2,243
-2 votes
0 answers
32 views

Does PLE have any relation to the backups performed? [duplicate]

Page life expectancy tells how many seconds a page remains in memory. Backup uses backup buffers which are separate to the sql server buffer pool memory. So this means that when a backup is performed, ...
user avatar
  • 2,243
-1 votes
2 answers
42 views

How to estimate cpu based on peak rate of query requests?

By default, SQL Server allows a maximum of 32767 concurrent connections which is the maximum number of users that can simultaneously log in to the SQL server instance. I am developing 10 web apis ...
user avatar
  • 2,243
-3 votes
0 answers
46 views

SQL Server differential backups too much slow and big

I configured a backup plan on my SQL Server 2019 Web Edition with this scheduling: a full backup every day at 00:00 a differential backup every 15 minutes What I was imaging is that every ...
user avatar
  • 127
1 vote
1 answer
42 views

Sql Server Large amount of unused database users and logins

Pardon asking what may be considered an opinion based question, but what are the benefits of removing unused database users and logins? I have a database with > 50k sql users which map to sql ...
user avatar
  • 71
-2 votes
1 answer
298 views

Is there a deterministic relation between the page life expectancy and disk IO throughput?

Start with Page Life Expectancy, which should be well above the 300. This tells you how long pages are staying in the buffer pool, and a value of 300 equates to 5 minutes. If you have 120GB of buffer ...
user avatar
  • 2,243
0 votes
0 answers
52 views

Resource consuming query

I have found below CPU consuming query with a query cost of 1540. I have tried creating appropriate indexes which reduced the query cost to 1234 but still, the query is expensive. Index Created : ...
user avatar
  • 1
-3 votes
1 answer
51 views

Does max SQL memory include SQL AG memory requirements?

I have sql server 2019 enterprise on windows server 2019, with 30 databases totalling 500GB size. RAM is 100GB. Of this 94GB is allocated as Max sql server memeory. So 6GB remains for OS. There is ...
user avatar
  • 2,243
0 votes
0 answers
39 views

How to Force Drop a Database that is in Emergency Mode and corrupt?

In SQL Server 2014. We have a database that is in Emergency mode and is corrupt beyond repair. We need to get rid of it and reclaim the space. But we're having trouble as the database isn't even ...
user avatar
  • 143
0 votes
0 answers
60 views

sp_BlitzFirst - SQL Server - slow data reads - huge wait stats

We are experiencing intermittent SQL Server issues, where from time to time, it would become unresponsive for a few minutes, causing an outage of a system, driving crazy many users. I started using ...
user avatar
0 votes
0 answers
31 views

Get distinct pairs with multiple columns on table with next row

I currently have a table that has a list of locations a schedule will use and I want to be able to get the location code and platform for both the current row and the next one, and then have the ...
user avatar
  • 133
-5 votes
1 answer
42 views

Does enabling readable secondary in the availability group put any load on the primary server's tempdb?

Enabling readable secondary will result in addition of 14byte extra information to each row in the primary as well as secondary. This will also automatically enable the snapshot isolation level on the ...
user avatar
  • 2,243
-1 votes
2 answers
37 views

How does the read committed snapshot isolation level relate (link) the row in tempdb to the original row?

I understand that the read committed snapshot isolation level holds the row versions in the tempdb. I enabled this feature on a test database and I was expecting to see a new column (guid) get ...
user avatar
  • 2,243
4 votes
3 answers
867 views

How to recover from a SQL server log file drive crash?

Assuming data files are on D: and Log files are on E: Say the E drive crashes and the log files (.ldf) are lost. I attach a new empty disk to the E: and start the sql server. Upon startup, SQL Server ...
user avatar
  • 2,243
-1 votes
1 answer
40 views

What is the reasoning behind allocating the OS 1GB for each 4GB of RAM?

Link: https://codingsight.com/understanding-the-importance-of-memory-setting-in-sql-server/ Let us now briefly look at the techniques to size the memory. 1 GB of memory reserved for Operating System ...
user avatar
  • 2,243
0 votes
1 answer
22 views

Software to help automate windows os patching with SQL Server high availability/failover clusters

is there a third party tool or a way to automate installing windows updates on windows clusters with sql server high availability groups installed? right now we have a manual process of installing ...
user avatar
-2 votes
0 answers
32 views

warning in execute plan [closed]

Who has an idea please for this type of warning as is corrected ?
user avatar
0 votes
1 answer
45 views

How to figure out which procedure or function is causing the most logical reads on a particular table?

One of my tables only has about 5k rows, but is the table with the most logical reads, of all the tables in the database (based on dm_db_index_usage_stats). I'd like to work on the indexes to this ...
user avatar
  • 169
0 votes
0 answers
25 views

xp_sqlagent_enum_jobs - Unable to enumerate Jobs due to failure to obtain serialization object

I a procedure whose job is to ensure that certain SQL agent jobs do not overlap with other jobs, the logic is as follows(Simplified): CREATE TABLE #Job ( job_id UNIQUEIDENTIFIER NOT NULL ,...
user avatar
5 votes
1 answer
283 views

Minimise duration of table lock

I've got a SQL table that needs to be updated daily. There may or may not be queries against that table while the update is happening. It's around 500,000 rows. We have an issue where there is locking ...
user avatar
0 votes
2 answers
42 views

Can't identify which server is primary and which is secondary in a SQL Server Availability group

I'm writing an application that queries the backup history for a number of databases on a number of SQL Server 2017 servers. Assuming a pair of servers (SQL001 and SQL002) and a database called "...
user avatar
1 vote
1 answer
43 views

Check auto-numa is enabled

I run query select cpu_count, hyperthread_ratio, softnuma_configuration, softnuma_configuration_desc from sys.dm_os_sys_info and got results, where said, that numa is off: cpu_count ...
user avatar
0 votes
0 answers
28 views

Two views yielding the same results switch places performance-wise when called remotly

An SQL Server 2000 has a view "A" that produces around 700 rows of data in about 30 seconds. I was tasked with optimizing "A" and created a view "B" that produces the ...
user avatar
-6 votes
1 answer
54 views

Does offline index rebuild of clustered or non-clustered index block user's SELECT/UPDATE/INSERT/DELETE queries?

I understand that index rebuild requires schema stability (Sch-S) lock which means it will block any query that tries to do the schema modification (Sch-M). Does an offline index rebuild of clustered ...
user avatar
  • 2,243
0 votes
0 answers
25 views

How to filter out some database roles from sql server login audit?

I have in place a Server audit that collects logins. Every 5 minutes I import that data into a table in another instace where I build and send by email a small report. That's fine but i need to set up ...
user avatar
  • 3,367
-4 votes
1 answer
32 views

Why is SQL AG sync commit mode classified as no data-loss HA solution even when the session timeout can make the mode async?

Assuming the SQL AG is in sync commit mode. Suppose the secondary becomes unresponsive or has secondary has network issues, then upon the session timeout expiry the AG will automatically get converted ...
user avatar
  • 2,243
0 votes
3 answers
73 views

Using Boolean Fields in a View to Identify Linked Tables

I am using SQL Server 2019. I am using SQL Server Management Studio on the client side. Suppose I have the following data model: -- A record of the overall test of a widget CREATE TABLE [TestRecords] ...
user avatar
  • 157
0 votes
1 answer
34 views

Restrict access to a new schema to a specific role despite users having db_datareader?

I'm working in a SQL Server 2016 database that is not of my design nor can I fundamentally change the security structure. I know it is likely trash/worst practice but I have to play the hand I am ...
user avatar
0 votes
0 answers
29 views

How to use "with execute as" while querying a linked server from SQL Server

I have a strange permission issue with SQL Server 2019 (same thing happened also with 2016 version). I have a database ReportDB, where reportuser (login: reportlogin) can run several reports. I also ...
user avatar
-1 votes
0 answers
35 views

Does restart in FCI/AG mean node restart or sql server cluster aware services restart?

There are failure condition levels for FCI and AG. I read the keyword restart. Just want to confirm whether restart means node restart or sql server cluster aware services restart?
user avatar
  • 2,243
2 votes
2 answers
181 views

Non-clustered Primary Key and Clustered Index

It is my understanding that in SQL Server, you can have a Primary Key that is non-clustered, and have another index that is the clustered one. To me, this seems the same as just having a Primary Key, ...
user avatar
  • 599
1 vote
1 answer
44 views

Unlike FCI, doesn't AG make use of heartbeat to check for server down?

SQL AG in WSFC checks whether server is down via the lease timeout Where as SQL FCI checks whether server down via heartbeat Does the SQL AG not make use of heartbeat at all? Below is a sample log ...
user avatar
  • 2,243
-2 votes
1 answer
43 views

What is recommended approach for fixing corruption related errors?

When there are errors of code 823 in the log or dbcc has detected corruption, then should the 1st choice to solve this be: dbcc checkdb with no data loss or restore from full backup+tlog backups?
user avatar
  • 2,243
-1 votes
1 answer
33 views

Is disk witness still the recommended best option for 2 node cluster (versus file share) in SQL server 2019?

Link: https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-r2-and-2008/cc770830(v=ws.10)#local-two-node-cluster It says that the disk witness is the recommended best ...
user avatar
  • 2,243

1
2 3 4 5
637