This tag is for SQL Server 2012, major build version 11.00.xxxx.

learn more… | top users | synonyms (3)

3
votes
1answer
4k views

FlushCache messages appearing in log at specific times

We've been having a lot of database performance issues lately, and I've been trying to see if I can figure out why. We don't have a DBA (I'm a software developer) so I'm sort of just winging it, and ...
3
votes
1answer
412 views

Failover Cluster Diagnostic Logs

I know there must be a simple answer to this question, but I cannot find it anywhere. Our SQL 2012 FCI have SQLDIAG .XEL files in their MSSQL\LOG folder. In some instances, these files are often ...
1
vote
1answer
1k views

Linked Server and MSDTC on cluster

I'm trying to set up a linked server between two instances we have on our network. I've done this many times before without an issue, but this time I repeatedly get the following error: OLE DB ...
1
vote
0answers
95 views

Is it best practice to include partition keys within foreign keys when referencing partitioned tables?

The large tables in our database are partitioned by an id in order to separate data and speed up IO. The table of the data is partitioned by the partition key, but the primary key is only on an ...
1
vote
0answers
33 views

Does the order of columns in a non-clustered ColumnStore index matter? [duplicate]

I'm reading about creating a non-clustered ColumnStore index. I noticed that when I modify an already created non-clustered ColumnStore in SQL Server Management Studio I have an option to change the ...
0
votes
0answers
108 views

SQL SERVER 2012 Express - Can't change Thesaurus Location

I have a requirement for an application to change the location where SQL SERVER looks for the Global Thesaurus file. According to the official documentation: "You can change the location and names ...
2
votes
0answers
28 views

How to add a missing database to an AlwaysOn Availability Group [duplicate]

Setup: Microsoft SQL Server 2012, and we have 3 production servers(prod1, prod2, prod3) with AlwaysOn. We recently added a new database and the new database was added to our Availability Group(named ...
2
votes
2answers
3k views

SQL Server 2012 Can't log in with machine account

We're migrating an IIS/SQL Server site from 2008 to 2012, and I'm having a problem with authentication. We use Windows Authentication to authenticate as DOMAIN\COMPUTER$, but this isn't working, ...
0
votes
0answers
43 views

Is it possible for a SELECT-only stored procedure to deadlock on itself?

Environment is Microsoft SQL Server 2012. Is it possible for a stored procedure consisting exclusively of SELECT statements to deadlock on itself? That is the report I am getting from a vendor for ...
2
votes
1answer
28 views

Obtaining the next partition number

For a partitioned table, how do I determine via T-SQL what is the next partition number that will be used? For example, the switch-in today went into partition number 50. I want to build the new ...
1
vote
0answers
795 views

Querying LDAP multi-value attributes using Openquery

I'm using SQL Server 2012 Express to query an LDAP linked server like this: SELECT * FROM OPENQUERY (MYLINKEDSERVER, 'SELECT * FROM ''LDAP://myserver:389/o=root'' where telephonenumber = ...
0
votes
0answers
66 views

Can't execute CreateAll.tt template in SQL Server Express

I have a CreateAll.tt template in a Visual Studio project. All the scripts I try to execute are correct - the template is tested on a different machine with SQL Server 2012 Developer Edition and it ...
4
votes
2answers
345 views

Cannot remove SQL Server 2012

I was in the process of installing SQL Server 2012 x64 Express Management Studio and encountered one heck of an issue. I was most of the way through the installation process when my computer locked ...
1
vote
1answer
1k views

Change code editor variable highlighting colour

Within the SSMS Code Editor (for SQL Server 2012) I have this code written within the editor, that has a Dark theme applied. When you click on a variable, SSMS highlights all the other occurrences of ...
1
vote
1answer
837 views

SSAS : An error occurred when instantiating a metadata object

Aside from installing SSAS, I have no experience using it. Our developers are saying that it is no longer possible to create a new aggregation, whatever the cube. The error occurs when hitting ...
0
votes
1answer
739 views

How to get SQL Server filetable path locator for subfolder by its literal path

I have a filetable which have several subtables. For example FileTable ->subtable1 ->->subtable1.1 ->->subtable1.2 ->subtable2 ->subtable3 etc How to get SQL Server ...
1
vote
1answer
6k views

Shrink Transaction Log While Using AlwaysOn Availability Group

We are using AlwaysOn Availability Group feature of SQL Server 2012. Regular full database backups and transaction log backups are done every day on the secondary database. I have read here doing the ...
0
votes
1answer
92 views

Can anyone recommend a beginner DBA course in London [closed]

My organisation is in need of a SQL Server DBA and as the companies only "SQL Guy" my boss has suggested me to take SQL Server DBA course. My general T-SQL knowledge is fairly good, I can write ...
2
votes
2answers
147 views

SQL Server: Apply Custom Server Role afterwards for already existing databases

I needed a custom SQL Server role which has readonly access to all databases, regardless of whether a database is existing or is newly created. The role is not a problem. The problem is that the ...
0
votes
1answer
48 views

Select rows other than first selected row in a table

My table looks like id ledgerID Amount Debit or credit TransactionType -------------------------------------------------------------- 1 1 500 dr ...
1
vote
1answer
1k views

Backup/restore of a database that has a filestream

I have a SQL DB to which I have recently added Filestream data. Now we have a daily backup/restore to a QA server, and since I added the Filestream data, this has started failing. So I modified the ...
1
vote
1answer
56 views

PowerShell 'get-ClusterQuorum' output is not showing 'QuorumType' column

PowerShell cmdlet 'get-ClusterQuorum' output is not showing "QuorumType" column. Has anybody seen this before? Thanks
1
vote
2answers
227 views

What happens when Core Cluster Group is failed over to another node? Will it take the whole cluster down? Single point of failure?

"Cluster group" fail-over will cause cluster go offline. Really? Single point of failure? Am I missing something? What happens when Core Cluster resources (Cluster Group) are failed over from one node ...
0
votes
2answers
106 views

How secure is a linked server in SQL Server

I am in a situation in which I should doubt everyone (even my developer team and database admin) so I want to Log all the logons made to my server. I have two SQL Servers, say A and B. I want to log ...
0
votes
1answer
2k views

Script that alters all stored procedure on the database

I have a database with thousands of stored procedures and I need to alter hundreds of them. In order to do it faster I thought of a way to write a script that runs over all stored procedures and adds ...
0
votes
2answers
71 views

Order by for 2 row result causes 800% performance hit

The following query, run against a table of about 6 mil rows takes 25 seconds. When I remove the final order by, it runs in 3 seconds. There are no indexes on the table (it's an intermediate SSIS ETL ...
2
votes
0answers
110 views

Why does Recursive CTE estimate just 1 row?

Given two cascading, self-contained (no real tables) recursive CTE's: create view NumberSequence_0_100_View as with NumberSequence as ( select 0 as Number union all select Number + 1 ...
1
vote
1answer
137 views

Windows SQL Server Express 2012 and LDAP external server integration

I have a new installation of Windows Server 2012 with SQL Server Express 2012 with the management tools. I'm planning to create there a simple synchronization that will make queries twice per day to ...
3
votes
2answers
1k views

Should I check for existence of temp tables in Stored Procedures?

Is there any edge case in which it would be recommended to explicitly check for ,drop and create temp tables at the start of a Stored Procedure instead of just creating them? Similarly, is there a ...
3
votes
1answer
181 views

Microsoft SQL Server Connection Coloring

I've been using the Redgate Query Tab Coloring feature (under SQL Prompt's Experimental Features) and I really like it. Somehow I've never known until today that this feature is built in to SSMS for ...
3
votes
2answers
2k views

Filtered index not chosen, and rejected with hint

I'm trying to understand why a non-clustered index is not available for a given query when filtered. The relevant part of my (large) query is this: ) results JOIN BE_Insurance ins ON results.PayorId ...
1
vote
1answer
39 views

Combine 2 tables

I am wanting to combine 2 tables. until recently the column names were different , and one had fewer columns than the other, however it was deemed unnecessary to have them as two tables. I have ...
3
votes
1answer
75 views

AlwaysOn Secondary Replica DB-size

I just noticed something peculiar. We're using AlwaysOn availability in one of our environments with a read-only secondary replica. However, checking the .mdf / .ldf files for the secondary replica, ...
1
vote
1answer
60 views

add a column to an existing table

I'm wanting the best way to add a column to an existing table in my database. I'm using SQL Server 2012. I don't want the data that is in the table to be affected, I just want to add a new column ...
0
votes
1answer
89 views

change column name

I'm needing to update a few column names in some tables I have in my database. I'm using SQL Server 2012. I'm aware of using the RENAME ability: ALTER TABLE table_name RENAME COLUMN old_name to ...
0
votes
0answers
78 views

Setup for Availability Group with Analysis Services

We currently have one server with the Database Engine, SSAS, SSRS and SSIS all running on it. We are moving to a two server Availability Group setup. Currently I am not too worried about resources ...
1
vote
0answers
2k views

Is it possible to use a temp table in a data flow source?

I have a data flow in a 2012 SSIS package where I'm trying to use a temp table as the data source. I'm using an OLE DB data source with a SQL Command Data access mode. My code looks like this: ...
1
vote
3answers
610 views

check if tables has same columns

I have a Database that creates day tables and some of the tables don't have the same number of columns and in some cases the columns aren't the same. What I'm needing to do is consolidate all the ...
-1
votes
1answer
93 views

Transferring Data from SQL Server to SQL Server Express? [duplicate]

How do I transfer a database from a physical server running SQL Server 2012 to another server running SQL Server 2008 Express? The database was created by sa under SQL Server 2012. Thank you.
5
votes
3answers
244 views

Rebuilding an entire Microsoft SQL Server Database

Is there a suggested way to rebuild an entire SQL Server database from another database in SQL Server 2012 SP2? Let me explain... we have a database that we are trying to enable partial containment ...
1
vote
3answers
211 views

How to separate Stored Procedures (i.e. all the business logic) from Client Database so that it only contains client data?

Scenario: Our Application Database (in SQL Server 2012) contains entire business logic in the Stored Procedures (SPs). Everytime we have to publish the DB to the client, it unnecessarily results in ...
3
votes
1answer
1k views

Execute T-SQL Only If AlwaysOn Database Is Primary

Little background on the system. It is SQL Server 2012 AlwaysOn Availability Group with 1 primary and 1 secondary. I have been following this article (SSIS with AlwaysOn) to make SSIS work with ...
2
votes
1answer
203 views

Table design for child tables with multiple many-to-many relationships

We are working on designing tables for a new Networking application, and have been having some problems with finding the best design for the top level tables. Network is the top level table. Under ...
1
vote
1answer
99 views

Query Plan Different When Using Compiled Values

I have pulled out an execution plan from the plan cache and from the XML pulled out the compiled values. Then I have run the query in SSMS using the compiled values and the query plans are different ...
0
votes
1answer
195 views

How can I restore ##MS_SSISServerCleanupJobLogin##?

At some point the SSIS cleanup job login (##MS_SSISServerCleanupJobLogin##) got deleted from my sql server (2012). I'm trying to upgrade to SP2 and I'm getting an error message I believe is related to ...
0
votes
0answers
42 views

Inserting INTO XML Column shows nothing at all

I am trying to populate xml in table by, Declare @docy xml = N'<elements>..... 6000 character file </elements>' SET IDENTITY_INSERT TableName ON INSERT TableName(Id, ID_A, ID_B, Xml) ...
2
votes
1answer
163 views

What is FT BATCH CMPLETE in the cmd column for sysprocesses?

I was trying to figure out what was causing high cpu usage in our sql server and found two processes that were consuming high CPU. When I queried sysprocesses to see what the spid was doing, it had ...
5
votes
2answers
435 views

SQL Server: Create New GUID Values for Existing Index

I have an existing table with about 6 million rows. The relevant columns are: ID int not null PK Key uniqueidentifier not null Read/write on this table by Key lookup is probably something like 100 ...
4
votes
1answer
164 views

Incorrect result when you execute a query that uses WITH RECOMPILE option in SQL Server 2012 [closed]

I just read the Brent Ozar Weekly Links email (July 28th Edition), and noticed the "Kendra's Link" talking about "Fix: Incorrect result when you execute a query that uses WITH RECOMPILE option in SQL ...
4
votes
2answers
409 views

Sql Server Management Studio login prompt will never forget other sql user than SA

When I login to a database, using some other user than SA, that username will stick the next time I login. If I then input the username SA and password and logs in, the next time I open the login ...