All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

0
votes
1answer
47 views

solve high stolen memory

I've a SQL Server 2012 SP2 with max memory set to 13GB but there is 8GB of stolen memory (SQLServer:Memory Manager - Stolen Server Memory (KB)). I know stolen memory is memory taken from the ...
0
votes
0answers
14 views

Better way to design self-referencing tables with undefined number of recursions to use with linq

I am about to create a self-referencing table (two columns: ID and ParentID) to use with C# and linq, and this question makes me think. Is there a better way to design self-referencing tables with ...
0
votes
0answers
30 views

Ola's IndexOptimize fails with log_shipping_primary_databases select permission error

I am using Ola Hallengren's IndexOptimize maintenance solution for online defragmenting a database on a SQL 2014 Enterprise edition server. Many tables get updated through file loading which results ...
2
votes
1answer
41 views

Is there a way to determine locks that a given query might apply on SQL objects?

Consider a simple query, as simple as a select statement: select * from Teachers where Name like N'%John%' As soon as I start this query, it finishes. Thus, I get no chance to see sp_who2 to find ...
5
votes
1answer
65 views

Index creation leads to large transaction log growth

I have an approximately 1 billion row table. I then create an index as follows: CREATE NONCLUSTERED INDEX [IX_Index1] ON [dbo].[MyTable]( [CustomerID] ASC ) -- CustomerID is uniqueidentifier WITH ...
2
votes
0answers
38 views

Loads of parallel inserts (OLTP) into heap table - Operation stops every now and then for 2-3 seconds

We got an analytics database and as new events are coming in we are adding them into our tempdb. From there we have backend processors that pick up the events and merge them into the right tables, ...
0
votes
0answers
24 views

sql server cpu usage

First off all thanks for your time. I am trying to understand the CPU utilization of our server box. This is a sql 2008 r2 running Hyper-V my question is why only CPU's 0-6 are hitting hard. How can ...
0
votes
0answers
64 views

SQL Server Performance: SELECT…INTO vs INSERT…INTO

I read couple of articles on comparing performance of SELECT...INTO vs INSERT...INTO. Some people even exaggerate that the first one is 10X or 100X faster! :) I tested it by inserting 25 million ...
5
votes
1answer
82 views

Benefit or use-case for empty variable / parameter / temporary table / temporary procedure names?

I just discovered, through sheer brilliance accidentally, that SQL Server allows you to create variables, parameters, table variables, temporary tables (local and global), and temporary stored ...
4
votes
4answers
144 views

Weird WHERE Clause Behavior. Why does this return a row?

In SQL Server 2008, why does this return the row, even when I add a whitespace, or two, or more, to the end of the where clause? shouldn't zero records be found in the following example? WITH SRC AS ...
4
votes
2answers
647 views

Is it possible to rebuild a dropped index?

I'm studying for the 70-462 exam and one of the training resources I'm using gives a scenario where an ETL process is taking a long time to complete an insert into a heap table due to non clustered ...
1
vote
1answer
19 views

Change database login default schema [duplicate]

I have a problem. I have a Windows user [domain\user] on my instance that has access to some database. I want to change the default schema for that login. I can do this when I create some test login ...
0
votes
1answer
21 views

SQL SERVER Verifying mirror status from 2 weeks ago

How we can verify if the mirror was working fine two weeks ago ?? I was checking the sqlerror log, I'm not sure whether the mirroring sync status will be logged, if so, I don't have that log. because ...
2
votes
1answer
27 views

Geography data type and Upgrading from 2008 r2 to SQLServer 2014

I am getting a warning about a data type called geography. Our system has 1 column of data type geography. I am trying to figure out if the upgrade will fail or just if an app may have an issue. ...
6
votes
1answer
119 views

How do I move or redistribute FILESTREAM files between containers?

We have a FILESTREAM container with a few million files, and we believe it's the cause of our performance issues we're experiencing (abundant timeouts). According to this blog about FILESTREAM best ...
0
votes
0answers
9 views

My company changed windows domains and now I cannot access SQL Server Management Studio

I'm able to connect to the object but cannot access the databases (except for master) and cannot change permissions. This is just a local instance and I have backups of the data so I'm not concerned ...
1
vote
1answer
30 views

patching SQL Server with Availability Group and Secondary Replica

Scenario: SQL Server 2014 CU6. 1 AG with 2 databases (compatibility level 100). Failover Cluster with 3 nodes on Windows Server 2012 R2. Primary Availability Replica on node 1 and node 2. Secondary ...
1
vote
2answers
51 views

Trigger performance - UPDATE statement joining on INSERTED

I have a trigger (MS SQL Server) on TABLE_A that fires on an update that looks basically like the following (I've changed the names of the tables/trigger to simplify): CREATE TRIGGER ...
0
votes
0answers
45 views

how to find overlapping or duplicated indexes? when is it O.K. to keep them?

this is a script that does the following: create a database called "radha" for testing purposes, create the table ProductItemDetailsDenorm create the clustered primary key create ...
2
votes
0answers
99 views

Advice on how to improve this stored procedure

Edited: Added table column datatype I have 2 tables ReportItem id (int, primary key clustered index) report_item (nvarchar(100)) flow_name (nvarchar(50)) source (nvarchar(50)) Tech_Details id ...
1
vote
1answer
62 views

SQL Server specific file group

I have a database with three file groups, Primary, FG_ExtendedTables, and FG_ReportingTables. I need to create another database (production db let's say), and restore only specific file groups and ...
9
votes
5answers
1k views

How to hide/disable tables without dropping them to check redundancy?

I have to maintain and extend an old legacy system which contains webservice methods and database tables that are no longer used. Since I’m not entirely sure that the tables are really redundant, I’m ...
6
votes
3answers
135 views

Does alter column change datatype use tempdb version store?

I was running alter column from INT to BIGINT, my table size is 250 GB and after 1 hour I got the version store error because tempdb was full. As far as I know it only takes your memory pages and log ...
1
vote
1answer
35 views

DR Site availability if primary goes down

I have two SQL Servers at a production site (2 node WSFC) in an Availability Group, and one SQL Server at a DR site. If I add DR site node to existing WSFC to configure AOAG: If both servers at the ...
0
votes
1answer
26 views

MS SQL server issue - 'PRIMARY' filegroup is full

I got below error whenever my application tries to update/insert any data to database. Could not allocate space for object 'myDB' in database 'I 3 Stroke' because the 'PRIMARY' filegroup is full. ...
1
vote
1answer
27 views

Databases created with different collations - why?

We have a SQL Server 2014 instance, which houses many related databases. New ones are added via our script regularly. When our app is used to create the database - the databases are created with ...
1
vote
1answer
22 views

File share Witness or Disk Witness

I have 2 SQL Servers at production site Windows Server Failover Cluster and 1 SQL Server at DR site. I will add the DR node to existing Windows cluster and configure AOAG. Should I go for File share ...
0
votes
0answers
15 views

Optimal Number of Queries Per Batch for SQL Server

I am optimizing an application for a scenario where it will import an sql file and execute over 11 million create commands, which have have some expensive subqueries that are outside my control. This ...
0
votes
0answers
21 views

Example Database for tunning

I've been searching for so long and I've still have not found what I'm looking for ,is there any database or similar resource that people uses for tuning a database, or optimize queries?. I would be ...
3
votes
2answers
47 views

Fill in missing dates with data value from previous populated date for group

Picture help desk tickets that gets transfered between departments. We want to know what the department is at the end of the day for each ticket for each day that the ticket is open. The table ...
-1
votes
1answer
105 views

Comma Separated values in sql server [closed]

I have three tables with relationship. TableDetail ID(PK), TableNo BookingDet ID(PK), BookID(FK BookingMstr.BookID), TblID(FK TableDetail.ID) BookingMstr BookID(PK), Date I want to get table ...
0
votes
0answers
20 views

Cannot drop schema sql server

I'm trying to delete a schema but I am with the following message: Lock request time out period exceeded. (Microsoft SQL Server, Erro: 1222) Does anyone know how I can fix?
3
votes
1answer
31 views

Reverting Compatibility Mode 120 to 100

We recently upgraded the compatibility level (CL) of our production databases from 100 to 120. Within a couple of days, users started complaining of increased wait times and general slowness. It was ...
3
votes
1answer
62 views

Delete User View named the same as system view

I am exporting a database to Azure and have found a user view created in a previous version of MSSQL (Currently running 2008) that is name INFORMATION_SCHEMA.TABLE_CONSTRAINTS which obviously should ...
0
votes
1answer
44 views

Open .mdf and .ldf files in Microsoft SQL Server without attach [closed]

I want to open .mdf and .ldf files in Microsoft SQL Server without attach. Are the following steps correct? Add new database with MYDBNAME name. Copy the files MYDBNAME.mdf / MYDBNAME_log.ldf to ...
1
vote
1answer
35 views

SQL Server - Create Additional Database File for New Schema

I'm trying to create a change script for some upcoming changes. We're adding a new schema to the database and our client wants the new schema to make use of a new database file. How do I, in T-SQL, ...
0
votes
0answers
26 views

How to audit user that reinitialized replication in sql server

How can we track who reinitialized our replication. Is there a way to check this user or login? Thanks in advance
1
vote
1answer
64 views

Repair with data loss did not fix index corruption. What now?

A few months ago we migrated our SQL Server 2014 database to a virtual environment. Soon afterwards, corruption started showing up in indexes, causing all sorts of trouble. I've made repairs using ...
0
votes
0answers
77 views

SQL query too slow to fetch single row of data

we have this SP, running extremely slow to fetch a single row of data from tables within 100K rows, which seems not that big. It takes onn avg 15-20 mins to complete. We have tried to remove all ...
1
vote
2answers
61 views

Get the Text of the currently running SQL?

This is something I am just interested in knowing if it is possible. Could I write an SQL query that could return the text of itself as a variable? Where I might use this would be on things like ...
3
votes
1answer
45 views

Wrong estimated number of rows using TOP condition

I guess this is a very basic question but I can't get my head around it.. I have perfect statistics in place for a column but while using a TOP condition the estimated number of rows are always a ...
0
votes
0answers
25 views

Secondary database log file in alwayson is very big [duplicate]

I have an alwayson environment (AlwaysOn AG). Database [UsersDb] in the slave server, His log file is very big . How can I reduce his size(shrink)?
3
votes
1answer
42 views

Converting SQL SERVER Procedure to POSTGRESQL

I have procedure in SQL Server to print all players of given country. I try to convert it into plpgsql, but I get errors. SQL Server Procedure CREATE PROCEDURE kraj_pilkarze @kraj varchar(30) AS ...
3
votes
0answers
55 views

Why would Stolen Memory counter be at a constant high? [duplicate]

I'm trying to understand memory stats a lot better and have been troubleshooting an issue with one of our SharePoint DB servers for a while now. Low PLE, frequent lazy writes, slow performance etc I ...
1
vote
1answer
29 views

Database Design for Sports Stats

I am designing tables for tracking statistics for multiple sports like basketball and football. So my question now is - would it make more sense to design a table when both data for basketball and ...
1
vote
0answers
29 views

Resolve “stuck” system process of dropping indexes

I am re-vamping some indexes, and have decided to drop 3 on a certain table. The process has been running for almost 2 hours now. The table does have quite a few million rows, but I still feel that is ...
11
votes
2answers
300 views

How to write a query in SQL Server to find nearest values

Let's say that I have the following integer values in a table 32 11 15 123 55 54 23 43 44 44 56 23 OK, the list can go on; it doesn't matter. Now I want to query this table and I want to return a ...
0
votes
0answers
37 views

INSERT Trigger not inserting values

I have a table called tbemissoes. When there is an insert, I need to execute this procedure : create procedure exec pr_replicaRemessa --49 , 2016 @RemessaNumero int, @RemessaAno int AS Begin ...
0
votes
2answers
35 views

Prevent duplicate record counts in one to many query

I'm trying to get a count of ethnicity for a teachers students. My first table contains the student number and ethnicity. The second table contains teacher id's, student numbers, course numbers, and ...
0
votes
1answer
52 views

Extracting data from SQL Server linked server

I am using MS SQL Server 2012, and I am trying to extract data from a Linked Server database. In SQL Server management studio, I can see the linked server. I can browse the database on the server. I ...