We changed our privacy policy. Read more.

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
1answer
64 views

SQL Server always on servers reboot

I have the below set up for availability group : Always on configured in SQL Server 2017: Primary is read write and secondary is read only. Both primary and secondary are configured with asynchronous ...
2
votes
1answer
46 views

Speed the response time of the Query

So I have this query that I am using on an API endpoint, the query takes between 3-4 seconds to run, returning a small OFFSET of 10 rows. Is there a way I can put indexes somewhere or improve on ...
2
votes
2answers
81 views

Keep full history of every values in each table

How would you do to keep full history of every values of all tables in a DB, which can be queried with a specific date (for each and every column of all tables)? I mean: one would just need to specify ...
-1
votes
2answers
29 views

Accessing SQL server on-Prem database views from remote server through Azure Data Factory

We need to copy the data from client's remote SQL server (on-prem) database to our Azure SQL server through Azure data factory. So we can automate the data pull on regular basis. Client tried to offer ...
-1
votes
0answers
39 views

Calculate extra space needed for Ola Hallengren's Index and Statistics Maintenance?

Suppose I make the following call to Ola Hallengren's Stored Procedure for Index and Statistics Maintenance: EXEC IndexOptimize @Databases = 'USER_DATABASES' ,@UpdateStatistics = '...
-3
votes
1answer
54 views

Count only new people added to payroll based on previous month

Every month we have new people added or removed from payroll. i'm trying to figure out how many new people are added based on the previous month. The image show an example of how the table is ...
0
votes
0answers
38 views

SQL Server index on uniqueidentifier, how is the data ordered

If an index defines the order of the data and a uniqueidentifier can't be ordered, then how does an index on a unqiueidentifier work. How is the data ordered?
0
votes
0answers
40 views

Creating sql server custom database role that only allows reading some columns and nothing else

I want to create an sql server Custom Database Role which only allows reading some columns of some tables, and nothing else. this is my attempt. CREATE ROLE CustomDatabaseRole01 GO GRANT ...
-1
votes
1answer
94 views

Rollback of ALTER TABLE ALTER COLUMN command taking forever

I have a very large table that needed a schema change to one column - I needed to expand a datatype from varchar(50) to varchar(100). I expected this to happen quickly since the column datatype is ...
-2
votes
1answer
48 views

Analyze query performance

I'm in the progress of analyzing the query performance of my SQL Server 2019 SE, I expect to have the information of the query performance, like below: Date_Time_Running, Func/Store_Proc, SQL_Command, ...
6
votes
1answer
167 views

MSSQL table variable identification

Is there any way to identify which stored proc is creating a table variable in tempDB? I am looking at the forward_fetch_count in sys.dm_db_index_operational_stats and we have a few tables that have a ...
7
votes
2answers
605 views

SQL Server / XML.query() causes massive memory grant

The following (on my systems, tested on SQL Server 2016 and 2019 std) produces a massive memory grant request (3327 MB) declare @wibble xml = '<wibble><wobble>1</wobble></wibble&...
1
vote
1answer
43 views

Can we use Suspend data movement as a Rollback Plan in AOAG

We have an always-on setup...we are doing some reprocessing of data and as part of that activity, we will be suspending the data movement between primary and secondary before making the changes on ...
0
votes
1answer
26 views

On premise to cloud restore - Automated?

What's the most automatic way to restore a on-premise SQL Server database to Azure SQL Database? Has anybody done or seen such a solution? Scenario: Need to do a weekly refresh of a dev database from ...
-1
votes
0answers
16 views

Why when I turn policy scheduler on, the new created scheduler name is a GUID?

On SQL Server, when I enable the policy scheduler on, the new created scheduler name is follow by a GUID? Why it's not follow my policy name?
5
votes
3answers
346 views

Hovering over an Operator in Execution Plans: How to make the tooltip not disappear when moving the mouse

I want to share my screen and show an execution plan but when I hover my mouse over an operator and what to point to something with my mouse in the tooltip then it disappears. Is there a way to make a ...
1
vote
1answer
71 views

SQL Server - memory usage per database level and query level

I am trying to find a query for two things: Get Current Memory Usage per database There are different databases at the same server so I need to get Memory Usage for specific database, not for all ...
0
votes
1answer
35 views

SQL Server Partitioning by Local And Remote database

We have a table related to documents(about 17m records on one table), and another one for properties of that documents. And team decided to archive old entries. I thought about partitioning with ...
-4
votes
1answer
44 views

How to join two tables in sql server? [closed]

I need some help i have a query, struggling to use join and database is sql server. Below is my table definition. // sql server query SELECT TOP 1000 [Station Code] ,[Transaction Code] ...
3
votes
1answer
20 views

Nested Stored Procedures Throwing Data Access Error on .NET Assembly Function

First and foremost, the data server this is happening on is running SQL Server 2005 SP4, and unfortunately it's not an option to upgrade. We've been pushing the client to upgrade for the three years ...
0
votes
0answers
40 views

Preventing read access for users connecting to primary replica and allow access only on secondary replicas [duplicate]

I have a requirement where Read only users group should not be able to access primary replica at all and group should have read access only through secondary replicas. I setup applicationintent=...
0
votes
1answer
43 views

Looking for help understanding what is happening in this SQL Script that shows key info about SQL Server Autogrowth Events

Here is the code below: DECLARE @current_tracefilename VARCHAR(500); DECLARE @0_tracefilename VARCHAR(500); DECLARE @indx INT; SELECT @current_tracefilename = path FROM sys.traces WHERE is_default = 1;...
0
votes
0answers
55 views

How to save the error returned by a stored procedure

I am trying to figure out how to get the error message returned from the proc sys.sp_testlinkedserver rather than the non-descriptive message returned by ERROR_MESSAGE(). The proc's message is: OLE ...
0
votes
1answer
52 views

Get current execution metrics from sys.dm_exec_sessions

I've got a pretty complex batch processing procedure that takes hours to run on real data. I've found that some CPU and I/O counters are stored in the master.sys.dm_exec_sessions table, and so thought ...
3
votes
1answer
122 views

How to know real SQL Server memory and CPU usage

My SQL Server 2019 SE runs on Windows Server 2019 SE, with hardware specs as below: 40 Core 1TB RAM I think the above spec is overkill!, but if I see from Windows Task Manager: the SQL Server memory ...
14
votes
1answer
1k views

Why does a comparison between 'tr' & 'tR' fail on a SQL Server with Vietnamese_CI_AI collation?

There seems to be something special about 'tR' in Vietnamese collation. Appreicate if anyone who knows about it can explain in simple terms. This issue was discovered during the installation of our ...
0
votes
2answers
44 views

Is there a simple way to add a read-only user?

I have a hosted (shared) SQL Server database and I'd like to add a read-only login. (This login will be used to build Power BI data visualization.) Looking into this, it's far more complex than I ...
1
vote
1answer
47 views

What are the risks in moving from GDR to CU servicing option?

I have SQL server 2019 with RTM baseline and GDR patches are applied. I am reading that GDR only contains security fixes and CU contains security + functional fixes. I also know that you are allowed ...
0
votes
1answer
47 views

On what basis do we decide whether to use GDR or CU?

I have installed sql server 2019 in Jan 2021. We are planning to patch the sqlserver and from this link there are 2 options: GDR CU https://docs.microsoft.com/en-us/sql/database-engine/install-...
0
votes
0answers
42 views

Database Scheme

I have a problem with a use case and I don't know how to design my database. Here are the detail : I have an entity "event" which can occur at different frequency but for each frequency ...
0
votes
0answers
21 views

ETL to Read File JSON data and load in Database every Min

I have a requirement where the files are shared in JSON format at shared location. I need to create a ETL package which can be schedule to run every min and look for the file at shared location and ...
1
vote
0answers
42 views

Default logins after installating SQL Server 2019 [duplicate]

I migrated my old logins from a SQL Server 2017 instance to a newly installed SQL Server 2019 instance, by following Microsoft's guidelines: https://docs.microsoft.com/en-us/troubleshoot/sql/security/...
2
votes
1answer
66 views

DB Migration complication

I am currently working on a database migration use case where the requirement is to migrate a database of size 220 GB from a source SQL Server 2005 (installed on Windows 2003 Server R2 edition, ...
0
votes
2answers
48 views

How can I suppress or disable temporal table history insertion for a single statement or transaction?

I have a table that stores Customer information (your bog-standard CRM stuff): CREATE TABLE dbo.Customers ( TenantId int NOT NULL, CustomerId int NOT NULL, ...
1
vote
2answers
117 views

Can I bring two values from a single row into a Pivot Table

I am trying to get two values from rows in a table into a single row in a pivot table. myTable has the following rows ID1 ID2 Item Value SP 11 22 Item 1 10 10 11 22 Item 2 100 100 11 22 Item 3 49 ...
16
votes
4answers
1k views

Is it a good idea to Partition a table (in MS SQL) based on date, when there's a clustered index present on ID (INT)

I have a table in MS SQL Server . Table Size: 806 GB Rows : 1.2 billion Index Space : 1.2 GB Table Usage: Logging from the Web Service calls 99.9% is usage is from the logging, developers look ...
1
vote
0answers
48 views

SQL Server: Track new rows across all tables

I am in the middle of collecting data from SQL Server to other locations for processing and storing and I need to find a way to only SELECT new rows since last nightly data collection so I dont need ...
1
vote
1answer
33 views

How to reset the logon for the sql server services?

I changed the logon from NT service accounts to my local account at some point for testing purposes. Now I want to reset this logon back, however, I do not know the credentials! Is there a way to ...
6
votes
1answer
376 views

Is deleted space from one table available for other tables?

A SQL Server Express database hit the 10GB limit. I cleared a lot of orders over a certain age. Is this cleared space available for other tables in the same database? I don't need to reduce the ...
0
votes
1answer
54 views

SQL Server database extent and pages

I want to ask about the SQL Server database, all I know about database structure is that under the database we have datafiles (.mdf/ndf & LogFile), then extents, then pages, and each extent ...
1
vote
1answer
22 views

How to specify differential backup option?

I'm implementing an automated log shipping solution, and so far I have the following: <# #Pre-req# a. The primary database needs to be in Full recovery mode #Primary Steps# 1. Backup: Generate ...
-3
votes
1answer
31 views

Get datetime based on change in another column using UDF and computed column in TSQL [closed]

Given: Given a Microsoft SQL database table Log with multiple columns including these important ones: id (primary key), code (an integer that can take multiple values representing status changes), ...
2
votes
1answer
64 views

What is the difference between creating database role and application role?

I want to have a role called ValidUsers. My application will check if the user belongs to this group. If so then allow him to access the application. Now when I right click roles under the database, I ...
6
votes
4answers
516 views

Performance Concerns with UniqueIdentifiers (GUIDs)

I am a software developer/architect at my company and occasionally our DBA whenever our primary DBA quits, so I think I kind of know what I'm doing, but this has me stumped as to the best course of ...
0
votes
1answer
20 views

Looking for authoritative Microsoft reference about db_owner automatically having db_datareader role and how to check specific role is check marked?

SELECT IS_MEMBER("DB_DATAREADER"); The above return 1 for user who has db_datareader. Also, it returns 1 for user who doesn't have this role but has got db_owner. I am looking for ...
2
votes
0answers
31 views

SQL Server - Holistic approach to managing permissions from a domain [closed]

We are small IT department and we are changing how our infrastructure works. We are trying to make it more secure, manageable and transparent for auditing. We are implementing a domain for our servers ...
0
votes
1answer
47 views

How to calculate maximum and minimum values from different tupples in SQL?

I'm working on a program, that would "predict" the next possible tupple in a table. One of the features would be the finding of arithmetic progressions, there are different ways to do this: ...
1
vote
1answer
13 views

What does it mean by add an existing msdb role to one of the following SQL Server Agent fixed database roles?

Link: https://docs.microsoft.com/en-us/sql/ssms/agent/configure-a-user-to-create-and-manage-sql-server-agent-jobs?view=sql-server-ver15#Security To configure a user to create or execute Microsoft SQL ...
1
vote
3answers
57 views

Is this true? `Members of the local computer administrator group can always elevate their privileges to sysadmin.`

Link: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/determining-effective-database-engine-permissions?view=sql-server-ver15#summary Is this true that Members ...
4
votes
1answer
124 views

SQL Server 2016 suddenly produces Error:402,State:1,Class:16 updating a sproc with DATETIME/DATE/TIME arithmetic, that was fine before

In one of stored procs I need to capture the duration of a live event. It is guaranteed to never exceed 24 hours, so the datatype for the result column is chosen as TIME(3) (mapping to TimeSpan on ...

1
3 4
5
6 7
614