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

learn more… | top users | synonyms (3)

1
vote
1answer
153 views

Find out who ran a query using transaction log backups

We have a SQL Server 2012 Enterprise database running in Full Recovery Model with transaction log backups running every minute. A query was run last night which means we need to recover data from a ...
0
votes
0answers
42 views

How best to index large table with many index keys

In our datawarehouse load, we bring data in the following manner. 1. from source to stage 2. stage to a consolidation layer 3. from consolidation layer to operational data store (ODS) ...
3
votes
1answer
170 views

Why does SQL authentication sometimes fail and how can I make it work reliably?

I have SQL Server Express installed locally on a machine and it works most of the time but occasionally it fails to make a connection with this error: Could not obtain information about Windows ...
2
votes
1answer
2k views

Restoring a SQL Server 2012 database in high availability

I have a database which is in always-on high availability mode synchronized with another database on a different instance. How can I restore from a .bak file into the primary database using T-SQL? ...
0
votes
1answer
57 views

Unable to call sp_start_job from website only

I have an agent job that I'm trying to use to kick off an SSIS package via a web page. I have configured my web site running in an application pool as user X. When I invoke sp_start_job as use X ...
0
votes
2answers
2k views

Does SQL Server require SSLv3?

We have a Windows 2008 R2 server running SQL 2012 Express RTM (11.0.2100.60). All three protocols (shared memory, named pipes, TCP/IP) are enabled for the server and both 32-bit and 64-bit clients. ...
3
votes
2answers
104 views

Trigger firing when it shouldn't

I have the following trigger: CREATE TRIGGER SalaryCannotDecrease ON Employees AFTER UPDATE AS IF EXISTS ( SELECT * FROM inserted, deleted WHERE inserted.Salary ...
1
vote
0answers
143 views

Adding a distributor for availability groups

According to this MSDN article, I need to add a distributor. http://msdn.microsoft.com/en-us/library/hh710046(v=sql.110).aspx I'm confused by this particular statement: The distributor should not be ...
-2
votes
1answer
64 views

Foreign Key Error [closed]

Getting this error on my database and I don't know how to fix it: Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint ...
0
votes
2answers
153 views

SQL Server 2012 Very Slow to Consume Memory

I have a bit of a conundrum. I recently upgraded a ~20gb database from Window Server 2008 R2 || SQL Server 2008 R2 to Window Server 2012 || SQL Server 2012. The previous server had 32gb of memory, ...
1
vote
2answers
1k views

Stored proc getting ANSI_NULLS and ANSI_WARNINGS error when executed by application

I have a stored procedure that is getting the following error when executed via an application : Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the ...
0
votes
1answer
347 views

ODBC connection for testing Availability Group Listener

I am having difficulty verifying my Availability Group Listener configuration. I would like to create an ODBC connection which points to the listener. When I configure the system DSN and try to test ...
0
votes
1answer
84 views

non clustered index on same table but the order is different?

I have one table it has 5 columns like col1...col5 . i need to create non clustered index on col2,col3,col4. I did .. then my boss is asking me create one more non clustered index on col4,col3,col2 ...
0
votes
0answers
78 views

sys.sp_audit_write Procedure not available

I am trying to execute the audit write procedure but it can't be found. I have looked in master for it in the system procedures list and confirmed it isn't there. Does anyone know why this might be? I ...
0
votes
1answer
2k views

Linked Server from SQL Server to SSAS not working - what is missing?

I have a SQL Server 2012 Standard edition sp1 that has ssas also installed. From this server I create a linked server to a ssas server called BIREPLON1, but when I try to access it, I get the error ...
2
votes
1answer
646 views

Is there a way to minimally log insert under Always on?

I have a primary database in FULL recovery mode which is part of Always On group. Is there a way to minimally log insert operation under FULL recovery model? I have a process that is executed each ...
0
votes
1answer
2k views

SQL Server 2012 how to create new schema from existing database selecting only subset of database objects?

I'm looking for a way to create new schema from existing database selecting only subset of database objects. I.e. I want to have some "database representation" which only includes subset of tables ...
0
votes
1answer
351 views

How SQL Server agent calculates next run date for a job

I am developing a system for scheduling task & I am following Microsoft SQL agent approach to schedule task. I have replicated sysschedules table for capturing users scheduling occurrences and ...
1
vote
2answers
635 views

SQL Server Grant execute on all stored procedures without ddl permissions

I have some users in my database. They are member of db_datareader. They now also need to have execute permissions on stored procedures. I was just wondering what would happen if one of the stored ...
0
votes
1answer
472 views

A transaction log grows unexpectedly [duplicate]

I am trying to understand what process is making my transaction log file to grow. For example, currently it is 120 GB and only 5-10 MB of it are in use. Before: FULL recovery mode a backup of the ...
1
vote
2answers
3k views

Can't create table in database with db_owner role (SQL Server 2012)

I have database in SQL Server 2012 SP2 Enterprise. I created new SQL Login and mapped to X database with db_owner role. Now I can't create table on this database. When I run simple create table script ...
1
vote
1answer
19 views

Can I minimize maintenance of duplicated Windows + SQL Auth logins?

In our environment, SQL authentication is the norm, but Windows auth is possible when users are using RDP. As a result, each physical user has two logical logins for each instance and two logical ...
0
votes
1answer
35 views

Select Ingredients of a product based on unit type

I have three tables UnitType,NutritionMaster, NutritionInProduct UnitTypeID UnitName 1 100ml contains 2 250ml contains NutritionID Name Description 1 Energy NULL 2 ...
0
votes
0answers
132 views

How to fix deadlock caused by “X” locks on same resource?

I have the following <resource-list>: <resource-list> <keylock hobtid="72058301601677312" dbid="7" objectname="myDatabase.dbo.TableOne" indexname="PK_TableOne" id="lock2c336fb380" ...
0
votes
1answer
470 views

SQL Server Developer Edition Downgrading to Older Developer Edition

I have seen answers for questions regarding downgrading from different editions (e.g. - Enterprise to Standard), but is anyone familiar with downgrade policies for SQL Server Developer editions to ...
1
vote
0answers
56 views

Resuming log shipping, do I just restore the db over itself?

I have log shipping set up. I then bring all the databases online on the secondary machine, so log shipping can't restore anymore. A bit later on I want to resume log shipping. I was told before ...
0
votes
0answers
85 views

SQL Server : how to decide if FULL backup or DIFFERENTIAL

The following scenario (on SQL Server 2008 R2 and 2012 servers) A co-worker of mine restored another DB into the one our process was trying to create a differential backup for. As I am currently only ...
0
votes
0answers
34 views

I have a SQL job that sends an e-mail. Can I make it run a program instead?

I set up a job, and in Job Properties > Notifications I checked E-mail When the job fails. I'm wondering if instead of this e-mail, I can make it run an .exe or similar with the details. The reason ...
7
votes
2answers
865 views

How can I reliably determine if a given SQL Server instance has the “Lock Pages in Memory” right?

The "Lock Pages in Memory" right can be granted to the service account used by SQL Server. This allows SQL Server to prevent memory being paged to disk. I've noticed several of our SQL Server ...
2
votes
3answers
181 views

Why is cost threshold for parallelism ignored?

SQL Server 2012 SP2 Enterprise Edition. Users complaining of slowness. Monitoring tool shows highest wait event is CXPACKET. Instance settings MAXDOP: 8 Cost Threshold for Parallelism: 175 Ran ...
0
votes
0answers
226 views

SQL Server Agent Job, Access Denied

I have a T-SQL agent job that I've created that adds steps to itself depending on a query of the MSDB database. I am able to run the query outside of the agent, inside a query editor window, but when ...
0
votes
0answers
80 views

How to specify localhost for SSIS package source server?

I have a job running on SQL Server 2012, where one of the steps is of type "SQL Server Integration Services Package". For the "Package source" I choose SSIS Catalog. For the "Server", if I type in the ...
1
vote
2answers
90 views

Managing logins with Availabilty Groups

I have created a user on my primary node that has access to all of the databases in Availability Group AG1. How do I ensure that same user has the same rights on all the other replicas in AG1? ...
0
votes
0answers
222 views

I want to ignore all SSIS errors, and copy over every row with the error column simply set to NULL

I have a simple SSIS package that reads from an OLE DB Source (Oracle), goes through a conversion transformation, and then to an OLE DB Destination (SQL Server 2012). I have configured the error ...
0
votes
2answers
136 views

Signal wait always high

I'm not sure if this is the right place to ask this question, so apologies in advance if not. Problem I'm facing is: One of my server's signal wait floats at around %35 and won't go down. Top 3 ...
0
votes
0answers
59 views

mirroring on sql server 2012 on different windows server 2012

I have 2 servers *.203 and *.205 I have sql server 2012 on each of them I tried to make mirroring between them every thing goes successfuly but when I tried to start mirroring it gives me ...
1
vote
0answers
62 views

Speed up changing of collation?

I'm trying to change collation on all our databases, and doing it on one of the databases (~15gb) took me 42 minutes in testing even though I put it on SSD disc. Do anyone know any way to speed it up? ...
2
votes
2answers
745 views

SQL Server 2012 DBCC memorystatus has wrong information. How can I correct it?

I have a Windows 7 64-bit machine with 16GB ram, 24GB pagefile, 500GB HD and SQL Server 2012 Standard Edition 64-bit running on it. With 5 minutes of starting up, it routinely uses all my virtual ...
1
vote
1answer
130 views

Can I create a database on a local folder mapped as a drive

I have mapped a local folder as a drive, as follows: subst E: c:\MyFolder I need to create a database on that drive, but it fails: CREATE DATABASE MyDb ...
4
votes
3answers
3k views

Keeping default max memory 2147483647 MB value in SQL Server, a bad practice?

Based on that msdn page : server memory settings it's a good practice to keep the default values for SQL-Servers min and max memory to keep it dynamic... From what I learned through tutorials you ...
2
votes
1answer
83 views

SQl Sever Backup Exclude Users

I have a database on SQL Server 2012 that I need to backup and send to someone on a different network / domain. Is there a way to create a full backup file that will not include the users / security ...
4
votes
1answer
1k views

SQL Server 2012 Standard using too much memory

For the past few months I have had a SQL Server 2012 Standard edition installation using up all the RAM in the server, up to 190GB (I'm not kidding). About every 3 to 4 days I end up having to restart ...
3
votes
1answer
179 views

Overlapping indexes

I have two non-clustered indexes on one of my dbs and their definition are: CREATE NONCLUSTERED INDEX [NC_Index1] ON [dbo].[anyTable] ( [X] ASC, [Y] ASC, [Z] ASC ) INCLUDE (A , ...
1
vote
1answer
375 views

What permissions do I need to grant on service accounts on SQL root path when relocating?

During a Win2012 with SQL Server 2012 two-node cluster installation, I accidentally set the system root to be the mount point volume hosting folders for the various attached LUNs for the system and ...
2
votes
1answer
1k views

Moving SQL Server default instance's folders to another path on the same server

My customer installed SQL Server 2012 default instance. But default instance's folders and data folder are on the same disk. I want to move the default instance's folders to another disk (C:Program ...
6
votes
2answers
635 views

Filter table before join

I have a stored procedure that populates the temp table #employee_benefits with a list of Ids. This table ends up being roughly 10,000 rows long. The query below then selects from a table called ...
0
votes
0answers
54 views

Wait Stats, Server Up Time and CXPACKET

I have been looking at the following post and using the query to look at my top wait stats: http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ My top wait stat is ...
1
vote
1answer
77 views

many Servers of SQL server 2012 needs mirroring

I have two SQL server 2012 instances and I want to configure mirroring between the 2 servers with database. My questions are: How can it automatically change host of one if the other one was dropped ...
1
vote
1answer
46 views

many Servers of SQL server 2012 needs mirroring [duplicate]

I have now simply 2 SQL Server 2012. I want to configure database mirroring between the 2 servers. My questions are: How can it automatically change host of one if the other one was dropped ? Is ...
1
vote
1answer
258 views

How to recover a database from a failed Availability Group?

We are currently testing a SQL Server 2012 AlwaysOn HA implementation where we have 2 out of the 3 nodes are synchronous. The third, asynchronous node, does not have a vote in the quorum. There is a ...