The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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

How to optimize stored procedure so it won't end with “Out of memory excetoption”

I have created a stored procedure that check against 2 and then delete from the 3. When I execute the stored procedure on Management Studio I get the exception as mentioned in this question: ...
0
votes
1answer
40 views

Sql Server - Multiple errors regarding to memory in error log

MSSQL-Server 2017 Express Edition runs on my Linux server. The server has 4GB RAM. The total size of DB is 300MB. My website and the sqlserver are on the same server, it's a small website with max of ...
-1
votes
0answers
16 views

DBCC checkDB stuck in killed/rollback state

I killed DBCC CheckDB Job of 3TB Database which has been rolling back since 17 hours with 89 %.as i have checked that the SPID running with waittype EXECSYNC. Could we have any other way to clear this ...
4
votes
1answer
153 views

Why the job's next_run_time is wrong?

I got an issue regarding job stats. So I have a job with the following schedule: Occurs every day every 5 minute(s) between 12:00:00 AM and 11:59:59 PM. Schedule will be used starting on 12/13/2017. ...
0
votes
1answer
15 views

Computed Column Operand type clash on Date Column When Checking For Null

This error is generated when trying to create a table with a computed column from a Date field. Operand type clash: int is incompatible with date If I comment out the computed column it works. [...
-4
votes
0answers
14 views

Database for Shipping Company [on hold]

To do database in SQL server for Shipping company .. So I need employee page to insert the shipment information then press ok and auto generate track number .. I will give to customer and he can press ...
-4
votes
0answers
26 views

SQL TEST ASSISTANCE [on hold]

Disregard, i will come back with additional info
0
votes
1answer
22 views

GROUP BY with date ranges when values change but then change back

Consider a historical table where a value was changed and then changed back: value | date 3 | 1/1/2019 3 | 1/2/2019 3 | 1/3/2019 4 | 1/4/2019 4 | 1/5/2019 3 | 1/6/2019 3 | ...
0
votes
1answer
59 views

Without rewriting this query, is there a way to force the query optimizer to better estimate the amount of resources required, e.g. with query hints?

I know there's a couple of alternate ways to write this query, but I'm more curious if there's another approach that I could use to improve the performance by helping guide the optimizer in the right ...
1
vote
1answer
32 views

Restore plan cache and buffer pool after database restore?

When restoring a database, is there a way to restore the plan cache so it doesn't have to be re-created during production use? Similar question: is there a way to pro-actively restore the buffer ...
-1
votes
2answers
51 views

Is there a way to increase the speed of the following insert without having to create an unclusterd index being suggested by sql server? [on hold]

Below is a query that we are running that takes a while to run. Table 1 has about 400+ million records with 80 columns (this is a db warehouse table). The index being suggested is on the columnx with ...
2
votes
2answers
46 views

Permissions using a TYPE as a Table Valued Parameter

I'm developing a .Net App that calls an stored procedure that have a table valued parameter declared as a TYPE READONLY. First time I tried to call the SP I received the next error: Msg 229, Level ...
0
votes
1answer
27 views

Session ID in SQLCMD running multi-batch script

My script contains multiple GO keywords. I know that SQLCMD interprets GO as a batch delimiter, so the code is not run as a single block but instead each part between the delimiters (a batch) is run ...
0
votes
0answers
35 views

Recommended ways to export large query result to JSON or other text format [on hold]

I am tasked with exporting a settings query result to a text file so it can be imported on other machines. I have a working solution in a WPF project however the problem is that the results are 28K+. ...
2
votes
1answer
36 views

UPDATE column to MIN of a SELECT result set - what went wrong?

I've got the following scenario: I've got a table of assets with standard columns a table of hardware assets with extension columns a history table where all changes to an asset are stored with ...
3
votes
3answers
123 views

SQL log file 2TB is not enough, now what?

This is a hypothetical question, that flows from the answer by sepupic where they explain that 2TB is the physical limit for SQL log file. if you need it more than 2Tb you add the second log file. ...
2
votes
1answer
37 views

what could be preventing my transaction log autogrowth to be set to UNLIMITED?

I have been trying to set the autogrowth of one of my databases to unlimited but despite whatever I do it remains the same. First thing I have checked if something is holding any locks to my database:...
0
votes
1answer
47 views

Restructuring table - start with PK or not?

I am restructuring a bunch of tables in my database (in this case using a smaller data type for some columns - in most cases cutting those by ~50%, and they tend to be large columns). I've gone ...
0
votes
0answers
35 views

AG lag for some weird reason [on hold]

I am having issue with Availability Groups, the secondary server lags for some reason and after restart of primary and secondary for week or two it works fine but back to lagging. Our consultant told ...
-1
votes
0answers
47 views

Incorrect syntax near the keyword 'CASE' [on hold]

When I ran my procedure, it got created successfully without any error. But when I try to execute it, I am getting an error. Error in executing bspCDRExtract in bspDynamicCDRExtract. Details: ...
1
vote
1answer
26 views

Some timeout in query store but get all SP:Starting and SP:Completed in profiler

I have a report SP running. The user complaint report timeout sometimes. And I do get some canceled execution plan in my query store. But in my profiler tracing file, every SP:Starting has its SP:...
0
votes
0answers
33 views

Stored procedure execution duration and sending email to DBA if it run long

Does anyone has a T-SQL query which checks the stored procedure run duration and inform DBA team if it run long may be more than 1 min. Any help would be appreciated Thanks
0
votes
1answer
33 views

Create a user on database A with a procedure from database B (with EXECUTE AS)

I have 2 logins : LoginA: role dbcreator + CONNECT ANY DATABASE LoginB: role sysadmin I have 2 databases : dbA: everyone has all rights on it dbB: only LoginB has rights on it, LoginA is only the ...
0
votes
0answers
36 views

Does SQL server support an in-built mechanism to copy a row to a log or history table when it is updated?

Is there an in-built mechanism that copies a row to a log or another table when it is updated. Or is the only option to write a custom trigger on update.
0
votes
0answers
23 views

Issue with table naming policy with PBM - can't create it passed in the condition or not

I used PBM to create a condition to blocking create tables if nomenclature no haven't the prefix tbl: EXEC msdb.dbo.sp_syspolicy_update_condition @condition_id=24, @description=N'Necessário ...
1
vote
1answer
23 views

SQL Server Dynamic Group By

I want to create a de-dupe (remove duplicates) records (actually Guest Profiles) from a table (TableA). The user can opt for 5 criteria (Surname, Name, Email, PostCode,Phone). This means it can create ...
1
vote
1answer
35 views

Can the SQL Server optimizer use indexes to update auto created statistics?

Consider the scenario where auto created statistics are created on a column to satisfy a query. Afterwards; a nonclustered index is created on the column, with or without other key columns. Could ...
0
votes
1answer
25 views

SQL Query- field calculation based on date

I'm writing a query for sales targets per week, however I'm getting a little stuck on this. Targets are per month, per year, so for example, Brand A has a monthly target (changes each month) The ...
1
vote
0answers
41 views

SQL Server mixed mode authentication not being set

I have a local developer instance of SQL Server installed on my machine. I set the mixed mode authentication: Open SSMS Connect to localhost (windows auth) Right click localhost connection, right ...
0
votes
0answers
24 views

Unable to connect to SQL server instance ubuntu 18.04 via SSMS which worked peviously

I am running SQL server on ubuntu 18.04 which I was able to connect to successfully via SSMS. But I am not able to connect anymore. My first instinct was to check if the SQL server was running by ...
1
vote
0answers
8 views

How to “update” change_tracking setting to enable TRACK_COLUMNS_UPDATED

I have a table where I have enabled change tracking without TRACK_COLUMNS_UPDATED. I know this because if I go through SSMS's Generate scripts, I get the line: ALTER TABLE [dbo].[dt] ENABLE ...
-1
votes
1answer
26 views

insert a variable and table value as parameters to procedure [on hold]

CREATE PROCEDURE [dbo].[GetFruitName] ( @quantity int ) AS BEGIN SET NOCOUNT ON; INSERT INTO Table1(fname,fprice,quantity) SELECT f.name,f.price from ...
0
votes
1answer
31 views

Memory Settings for SQL Server, SSAS, and SSRS

I started a new job about 4 months back and was surprised to learn that there has NEVER been a SQL Server DBA here, just developers who don't really know what they're doing (like me). I am not a DBA ...
0
votes
2answers
20 views

Query returns repeating arrows when getting sum of a column grouped by some others columns

I have table Inventory and I want to get the sum(quantity) grouped by the columns gid, version, warehouse +----------+----------+-----------+----------+--------------+ | gid | version | ...
0
votes
1answer
16 views

About Apex SQL Refactor and SSMS keyboard shortcuts being broken [on hold]

Hi DBA Stack Exchange! Today I tried Apex SQL Refactor and right from the get go, once installed and integrated into my T-SQL dev tool of Choice (SSMS 18.3.1) I noticed that some Keyboard shortcuts ...
1
vote
0answers
39 views

Tempdb. There is no enough space on the disc encountered

Does anyone know what really happened here? All of the sudden database was not accessible. How to find transactions that cause tempdb filled up? Seems like it happened right after SQL profiler ...
1
vote
3answers
79 views

Filtered index is not used when variable in WHERE condition

Why MS SQL Server refuse using supporting filtered index in this scenario? -- demo data CREATE TABLE #Test ( ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Test_ID PRIMARY KEY ,Col1 NVARCHAR(36) ...
0
votes
0answers
38 views

Executing a stored procedure inside another stored procedure from a linked server

I'm having a problem and I don't know how to solve it, I have searched the web and found good advice but I can't work it out. This is the problem: I have a SQL Server instance running on my PC, and I ...
3
votes
0answers
10 views

How to identify writeable/partitioned views?

Assuming all the necessary prerequisite requirements are met, Sql Server will make partitioned views writable. How can I identify what views are in this state? A followup question would be- does the ...
0
votes
0answers
14 views

ALLOW_SNAPSHOT_ISOLATION ON is possible on Basic Availability Group?

Is it possible to enable ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT on a basic availability group? If yes, is there a specific procedure? Drop the database from the Availability Group ? ...
0
votes
1answer
27 views

How to check distinct set of column values against multiple sets

I am trying to group multiple rows; aggregate distinct values in a column (for the group); and then check if that aggregate set is a subset of some other set. I spent several hours researching and ...
1
vote
1answer
57 views

Indexing Strategy for the query performance

I have a question on indexing strategy as i am trying to do indexing for a database. I understand the basic indexing strategy which are: Clustered index on PrimaryKey Add Non Clustered index for ...
5
votes
2answers
53 views

Grouping Data Based on a Header Record

I have a spreadsheet I'm importing into the database raw, a SQL Server 2016 database. It has records in what is known as the "Lockbox format", used by banks apparently. It looks something like this ...
0
votes
1answer
32 views

Understanding window functions to deduplicate records while retaining true changes

I am close to figuring this out but I'm just stuck at a wall. I'm attempting to understand a post by Aaron Betrand and apply it to a situation I've encountered where I have a changes table that's ...
0
votes
0answers
32 views

DB Maintainance Job Fails Single User Mode

we have a stored proc where we rebuild indexes and also take Backup of filegroups.Following is part of the code. But this job is failing since few weeks for error message : Error : Executing the ...
0
votes
0answers
17 views

Error message when trying to attach Adventureworks database to SQL [on hold]

I receive an error message that reads "cannot attach database with the same name as an existing database. I inadvertently downloaded the adventureworks database more than once. I try to delete them ...
0
votes
0answers
27 views

Best way to store large number of Blobs

In our production database, we currently hold 1.75 million rows of blobs (on average these blobs are less than 1MB, so we have determined that blobs are the best way forward), which takes up just over ...
0
votes
0answers
16 views

Two query statament, two result set but need combine as Pivot

I have two table which record Monthly-Allowance and OT-Allowance which both table have Department column and Track column I need report that have all department but separate with Track Column 'DL','...
1
vote
1answer
52 views

Backup too big to restore

I have a backup which in file explorer is roughly 160GB. If I run select sum((size*8)/1024) From sys.database_files where type=0 I get a result of 169493, fine. When I try to restore this ...
1
vote
0answers
22 views

How to force SQL Studio not to use copy-only backups to build database restore script?

How to force SQL Studio not to use copy-only backups to build database restore script? I want to ommit copy-only backup (because it's not longer available) and force studio to use last full backup (I ...