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

learn more… | top users | synonyms (3)

0
votes
1answer
199 views

SQL Server hitting System.OutOfMemoryException but server has 10GB free

SQL Server 2012 11.0.5058.0 I'm running a very large batch of queries (about 2700) to simulate a user doing X, Y and Z in our app. I have SQL Server's max memory set to 2147483647 MB. However when I ...
0
votes
0answers
21 views

SQL Server Transaction Log Huge [duplicate]

I came to work today and I saw the transaction log with 441GB, using almost all disk space. I use SQL Server 2012 and have Full backup, but somehow I lost control of the t-log. So, I need to do 2 ...
0
votes
1answer
181 views

Index rebuild canceled because of .mdf growth

I have the Standard Edition of SQL Server 2012 and used a script to rebuild index. I ran the query and then I saw that the .mdf file for the database started the grow a lot. I chose to cancel/stop my ...
3
votes
3answers
126 views

Is Policy-Based Management dead? Is it scalable?

Policy-Based Management was introduced in SQL Server 2008. I'm looking for software/tool I can use to enforce configuration standards across a SQL Server 2012 /2014 database environment. Instances ...
0
votes
0answers
14 views

Is it possible to rename a SQL Server 2012 Express instance? [duplicate]

I have a MySQL background, so when I installed SQL Server 2012 Express I wrongly put my desired database name as the instance name. I guess I want a single instance installed and then each App would ...
1
vote
1answer
666 views

SSRS 2012 Report variables scope

I have a report that uses data from several datasets. I chose to do it this way because the alternative would be to join about 12 tables. From what I can gather, I have to use the dataset name to ...
1
vote
2answers
126 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 ...
0
votes
0answers
99 views

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

We have a daily full backup scheduled at 8.30 AM. While the backups are occurring, we get Timeout expired when we try to access a web page that displays data from the database. The web page has ...
1
vote
0answers
88 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
1answer
62 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 ...
1
vote
0answers
33 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 ...
4
votes
1answer
354 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
68 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
706 views

Change Tracking cleanup

Is there a way to know when cleanup of change tracking tables occurs? How to check the last occurrence of a cleanup process? BACKGROUND: I have Change Tracking on database with retention set to 1 ...
4
votes
1answer
398 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 ...
1
vote
1answer
54 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 ...
2
votes
2answers
4k 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). ...
5
votes
1answer
363 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 ...
1
vote
1answer
421 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 ...
2
votes
3answers
118 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 ...
1
vote
2answers
89 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 ...
-4
votes
1answer
67 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 ...
2
votes
0answers
543 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
0answers
212 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 ...
1
vote
0answers
58 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 ...
1
vote
1answer
165 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 ...
10
votes
1answer
430 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 ...
1
vote
1answer
299 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
33 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, ...
2
votes
1answer
153 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 ...
2
votes
2answers
116 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
81 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 ...
3
votes
2answers
401 views

Searching multiple tables in SQL

This question will involve some C# code but I hope that is fine, in the end it is the SQL that is the issue. (To see short question look at end of post) So I have a site, where you can add a site in ...
1
vote
1answer
3k 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 * ...
1
vote
2answers
63 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?
3
votes
1answer
203 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
85 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
77 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
239 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 ...
0
votes
1answer
58 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 ...
3
votes
1answer
191 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
380 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 ...
2
votes
1answer
965 views

Error while running SSIS Package job in SSMS

Error while running SSIS Package job in SSMS Hi! A SSIS package named "My_Package" has been scheduled as a job in SSMS to be executed daily at 5:00 AM daily. SQL Server Version : Microsoft SQL ...
0
votes
0answers
95 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 ...
0
votes
0answers
48 views

SQL Server 2012 Downgraded from Enterprise to Standard. Permission errors

We downgraded our SQL Server from Enterprise to Standard. Now on 3 of my 5 non system databases a lot of users no longer have effective permissions. My admin account is in the same boat. I have ...
0
votes
2answers
2k 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.
1
vote
1answer
48 views

Differntial back up: what happens to existing data?

Ok, this is a question borne purely from paranoia. But i want to ask all the same. If I make changes to a db, and then apply a differential back up at a later date, what happens to the changes I ...
0
votes
2answers
128 views

Creating joined rows from multiple tables on date field

I need to be able to take Table A and find the records that match in Table B and Table C based on its ID and add them together. The trick is that I only want to add the results from Table B and Table ...
-1
votes
1answer
59 views

System.Data.SqlClient.SqlException when try to run simply queries

I've installed SQL Server 2012 Express. I'm doing some test with this and Server 2008 Express. With 2008, all is ok. But with the 2012, I get some troubles. I'm using it from my c# app, with code ...
0
votes
1answer
68 views

How to lift a nested windowed query from a left outer join?

So... I came across the following view. Aside for table design (some names and variables changed to protect the innocent), joins etc. I would like to simplify the query by lifting the two nested ...