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

learn more… | top users | synonyms (3)

1
vote
0answers
34 views

What's the deal with transactions in a stored procedure called by an SSRS report?

Consider the following: sp_a contains an sp_executesql call to exec sp_b sp_b begins and commits a transaction, then immediately logs @@TRANCOUNT. When I call sp_a manually from within SSMS, the ...
6
votes
1answer
115 views

Why would a procedure sometimes hang executing SELECT…INTO #Temp2 FROM #Temp1 JOIN LocalTable…?

We have several stored procedures, called by jobs, that follow a specific pattern (designed to minimize blocking). Some of these procs tend to hang on occasion, running up the CPU, tempdb allocations, ...
3
votes
1answer
178 views

How many rows can a SQL Server 2012 table hold, and when should I partition it?

Say a table stores granular data about some event. It has the date of the event, a type dimension with around 30K types, and a category dimension with around 100 categories, plus some numeric facts. ...
11
votes
1answer
827 views

Why is adding a NOT NULL column with a default constraint instantaneous?

CREATE TABLE TestTab (ID INT IDENTITY(1,1), st nvarchar(100)) INSERT INTO TestTab (st) values ('a') INSERT INTO TestTab (st) values ('b') INSERT INTO TestTab (st) values ('c') INSERT INTO TestTab (st)...
1
vote
1answer
220 views

SQL Server 2012 Cluster Service pack 3 fail to failover node

Roughly a year ago, i had this problem with an 2008R2 cluster. I asked about it here, but i didn't grab enough logs in the process. And now i have the same problem on my 2012 failover cluster. So i'm ...
3
votes
2answers
51 views

How to get the size of a spatial index?

I want to see the size of a spacial index. I am usually using one of the following code blocks to get the indexes sizes of a given table, but none of them is working for a spatial index. DECLARE @...
1
vote
0answers
73 views

Improving the performance of procedures showing warnings for implicit conversions

We have recently migrated from SQL Server 2008 R2 to SQL Server 2012 Standard Edition. We and witnessing poor performance related to quite a number of stored procedures running against a database that ...
3
votes
2answers
188 views

Measure compilation time of stored procedure

I need to measure how much time it takes for the MSSQL-2012 to compile a stored procedure. Is it possible?
0
votes
1answer
144 views

Apply SP3 on SQL Server 2012 AlwaysOn AAGs

If Always-On is configured, while applying Service Pack, should I: Directly move SQL Server Instance (Secondary Replica) to make it passive and apply Service Pack Or First move Availability Group ...
1
vote
1answer
44 views

Always-On Configuration Clarifications

Do we have to Install stand-alone instances of SQL Server 2012 or Failover-Cluster SQL Server Instances while configuring Always-On? I am asking this question because I saw few configurations using ...
0
votes
1answer
77 views

Setting up log shipping with an availability group

In my production environment I have a SQL Server 2012 AlwaysOn AG. The Server1 there is database "MessagesDB" and in an AG to secondary Server2 (in synchronous mode). I want to setup log shipping ...
0
votes
0answers
30 views

SSIS buffer size and table with many columns

I have an Oracle view with around 10 million rows, each row has about 130 columns, I wrote a SSIS 2012 package which uploads the data of this view into a SQL Server table. The SSIS package fails each ...
3
votes
1answer
87 views

Add a total quantity column for groups of rows

I have an Items table and an Inventory table. An Item can belong to multiple Inventory records. I'm trying to return a list of ALL Inventory records but with their quantity in a new column. For ...
2
votes
1answer
61 views

SSMS: possible to script 'file per object' into categorised folders like in object explorer?

e.g. if you run the 'generate scripts' function (by right clicking on a db in object explorer) to script out an entire database, it creates all the files in one folder and annoyingly names them as [...
7
votes
2answers
235 views

Finite State Machines in SQL

I'd like some input on an issue I'm having. We have a section of code that we repeat throughout our stored procedures, and every time it takes quite some time to process, and when combined the number ...
3
votes
1answer
75 views

Login issue in Logshipping

I have configured log shipping in my primary serverA and secondary serverB. Everything works fine until I bring the standby database online. Users are not able to login to the secondary serverB ...
0
votes
1answer
40 views

Error when trying to start Mirroring

Thanks for looking at this, and thank you in advance for any answers! I am attempting to start a mirroring session, but I am getting an Error 1418, which basically states that the TCP port doesn't ...
1
vote
1answer
25 views

Changing replication snapshot folder, require re-snapshot?

We have SQL Server 2012 SP2 running on Windows Server 2012. We need to change the snapshot folder for all (including default) transaction replication publications. I have read that this means the ...
1
vote
1answer
16 views

execute as login for nonexistent login successful

On Sharepoint database server, we found 18456 state 11: infrastructure error for account Domain\machineA$ every 10 minutes and nt authority\anonymous every hour. I checked syslogins but didn't find ...
0
votes
1answer
106 views

SQL Server 2012 doesn't use enough memory

Under heavy Load, my SQL Server database is executing queries really slowly. I have 96 GB of RAM and my CPU is high (100%) but my physical memory usage is at 9%. I looked at the SQL Server memory ...
3
votes
1answer
37 views

High availability on a subset of the data

On my site, I have a set of big databases (+6TB total) that is running on a SQL Server 2012 instance. One of these is the main DB and the other ones are archived data that are queried sometimes (...
1
vote
2answers
76 views

Linked servers - Upgrading from 2005 to 2012

Best way to find other server are linked to 2005 SQL Instance as I am migrating all to 2012 SQL Server. I will migrate all linked server that is linked to 2005 SQL Instance but what is the best way ...
0
votes
1answer
32 views

Always on viability/configuration for very specific purpose

We need to know the risks/pitfalls of a particular AlwaysOn configuration. We want an AlwaysOn configuration with the following requirements(below). My question is: Is this a sound configuration or ...
1
vote
0answers
60 views

SQL installation Product update delaying [closed]

SQL server 2012 installation is taking too much time in scanning for product update and it is delaying my installation....Please can someone help me out?
22
votes
1answer
200 views

Very strange performance with an XML index

My question is based on this: Improve Xquery performance in SQL Server To give an answer there I did the following test-scenario. Test scenario First I create a test table and fill it with 100.000 ...
1
vote
1answer
25 views

Sql server 2012 replication scenario

I am under a really specific scenario. I have a database on a customer's site and a website on Azure cloud that connects to that database. User is on a 50mbit fiber but this is obviously slow. So I ...
2
votes
1answer
48 views

Log Shipping vs Data Replication in SQL Server 2012

Just wonder if anyone has tried to use Log Shipping on SQL Server 2012 collecting data from SQL Server 2008R2 and SQL Server 2005 databases? I heard it would be an easier option to implement compared ...
1
vote
0answers
31 views

How to properly re-run a deadlocked trasaction in TSQL

In TSQL (SQL Server 2012), is this a proper way of re-running a transaction that was killed due to deadlocked - it was chosen as the deadlock victim. EX: "Transaction (Process ID 118) was deadlocked ...
1
vote
1answer
67 views

Xquery, insert, Trigger and no place to squeeze stored procedure

I have a Stored procedure, which is, basically, xquery pulling data from xml and inserting it into multiple tables. Once it's inserting into last of the mentioned tables, the trigger fires up and then ...
1
vote
0answers
67 views

High CPU usage by only 4 users

New as DBA and I feel really confused with this... 4 users working, doing stuff with macros, commited_snapshot ON, 90% cpu ran sp_blitz and found that 1 query is using more than 50 plan caches... ...
3
votes
2answers
88 views

Handle expired SQL login's passwords in SQL Server

Is it possible to change the password for a login with an expired password using SQL Server Management Objects (SMO) programming? Many times during working hours I got this message from a production ...
1
vote
0answers
43 views

SQL Server 2012 Express and Hardware Differences affecting performance [closed]

Here is the setup. I'm running SQL Server 2012 Express on the following two boxes: Box 1 (Server) : Dell T710 2x X5680 3.33Ghz Xeon 92GB Memory RAID 10 - 146GB Dell drives, w/ Cachecade SSD drive ...
1
vote
1answer
119 views

unable to run step 3 of syspolicy_purge_history job on SQL Server 2012

After installing an instance of SQL Server 2012 (SP3) on a server that had previously had SQL Server 2008R2 on it, I began encountering the following error while attempting to run step 3 of ...
0
votes
2answers
146 views

query processor ran out of internal resources - extremely long sql query

I have a SQL Server 2012 database with around 2000 tables (daily log files) with around 20 million records each. Output of select @@Version Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Standard ...
1
vote
2answers
63 views

Encrypted column - limit key access

I'm new to SQL encryption and have a SQL Server 2012 database. The database itself needs to be accessible by DBAs but the data within one of the columns a table should only be viewable in plain text ...
2
votes
1answer
47 views

How to use two actions and a function in an Extended Events Histogram for deadlocks?

I'd like to get a histogram through extended events that shows the count of deadlocks per databasename by date, but I'm hitting two walls: How get a histogram to include two actions How to get the ...
2
votes
1answer
47 views

Does the query optimiser re-use usable data?

This question is to expand my knowledge on how the query optimizer of SQL Server works. I like to know how stuff works behind the scenes so I can better optimist it/debug it. I have the following ...
1
vote
2answers
91 views

How can I pass a list of databases to sp_MSforeachdb?

I have the following script that when run gives me a comma delimited list of database names. SET NOCOUNT ON declare @db_list NVARCHAR(MAX) DECLARE @command varchar(1000) ...
4
votes
1answer
93 views

Table Joined To Itself Appears Inefficient

My ultimate goal is to compare two rows (only two will exist) and for a specific type and determine if its rows values are greater than the other rows type values. The result I came up with seems to ...
0
votes
0answers
36 views

In SQL server sys.sp_datatype_info_90 running for more time

From where is the stored procedure sys.sp_datatype_info_90 being triggered, and how can I stop or avoid this in my environment? Does it affect my server performance in production? Is it common in SQL ...
2
votes
1answer
122 views

SQL Server access issue

I am trying to connect 3 SQL servers using SSMS from a different landing server. The issue is I am able to connect 1 server but unable to connect other 2. The error I get is below: A connection ...
1
vote
0answers
64 views

Force collation when querying Visual FoxPro through OPENROWSET

How can I force collation in the following DBF select statement: SELECT * FROM OPENROWSET ('VFPOLEDB','FilePath\FileName.DBF';'';'','SELECT * FROM FileName') I've tried with COLLATE to each ...
3
votes
0answers
56 views

Import from dbf - collation

I select data from dbf files through OPENROWSET Visual FoxPro OLEDB. Despite forcing collation on columns (e.g. SELECT name COLLATE Polish_CI_AS FROM Employees) the polish specific characters keep ...
5
votes
2answers
78 views

Confusing Query: Mixing Aggregates and Non-Aggregates

Hope someone can clarify how to do this query from SQL Server 2012, or maybe what it is asking; it seems I am being asked to do a query that combines aggregates with non-aggregates, leading to what ...
1
vote
1answer
72 views

Getting “There is already an object named 'Mytable' in the database” error

I keep getting the following message: Msg 2714, Level 16, State 6, Procedure tr_AuditTableChange, Line 9 There is already an object named 'Mytable' in the database. Not sure what I am doing ...
8
votes
4answers
451 views

Query to select max value on join

I have a table of Users: |Username|UserType|Points| |John |A |250 | |Mary |A |150 | |Anna |B |600 | and Levels |UserType|MinPoints|Level | |A |100 |...
0
votes
1answer
39 views

Max redo speed on replica side in Availability Group

I heard about maximum 50 Mb/sec for receive and 50Mb/sec for redo. But really, I have only 20Mb/sec redo speed. This is speed from Perfmon, so I know about the following issue: AlwaysOn ...
2
votes
0answers
77 views

List of windows firewall exception for sql 2014 cluster [closed]

I am setting up a SQL Server 2014 cluster in Windows Server 2012 r2 Standard edition. I need to identify the inbound/outbound ports to be exempt in Windows firewall. I have compiled some already ...
4
votes
1answer
81 views

Why is sp_update_alert failing?

In SQL Server 2012, I have a Job Agent Alert set up as follows: EXEC msdb.dbo.sp_add_alert @name=N'Deadlock Alert', @message_id=0, @severity=0, @enabled=1, @...
13
votes
2answers
3k views

Newer Version of SQL Server Less Stable?

Our database architect told a client of ours that SQL Server 2014 is a poor choice over 2012 because it lacks the performance and stability advantages that 2012 has. Everything I've read contradicts ...