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.
31,834
questions
-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 ...
- 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. ...
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 ...
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/...
- 4,408
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 ...
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 ...
- 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 ...
-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 ...
- 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%). ...
- 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 ...
- 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 ...
- 105
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 ...
jmichaelwDBA
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 ...
- 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 ...
- 140
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 ...
- 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 ...
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 ...
- 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, ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 :
...
- 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 ...
- 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 ...
- 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 ...
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
-2
votes
0
answers
32
views
warning in execute plan [closed]
Who has an idea please for this type of warning as is corrected ?
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 ...
- 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
,...
- 151
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 ...
- 53
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 "...
- 101
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 ...
- 123
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 ...
-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 ...
- 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 ...
- 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 ...
- 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]
...
- 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 ...
- 3
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 ...
- 11
-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?
- 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, ...
- 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 ...
- 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?
- 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 ...
- 2,243