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)

5
votes
2answers
165 views

Running query against DMVs for Query Stats and Execution Plans joined to sys.databases

I originally posted this as a different question in Twitter #sqlhelp, but wanted to post it here differently. I'm trying to create a job that tracks worst performing statements for various statistics ...
11
votes
2answers
139 views

Recursive CTE to find Total for all children

Here is an assembly tree that I want to search using a recursive T-SQL Query (presumably CTE) with the expected results below. I want to know the total amount per assembly given any part. Meaning if ...
0
votes
1answer
25 views

HADR procedures

We are thinking of a SQL Server 2014 + Windows Server 2012 configuration for a HADR scenario. My questions are: on a HA scenario, how does the primary server get updated once it is again available ...
1
vote
0answers
14 views

SSRS: UserName value in Subscriptions table, ExtensionSettings column

SSRS subscription to a Windows File Share. Is there a way to query for all the "credentials used to access the file share" in plain text? It appears that the UserName value in the ExtensionSettings ...
1
vote
2answers
64 views

Update table using values from another table in SQL Server

I have 2 table in my database. Table #1 ------------------------------------------------------------------------- | name | family | phone | email | gender | phone2 | address | birthdate | ...
0
votes
0answers
30 views

Distributed transaction debug

I have the classic error: Msg 7391, Level 16, State 2 When I try to execute a transaction over a linked server. I have the same identical configuration in 2 environment, production and test. It ...
0
votes
1answer
34 views

Row of database is unreacheble

Here is the problem: I have a table called "flags" with 54 columns: PVKEY PEOPLEKEY ADDRESSKEY FLAGTYPE FLG1 FLG2 FLG3 FLG4 FLG5 FLG6 FLG7 FLG8 FLG9 FLG10 FLG11 ...
2
votes
1answer
52 views

Select a table's current identity from another database

How can I get the current identity of a table using OPENQUERY in SQL Server? How to select the current identity on the table from another database?
1
vote
0answers
19 views

Views dependent on other views [duplicate]

Is it bad practice to have views dependent on other views? If one dependency doesn't affect performance, at what level of dependency will it start showing negative effects? In the picture is an ...
1
vote
1answer
12 views

How to find the 'Instance ID' for PowerShell Invoke-TroubleshootingPack?

I was doing a PowerShell training, that said use the PowerShell help to run a "Networking troubleshooting pack" I found, ran and read Get-Help Get-TroubleshootingPack -full Get-Help ...
3
votes
1answer
156 views

How to lower HEAP Fragmentation in SQL Server?

i recently found out that one heap table had more than 70% fragmentation. So i decided to do a ALTER TABLE dbo.myTable REBUILD Funny enough, afterwards i had 20% fragmentation. There was no write ...
0
votes
0answers
42 views

Speed Up Garbage collection in FileStream process

In my current process, we have two production site SQL Servers 2014 (Sync), and one DR site SQL Server 2014 (ASync) in one WSFC. I will be hosting 5-6 databases for AOAG set up, out of which one of ...
-1
votes
0answers
22 views

How to see if i excluded databases from backup with TDP for MSSQL?

I have this in my dsm.opt exclude "\...\*test*\...\*" so I want to see if it's really excluded before the schedule kicks off. I know there is a command for regular tsm client to see this but I'm ...
0
votes
0answers
27 views

Error: 18456, Severity: 14, State: 11. Login-based server access validation failed with an infrastructure error

We are getting the above error on a SQL 2012 server running Windows Server 2008R2 when connecting using an application and a service account. The server also has Config Manager database and GAP policy ...
1
vote
1answer
37 views

SSIS: To run the package outside of SQL Server Data Tools you must install expression task of integration services or higher

Me and my colleague using the same version of .Net framework (4.5) and installed SSDT in visual studio 2013. He prepared a package (not so complex) and can able to execute the same from SSDT without ...
0
votes
1answer
46 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
28 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
64 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
61 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
141 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
646 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 ...
6
votes
1answer
116 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
2answers
49 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
43 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
54 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
134 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 ...
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. ...
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
45 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
103 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
60 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
75 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 ...