SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.

learn more… | top users | synonyms (3)

8
votes
3answers
4k views

Change data capture - how to know who made the change?

Tracking who made the change identified by CDC. Along the lines of my datetime hack I tried the same approach by adding suser_sname as a new field with default value on the cdc change track table. ...
0
votes
1answer
513 views

Installation of SQL Server 2012 does not contain Virtual Interface Adapter?

I have installed the entire Enterprise edition of SQL Server 2012 and have noticed that I do not have the Virtual Interface Adapter. What is the cause of this?
1
vote
2answers
535 views

Query Performance Tuning on a huge table

I have the following table Transactions. The table looks like this: CREATE TABLE [dbo].[Transactions]( [TransactionID] [bigint] IDENTITY(1,10) NOT NULL, [PlayerID] [bigint] NOT NULL, ...
3
votes
1answer
3k views

SQL Server Create Access Denied even after adding MSSQLSERVER user permissions

I am aware that SQL Server instances need write access not for my user account (which is in fact irrelevant to that service's context) but for the user account or security principal under which the ...
1
vote
1answer
61 views

when can AG primary replica be a different host to WFC primary?

To set up an Availability Group with two servers, the servers both participate in a Windows Failover Cluster. I've seen cases after certain failover modes where the primary IP address in the cluster ...
3
votes
1answer
2k views

Error: 9245, Severity: 16, State: 1. / During the last time interval XXX query notification errors were suppressed

I've got this sequence of error messages showing up regularly in my server error log: Error: 9245, Severity: 16, State: 1. During the last time interval 257 query notification errors were ...
5
votes
3answers
8k views

Removing Databases in Recovery Pending Status after AG group was removed

I have removed a High Availability Group, disabled the option in sql configuration manager and restarted and I have also removed the Failover cluster components from windows and restarted. ...
2
votes
1answer
477 views

Inside SQLOS – Scheduling

Using SQL Server 2012 Enterprise Edition SP1 running on Windows Server 2008 R2 SP1 For each logical processor core on SQLOS has a scheduler assigned to it. Those scheduler can be viewed as status ...
8
votes
1answer
2k views

Warning in query plan “Cardinality Estimate”

create table T(ID int identity primary key) insert into T default values insert into T default values go select cast(ID as varchar(10)) as ID from T where ID = 1 The query above has a warning in ...
2
votes
1answer
7k views

Active/active SQL Server Clustering in Installation Center (setup.exe)

I like to know which option to choose in the setup.exe in SQL Server 2012/2008? What I have here is Windows Active/Active Cluster setup with one quorum and one msdtc configured. I installed my first ...
5
votes
1answer
1k views

Enable TDE for existing AlwaysOn databases

I need to do a little benchmarking of AlwaysOn, both with and without TDE enabled. All the instructions I see cover adding encrypted databases to an availability group, but I see no mention of ...
2
votes
0answers
329 views

dropping and adding subscription only picking up new article on some subscribers

I have used the following script to drop aand re-add a table from a subscription so I could move replace the ntext fields with nvarchar(max) columns. the script worked fine, but when I ran the ...
1
vote
4answers
6k views

Cannot remove Mirroring / nor restore or delete database:Error 3456 (SQL Server 2012)

I have a database that was a mirrored partner and went into suspended state. (Mirror Server was shut down). I took the principal database out of mirroring using SSMS. But now I am unable to restore ...
0
votes
0answers
4k views

0x84BB0001 Error when installing SQL Server 2012. Could not create a handshake [closed]

I have hopelessly attempted to install SQL server 2012 onto my 64 bit Windows 8 machine. The database engine always fails to install with error 0x84BB0001. Native reporting also fails, I reckon from ...
2
votes
0answers
274 views

tempdb objects creator wanted, bufferpool issue caused by tempdb objects

I have a number of tables in tempdb named like #A0030B77 #A01D908C #A06D1FCB #A0F72FB0 #A1614404 #A1EB53E9 #A205D8FE #A255683D How (if) can I get spid of the process which created them? These ...
2
votes
1answer
285 views

How can I rename a table, but also all FK etc. references to that table?

I would like to e.g. rename my Applicant table to ApplicantProfile, as applicant is an abstract entity that has a profile. The profile is not the applicant. Simply renaming the table is hopelessly ...
2
votes
1answer
84 views

How to create an object and then alter it

I'm using sql server 2012: I would like to script out creation of an object(if it doesn't exist first), and then alter it. I'm trying to avoid the usual drop/create as it would impact stats. It ...
-2
votes
1answer
439 views

SSIS SQL Server 2012 cluster Availibility Group [closed]

We have a three server cluster using AlwaysOn technology, and have created an Availability Group for the SSISDB catalog. How would you change the code to only execute on the primary server?
14
votes
1answer
15k views

Why does SQL Server 2012 Express use 9.5GB of RAM on my server?

I am building an application in which I plan to embed SQL Server 2012 Express as the primary datastore. When testing on my development machine (Win7-32 with 3GB RAM), I never observed the ...
2
votes
1answer
2k views

Decryption not working when SQL Server database copied to new server

I am copying a database from an old server to a new server. The database has an encrypted column. From my searches I see that I need to backup the master key from the old server and restore it in ...
3
votes
0answers
602 views

SqlDependency permissions

The db is SQL Server 2012. All data tables are in the dbo schema. All data access is done via stored procedures in a different schema (user rt). I want to add query notifications for some tables, so I ...
2
votes
1answer
2k views

Why do I have to drop a snapshot in SQL Server 2012 to restore a database?

I never had to drop a snapshot with SQL Server 2008, but now when I try to restore in SQL Server 2012, I get the error: System.Data.SqlClient.SqlError: The operation cannot be performed on a ...
2
votes
2answers
1k views

Snapshot not picking up new articles in SQL Server 2012 transaction replication when added through script

I am trying to add new articles to an existing publication in SQL Server 2012. I am adding the article to the publication with the syntax --add the table into the correct publication sp_addarticle ...
4
votes
1answer
296 views

Do locks on readonly secondary databases propagate to the Read/Write Database

I'm thinking about using SQL Server Availability Groups in SQL Server 2012 to offload reporting on our main database to a second read only server that is kept in sync through the availability group. ...
1
vote
0answers
112 views

Page Header DB Frag ID Property

I am using SQL Server version: Microsoft SQL Server 2012 - 11.0.2100.60 (X64). I created a single table containing one and only one integer-valued column. I then added one and only one row to the ...
3
votes
1answer
11k views

Cannot connect remotely to sql server 2012 instance using SSMS

I have installed sql server 2012 Express on a local network machine, then added a new login: "sqlUser" mapped to "database1", and configured the instance to accept the remote access with both Windows ...
1
vote
0answers
893 views

SQL Server keeps crashing [closed]

I have had SQL Server 2012 on my server for about 6 months. Over the last few weeks, a problem has developed, whereby the SQL Server stops functioning, and any attempt to connect or query returns the ...
2
votes
0answers
140 views

How to use a foreach loop with project deployment model

I have a SSIS project that is configured to project deployment model, within this I have two connections one for a flat file and one for a sql db. Both connections are in project mode. Now I want to ...
5
votes
1answer
1k views

sql_slovak_cp1250_ci_as vs Slovak_CI_AS

Can someone explain to me what is the difference between collation sql_slovak_cp1250_ci_as vs Slovak_CI_AS? I have a db set to sql_slovak_cp1250_ci_as and shared hosting supports only Slovak_CI_AS. ...
5
votes
3answers
3k views

SQL Server 2012 database backup successfully report but no backup file

I have created maintenance plan in SQL Server 2012. And every day, the maintenance plan should backup the database. There is no database backup file when I look in the folder where the backups must ...
2
votes
1answer
236 views

Availability Groups and Partition Swapping

Is it possible to use SQL 2012 Availability Groups over two machines and perform partition swapping on the primary machine ?
2
votes
0answers
361 views

Best Practice to update columnstore indexed tables

I have a table with aprox. 22 Bn rows, for each month I have made one partition that and each partition has two data files. I created the columnstore index on this table and the performance ...
6
votes
1answer
166 views

Ok to drop indexes on FK's if they have no stats in the DMVS

I have used the well known query below from Kevin Kline to check for unused indexes. Several indexes created on Foreign keys returns no read stats, only writes. Are you 100% safe to drop these ...
5
votes
4answers
9k views

Moving rows from one table to another

I am moving records from one database to another, as a part of archiving process. I want to copy the rows to destination table and then delete the same rows from the source table. My question is, ...
4
votes
3answers
252 views

Easiest Way to Move 100 Databases

I need to move about 150 databases from one server to another server. SQL Server 2008 Web Edition SQL Server 2012 Preview (in a different datacenter - East Coast Azure) I was planning on moving ...
4
votes
2answers
2k views

Color syntax printing in SSMS 2012

It seems the SSMS of Sql server 2012 cannot print the SQL statements with syntax color anymore. Is it a bug? Does it have any workaround?
1
vote
1answer
160 views

Is it possible to use Native Clients from previous releases to connect SQL Server 2012?

Is it possible to use SQL Server Native Clients from previous SQL Server releases (e.g. SQL Server 2008 R2 Natice Client) to connect SQL Server 2012 database?
4
votes
1answer
717 views

SQL Server Plan Guide vs Plan Freezing

I am bit confused with terminology as official Microsoft SQL Server documentation including BOL refers to plan guide the one you create using sp_create_plan_guide proc. Does plan freezing is same ...
10
votes
1answer
6k views

Upgrade SQL Server 2005 to SQL Server 2012

I have a test environment set up with the following: Virtual Machine (Hyper-V) Windows Server 2008 R2 SP1(x64-bit) Windows SQL Server 2005 Developer Edition SP4 (x64-bit)(default instance name) 1 ...
2
votes
1answer
613 views

SQL Server table appears locked for no reason

My database was running just fine this morning, up until it wasn't. This query: SELECT TOP (1) * FROM tblTask; comes back in no time at all, while this query: SELECT * FROM tblTask this_ with ...
27
votes
2answers
5k views

Optimising plans with XML readers

Executing the query from here to pull the deadlock events out of the default extended events session SELECT CAST ( REPLACE ( REPLACE ( XEventData.XEvent.value ...
2
votes
2answers
2k views

Accessing a network share via a SQL Server 2012 Availability Group

If you have a SQL Server Cluster called CLS, with machines CLS1 and CLS2, accessing: \\CLS\network\path is equivalent to accessing either \\CLS1\network\path or \\CLS2\network\path depending ...
6
votes
1answer
1k views

Recovery time effects of Availability Groups

We did different kinds of basic Tests and AlwaysOn passed in many tests. We finally did a heavy write test for AlwaysOn and it gave surprising results. The actual Test details are here, the goal is ...
8
votes
1answer
4k views

Timeout expired error while using copy database wizard

Microsoft made me believe that SQL Server 2012 Copy Database Wizard is the most optimal way of copying a SQL Server 2000 database to SQL Server 2012. After struggling a few hours, I was able to get ...
5
votes
2answers
6k views

Procedure cache (plan cache) size

I'm developing an application with many, dinamycally-assembled, complicated SQL queries. I'd like to keep the query plans in the procedure cache for as long as it is possible. To prevent discarding ...
1
vote
1answer
330 views

2012 Transactional replication not delivering new articles

I have added a new article to an existing push publication with the following code sp_addarticle @publication='statics', article='FlightBaggageMarkup_tbl',source_object='FlightBaggageMarkup_tbl' ...
8
votes
1answer
5k views

How do I fix SQL Server assembly optimization errors and memory exhaustion?

The .NET Runtime Optimization Service (mscorsvw) keeps consuming all my workstation's memory since I installed SQL Server 2012 Service Pack 1. It consumes about 1.6 GB of memory before exhausting all ...
7
votes
1answer
1k views

What is the migration/deployment path from a LocalDB solution to Enterprise instance?

Given the extant definition for SQL Server 2012's LocalDB as SqlLocalDB is a local, low-overhead database engine of SQL Server 2012 (and future versions) that allows developers to focus on ...
5
votes
1answer
727 views

Expanding a partitioned table

I have a table that holds four years of data, where for each month I have a partition that is based on a filegroup with two files. Unexpectedly, I received eight more years of data and prepared the ...
5
votes
5answers
22k views

Cannot start SqlLocalDB instance with my Windows account

I'm the administrator and simply run the command: sqllocaldb start v11.0 Result: Start of LocalDB instance "v11.0" failed because of the following error: Error occurred during LocalDB instance ...