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

learn more… | top users | synonyms (2)

4
votes
2answers
1k views

Join on dm_db_index_physical_stats very slow

I'm running a small query below in a MS Dynamics AX 2012 database and it runs more than 5 minutes which I had to cancel and it is showing PAGEIOLATCH_SH wait type. The database data file is 560GB and ...
10
votes
2answers
198 views

Understanding statistics, execution plans, and 'ascending key problem'

I'm trying to better understand (conceptually) the relationship between statistics, execution plans, stored procedure execution. Am I correct in saying that statistics are only used when creating the ...
2
votes
1answer
124 views

Why can't trigger be dropped from database?

One of the databases I manage is provided by my company's vendor which we restore in order to pull data out for our data warehouse. I'm writing a stored procedure to go through the database users and ...
1
vote
3answers
107 views

Building year to date aggregations using SQL

I need some help with building a monthly aggregation using only SQL. Imagine the following table: TranID DateCode Account Value 1 20140101 1 5 2 20140106 1 -3 3 ...
4
votes
0answers
154 views

What is the cause of this problem with CONVERT()?

Consider the following two statements: PRINT CONVERT(NUMERIC(38, 0), 0x0100000001, 0); PRINT CONVERT(NUMERIC(38, 0), 0x0100010001, 0); Both statements return -1; isn't that incorrect since the ...
2
votes
2answers
335 views

Multiple named Instances with Always On

We have a two-node multi-subnet Windows Server 2012 R2 cluster in two regions. We have three named SQL Server 2012 standalone instances on each node and are in the process of setting up Always On ...
1
vote
1answer
80 views

Best way to document Dwh views [closed]

I works in a company that provide Dwh views as an API for external Customers (We use MS-SQL 2008 & 2012). every once in a while we release new views and updates the existing views. We have ...
1
vote
0answers
31 views

Can I revert to local logging in IIS 7.5 if ODBC fails? [closed]

Environment I have 3 Servers running instances of IIS 7.5 hosting different websites. 1 Server is on Domain A, 2 Servers are on Domain B. I have a one way trust setup from Domain A to Domain B. SQL ...
1
vote
0answers
38 views

How to modify an existing AlwaysOn Availability Group IP

I have an unusual question regarding AlwaysOn Availability Group configuration. I have built out an AOAG cluster on a VM. The build out did not use a Heartbeat network as I had originally requested ...
1
vote
1answer
39 views

Pivoting with NULL Dates

I'm attempting the pivot below: SELECT EmpID as ID, [1] as January, [2] as February, [3] as March, [4] as April, [5] as May, [6] as June, [7] ...
1
vote
1answer
29 views

Remote Access to SQL Server

I have tried everything to be able to connect to my MS SQL Server 2012 remotely. I have read and setup everything like Kyralessa wrote in his answer HERE. Enable TCP/IP done Add 1433 TCP Port ...
0
votes
0answers
18 views

Efficient settings for linked server to SSAS database

We are using OPENQUERY scripts to run MDX from sql-server. We're experiencing efficiency issues when we first run these queries each morning. The linked server objects which link to our SSAS ...
1
vote
1answer
63 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
32 views

My database engine cannot see one of the drives

I installed SQL Server 2012 on a VM that has two harddrives, C and E. I can log into the Database Engine, but when I attempt to attach a database that is on E, Drive E is not available. Only Drive C ...
1
vote
3answers
61 views

Can I install SQL server 2012 on a same machine with SQL server 2005?

I have been using SQL Server 2005 standard for quite a while, and it is working great. Now I need to use SQL Server 2012. Will this effect my existing databases? How much risk is involved in ...
0
votes
1answer
49 views

SQL Server Chain Permission

We have the 2 databases. Reporting HR There are some users who want to extract data from HR database. But we don't want to give direct access to that HR database. So, we created the special ...
4
votes
1answer
3k 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 ...
3
votes
0answers
43 views

SQL Server Management Studio 2012 separated toolbars

In SQL Server Management Studio 2012, is there a way to get the toolbars separated, so there is space between them? I can do this with 2008 R2 by just dragging the toolbar handle. But in 2012, I can ...
1
vote
0answers
76 views

“Skipping the default startup of database” SQL notification for AlwaysOn Cluster

So I set up our SQL servers to notify us of alerts at severity 17 or higher (as advised by various SQL Server articles). I've done this to each node of a SQL Server Always On Availability Cluster, ...
1
vote
1answer
52 views

How to join a table with a table valued function?

I have a user defined function: create function ut_FooFunc(@fooID bigint, @anotherParam tinyint) returns @tbl Table (Field1 int, Field2 varchar(100)) as begin -- blah blah end Now I want to join ...
2
votes
2answers
202 views

Is it possible to define a function within a stored procedure?

I have a stored procedure: create proc sp_MyProc(@calcType tinyint) as begin -- some stuff collating data into #MyTempTable if (@calcType = 1) -- sum select A, B, C, CalcField = ...
2
votes
4answers
161 views

Identical tables, identical columns, identical records, but why different record sizes?

Identical tables, identical columns, identical records, but why different record sizes? USE [test] GO CREATE TABLE [dbo].[mybit]( [col1] [bit] NOT NULL, [col2] [bit] NOT NULL, [col3] ...
1
vote
1answer
91 views

SQL Server - Log Shipping Into an AlwaysOn Availability Group

I have a requirement to build an AlwaysOn Availability Group, which I have done. Now the user wants me to set up log shipping from another server into the AlwaysOn Availability Group. I’m not sure ...
0
votes
1answer
34 views

How to get the row number where its ID is 'n' and rows are ordered by some column?

I'm trying to get the row number, knowing the row ID, smomething like this: Get the row number (position) from Table1 order by Col1, where this row ID is 263 ID | Col0 | v Col1 | Col2 ...
0
votes
0answers
16 views

Regarding checkpoints

We have been stuck with some vendor companies who designed and created some of our front end applications, but are not able to help us when we have problems. The latest being, they asked us to run ...
0
votes
2answers
82 views

good strategy for big tables and archiving in sql server 2012

I have a table with roughly ~500,000 rows a day getting added into it. The database supports an OLTP system. The make up of it is a bit like this: create table Stuff (StuffID int identity not null ...
0
votes
1answer
20 views

Find out CPU usage by reporting server

Is there a way to found out what is the CPU usage of the reporting server? Sometimes the CPU usage is rised up by 40% percent for several seconds. I think the reporting server is causing it but I am ...
4
votes
1answer
156 views

Get minimal logging when loading data into temporary tables

After reading The Data Loading Performance Guide I am unsure if its necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index in order to get minimal logging. ...
0
votes
1answer
47 views

delete WSFC 2012 cluster role/group fails

PS C:\Windows\system32> get-cluster Name ---- sql2012poc_cl1 PS C:\Windows\system32> get-clustergroup Name OwnerNode State ...
1
vote
3answers
276 views

Incorrect query results for execution plan, unsure of exact cause of problem

We had a query yesterday that was intended to page through results but on the first page of results the query only returned 4 records instead of the expect 25. Changing the query in any way resulted ...
1
vote
0answers
26 views

Datamining using holidays

My setup: I am currently running a SQLServer 2012 database paired with a SSAS. My database features sales per store at specific times. My goal: I am interested in predicting future sales (amounts) ...
1
vote
2answers
103 views

SQL Server 2012 Table Partitioning - Have I done it correctly?

I have a table that stores accounting transactions for what we call "jobs". The table looks like this: ( REGION char(3) JobNumber char(17) SeqNum decimal(5,0) ..... ..... ..... AcctYear decimal(4,0) ...
4
votes
2answers
215 views

Skip sections that have GO

I'm working on a script I can run on new servers to do some initial configuration. I would like to be able to skip some sections but the problem I'm running into is the "GO" that's required. Because ...
0
votes
0answers
33 views

Create Temporary Index for bulk operation and Delete after its completed

I am moving bulk data to archive tables. Some times query returns up to 4.5million records, which takes about 2:15 minutes for a table of 40 columns. I want too increase the performance for the ...
1
vote
1answer
57 views

Wait for an AlwaysOn replica to be synchronized in Powershell

I am working on a disaster recovery script. One part of it is not being consistent. This is the code: ... ## Switch replica to Synchronous Set-SqlAvailabilityReplica -AvailabilityMode ...
2
votes
1answer
521 views

How to allow to execute stored procedures but not writes?

I would like to set up a "reading" user on my SQL Server 2012 instance. I would like to allow him the right to execute any stored procedure, or function, or direct SQL statement, which retrieves data ...
0
votes
1answer
632 views

Create relationship between two tables with composite primary key

I've created a table [t1] with two fields and a composite primary key, show in below: create table t1( id int, name nchar(10), primary key(id,name) ); I want to create a second table ...
1
vote
1answer
89 views

Index optimization on a view using a full outer join

Table structure: Foo FooChild Bar --- -------- --- ID ID ID Date FooID Date ...
0
votes
2answers
79 views

Setting for default column type

Is there a setting in SQLServer 2008 and newer for default data type of date-time columns, i.e. datetime or datetime2? Actually more like something that would tell SQLServer to use datetime2 instead ...
1
vote
1answer
161 views

Does “Synchronous-Commit Availability Mode” ensure consistency between Replicas?

If one refers to the MSDN documentation, on Synchronous-Commit Availability Mode, you may read: Under synchronous-commit availability mode (synchronous-commit mode), after being joined to an ...
1
vote
1answer
46 views

Which Indexes in which situations is more efficient in SQL Server 2012

I use SQL Server 2012. I want know when I want to know in what situation I have to use CREATE NONCLUSTERED COLUMNSTORE INDEX .... and in what situation I have to use Create NonClustered Index... ...
0
votes
0answers
27 views

Database Read Only after Merge Replication

Win Server 2012, SQL Server 2012 Std. I have a previously accessible database with which I'm testing merge replication and after I publish the DB, my application is saying the DB does not have write ...
0
votes
4answers
51 views

Setting Select Permission on 100+ tables in a database

I got the 'Select permission denied' error for some of my tables in a database. In MS SQL Server 2012, setting Select Permission on 1 table is easy. I just right click on the table > Properties ...
0
votes
2answers
61 views

SQL Server: Enable Full-Text Search on model Database

I want to use the Microsoft Data-Tier Application framework and its associated .Net API to manage the schema of several SQL Server databases, including the model database. However, when I try deploy ...
3
votes
2answers
265 views

SQL Server 2012 Standard Edition using more than 64GB of memory, why and how?

I put SQL Server 2012 Standard on a virtual machine (VMWare) which has roughly 98GB of Memory. Now I checked the query below how much memory is taken by SQL Server. Microsoft SQL Server 2012 - ...
0
votes
2answers
244 views

How to import DB with .sql extension into SQL Server 2012?

I have a database with .sql extension which is exported from server using putty. How to import this into SQL Server 2012 in Management Studio? It is not accepting .sql as database, throwing error. ...
1
vote
0answers
117 views

SQL Replication With Always On Availability Group

We have an always-on SQL 2012 availability group with two replica servers and an AG listener. Doing merge replication via anonymous subscriptions through IIS between SQL and multiple remote clients ...
2
votes
1answer
75 views

Run SQL Server agent job in a particular resource governor pool

I read Considerations for Writing a Classifier Function on MSDN. The only way I see possible is to create a separate user account for each resource governor pool and to classify sessions based on that ...
9
votes
3answers
274 views

What is a deterministic method for evaluating a sensible buffer pool size?

I'm trying to come up with a sane way to understand if the max server memory (mb) setting is appropriate (either should be lower, or higher, or stay the way it is). I am aware that max server memory ...
1
vote
0answers
62 views

Non clustered indexes

I have a questions on Index Our Application is throwing the following SQL eroor Index 'INDEXNAME' on table 'dbo.TABLENAME' (specified in the FROM clause) does not exist. Error: Unknown Error ...