0
votes
2answers
29 views

Management Studio System.OutOfMemoryException

I'm using Microsoft SQL Server 2012 and trying to run a simple query against it within Management Studio. I'm getting the following error: An error occurred while executing batch. Error message ...
0
votes
1answer
41 views

Simplest way to edit single entry in XML column?

As I understand MS SQL Server Management Studio doesn't allow directly editing/replacing entries in XML columns via GUI (copy/paste doesn't work etc.). What is the easiest option to replace a single ...
1
vote
2answers
165 views

How to get Identity Column to jump ahead (by say 1000) on SQL Server 2012

I am using SQL Server 2012 and I would like to keep my pre-existing data but to cause the ID (identity) column to skip by 2000 rows. Why is this happening? I have a scenario where I may have ...
1
vote
2answers
35 views

Changing compatibility level of database causes performance improvement of stored procedure

We have a Web Application in a Windows Server 2012 Datacenter (IIS 8), running under .NET Framework 4.0, which connects to another Windows Server 2012 Datacenter with SQL Server 2012. My Database has ...
0
votes
1answer
38 views

Why do my backup policies fail when a database's guest user is disabled?

I'm attempting to implement Policy Based Management on a SQL Server 2012 instance also acting as a Central Management Server using the Enterprise Policy Management Framework. The job running the ...
1
vote
0answers
58 views

SQL select top X rows having sum limit

I am using SQL Server 2012, I have a table with a format such as : MyOrders : ID -> PK int Cost -> float Category -> int I want to get the following : Input : category (lets say 2). ...
3
votes
1answer
74 views

Does sp_indexoption cause index rebuilds?

I want to switch AllowRowLocks=ON on a number of indexes. Some of those indexes are on large tables, and I don't really want to rebuild or reorganize the index. It is not clear from the MSDN article ...
1
vote
0answers
28 views

How to add SQL Server in networks list?

After changing my PC name and workgroup, I am no longer able to connect to SQL Server. Currently in my servers list I have only Home & localhost. Anybody know how to add server to networks list? ...
2
votes
0answers
43 views

AlwaysOn FCI + HA Setup Question

I am planning to create FCI + HA solution in the same datacenter, but running into some problems. I'd really appreciate if someone can provide some guidance. Here is the current configuration (All ...
0
votes
2answers
80 views

Stored Procedure null parameter within where clause

I want to use a parameter within where clause only if it's value is provided by strongly typed dataset, this is what I am trying at the moment, I get right results when I provide parameter3 and no ...
3
votes
1answer
63 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 ...
2
votes
1answer
39 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
34 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
30 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 ...
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
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 ...
4
votes
2answers
79 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 ...
0
votes
1answer
42 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 ...
0
votes
1answer
38 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
2answers
46 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
65 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
70 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
55 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
62 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 ...
2
votes
2answers
66 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
67 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 ...
2
votes
2answers
82 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 ...
0
votes
0answers
23 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
55 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: ...
5
votes
3answers
184 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
103 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
97 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 ...
1
vote
1answer
78 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
28 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 ...
5
votes
2answers
110 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
86 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
66 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 ...
2
votes
2answers
66 views

Help reading deadlock XML

Even with reading the Bart Duncan blog about reading deadlocks I still struggle with this. I have included the XML from a particular deadlock. <?xml version="1.0" encoding="utf-8" ?> - ...
-1
votes
2answers
66 views

SQL Server 2012 Browser Service doesn't appear [closed]

I've installed SQL Server 2012 and Management Studio Express. I attached the database that belongs my previous Project in Visual Studio. But I am having SqlException Error , I think the reason of the ...
0
votes
1answer
56 views

Page compression not working?

I have created a table where I have around 100 partitions (per month), all of them are configured to use page compression. After that I'm inserting data to this table month by month. I now would ...
0
votes
0answers
21 views

Error in 'Master Database Restore' in SQL Server 2012 (Because of difference in SQL Server Build)

I have a backup of a master database that was taken from SQL Server 2012 11.00.2218 build. Now I have a new SQL Server installation with build 11.00.2100. I tried restoring the master database after ...
1
vote
1answer
23 views

Workload Groups in SQL Server 2012

I have been looking for an answer about "Workload Groups" in SQL Server? What exactly it is Workload Groups? Could you explicit a real world situation when this term is used? Thanks in advance ...
0
votes
1answer
66 views

SQL Server slows down on one expensive query

I have a Sql Server 2012 with lot of CPU and memory. When I run a query with a bad plan the server slow down and all other query, even of other databases, become very slow. When I run the query: - ...
5
votes
0answers
76 views

SQL Server AlwaysOn log_send_rate

On all our AlwaysOn setups, running Windows 2012 and SQL Server 2012 in virtual machines and on bare-metal, I find that the log_send_rate in sys.dm_hadr_database_replica_states is consistently ...
0
votes
1answer
68 views

What should I consider before I upgrade my SQL Server memory? [closed]

I want to upgrade the memory in my SQL Server. What should I check first? Version: SQL Server 2012 64-bit Standard edition
2
votes
3answers
183 views

Collecting Wait Stats

We currently use a monitoring tool which shows us our top wait stats either by number of waiting tasks or total wait time. Below are the wait stats by number of tasks waiting and also wait time per ...
0
votes
1answer
31 views

SQL Server 2012 Vs PDW [closed]

We are migrating to SQL server 2012 to PDW. Any SQL script for Comparing the data between SQL server 2012 and Parallel dataware house or any tools to compare.
2
votes
1answer
60 views

SQL Server 2012: NUMA node shown as offline without special affinity settings

My main production server has four NUMA nodes with two SQL Server 2012 instances on it. One of the instances has the CPU affinity set to use only one NUMA node, and sys.dm_os_nodes reflects that ...
2
votes
0answers
64 views

Stored Procedure slow after weekend

When I come to work on monday some stored procedures take several minutes to execute and STATISTICS IO shows a lot of PHYSICAL READS and READ AHEADS going on. However, after running the procedures ...
2
votes
1answer
100 views

Can I replicate a database from SQL Server 2012 to SQL Server 2014 (with 2014 distributor)?

I am planning to have database replication running from SQL Server 2012 (publisher) to SQL Server 2014 (both distributor and subscriber) -- pull replication. Has anyone done this type of ...