This tag is specifically for SQL Server 2012
1
vote
1answer
117 views
How can I change SQL Agent Log file to use unicode format?
I've created a database with tables that heavily leverages nvarchar for most of the columns. One of the challenges I'm running into is that when I go to view the SQL Server Agent Log files, I get the ...
2
votes
0answers
105 views
SQL71561 Error on Schema compare after initial import from database
I have created a new sql server project with SSDT 2012, then I went to import an existing database and it created all my scripts. After that I did a schema compare between the project and the database ...
3
votes
3answers
2k views
Can SQL Server Management Studio Express be installed standalone?
I was wondering if the SQL Server Management Studio Express (mainly for Microsoft SQL Server) be installed on a standalone machine (without any of the SQL Services and stuff) to connect to a remote ...
2
votes
0answers
100 views
TFS 2012 - Database Project - User with NO LOGIN map to Windows - best practices
I have a SQL Server Database Project (.NET 4.5) in TFS 2012. As a DBA, i'm looking for a mechanism whereby the developers can create 'CREATE USER' sql scripts and assign their permissions within the ...
9
votes
2answers
217 views
Best way to defrag/compact a database for archival purposes
We've got an SQL Server instance that's used for email archiving (courtesy of a 3rd party archiving package). Every so often, the software is rolled over to a new empty database. We've done this ...
3
votes
1answer
390 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
65 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
170 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,
...
1
vote
0answers
303 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
0answers
31 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 ...
2
votes
1answer
170 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 ...
1
vote
2answers
494 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
119 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 ...
6
votes
1answer
218 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
509 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 ...
4
votes
1answer
120 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 ...
1
vote
0answers
44 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
245 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
532 views
0x84BB0001 Error when installing SQL Server 2012. Could not create a handshake
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
104 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
83 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
48 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 ...
0
votes
1answer
94 views
SSIS SQL Server 2012 cluster Availibility Group
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?
2
votes
1answer
159 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
119 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
126 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 ...
1
vote
1answer
122 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 ...
1
vote
1answer
78 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
49 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 ...
2
votes
1answer
619 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
113 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
75 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 ...
3
votes
1answer
109 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. ...
4
votes
2answers
280 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
68 views
Always On and Partition Swapping
Is it possible to use SQL 2012 Always On over two machines and perform partition swapping on the primary machine ?
1
vote
0answers
88 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
99 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 ...
1
vote
3answers
470 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, ...
3
votes
1answer
106 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 ...
0
votes
1answer
121 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?
0
votes
1answer
41 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?
2
votes
1answer
102 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 ...
6
votes
1answer
720 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 ...
1
vote
1answer
146 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 ...
13
votes
2answers
569 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
195 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
189 views
Recovery time effects of AlwaysOn
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 ...
6
votes
1answer
467 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 ...
3
votes
2answers
287 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
86 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'
...
