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

learn more… | top users | synonyms (3)

3
votes
2answers
63 views

Fill in missing dates with data value from previous populated date for group

Picture help desk tickets that gets transfered between departments. We want to know what the department is at the end of the day for each ticket for each day that the ticket is open. The table ...
5
votes
2answers
183 views

Always On Availability Group, Always redirect user to read only instance

We have an Always On availability group with a primary and a read-enabled secondary. We have a user for the implementation team who uses the database to check the correctness of the data that they are ...
2
votes
3answers
64 views

Vendors with Most Invoices in State

I have the tables Vendors(VendorName, VendorID,...) and Invoices(InvoiceID,...) and I am trying to find vendors with the most invoices in their respective states. Please critique my work: SELECT Max(...
0
votes
0answers
28 views

SQL Server Collation conflict

The problem is, one of the server had wrong collation, so I tried fixing it by using sqlservr -m -T4022 -T3659 -s"" -q" SQL_Latin1_General_CP1_CI_AS". It worked perfectly, it fixed all the system ...
0
votes
0answers
81 views

SQL query too slow to fetch single row of data

we have this SP, running extremely slow to fetch a single row of data from tables within 100K rows, which seems not that big. It takes onn avg 15-20 mins to complete. We have tried to remove all ...
3
votes
0answers
57 views

Why would Stolen Memory counter be at a constant high? [duplicate]

I'm trying to understand memory stats a lot better and have been troubleshooting an issue with one of our SharePoint DB servers for a while now. Low PLE, frequent lazy writes, slow performance etc I ...
6
votes
2answers
14k 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?
8
votes
2answers
588 views

Are the cost percentages in this SQL Server plan over 100% for a valid reason?

I'm looking through the plan cache, looking for low-hanging optimization fruit and came across this snippet: Why are many of the costs listed above 100% ? Shouldn't that be impossible?
0
votes
1answer
110 views

User databases inaccessible after physical restart

We have an unusual error happening on the SQL Server. After restarting the physical server where the SQL Server instance is installed, the user databases are not accessible. System databases are ...
2
votes
1answer
56 views

SQL Server Always ON

We have a SQL Server 2008 R2 cluster with 2 node and total data is about 2 TB now that cluster is log ship to 4000 Miles another data center. I know about a 2 years back we have mirroring setup ...
2
votes
1answer
36 views

SQL Server 2012 In-Place Upgrade and Timing of Database Version Change

We are updating several SQL Server 2008R2 instances to SQL Server 2012 SP3 CU2. I know that the database versions will be changed from 100 to 110 during this process (e.g. once finished, the databases ...
2
votes
1answer
3k 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
0answers
17 views

Is it possible to configure exceptions for SQL Server Agent tasks?

I use SQL Agents tasks to execute my backup, maintenance & ETL tasks. IT would like to have a regular maintenance window to apply server patches. They're suggesting the 3rd Tuesday of every month ...
2
votes
1answer
6k views

Domain Admins can't connect to SQL Server

I have a Windows Server 2012 R2 domain controller and a database server (W2K12R2/SQL Server 2012). As allowed usergroup I have given the group "domain-admins" sysadmin rights. But somehow any domain-...
6
votes
3answers
10k views

Connect to SQL Server 2012 from an Windows XP client

We have an SQL Server 2012 Standard database, to connect to it we use SQL Server 2008 Management Studio (since 2012 SSMS is not supported on Windows XP). The problem is that SQL Server 2008 gives all ...
2
votes
0answers
37 views

Multiple transaction logs and preferred order of use

Here's my situation: I have a SQL Server 2012 installation with a relatively small (185GB) SSD volume, and a large (3TB) RAID10 spinning disk volume. I currently have tempdb data and log files on ...
1
vote
2answers
27 views

SQL Server Cluster + Availability Groups?

I'm actually finding a way to provide a good High Availability and Failover solution while I have a way to manage some sort of horizontal scalability and Load-Balancing... I actually have a SQLServer ...
0
votes
0answers
74 views

automate import and export of process EXCEL -> SQL SERVER - > EXCEL without using SSIS

I have an ETL process that is entirely written using T-SQL scripts. The script grabs an Excel spreadsheet and imports it using openrowset. Once I get the transformed data I copy with headers and paste ...
3
votes
1answer
24 views

Error When Trying to Run MDW Data Collector

I have SQL Server Management Data Warehouse (MDW) set up across multiple instances, all in the same way with the same accounts and permissions. On one server I get an error when trying to run the ...
5
votes
2answers
73 views

Stable triangle relationship?

I have a table that represents a specific type of action. Let's call it [ACTION]. An action can be performed by a user ([USER]), so I have the relation [USER] 1:N [ACTION]. The user can have ...
0
votes
1answer
1k views

Where can I get the master.dacpac file for SQL Server 2012?

I am using Visual Studio 2012 Professional with SQL Server Data Tools with a SQL Server 2012 standard edition database instance. I have been unable to locate a master.dacpac file on either my ...
7
votes
1answer
242 views

Is there a way to see how SQL Server 2012 defines new identities?

On Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64), is there a way to see what mechanism SQL Server uses to calculate the new identity values for tables created by SELECT INTO? SAMPLE DATA -- ...
6
votes
1answer
41 views

Update an Indexed View “Online”

Say I have an indexed view that looks like this: ALTER VIEW dbo.MyIndexedView WITH SCHEMABINDING AS SELECT ord.SomeColumn, COUNT_BIG(*) AS Count FROM dbo.Ordered ord WHERE ord....
0
votes
0answers
27 views

Creating clustered primary key index with ONLINE option

I have primary key clustered index which is constantly modified (insert and update) and read (select). Now, in order to change one of the types of the columns from which it is composed I need to re-...
1
vote
1answer
100 views

Proxy for MDW data collection on remote servers - SQL Server 2012

I am trying to setup data collection (for disk usage) from remote servers and upload it to my MDW database. I understand I will need a proxy set up for the agent to do this but which subsystem to I ...
4
votes
1answer
50 views

Scripting Microsoft Data Warehouse Wizard

I am trying to automate the MDW setup process in my environment. Currently I have everything complete except for enabling the data collectors (usually uses a wizard as seen ). Has anyone seen or ...
1
vote
1answer
44 views

Create a stored-procedure to insert multiple rows on a junction table and other tables

I am new to Databases and Design and needs some help on a INSERT issue. I want to create a procedure that will insert multiple values to different tables, I need to make sure it stays atomic so I need ...
0
votes
1answer
15 views

Combine addresses missing Unit/Suite #

I have a list of addresses which need to be updated in SQL Server. How can I go about adding the Suite # to the addresses which are missing it? Address 1 Burton Hills Blvd, Suite 250 1 Burton Hills ...
6
votes
3answers
459 views

Would a table benefit if it was a heap

I've a logging table with approx 1.500.000 rows, the primary key is an ascending identity and the clustered index is on the primary key. The identity value is auto-generated => records are always ...
3
votes
0answers
92 views

Rebuild index online without risk to other requests

We have a SQL Server 2012 Enterprise Edition database whose indices fragment very quickly. We use a maintenance plan that reorganizes those indexes daily, and rebuilds them weekly. The rebuild ...
5
votes
2answers
109 views

How to find the specific file in a filegroup in which an object resides

Today, I encountered below error on one my SQL Server which I manage. Could not allocate space for object '%.*ls'%.*ls in database '%.*ls' because the '%.*ls' filegroup is full. Create disk ...
7
votes
3answers
258 views

Execution Plan is NOT using INDEX, It uses Table Scan

I know when it comes to using an index or a table scan, SQL Server uses statistics to see which one is better. I have a table with 20 million rows. I have an index on (SnapshotKey, Measure) and this ...
6
votes
2answers
438 views

SQL Server 2012 catalog.executions to sysjobhistory - any way to join them?

I have exhausted my resources and can't find a foolproof way to join the ssisdb.catalog tables to the jobs that run them. Trying to write some custom sprocs to monitor my execution times and rows ...
1
vote
2answers
49 views

Primary keys, clustered indexes and partitioning

We store data for financial records, splitting it up over 4 tables. I'm at a bit of a loss as to what's optimal for our setup here, or at least which direction to head in as I'm getting a lot of ...
4
votes
1answer
62 views

Buffer Cache Size when Page Life Expectancy Drops

I'm troubleshooting an issue with one of our SharePoint databases, essentially huge drops in PLE which I think are caused by increased I/O activity (I see increased reads, writes and lazy writes at ...
0
votes
0answers
83 views

SQL Server not using all the memory

We have a server with 32 GB of RAM, Windows 2012, 64 bit and SQL Server 2012. SQL Server is not consuming all the memory available on the server. Max and min settings are in place. No resource ...
2
votes
1answer
168 views

App Domain unloaded when using GDAL/OGR ogr2ogr to insert many Geometry records

SQL Server 2012 x64, Total memory allocated 2GB, set to low value to enable us to reproduce "AppDomain unloaded" issues much quicker Windows Server 2012, Total Memory on Server 16GB (in production, ...
3
votes
1answer
15k views

SQL Server 2012 installation failure An error occurred for a dependency of the feature causing the setup process for the feature to fail

I am installing SQL Server 2012 Developer (from ISO image written on DVD), my machine has Windows 7 (Edit: Windows 7 with service pack 1) and it already has SQL Express and SQL 2008 R2 Express ...
2
votes
1answer
402 views

Having 2 identities in a table

I need to have some how 2 identity columns in one table. One that is normal identity and another one that will reset yearly. I have done the reset part with the help of DBCC CHECKIDENT ('TestTable', ...
6
votes
1answer
573 views

SQL Server instance using only a few hundred MB. Well below the minimum server memory setting

I've got two SQL Server 2012 instances running on a server with 32GB of RAM and running little else. One instance is behaving normally. Its working set memory is running close to its commit which is ...
1
vote
1answer
5k views

AWE memory usage growing with SQL Server 2012

I've got a problem with my SQL Server 2012 installation on my Windows 2008 R2 x64 box. Because my memory seemed to be rapidly disappearing (according to the Task Manager graph), but I couldn't see ...
0
votes
1answer
106 views

SQL Server 2012 doesn't use enough memory

Under heavy Load, my SQL Server database is executing queries really slowly. I have 96 GB of RAM and my CPU is high (100%) but my physical memory usage is at 9%. I looked at the SQL Server memory ...
0
votes
1answer
40 views

Error when trying to start Mirroring

Thanks for looking at this, and thank you in advance for any answers! I am attempting to start a mirroring session, but I am getting an Error 1418, which basically states that the TCP port doesn't ...
2
votes
1answer
522 views

SQL Server Mirroring can't connect to mirror database

I am at my wits end with this. I am trying to set up mirroring between a development server and my local machine. The error that keeps coming up is: Msg 1418, Level 16, State 1, Line 1 The ...
0
votes
1answer
22 views

DR site SQL Server DB server not need Quorum Disk?

We have two SQL Server DB Servers and Shared disk in production site and 1 DB in DR Site. Production site has Quorum Disk on Shared Storage. Production site DB servers can access this share storage, ...
6
votes
0answers
104 views

SQL Server Sharding strategy optimization

I have implemented a sharding solution for our SQL Server databases as per the MSDN documentation here: https://msdn.microsoft.com/en-gb/library/dn589797.aspx This has been running for around 6 ...
0
votes
0answers
34 views

SQL Server - “…failed to retrieve text for this error. Reason: 317” error on FILEGROUP

I log in on my local SQL Server 2012 via Windows Authentication. If I try to execute: CREATE DATABASE MediaDB ON PRIMARY ( NAME = FileStreamDB, FILENAME = 'C:\temp\FS\MediaDB.mdf'), FILEGROUP ...
0
votes
3answers
80 views

Syntax on Changing return String by Concatenation?

I successfully created the scalar function FN_TotalBilled in SQL Server, which gives me the total amount invoiced to a company using tables Invoices(InvoiceID, InvoiceTotal,..) and Vendors(VendorName, ...
3
votes
1answer
67 views

Correlated subquery to find total larger than state average

I have tables Vendors (VendorName, VendorState,....) and Invoices (InvoiceID, InvoiceTotal,...). I want to get the Invoices (as InvoiceId) that are larger than the Avg state InvoiceTotal. I know I ...
1
vote
0answers
30 views

How can I create a Web-service to share data from SQL Server?

Our organization has an externally hosted website that displays data from a monitoring system. Data is currently provided by FTPing csv files. The data is stored in a SQL Server Database. The ...