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

learn more… | top users | synonyms (3)

0
votes
2answers
554 views

Migrate sharepoint database from SQL Server 2012 express to SQL Server 2012 Standard

My sharepoint is using SQL Server 2012 R2 Express as a database Server, and I already have reached the limit of space (10GB). In the other hand I have a SQL Server 2012 Standard with proper license. ...
0
votes
1answer
35 views

Optimal SQL Server 2012 Disk Configuration

I will be installing SQL Server 2012 on a new Dell T430 server containing four (4) physical 15K SAS 6Gbps disk drives using a PERC H730P RAID Controller and RAID 1. Since this will be the first time ...
0
votes
3answers
54 views

Query for total downtime of site using datestamps and status columns

what want to find the total downtime for a site. I'm thinking I can subtract the 'down' DateStamp from the following 'up' DateStamp. then add the totals together for all downtime. I want to get a ...
0
votes
0answers
25 views

Sync two SQL Server databases

So I've got a hosting package from hostgator with an MS SQL database and a local SQL Express 2012 database running. I'm trying to investigate syncing the two together, two-way syncing, and I'm unable ...
2
votes
1answer
116 views

Work queue with complex select and long processing: Ensuring concurrency

I have a queue table from which worker processes claim records, one at a time. It is crucial that no two workers claim same item to process. Normally this kind of thing is done with select top (1) ...
3
votes
1answer
2k 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 ...
2
votes
1answer
38 views

Create Database and LCK_M_S

We are experiencing issues when creating a database in the SSMS GUI in that sometimes it times out complaining that it could not get an exclusive lock on the Model database. Other times the database ...
3
votes
1answer
46 views

Why is the secondary selective index not used when the where clause filters on `value()`?

Setup: create table dbo.T ( ID int identity primary key, XMLDoc xml not null ); insert into dbo.T(XMLDoc) select ( select N.Number for xml path(''), type ) from ( ...
1
vote
2answers
411 views

After Restore Log_reuse_wait_desc of Replication

I'm wondering if anybody has ran into this issue before and has any suggestions on what's causing it/how to fix it. We are restoring a database into lower Dev environments and after the restore the ...
0
votes
1answer
338 views

Mirror / replicate only 1 table of a database

I need a good way to replicate a single table in SQL Server 2012 between different servers and different databases. I tried replica which works just fine, but there might be a much better / easier way ...
5
votes
2answers
69 views

Does SA account need to be called “SA” or can I rename it safely?

We have some applications on our network (from before my time) that use SA account in their SQL Server connection string. It's hard-coded in the source code, and for whatever reason, we can't change ...
2
votes
0answers
45 views

MEMORYCLERK_SQLOPTIMIZER in SQL 2012 Memory usage extremely high and causes server to crash

I have been having issues with our SQL server for a while now. It seems to run out of memory every few days and when I look at the memory dump, the MEMORYCLERK_SQLOPTIMIZER seem to take over memory ...
1
vote
1answer
58 views

Full backup at 1am, not started by my maintenace plan

I set up a complete set of Maintenance Plan. Full backup of all databases at 3AM. Differential backup at 6h30, 12h30, 18h30 and 00h30. Log backup at each hour, at 45 minutes (1h45, 2h45...) In my ...
1
vote
3answers
720 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 ...
2
votes
1answer
34 views

Difference between clustered index and partitioning column

As I understand partitioning, the column that you partition on is how things are divided into partitions. But does the partitioning column have anything to do with order on the disk? (Aside from ...
-1
votes
0answers
31 views

SQL Server Cluster hardware and OS upgrade question

We have a 2-node and a 3-node SQL Server Cluster running on Windows Server 2008. It is time to update the hardware and the OS. Can you point us to resources on how to migrate to new hardware running ...
3
votes
1answer
26 views

Partially contained databases and Windows Logins - best practices?

In versions of SQL Server prior to 2012, in order for anyone to log in to a database using Windows credentials, you first had to create a server-level login, them map that login to a user for each ...
1
vote
1answer
168 views

Multi-server administration - force poll on target fails, server needs to be registered locally without FQDN

I'm experimenting with multi=server administration on my SQL lab. Running SQL 2012 Enterprise SP1 on Windows 2008 R2 on a domain. I've created a domain account for my sql server agent user on each ...
1
vote
2answers
514 views

Updating large table (~6.5 million rows) & indexes

I'm trying to update a table with about 6.5 million rows in a reasonable amount of time, but I'm having some issues. We're adding a new column to an existing table and then setting a value for all ...
4
votes
1answer
63 views

SQL Server 2012 Crashes and service doesn't restart

We've been having an issue with our SQL server lately. It seems that the server will run out of memory and then crash. For some reason, the sql service wouldn't restart. I would have to go and ...
4
votes
2answers
198 views

How to configure SSRS (SQL Server 2012) to run on a different instance, but same server as SharePoint

I have an instance of SQL Server 2012 running on Windows 2008R2 along side another instance of SQL Server 2012 that is running SharePoint (I think it is SharePoint 2010). When I try to access the ...
3
votes
4answers
2k views

SQL Server Concise List Of Trace Flags

I did my 5 minute research and have not come up with a concise list, or even a list really except for Trace Flags (2012) which is a bit lacking. Looking at this query which uses 8691, I was wondering ...
2
votes
1answer
15 views

Retreive a Customized List with input data

I have a database that has about 223 tables and I need to locate the name of the constraint. My request is to add a parameter that is the name of the constraint and you retrieve a list that shows the ...
0
votes
1answer
69 views

CLR Assemblies and SQL Server Availability Groups

I have a SQL 2012 Availability group. On that I also have a database with a .Net assembly that is using PERMISSION_SET = UNSAFE. CREATE ASSEMBLY drawing FROM ...
0
votes
1answer
55 views

SQL Server Data File/Log File poor performance

I've been asked to look at a database/application server that's "performing slow". No one could really tell me what was performing slow (except for the database backups) so this has been a discovery ...
0
votes
1answer
48 views

fn_dblog not showing up deleted table data

I unintentionally deleted 4 rows from a table. I tried to fetch back the deleted data using a stored procedure (got from internet). However, I was able to see my data in the result of stored ...
0
votes
0answers
32 views

Can't connect to an Active Node in an Active/Passive cluster

While being logged into an active node SQL Server 2012 node, I tried connecting to it from within the SQL Server Management Studio but was unable to do so. I tried the fully qualified name and the IP, ...
22
votes
3answers
3k views

Are there disadvantages to contained databases?

SQL Server 2012 introduced the concept of "contained" databases, where everything (well, mostly everything) the database needs is contained within the database itself. This offers big advantages when ...
2
votes
1answer
820 views

AlwaysOn Availability Groups go to Resolving status

We have a two-node cluster set up running SQL Server 2012 on Windows Server 2012 R2. The base cluster consists of only those two nodes. The quorum is set up as node + disk majority, with a shared ...
8
votes
1answer
284 views

What is the different between compression on the PK vs. on the Table?

Data compression can be set on the table: CREATE TABLE dbo.SomeTable( SomeId [bigint] NOT NULL, OtherId [bigint] NOT NULL, IsActive [bit] NOT NULL, CONSTRAINT [PK_Some] PRIMARY KEY ...
4
votes
1answer
378 views

Oldest unsent transaction alert for Availability Group

In SQL Server 2008 when using database mirroring, it was possible to set up alerts when the oldest unsent transaction exceeded a certain threshold. We could check the state of any database mirroring ...
1
vote
1answer
386 views

Transactional Replication Snapshots causing serious locking after 2012 upgrade

I am experiencing serious locking when adding new articles to an existing transactional replication publication, which is hanging the website. This is the first time I have attempted this since I ...
1
vote
1answer
152 views

Migrate Management Data Warehouse to a New Instance

Is there a defined procedure to follow for moving a SQL Server 2012 management data warehouse to a new server\instance? We intend to setup an MDW on a test sql server instance first, but will then ...
2
votes
1answer
1k views

Inline edit SQL Server database rows from Visual Studio

I'm pretty sure Microsoft have pulled one of the most useful features for performing quick edits on a SQL Server Database within the Visual Studio IDE. It seems to have only affected SQL 2012 ...
12
votes
0answers
339 views

What is the cause of this problem with CONVERT()?

Consider the following two statements: PRINT CONVERT(NUMERIC(38, 0), 0x0100000001, 0); PRINT CONVERT(NUMERIC(38, 0), 0x0100010001, 0); Both statements return -1; isn't that incorrect since the ...
2
votes
2answers
352 views

Detached database file permissions

I have a script that detaches databases for archival when they meet certain criteria. The script is executed by SSIS under the local Administrator account by the Ghost of DBA Past. Per design it (by ...
0
votes
1answer
191 views

Distributor latency - slow sp_MSget_repl_commands performance

One of our publishers is subscribed to by three subscribers, each subscriber is hosted on its own physical server. Two of those subscribers are 2008R2 and the third 2012. The 2012 subscriber is ...
2
votes
1answer
44 views

Controlling how tempdb activity is split across drives (tempdb on both solid state and spinning drives)

I'm seeing the following tempdb I/O behavior over a one hour period: There is a fair amount of disk I/O generated by multiple DW workloads executing on the machine, some of which cannot fit in the ...
0
votes
0answers
18 views

2012 Always on (AG only) Where should the Windows Cluster SIT?

2012 Always On and the Windows Cluster, shouldn't the Windows Cluster be on a separate server like the Domain controller/BackupDomainController? With Windows clustering Services on each node ...
0
votes
1answer
34 views

Using * to enforce the need to update both tables together

I am a Java Programmer and definitely NOT a DBA. I am just an "accidental helper" to a DBA as we try to troubleshot an inherited VB.NET application situation. The guy that wrote this MS SQL got fired ...
0
votes
1answer
49 views

Do I need to rebuild indexes when upgrading a DB from 2008 to 2012?

I can't find anything on BOL that indicates I need to rebuild indexes or statistics after the migration and compatibility level upgrade of a database. But I have read a few articles/answers around the ...
2
votes
2answers
49 views

Why Does Partition Range Right Skip Deletes/Inserts?

I have two example Queries (shown below). Both (drop and) create a database and a partitioned table in it. The table has several partitions, with data in each of them (including the last, unbounded, ...
1
vote
0answers
62 views

INSERT INTO with TABLOCK taking longer than without TABLOCK [closed]

I'm doing something like below to ensure the insert is minimally logged: insert into AnotherDatabase.dbo.HeapTable with (Tablock) ( <column fields ) select <column fields> from ...
0
votes
1answer
378 views

Report Builder cannot publish to report server

This is currently in a cloud lab environment. We're not allowed to join any AD domains. We have a linux server (RHEL 6) running MySQL 5.3 and a Windows SQL Server 2012 on Win 2k8 R2. Our laptop is ...
5
votes
1answer
120 views

Is there any way to monitor execution of xp_cmdshell in SQL Server 2012?

I've been tasked with assessing the security risk involved with allowing a third party vendor to debug their queries remotely within SSMS. This means that the user's account must be in the sysadmin ...
5
votes
0answers
77 views

How to terminate a suspended SQL Server transaction waiting for IO_COMPLETION?

We have a transaction that has been running for more than 5 hours. We are running out of disk space. The session has been killed but it is still waiting for IO_COMPLETION. Actually the wait_type just ...
3
votes
1answer
66 views

Decode and aggregate a BitMask in t-sql

I have a table that contains a bitmask field that stores permissions, where each bit denotes whether a particular permission is granted or not. Here's a simplified example: DECLARE @T TABLE (id ...
2
votes
1answer
2k 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 ...
5
votes
2answers
5k views

Understanding two NICs requirement for SQL fail over cluster

I am trying to make an SQL Server 2012 failover cluster. I have two DB machines. I understand that both machines need 2 NICs each. In my organization the IP scheme assigned is something like ...
0
votes
1answer
48 views

Check for duplicates in column and determine which row to keep

I've found myself running in to this on multiple occasions now where I got a query and would need too further refine it. Where one column might contain duplicates and I need to evaluate which row to ...