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

learn more… | top users | synonyms (2)

1
vote
1answer
36 views

SQL Server generate self-signed SSL certs performance concern?

Read this msdn article and doesn't seem to say if it keeps using the same self-signed cert after one is automatically generated. If SQL Server generates a new SSL cert upon every connection (anyone ...
0
votes
2answers
92 views

SQL Server Date Conversion

On applying the where condition : WHERE DateFilled BETWEEN '02/01/2014' AND '02/28/2014' My syntax is not detecting the values which are filled on 02/28/2014. If I write the syntax as WHERE ...
2
votes
1answer
74 views

Zombie Sql Server DB on Secondary Replica is both in and out of an Availability Group

I have a database in Sql Server 2012 that is on the secondary replica of an availability group. I removed the database from the AG db list on the primary and the db is currently in the Restoring ...
3
votes
2answers
226 views

SQL Server Memory Issues

We have a SQL server that appears to be leaking memory. Memory consumption linearly increases over time until the server is completely out of memory, causing major performance issues. The SQL server ...
0
votes
1answer
88 views

How does the Distributor know you have seeded/initialized your Subscriber using a backup?

Im building my first transactional replication database for our Sharepoint team. The Publisher database is fairly large 330GB+ Because the Publisher database is in a different data center than the ...
3
votes
1answer
143 views

Frequent Delete & Insert, impact on performance

In my application, we have very few updates. Most of the data gets deleted and inserted. My question is, how this will impact the performance? We are using SQL Server 2012. Table statistics (will ...
2
votes
2answers
89 views

Estimated vs Actual Number of Rows

I have 5 tables that contain data split by quarter each table has a similar structure to the following: ID_Column bigint PK, EventType int, DateStamp datetime PK CustomerId int, ARG_1 nvarchar(32), ...
0
votes
1answer
88 views

SQL Server: multiple failover cluster instances with disjoint storage

This is for SQL Server 2012. I currently have two datacenters that have separate SANs. I'm using failover cluster instances (FCIs) for server redundancy, and then AlwaysOn Availability Groups (AGs) ...
1
vote
3answers
2k views

How to view an encrypted view or stored procedure

I am working on a third party database. When I try to view the definition of a view by right clicking, CREATE TO and then to NEW QUERY EDIT WINDOW, I am getting an error: This property may not ...
0
votes
2answers
211 views

why stored procedure performance is very high compared to plain select query

I have a table with more than 2 million rows, with needed indexes. When I run a simple query it takes so much time so I decide to stop executing it; while when I wrap the query in a stored procedure, ...
0
votes
3answers
80 views

How to script data in deterministic order

When I script data in my database in SSMS Tasks->Generate Scripts and then select data-only it turns out that the insert statements are generated in different order each time when even a smallest ...
0
votes
2answers
150 views

Recommendations for a SQL Server 2014 Database Administrators Course [closed]

I am after some personal recommendations for a SQL Server 2012 Database Administrators Course with exams. Exam 70-461: Querying Microsoft SQL Server 2014 Exam 70-462: Administering Microsoft SQL ...
0
votes
1answer
46 views

Rebuild Index in system database

I use SQL Server and have multiple indexes in system database whit index fragmentation 30% or more. I don't know that SQL server manage system database index automatically or I want to manage them. ...
4
votes
1answer
88 views

If user owns a database fixed role are they the admin of the role?

I have SQL Server 2012. When I create a user in "Owned schemas" screen one of the option is fixed database role. If I own a fixed database role: What permissions am I granted? I am the admin of ...
2
votes
0answers
124 views

Significant PLE Drop

This morning at 4am we experienced a huge drop in Page Life Expectancy on a new sql server 2012 box. No jobs (backup etc) ran at this time and traffic was very light compared to say 20:00. Current ...
3
votes
4answers
624 views

indexing on multiple nvarchar(max) columns

I have multiple nvarchar(max) columns in my DB named ShipperName,ConsigneeName, ProdDesc,BillOFLading. What I need to do is to have UNIQUE INDEX for the combination of these columns. As I am failed ...
8
votes
1answer
274 views

Re-run a specific actual query plan

I have captured an actual query plan for a specific query. After this I've changed a few things around (including updating the statistics) and re-ran that specific query. Now the actual query plan ...
0
votes
1answer
34 views

What is the MS_PerfDashboard schema in msdb?

We just migrated an old reporting server (running on 2005) to 2012 and some of the database performance monitoring jobs have started failing. I looked into them and found that they use a couple of ...
1
vote
1answer
218 views

How do you restore a DB to Multi-User mode when deadlocked by a sys process

I have a database that is stuck in Single User mode after a failed restore process. I have checked the sysprocesses table and none of the processes have spid > 50. They are all sa processes. Using the ...
1
vote
1answer
65 views

Partitioning a table state

So far I always used date ranges to partition my tables, however now I like to do it by state or a selected list of cities where all others that are not explicitly listed in an "other" partition, is ...
2
votes
0answers
39 views

Setting up triggers based on exposed columns in a view's source table

I have a table where I want to do a AFTER UPDATE trigger if one of the columns modified was in a specific view CREATE VIEW [dbo].[iv_EmployeesView] AS SELECT EMPLOYEE_GUID AS EmployeeGuid, ...
2
votes
2answers
1k views

How to enable a SQL Server 2012 login to create, alter and drop the created databases?

I'm trying to assign a proper GRANT permission to CI login, that will be able to: Create test DB Alter it Drop this test DB (and only it) But it appears that SQL Server 2012 GRANT CREATE ANY ...
1
vote
1answer
156 views

Performance of dynamic SQL Server reporting queries?

I have to build an application that allows the end user to select what columns they want in their report. For this I build a dynamic T-SQL stored procedure. However, there are up to 60 columns ...
0
votes
0answers
50 views

TempDB causing high IO writes - dependent on server's max memory setting [duplicate]

I have a mirrored SQL Server 2012 database. The first server has a hugh tempdb write IO load when the maximum memory limit is set above 8192 mb. These 8192 where the original setting for both the ...
3
votes
1answer
218 views

Tempdb data file disappearing

I recently moved my Temp database files onto a separate drive (I had two data files and one log file). However, after restarting the server, my second data file disappeared both from the file list on ...
5
votes
2answers
354 views

Capture very expensive queries

I have an Sql servers of 2008/2012. Some users run very expensive queries, such as updating 1G rows in one transaction or cross join several very big tables, and it causes the server no response from ...
2
votes
1answer
282 views

User Mapped to a certificate in SQL

I need to create a user ,who can log on to a contained DB.In this context what is the benefit of using a user mapped to a certificate?
1
vote
1answer
257 views

SQL Server 2008 mirroring migration to SQL Server 2012 always on

Quick question, request your input. I am migrating a SQL Server 2008 R2 instance with database mirroring to SQL Server 2012 with always on. Should I : Restore the SQL Server 2008 R2 backup to SQL ...
3
votes
1answer
72 views

Recursive Updates on large table

I am desperately seeking a solution for a problem, which i will try to fully describe hoping that a somebody can come up with a clever solution. The metadata : At the moment i have the following ...
0
votes
0answers
64 views

SQL Server 2012 Replication Initialisation Fails — Table keeps being dropped

I have two servers - one located in Australia (Distributor / Publisher), and a second in the UK (Subscriber) both on Amazon's cloud. I have attempted to configure replication as both pull and push ...
0
votes
0answers
33 views

Post Database upgrade on new instance [duplicate]

I reviewed the SQL Server 2012 technical upgrade guide and also googled for more recommendations. I am migrating a SQL Server 2008 R2 instance to SQL Server 2012 on a separate machine. Here is what ...
4
votes
1answer
530 views

What permissions does the service account need to use database mail?

I have an SQL Server 2012 instance running under a managed service account. I've configured database mail with one account and am trying to send a test email, but I'm getting some errors in the ...
0
votes
1answer
344 views

Error creating database in SQL Server Management Studio

I'm trying to create a new database in SSMS, but I get this error: Could not load file or assembly 'Microsoft.SqlServer.Sqm, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or ...
6
votes
2answers
3k views

What is the fastest way to insert large numbers of rows?

I have a database where I load files into a staging table, from this staging table i have 1-2 joins to resolve some foreign keys and then insert this rows into the final table (which has one partition ...
2
votes
2answers
197 views

SQL Profiler - Insert values?

I setup an SQL profiler to run, but I am not seeing any values for updates/inserts. Is there a way to see them? All I see is stuff like this: insert into some_tbl(id, col1, col2, col3) values ( @P0 ...
5
votes
2answers
1k views

Does the Sql Server Agent account need sysadmin permission?

I ran Brent Ozar's sp_blitz script, and one of the things it's complaining about is that my SQL Server Agent account has sysadmin permission. I removed the sysadmin permission, but then the agent ...
1
vote
0answers
199 views

SQL Server 2012 - Large backup file size

We have the following setup: Once a week (Sunday at midnight), a full database backup is performed. A new BAK file is created for this purpose, let's call it Backup1.bak. From then, a transactional ...
3
votes
1answer
241 views

With SQL transactional replication, does changing user permissions to a table trigger a new snapshot?

I was hoping to get some help with a SQL Server 2012 transactional replication scenario - or maybe just my understanding of where permissions are stored in SQL server. If I change a users permissions ...
0
votes
2answers
59 views

Full Transaction Log on closed source system [duplicate]

I have a closed source application which has the transaction log filling up on a daily basis to 60+gigs. I have ran a trace on the database and no inserts and updates are occurring. The following ...
0
votes
0answers
109 views

SQL Server 2012 Transactional Replication Issue — Table Data not updating at subscriber

In my projects we configured Transactional Replication for my Database - one publisher, one distributor and two subscribers For 2nd subscriber every table data is updating properly But for 1st ...
1
vote
0answers
357 views

SQL Server Configuration Manager 2012 - TCP/IP, unable to set IP All = No

I have a 4-node SQL Server 2012 SP1 cluster, and under Network Configuration -> TCP/IP -> Protocol (tab), I am able to switch Listen All from Yes to No. However, when I hit OK (or Apply), I get the ...
21
votes
2answers
2k views

Why does query error with empty result set in SQL Server 2012?

When running the following queries in MS SQL Server 2012 the second query fails but not the first. Also, when run without the where clauses both queries will fail. I am at a loss why either would fail ...
2
votes
1answer
128 views

How to allow connections to SQL Server with 2 differents ip

I have a small issue here: I have a SQL Server instance 2012 (let's call it INSTANCE) on a cluster. I also have 2 network interfaces for this instance: SQLINSTANCE.my.metwork ...
2
votes
2answers
723 views

SQL Agent - PowerShell step “syntax error”

I have the following code setup in SQL Agent Job step that is not running. The message received is simply: Unable to start execution of step 1 (reason: line(46): Syntax error). The step failed. ...
2
votes
1answer
284 views

Speed of Searching Text vs INT/BIGINT - SQL Server 2012

I am building an application that will need to handle extremely high amounts of traffic. The traffic will consist of incoming requests for information from a SQL Server 2012 database. When a request ...
0
votes
2answers
240 views

Can SQL Agent job notifications be sent to one operator for success and another for failure?

We have a requirement that some people are sent notification on success, and other on failure. But the UI seems to only allow a combination of 1 operator + 1 condition. So I was wondering if we ...
2
votes
2answers
288 views

Dropped the Distibution database before disabling publishing and distribution, how to proceed?

I'm setting up my first Distributor database on SQL Server 2012. I successfully created one and then wanted to blow it away and perform the task again just for practice. This was a brand new ...
1
vote
1answer
59 views

CDC not picking up changes [closed]

CDC has been set up for one of our databases in our test environment but it's not capturing any of the changes. I've made sure the capture job is running. I've disabled CDCs for the database, enabled ...
0
votes
0answers
115 views

Generating dynamic table CREATE and iterative INSERT INTO script in TSQL?

I'm starting to create a dimensional database for BISM development using tables and views from a relational database on the same SQL instance. I've been looking for a way to dynamically generate ...
2
votes
1answer
190 views

How can I use running total aggregates in a query to output financial accumulations?

I am attempting to write a query that will be consumed by a SSRS 2012 report. Given a paid invoice, with a certain set of account allocations towards which the money paid towards it must be saved, I ...