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

SQL Server is terminating this session. c0000005 EXCEPTION_ACCESS_VIOLATION

I Got Error: A user request from the session with SPID 183 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log ...
user avatar
0 votes
0 answers
14 views

stored procedure runs fine in all sessions but one

There are procedures that run fine manually but not in a job, or fails when run from an application, or not work in SSIS SQL task Mine works in all sessions but one. this is the code I am running - it ...
user avatar
1 vote
1 answer
17 views

DBCC CHECKDB getting terminated abnormally

We have a SQL Server 2014 set up on Always on AG with one primary node and 2 secondary nodes. On the primary node, DBCC CHECKDB for one of the databases is scheduled on SQL agent job. This job was ...
user avatar
  • 21
-3 votes
1 answer
45 views

Detail on MS SQL CTEs

I am trying to find out how MS CTEs actually access data. Trying to find out if CTEs will actually run slower when pulling data and what is the order that a CTE will pull data. Does a CTE pull the ...
user avatar
  • 1
0 votes
0 answers
19 views

Issue with idle connections/open transactions from Python to SQL Server

I am having issue with Python code leaving idle connections to SQL Server while having open transactions in them. Background Recently, we had some major blockings on the SQL Server database due to ...
user avatar
  • 1
0 votes
1 answer
33 views

Finding the maximum values of a group count

i am using SQL Server, considering the following table: Content Table title topic id_user Title 1 Topic1 1 Title 2 Topic1 1 Title 3 Topic2 2 Title 4 Topic1 3 Title 5 Topic1 1 Title 6 Topic1 3 ...
user avatar
  • 101
5 votes
2 answers
299 views

Which execution plan is stored in the plan cache?

When query is executed, SQL server will produce a list of query plans and heuristically choose the plan with lower cost. The chosen plan will be stored in the plan cache for subsequent use when it ...
user avatar
  • 2,285
0 votes
1 answer
29 views

How to allow remote MSSQL user to bulk insert my local txt file?

I have a txt file on my local PC. I run localy Java app which connects to remote MSSQL. Now I need to bulk insert from that text file into my database table. However, SQL login can't read my file. I ...
user avatar
  • 135
0 votes
1 answer
14 views

Remove results if id has specific value with multiple values

The title doesn't help and hopefully I can explain this right. I need to exclude the PartNum from a query if it is in the KARDEX bin. These parts have multiple bins. If a part has the Kardex bin I ...
user avatar
2 votes
1 answer
46 views

How do I create a SQL loop that generates future dates based on different frequencies and intervals for each item until a specified end date?

I have a data set that appears like this: next_generation_date procedure interval frequency 2021-01-17 00:00:00.000 Clean Restroom 1 day 2021-01-17 00:00:00.000 Vacuum 2 week 2021-02-01 00:00:00....
user avatar
-5 votes
0 answers
31 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,285
0 votes
0 answers
17 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
1 answer
21 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
1 vote
1 answer
63 views

Storage space used for NULL values

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
1 answer
26 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
24 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
57 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
50 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,285
0 votes
1 answer
46 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
50 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
66 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
34 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
43 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
55 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
1 answer
50 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
65 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,285
-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,285
-1 votes
2 answers
45 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,285
-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
309 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,285
0 votes
0 answers
55 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
54 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,285
0 votes
0 answers
40 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
61 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
43 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,285
-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,285
4 votes
3 answers
873 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,285
-1 votes
1 answer
41 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,285
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
33 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
46 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
286 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
44 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
46 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,285

1
2 3 4 5
637