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
47 views

How a batch can get how much CPU it used in SQL Server 2019

Is there a statement I can put at the end of a batch to find out how many milliseconds of CPU it used? Something like SET STATISTICS TIME but for the whole batch, not each statement.
user avatar
  • 39
0 votes
1 answer
33 views

error [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified when import excel data to sql server

I work on SQL server 2017 I need to import data from excel 2016 to sql server 2017 I using python script to do that I create odbc and success test with name Testserver path G:\ImportExportExcel have ...
user avatar
0 votes
1 answer
38 views

Keeping schemas in sync with a database?

I have been asked to work on a database that has a regretful design and cannot be changed so I would ask that we work within these parameters. This is all in SQL Server 2019. I have SQL Server that ...
user avatar
2 votes
0 answers
43 views

SQLAlchemy ORM not sending prepared statements to SQL Server [migrated]

We use SQL Server with SQLAlchemy in this case. When looking at our plan cache we noticed tens of thousands of single use adhoc compiled plans. As you know this takes up a ton of memory, and every ...
user avatar
  • 29
0 votes
2 answers
102 views

How to handle tables that are needed in 2 or 3 databases?

My situation is as follows I have a sql-server running, which holds a few databases. The physical size of the largest database is about 2 GB, it has 343 tables, 129 views and some stored procs and ...
user avatar
  • 119
4 votes
3 answers
635 views

SQL Server / SSMS taking up lots of disk space

I installed SQL Server + SSMS for my univ class and ever since then my disk space is going missing (every so often, approx. 3 gb of space goes missing). I strongly suspect SQL Server and/or SSMS to be ...
user avatar
5 votes
1 answer
2k views

Using an extended event to track a single stored procedure

I'm attempting to track all executions of a stored procedure (Including from inside other stored procedures) and the statements of that procedure - is this possible with extended events? My current ...
user avatar
0 votes
0 answers
49 views

SQL 2014 v12 server - How to calculate how old is a CPU on a device

Trying to do a query to find out how old is the CPU on the device. We do have a way of doing it but it involves a manual step which we are trying to move away from. Basically now we are taking the CPU ...
user avatar
  • 1
2 votes
1 answer
39 views

Database stuck in a read only state when testing a DR scenario with a multi-subnet SQL Server Always On AG, how can I get the db in a healthy state?

I am testing a disaster recovery scenario with azure VMs. I have sql servers deployed across 2 regions joined together via Always On availability groups sitting on top of a multi-subnet WSFC. When I ...
user avatar
0 votes
0 answers
28 views

Is my database design correct?

I am designing the database for my ERP program. I have StokCards table which has all stock information in it belong to the company (products,bulk products,raw materials etc..) And this stocks have ...
user avatar
  • 1
0 votes
4 answers
52 views

What are untrusted constraints and what action needs taken to rectify?

Link: https://docs.microsoft.com/en-us/sql/dma/dma-bestpractices?view=sql-server-ver15 Check for untrusted constraints on both the source database and the target database before migrating data. What ...
user avatar
  • 2,140
0 votes
1 answer
22 views

Does the DMA tool do absolutely no changes on the source sql server?

I am planning to use the DMA tool for migrating sql server 2016 to sql server 2019. Both on-prem. I will be carrying out a test migration this weekend, followed by deleting the dbs and logins from the ...
user avatar
  • 2,140
0 votes
2 answers
33 views

Deadlock vicitm detected with two UPDLOCKS

How is this possible? For my understanding if i already have an UPDLOCK in the first process. The second process needs to wait or hang in the select, during the time the first process are not ...
user avatar
0 votes
1 answer
23 views

On what basis is the sql job's step linked to a database?

I have couple of SQL jobs on source sql server, and on the job step a particular database is selected. I want to move the databases and jobs on a new sql server, so I have scripted the jobs. In the ...
user avatar
  • 2,140
0 votes
0 answers
42 views

Does the replay functionality of data experimentation assistant (DEA) tool result in any database changes on target sql server?

I am looking at exploring the data experimentation assistant (DEA) tool on target SQL server. I have captured the trace using this tool on the source SQL server. Suppose I replay it on the target sql ...
user avatar
  • 2,140
0 votes
0 answers
37 views

IIS web application access SQL DB as service account

I've setup a new Python site on iis using FastCGI handler. The site has windows authentication enabled in iis and the app checks that the AD user belongs to an active directory group when they access ...
user avatar
  • 101
0 votes
1 answer
32 views

How to rename the target sql server instance after migration?

I am planning to migrate the sql server 2016 to 2019. After migration I want to take down the source server and rename the target server with the name of the source server. Thus applications will not ...
user avatar
  • 2,140
5 votes
3 answers
399 views

Does upgrading the database compatibility level post migration result in any breaking results?

Link: https://docs.microsoft.com/en-us/sql/database-engine/install-windows/change-the-database-compatibility-mode-and-use-the-query-store?view=sql-server-ver15 I am migrating sql server from 2016 to ...
user avatar
  • 2,140
0 votes
1 answer
20 views

How to check if the service broker is enabled at the server level and whether it is being utilized?

The following query return 1 for is_broker_enabled for a couple of databases. However, I don't recollect ever using the service broker feature. SELECT db.database_id, db.[name] AS [Database Name], ...
user avatar
  • 2,140
0 votes
1 answer
22 views

What exactly does `GRANT, REVOKE, and DENY Permissions on System Objects` mean?

Link: https://docs.microsoft.com/en-us/sql/relational-databases/databases/manage-metadata-when-making-a-database-available-on-another-server?view=sql-server-ver15#grant-revoke-and-deny-permissions-on-...
user avatar
  • 2,140
0 votes
1 answer
41 views

What is the recommended approach to migrate SSIS and related packages from sql server 2016 to sql server 2019?

I have SSIS packages in msdb database as well as in the ssis catalog (ssis db). What is the recommended approach to migrate SSIS and related packages from sql server 2016 to sql server 2019? For ...
user avatar
  • 2,140
0 votes
1 answer
24 views

Does SQL server support database migration using LUN re-pointing for system, user databases across different sql server versions (2016 to 2019)?

I have SQL server 2016 on server A. I want to migrate to SQL Server 2019 server B. I have installed the SQL server which auto creates the system dbs (master, msdb, model). D drive is for data files ...
user avatar
  • 2,140
-1 votes
1 answer
16 views

How to ensure that the source database cannot be modified when using the DMA's migrate option?

I am performing a database migration (onprem sql 2016 to onprem sql 2019) using the DMA tool. The tool only supports one time migration in the above scenario. It does not support an ongoing continuous ...
user avatar
  • 2,140
3 votes
1 answer
71 views

How to conclude whether the full text service is installed and/or used?

SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') Above query returns 0 However, the below query returns 1 for all user databases. How to conclude whether the full text service is installed and/...
user avatar
  • 2,140
0 votes
1 answer
28 views

matching names and assigning unique identifier - master client index

create table dbo.data1 ( ClinetKey int, SourceID int, SourceName varchar(5), ClientID int, ClientFirstName varchar(50), ClientLastName varchar(50), UID int ); insert into dbo.data1 (ClinetKey, ...
user avatar
0 votes
1 answer
31 views

How do I get the sum of a field and avoid duplication from an inner join?

I am pulling Geo from the Accounts table and I am ranking the Geos by Opportunity $$ Amt based on Accounts that have the name like "Brinks%". Accounts are unique, and there can be many ...
user avatar
  • 3
0 votes
0 answers
35 views

Is there a readymade script that I can use to automate sync of server level logins, roles and securable permissions between 2 SQL servers?

I have configured log shipping from SQL1 to SQL2. Database level users, roles and securable permissions are getting transferred. However suppose the user doesn't exist on the server level login, then ...
user avatar
  • 2,140
0 votes
1 answer
25 views

What does it mean to reach a stable point during the execution of a job?

Trying to use sp_stop_job to stop a simple job that inserts data on a table when 100 rows were inserted I got confused to see the table end up with more than 100 rows every time. Under the Remarks ...
user avatar
  • 4,384
0 votes
1 answer
40 views

How to restore backups with recovery even when there are subsequent backup files that need restored?

Suppose I have a full backup, differential backup and 10 log backup files. The recommended practice is to restore each with no recovery and then apply the final log backup with recovery. Suppose I do ...
user avatar
  • 2,140
-1 votes
1 answer
15 views

Log backup after switching recovery model from full to simple to full, followed differential backup (not full backup) contains which transactions?

Suppose I have configured full backup for every Sunday, differential backup every day at 1AM. Then switch db from full to simple recovery model. Then switch back to full recovery model. Now if I try ...
user avatar
  • 2,140
0 votes
1 answer
24 views

What happens when full backup, differential backup and log backup schedules collide?

Suppose I have configured full backup for every Sunday 1AM, differential backup every day at 1AM and log backups every hours from 12AM. So, every day the differential and log backups collide at 1AM. ...
user avatar
  • 2,140
0 votes
1 answer
21 views

How to handle logins and database users for migration of sql server into new domain (new installation, no trust with original domain)?

I want to migrate SQL server 2016 to SQL server 2019 (both machines are onprem, separate domain, no trust/connectivity between the domains, the new sql server will be a new installation on target ...
user avatar
  • 2,140
-3 votes
2 answers
76 views

I am facing difficulties in backing up a database in MS SQL Server

When I try to backup a database, it is showing this message : to accomplish this action set property devices. (microsoft.sqlserver.smoextended) I cannot able to select the file location to backup as ...
user avatar
3 votes
1 answer
548 views

Is there a way to Delete inserted data, or vice versa?

I'm trying to get Data inserted and then Deleted in the same query. I'm trying to delete it in chunks to not cause LOG issues: DECLARE @BatchSize INT SET @BatchSize = 100000 WHILE @BatchSize <> ...
user avatar
  • 7,100
0 votes
0 answers
19 views

Linking synonym in ServerA.DatabaseA to ServerB.DatabaseB

There are a handful synonyms in ServerA.DatabaseA that references DatabaseB in ServerB. The synonyms in ServerA are constructed like this: USE [DatabaseA] GO CREATE SYNONYM SynonymName FOR ServerA....
user avatar
2 votes
1 answer
56 views

ownership chaining not working with sp_cursorfetch

ownership chaining is not working for me in some cases. I use Microsoft SQL Server 14.0.3370.1. This is the setup for my tests: create function fnTest() returns int begin return 3 end go create ...
user avatar
  • 121
0 votes
1 answer
39 views

How to sync sql server login and database level permissions between 2 sql servers?

If I have log shipping configured between 2 sql servers on premise, then, in addition to the database data and schema, will the database user permissions and server login permissions/role membership ...
user avatar
  • 2,140
1 vote
0 answers
33 views

Resolve Orphan Transaction locking

My SQL Server version is Microsoft SQL Server 2019 (RTM) and I faced a new locking problem. An SSIS package was running. After a while it became suspended by a session with ID of (-2). I know this ...
user avatar
2 votes
5 answers
600 views

Is there any purpose of Ola Hallengren's SQL Server Index and Statistics Maintenance scripts on sql server running on SAN storage?

I have configured Ola Hallengren's backup and integrity check scripts. However I want advice whether it is required to setup the SQL Server Index and Statistics Maintenance scripts, because my SQL ...
user avatar
  • 2,140
0 votes
0 answers
21 views

Is there a script to migrate the sql server login's securable permissions?

I have scripted server level logins, roles and role membership and migrated this onto another server. However it is missing the securable permissions. Is there a script to migrate the sql server login'...
user avatar
  • 2,140
-1 votes
1 answer
35 views

Recreate broken Database

Here is the scenario - I have a Database that is suffering from Error 824, dbcc checkdb repair_allow_loss has not rectified it and unfortunately the Backups are also corrupt. However, I am able to ...
user avatar
0 votes
1 answer
59 views

Can not login with windows authentication after changing sa and windows passwords

I changed sa and Windows administrators password successfully, I can login to server with sa without any problem, services working normally, database is not corrupted and working without any problem. ...
user avatar
1 vote
1 answer
34 views

Creating Random Set Values For Each Cross Join from CTE

I am trying to load up a table with values that are generated randomly based on another table. The issue is that the result set from the final Cross Join, is not unique (random) for each of the rows ...
user avatar
  • 377
1 vote
1 answer
35 views

Restore filegroup without log sequence number (LSN)

I have the following error: The roll forward start point is now at log sequence number (LSN) 5470633000000066000001. Additional roll forward past LSN 19429240000000043500001 is required to complete ...
user avatar
1 vote
1 answer
46 views

How to configure Query Store for resource usage

I want to know how many milliseconds of CPU utilization happens for a particular database per 5-minute period. Please correct me if I'm wrong, but the Query Store looks like a great way to find that ...
user avatar
  • 39
0 votes
0 answers
31 views

DB design help in approve reject workflow

I have an issue for designing a Db in SQL. I have following scenarios. There are many task Each task has many questions Each question has answers in the form of (yes/no/NA) only 3 answers (fixed). ...
user avatar
  • 1
2 votes
0 answers
86 views

Why does SQL Server 2019 stored proc get Adaptive Join on Development Server, but not on Production Server?

I have a stored proc with one select statement that gets a plan w/ Adaptive Joins on my development server, but not on my production server. What could explain that? I'm not much interested in ...
user avatar
2 votes
1 answer
36 views

BCP Insert through View vs Manual insert

Consider the following code use tempdb go drop table if exists ImportTest1; create table ImportTest1 ( Column1 int null, Column2 int null, NewColumn int null ); insert into ImportTest1 select 1,...
user avatar
  • 2,204
0 votes
2 answers
66 views

SQL Server - Exclusive lock taken by a SELECT statement - Why?

I'm trying to make sense of why SQL Server (2014) is placing an eXclusive key lock during a deadlock scenario. I've pasted the entire deadlock graph below. I'm confused because the deadlock is ...
user avatar
0 votes
0 answers
48 views

Clear database cache in SQL Server and also Postgresql

My current tasks involve optimizing queries for both SQL Server (using TSQL language) and PostgreSQL. There are ways to run large batches of the same query to get timing results. However, it appears ...
user avatar

1
3 4
5
6 7
637