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

learn more… | top users | synonyms (2)

1
vote
2answers
57 views

What is the best way to compare values of whole tables between many databases?

We are trying to compare the values of whole tables between many databases. The user can enter in input the table names and the columns names, and the database names he wants to compare. They can ...
4
votes
1answer
5k views

Installing SQL Server 2012 - Wait on the Database Engine recovery handle failed

I am installing SQL Server 2012 SP1 on a new Windows 8.1 machine. During installation I get this error: (Screenshots similar to this question) TITLE: Microsoft SQL Server 2012 Service Pack 1 ...
0
votes
1answer
32 views

When does a port get assigned if SQL Server is set to TCP Dynamic Ports

I'm trying to determine when a dynamic port is assigned to SQL Server. If the TCP/IP Properties have the TCP Dynamic Ports enabled, does that port get assigned at the time the Server is installed or ...
0
votes
0answers
19 views

Timeout expired error when database is being backed up in SQL Server 2012

We have daily full backup scheduled at 8.30 AM. We are getting Timeout expired when we try to access a web page using the database being backed up. The web page has several select and few ...
0
votes
0answers
16 views

How do I manage environment specific connections for SSAS projects?

I am using SQL Server 2012 and I am responsible for implementing release automation for our SQL, SSIS, and SSAS projects. SSIS projects have project parameters and you can link those to SSISDB ...
1
vote
0answers
58 views

Why wouldn't statistics update if all rows in a table were replaced?

We have several "reporting" tables in a SQL Server 2012 SP1 instance. Every hour, a job runs procedures that delete all the rows from these tables and then insert current data (involving lots of ...
1
vote
2answers
43 views

Finding AD group permissions source

We have an AD group as a Login in several servers, usually as sysadmin, but I've found one server that this login has no access to any DB and has a server public role only. I've talked to a member of ...
1
vote
1answer
33 views

Why didn't this statistics object on a filtered index auto update?

In a SQL Server 2012 SP1 instance, I have a filtered index on the PK column of a table that looks like this: CREATE INDEX [RF_IXF_Orders_OrderNumber] ON [dbo].[Orders] ( [OrderNumber] ASC ) WHERE ...
0
votes
0answers
102 views

ADSI Linked Server: Why can I query AD in VBA, but not as a Linked Server?

After all the reading and research I've done, this seems like the most logical place to post this question: Why can I query Active Directory using VBA, but not a Linked Server in SQL Management ...
0
votes
1answer
25 views

Maintenance plan to backup differentials hourly each to a new file name in SQL Server 2012

In addition to a nightly full backup, I'd like to set up an hourly differential backup maintenance plan in SQL Server 2012, where each differential backup file should receive a different name. It ...
0
votes
1answer
52 views

Change Tracking clenup

Is there a way to know when cleanup of change tracking tables occurs? How to check last occurrence of cleanup process? BACKGROUND: I have Change Tracking on database (retention = 1 minute): ALTER ...
0
votes
1answer
57 views

SSIS Package fails to “Transform File and create output file”

I have an SSIS (2012) package which was written by one of the developers. I've been tasked to with fixing the error. Each time the package is run I receive the following three messages indicating the ...
1
vote
2answers
188 views

Optimizing queries for 25+ million rows

I am using MS SQL, and I have to run several queries on the same table on different criteria. At first I ran each query on the original table although they all share some filtering (i.e Date, status). ...
4
votes
1answer
310 views

Subselect of MAX(Version) takes many minutes, though there are only ~20K records

I have a pretty complicated data model. I can't understand most SQL examples without an explanation of what's being modeled, so I'll try to explain. Mainlines -> Releases -> Overlays -> Calibrations ...
5
votes
1answer
305 views

Should I use Database Mirroring?

So I have the following scenario on my hands: I have a product database that runs for a specific app, all inserts into the database come in through three separate connections. Recently I have ...
7
votes
1answer
156 views

High PAGELATCH_* and WRITELOG waits. Are they related?

We are seeing very high PAGELATCH_EX and PAGELATCH_SH wait types along with high WRITELOG waits. I've diagnosed the query causing the PAGELATCH waits and can eliminate them by reducing the insertion ...
4
votes
3answers
1k 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
3answers
68 views

Why is sql server waiting for acknowledgment?

I have an application that often request a big set of rows [about 20K] from a sql view, in the mean time other applications can't update the table from where the data comes from, the update query's ...
0
votes
0answers
104 views

Peer-to-Peer Transactional Replication Load Balancing in MS SQL Server

I'm looking at Peer-to-Peer Transactional Replication and it looks like a perfect fit for my requirements, but there's one unclear part - load balancing (see picture below). How it is implemented in ...
3
votes
3answers
742 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 ...
-4
votes
1answer
49 views

Create database unique Surrogate Keys without using GUID’s

What is the best way to get database unique surrogate keys in Microsoft SQL Server when using something like “auto increment” as opposed to GUID's. The purpose is to increase performance over using ...
1
vote
0answers
30 views

How does SIOS Data Keeper make consistent backups? [closed]

We are looking to change our DR solution to get our RTO and RPO as close to 0 as possible. We have around 80 databases on SQL Server 2012 sp2 that come to about 1TB in total including log files. I ...
3
votes
3answers
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
0answers
69 views

How to raise Procedure Cache Hit Ratio?

I have a SQL Server 2012 instance with 96 GB of RAM that has the Buffer Cache Hit Ratio consistently at 100% and the PLE consistently over 500,000, so memory is obviously not a problem. However, my ...
1
vote
2answers
57 views

SQL Server 2012: set a column to only use a single case?

Is there a way to tell SQL Server 2012 to store both A and a as A (or both A and a as a) for some given column such that every insert statement does not need to have UPPER() in it?
0
votes
0answers
51 views

Using loops inside Openrowset

I'm trying to execute a query across around 200 servers using openrowset function. This function needs to execute in a loop - the query is sucessfull in SQL Server 2012 environments, but failing in ...
3
votes
2answers
6k views

sp_dboption is not available in SQL Server 2012

The system stored procedure sp_dboption is not available in SQL Server 2012 anymore. How could it be replaced?
1
vote
1answer
80 views

Upgrade to SQL Server from 2008 to 2012 - Developer perspective [closed]

We are in the process of migration from SQL Server 2008 to SQL Server 2012. The entire process of migration will be taken care by dedicated DBA team. From a developer prespective, I would like to ...
4
votes
1answer
1k views

Restore master database from SQL Server 2008 R2 on SQL Server 2012

I have a process that restores all my backups on a centralized server for testing. I have recently upgraded this server to SQL server 2012, and now I am getting an error when restoring master from SQL ...
1
vote
1answer
71 views

SQL Server 2012 - Dropping Snapshots takes hours

We recently did a SQL Server upgrade from 2005 to 2012. We have a nightly job that drops a snapshot of an active database. The snapshots gets created again later in the day. Before upgrading, in SQL ...
1
vote
1answer
72 views

How to improve query to include 2 critical pieces of employee data

I’m going to preface by saying that I’m fairly green with SQL. My code below works, however I am missing two vital pieces of my employee population: (1) those employees who have been invoiced but ...
1
vote
1answer
62 views

Using LIKE or Full-text when searching database

I am fairly new to SQL and I am currently trying to create a search function for my website, much like the one on imdb.com. I have 3 tables and I need to search two rows (nvarchar) in each table when ...
1
vote
0answers
20 views

SQL Server Transactional Replication

I am using SQL Server 2012 and I am investigating transactional replication and had a question on SQL Versions that can be used. The Distribution and Publisher will be on SQL2012 Standard Edition, ...
4
votes
2answers
3k views

Suspended in index rebuild

In SQL Server 2012, an Index Rebuild job is taking a very long time (up to 8 hours). However, not even one Index rebuild completed, so I stopped the index job. In monitoring SQL task: state = ...
2
votes
2answers
38 views

How do I add keyboard shortcuts in SSMS 2012 to run a specific query?

How do I add keyboard shortcuts in SSMS 2012 to select all the data from table? In SSMS 2008 we could assign a shortcut in the following way: ...
1
vote
1answer
170 views

SQL Server - Linked Server - Using OPENROWSET with windows integrated security

I have seen many articles regarding OPENROWSET using integrated security (Windows Authentication), but I could not make it work for me. It is working fine using SQL Server authentication: select * ...
5
votes
5answers
5k views

SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell

I've installed and successfully configured our SQL Server 2012 AlwaysOn 2-node servers for our new "Intranet" that is coming out. I've gotten AlwaysOn working great, and our Front End servers for the ...
2
votes
1answer
40 views

SSIS 2012: What rights are needed to connect to SSDB via DMO

I'm trying to connect to the SSDB catalog to execute a package using the .NET object model e.g. using Microsoft.SqlServer.Management.IntegrationServices; var sqlConn = new ...
1
vote
1answer
28 views

how to output 3 column into 3 element in xml format with the same name?

I have this code and I can't figure out how to get the expected result? declare @table table (TAG int, PARENT int,col1 varchar(100) ,col2 varchar(100) ...
3
votes
1answer
62 views

Tool for quickly backup and restore a table

Is there a tool for quickly backup and restore a table's data, preferably to a csv file? It would be best if I could do it using batch. Run a .cmd file and data is backed up into a file with datetime ...
1
vote
0answers
59 views

SQL Server database corruption in multi-user mode but not single-user mode

Using Microsoft SQL Server 2012 SP2 Standard Edition on a 2 node SQL Cluster. When I run dbcc checkdb on a certain database when it is in multi-user mode, it finds corruption in the clustered index ...
4
votes
2answers
142 views

Execute SP whenever any job is finished

I have many SQL Server jobs on the server. I need to execute a SP whenever any job execution is done. One hard way is to create a step on every job which calls that SP. Is there any simple way to do ...
1
vote
1answer
86 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
82 views

PAGELATCH_EX on 2:1:128

We are having problems on SQL Server 2012 with contention on tempdb page 2:1:128, which seems to be related with system table sysobjvalues. We have about 400 sessions waiting on this resourse ...
0
votes
1answer
22 views

Rank function creating duplicate partitions? (two rank 1's for the same key)

The below query is outputting two rows. Given that I am partitioning on ref_number and also have ref_number in the where clause, I believe I should only ever get a single row back. WITH rank_cte AS ...
1
vote
1answer
67 views

Shrinking old partitions

I have a large partitioned time series table where each partition is stored in it's own file. Since it's time series based partitioning divided by week and I'm only storing data that is new I have ...
0
votes
0answers
44 views

SQL Server Partitioning - parent/child tables reference partitioning

I have a requirement to partition quick growing tables in our product and below are the table information with key details. PERIODIC_EVENTS (PARENT TABLE which has ID (sequence no) as Primary KEY and ...
-4
votes
1answer
48 views

Who is depends on specific column? [closed]

I want to alter column table from varchar X to Y. And for that I need to know who is depend on this column . Any suggestion?
0
votes
1answer
80 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 ...
0
votes
2answers
160 views

How to get the column name having a particular value from a SQL Server table?

I have a value (say 5) but I do not know in which column of the table it is stored in. I want to get the column name which has this value. Please help me with this.