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

learn more… | top users | synonyms (2)

1
vote
1answer
732 views

SQL Server 2012: Security_error_ring_buffer_recorded: ImpersonateSecurityContext

a couple servers I manage are recording a lot of events in the system_health XE session. security_error_ring_buffer_recorded api_name ImpersonateSecurityContext calling_api_name ...
1
vote
0answers
68 views

How to take advantage of multiple spatial indices?

All, I'm building a view which uses a spatial intersect (STIntersect in MSSQL 2012) between points (student addresses with address information) and polygons (school boundary polygons with school ...
3
votes
1answer
116 views

Putting a condition related to a table in JOIN is better?

I have seen this a lot that people are putting table related condition on JOIN clause. See this example, SELECT i.Name FROM sys.TABLES AS tbl INNER JOIN sys.indexes AS i ON (i.index_id > 0 ...
22
votes
3answers
2k views

At what point does one database per client become unfeasible?

For one of our systems, we have sensitive client data and store each client's data in a separate database. We have about 10-15 clients for that system. However, we're developing a new system that ...
2
votes
0answers
210 views

Can I recover a single table from a SQL Server 2012 database backup [duplicate]

I have accidentally dropped a specific table and I don’t want to restore whole database (it is huge) but instead I want to recover only that table from a backup? What would be most simplest ...
4
votes
1answer
263 views

A clarification of MERGE best practices

I just wanted to ask a clarification about something on the MSDN from the "Optimizing MERGE Statement Performance" page. I am working with a data warehouse that takes in records from many different ...
2
votes
1answer
57 views

How can I automatically have a node become primary in an Availability Group?

In my environment, I have Db01 and Db02 setup with SQL 2012 AlwaysOn. Db01 is the preferred master as that is where all of the backup jobs reside. Typically, the only time that Db01 is unavailable is ...
2
votes
0answers
77 views

SQL Server 2012 High availability Groups for OLTP and Reporting environments

I would like to seek your advice in designing a high availability architecture at my current work place. We are moving to SQL Server 2012 platform and we currently have a set of OLTP databases ...
2
votes
0answers
31 views

TFS 2010 and SSMS2012

i was trying to use TFS 2010 in SSMS 2012 and, even if i installed Team Foundation Server MSSCCI Provider 2012 64-bit and Visual Studio 2010 Team Explorer, there's no way to see TFS under the Current ...
0
votes
1answer
292 views

Reporting Services TempDB lock blocking report generation

Two days ago one of our reports in Reporting Services started to take much longer to generate. Its generation time had increased from 4 seconds to 2 minutes. Investigation found: The Stored ...
1
vote
0answers
73 views

Efficient way to Add NON-CLUSTERED Index?

I have the following table, CREATE TABLE [dbo].[Products]( [ID] [int] IDENTITY(1,1) NOT NULL, [RetailerID] [int] NOT NULL, [Name] [nvarchar](255) NOT NULL, [Price] [float] NULL, ...
1
vote
1answer
3k views

How can I enable distributed transactions for a linked server?

I have a SQL Server 2012 instance with a linked server pointing to a db2 database on an IBM Iseries system using the System i Access ODBC Driver. I'm trying to enable distributed transactions. I ...
1
vote
1answer
961 views

Fetch large data from Sql Server and process in chunks

I am working on an API which would need to process large dataset. The query to select records is complex and takes time to complete. I cannot use the pagination using ROW_NUMBER as not all records ...
7
votes
2answers
370 views

SQL Server performance: PREEMPTIVE_OS_DELETESECURITYCONTEXT dominant wait type

I received a call yesterday from a customer who was complaining about high CPU usage on their SQL Server. We're using SQL Server 2012 64 bit SE. Server is running Windows Server 2008 R2 Standard, 2.20 ...
1
vote
1answer
444 views

copy/sync only data between 2 database with non identical table structure

I have the following issue and was wondering if someone knows if this is possible. We have a MS SQL Server 2012 Instance with 2 databases database A and database B. On Database A the data from 6 ...
3
votes
2answers
2k views

How to create a read-only server role on SQL Server 2012?

I am granting the "View any database" permission when creating a new server role, but realized this permission only allows the user to view the system databases. I am trying to create a server role ...
1
vote
1answer
136 views

Is indexing binary?

The scenario: When a user updates some information - the correct row must be found and updated. I'm debating between two options: Let the server search through all of the rows (-slow). But be ...
14
votes
2answers
525 views

Lock CREATE TABLE

In another application I was struck by bad design: multiple threads execute an EnsureDatabaseSchemaExists() method concurrently, which looks basically like this: IF NOT EXISTS (SELECT * FROM ...
2
votes
1answer
47 views

Update only the oldest entry in a table with foreign key criteria

I'm working on a forum website. What I am trying to do is updating the thread Title and Content (first post), which resides in different tables - "Threads" and "Posts". "Threads" "Posts" I'm ...
-4
votes
1answer
122 views

SQL Server 2012 Not matching Join Query

I have to generate a query output for not matching records. Assuming that i have two tables Table1 and Table2. Both Tables have the columns as DrugName. No Id column. Just the value column. I ...
1
vote
1answer
40 views

SQL Server generate self-signed SSL certs performance concern?

Read this msdn article and doesn't seem to say if it keeps using the same self-signed cert after one is automatically generated. If SQL Server generates a new SSL cert upon every connection (anyone ...
0
votes
2answers
98 views

SQL Server Date Conversion

On applying the where condition : WHERE DateFilled BETWEEN '02/01/2014' AND '02/28/2014' My syntax is not detecting the values which are filled on 02/28/2014. If I write the syntax as WHERE ...
2
votes
1answer
92 views

Zombie Sql Server DB on Secondary Replica is both in and out of an Availability Group

I have a database in Sql Server 2012 that is on the secondary replica of an availability group. I removed the database from the AG db list on the primary and the db is currently in the Restoring ...
3
votes
2answers
282 views

SQL Server Memory Issues

We have a SQL server that appears to be leaking memory. Memory consumption linearly increases over time until the server is completely out of memory, causing major performance issues. The SQL server ...
0
votes
1answer
98 views

How does the Distributor know you have seeded/initialized your Subscriber using a backup?

Im building my first transactional replication database for our Sharepoint team. The Publisher database is fairly large 330GB+ Because the Publisher database is in a different data center than the ...
3
votes
1answer
163 views

Frequent Delete & Insert, impact on performance

In my application, we have very few updates. Most of the data gets deleted and inserted. My question is, how this will impact the performance? We are using SQL Server 2012. Table statistics (will ...
2
votes
2answers
115 views

Estimated vs Actual Number of Rows

I have 5 tables that contain data split by quarter each table has a similar structure to the following: ID_Column bigint PK, EventType int, DateStamp datetime PK CustomerId int, ARG_1 nvarchar(32), ...
0
votes
1answer
104 views

SQL Server: multiple failover cluster instances with disjoint storage

This is for SQL Server 2012. I currently have two datacenters that have separate SANs. I'm using failover cluster instances (FCIs) for server redundancy, and then AlwaysOn Availability Groups (AGs) ...
2
votes
3answers
3k views

How to view an encrypted view or stored procedure

I am working on a third party database. When I try to view the definition of a view by right clicking, CREATE TO and then to NEW QUERY EDIT WINDOW, I am getting an error: This property may not ...
0
votes
2answers
260 views

why stored procedure performance is very high compared to plain select query

I have a table with more than 2 million rows, with needed indexes. When I run a simple query it takes so much time so I decide to stop executing it; while when I wrap the query in a stored procedure, ...
0
votes
3answers
80 views

How to script data in deterministic order

When I script data in my database in SSMS Tasks->Generate Scripts and then select data-only it turns out that the insert statements are generated in different order each time when even a smallest ...
0
votes
2answers
155 views

Recommendations for a SQL Server 2014 Database Administrators Course [closed]

I am after some personal recommendations for a SQL Server 2012 Database Administrators Course with exams. Exam 70-461: Querying Microsoft SQL Server 2014 Exam 70-462: Administering Microsoft SQL ...
0
votes
1answer
50 views

Rebuild Index in system database

I use SQL Server and have multiple indexes in system database whit index fragmentation 30% or more. I don't know that SQL server manage system database index automatically or I want to manage them. ...
4
votes
1answer
98 views

If user owns a database fixed role are they the admin of the role?

I have SQL Server 2012. When I create a user in "Owned schemas" screen one of the option is fixed database role. If I own a fixed database role: What permissions am I granted? I am the admin of ...
2
votes
0answers
132 views

Significant PLE Drop

This morning at 4am we experienced a huge drop in Page Life Expectancy on a new sql server 2012 box. No jobs (backup etc) ran at this time and traffic was very light compared to say 20:00. Current ...
3
votes
4answers
739 views

indexing on multiple nvarchar(max) columns

I have multiple nvarchar(max) columns in my DB named ShipperName,ConsigneeName, ProdDesc,BillOFLading. What I need to do is to have UNIQUE INDEX for the combination of these columns. As I am failed ...
8
votes
1answer
294 views

Re-run a specific actual query plan

I have captured an actual query plan for a specific query. After this I've changed a few things around (including updating the statistics) and re-ran that specific query. Now the actual query plan ...
0
votes
1answer
42 views

What is the MS_PerfDashboard schema in msdb?

We just migrated an old reporting server (running on 2005) to 2012 and some of the database performance monitoring jobs have started failing. I looked into them and found that they use a couple of ...
1
vote
1answer
313 views

How do you restore a DB to Multi-User mode when deadlocked by a sys process

I have a database that is stuck in Single User mode after a failed restore process. I have checked the sysprocesses table and none of the processes have spid > 50. They are all sa processes. Using the ...
1
vote
1answer
67 views

Partitioning a table state

So far I always used date ranges to partition my tables, however now I like to do it by state or a selected list of cities where all others that are not explicitly listed in an "other" partition, is ...
2
votes
0answers
42 views

Setting up triggers based on exposed columns in a view's source table

I have a table where I want to do a AFTER UPDATE trigger if one of the columns modified was in a specific view CREATE VIEW [dbo].[iv_EmployeesView] AS SELECT EMPLOYEE_GUID AS EmployeeGuid, ...
2
votes
2answers
1k views

How to enable a SQL Server 2012 login to create, alter and drop the created databases?

I'm trying to assign a proper GRANT permission to CI login, that will be able to: Create test DB Alter it Drop this test DB (and only it) But it appears that SQL Server 2012 GRANT CREATE ANY ...
1
vote
1answer
192 views

Performance of dynamic SQL Server reporting queries?

I have to build an application that allows the end user to select what columns they want in their report. For this I build a dynamic T-SQL stored procedure. However, there are up to 60 columns ...
0
votes
0answers
51 views

TempDB causing high IO writes - dependent on server's max memory setting [duplicate]

I have a mirrored SQL Server 2012 database. The first server has a hugh tempdb write IO load when the maximum memory limit is set above 8192 mb. These 8192 where the original setting for both the ...
3
votes
1answer
241 views

Tempdb data file disappearing

I recently moved my Temp database files onto a separate drive (I had two data files and one log file). However, after restarting the server, my second data file disappeared both from the file list on ...
5
votes
2answers
503 views

Capture very expensive queries

I have an Sql servers of 2008/2012. Some users run very expensive queries, such as updating 1G rows in one transaction or cross join several very big tables, and it causes the server no response from ...
2
votes
1answer
364 views

User Mapped to a certificate in SQL

I need to create a user ,who can log on to a contained DB.In this context what is the benefit of using a user mapped to a certificate?
1
vote
1answer
328 views

SQL Server 2008 mirroring migration to SQL Server 2012 always on

Quick question, request your input. I am migrating a SQL Server 2008 R2 instance with database mirroring to SQL Server 2012 with always on. Should I : Restore the SQL Server 2008 R2 backup to SQL ...
3
votes
1answer
78 views

Recursive Updates on large table

I am desperately seeking a solution for a problem, which i will try to fully describe hoping that a somebody can come up with a clever solution. The metadata : At the moment i have the following ...
0
votes
0answers
66 views

SQL Server 2012 Replication Initialisation Fails — Table keeps being dropped

I have two servers - one located in Australia (Distributor / Publisher), and a second in the UK (Subscriber) both on Amazon's cloud. I have attempted to configure replication as both pull and push ...