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

learn more… | top users | synonyms (3)

4
votes
2answers
380 views

Outer self join filters vs subqueries

I have a table storing information about tickets. There is one record when ticket is sold and another when ticket is used. There is a column called TransType which is set to either 'sold' or 'used' ...
6
votes
2answers
172 views

Does table size matter?

I have a table like this (with more columns though): CREATE TABLE [dbo].[MyTable]( [SnapKey] [int] NOT NULL, [SnapDt] [smalldatetime] NOT NULL, [Company] [varchar](4) NOT NULL, ...
0
votes
0answers
29 views

I do not see much performance between implicit & explicit transactions

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR); IF OBJECT_ID('tempdb..#wait') IS NOT NULL DROP TABLE #wait GO /************************************* ** Create a test database ...
0
votes
0answers
10 views

Workload Capture

Hopefully this is a straight forward question. But if I want to capture a workload then all I need to capture are: RPC:Completed SQL:BatchCompleted I don't need to capture SP:StmtCompleted do I, ...
2
votes
1answer
124 views

What is the best way to import this non-standard text file into SQL Server?

I have a text file that is generated daily. It is a fixed width format with one frustrating exception. The third column (NAME) has a variable width. The column is always exactly as long as the longest ...
0
votes
1answer
38 views

sql server 2012 Express bulk-insert fialed due to message Operating system error code 5(Access is denied.)

I used my personal laptop computer (Samsung RV520) OS: Windows 7 home premium (x64 bit) SQL Server 2012 Express version 11 I encountered an error when execut the code below. error message: Msg ...
0
votes
1answer
52 views

Duplicates when checking for two different column values based on a key that is not unique in table

I want to check to see if two orders were placed for one person within an hour of one another. Example data PERSONID | proc_code | TIME 123 | 1 | 4/25/2016 00:01:00 123 | 2 | 4/25/2016 00:01:00 123 ...
0
votes
0answers
26 views

Unable to Remove Filestream Filegroup and Associated Database File

I had 5 FileTables which I have since converted to regular tables with varbinary(max) columns instead, migrated all the data from the filetables to the new tables and dropped the filetables ...
0
votes
1answer
37 views

Sql service account password change for environment on Availability groups 2012

My current environment is on Availability groups sql 2012 with 1 Synchronous and 1 Asynchronous replica. Soon we need to change the sql service account passwords. Can any one suggest to me do I need ...
3
votes
1answer
33 views

After processing complete, SSAS DB slowly consumes all available space on disk, w. high memory use also

This problem is with an SSAS 2012 SP3 (11.0.6020.0) multi-dimensional server, hosting a single cube database, containing 1 cube and 16 dimensions. Aggregations have been designed using SQL Management ...
2
votes
1answer
376 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', ...
1
vote
1answer
22 views

Transactional replication using articles filters and delete operations

I have a problem with my transactional replication when I use articles, filters, and delete operations. When I insert the new data into the publisher all the data that meets the specified filter is ...
0
votes
2answers
57 views

How can I return top 10 rows from each state [duplicate]

How can I return the top 10 rows for each category? Say I have a table named xyz. It has a column called state, in the political unit sense of the word. I want the top 10 rows from each state. Do I ...
0
votes
1answer
47 views

Copying data from two tables into one using INSERT in SQL 2012

I've been trying to copy data from two tables into one using the INSERT statement. The tables are from the same database and have the same numeric types and I only need to copy one column from each ...
7
votes
3answers
247 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 ...
1
vote
2answers
40 views

Can we have dynamic SQL jobs in SQL Server Availability groups

We need to push all sql data changes to our local caching infrastructure in real time. We are using SQL Server 2012 Availability Groups. In the process of avoiding triggers for population SQL Service ...
0
votes
0answers
37 views

Log Shipping Backup Job Stuck at In Progress for several days

I have a SQL 2012 server with Log Shipping for 6 databases to a secondary server on the same network, however when checking SQL Agent for the backup job this is still at In Progress for all 6 ...
2
votes
1answer
54 views

Predicate re-write in where clause

How can I rewrite where col % 5 = 4 in SQL Server. The reason to rewrite this is because I am selecting some columns from the table based on this condition. Because of % used in where it is causing ...
1
vote
1answer
78 views

Include scripts for Indexes on Alter Views

After a Developer altered an Indexed View to add a comment, the clustered index that was created on it was dropped with no warning. This caused a failure as there are a couple of procedures that have ...
0
votes
0answers
53 views

SQL Server 2012 service will not start after each server reboot - corrupt SQLBOOT.DLL

When I tried to start the service manually from a command prompt, I get a dialog stating: Your SQL Server installation is either corrupt or has been tampered with (Unable to load SQLBOOT.DLL (HRESULT ...
1
vote
1answer
44 views

Add New Cluster node

I have a Windows Server 2012 Failover Clustering Instance, hosting a SQL Server 2012 Enterprise Cluster, via NODE1 and NODE2. I need to add a new read-only Replica to this cluster: NODE3, which is a ...
0
votes
3answers
60 views

Backup Error with Msg 3201, Level 16, State 1, Line 1 & Operating system error 3

When i am taking the backup in network share drive with mention below TSQL. Use DatabaseName Go Backup Database DatabaseName To Disk=N'H:\DatabaseBackup\DatabaseName.bak'; Go I am getting the ...
0
votes
1answer
51 views

Scalar UDF causing performance issues in SQL Server

I have a scalar UDF doing some string manipulations. When I use it in select clause it causes performance issues. I read that if we do not query tables or views in scalar functions it won't affect ...
-1
votes
1answer
59 views

Patching SQL Server 2012

If I apply Cumulative Update 3 in SQL Server 2012 SP1, do I still have to do Cumulative update 1 and 2 before I can apply CU 3? And also, how would you know what CU's or hot fixes that has already ...
7
votes
1answer
117 views

Should I disable HyperThreading

BACKGROUND I've recently been looking into some fairly high CXPacket wait times recently which has had me using SQL Sentry to monitor processor activity pretty closely. One thing that I've noticed as ...
1
vote
2answers
49 views

Database logging optimization and maintenance in SQL Server

One of the Web applications I am working on, uses NLog to log debug and error context information in the database. Basically, it performs a moderate number of inserts (I appreciate some tens of ...
3
votes
1answer
44 views

What is the impact of temporarily setting ALLOW_PAGE_LOCKS ON

The MSDN ALTER INDEX page says "An index cannot be reorganized when ALLOW_PAGE_LOCKS is set to OFF." I'm modifying our index reorg/rebuild process to be sensitive to indexes that have ...
0
votes
0answers
26 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 ...
2
votes
1answer
29 views

Windows Authentication fails with “Cannot generate SSPI context”

We have a Windows Server 2012 with SQL Server 2012 instance. Its services used to run under an Active Directory account from an old domain, from Windows 2000 Server. A few years ago we replaced our ...
1
vote
1answer
31 views

no free memory but low total memory

I have SQL Server 2012 SP2 that has a max memory of 25.4GB but SQL Server is only using 15GB. When I check free memory with the PerfMon SQLServer:Memory Manger Free Memory (KB) counter only 40MB is ...
1
vote
1answer
30 views

Accessing file share from ETL package in SQL Always-On configuration

How can I access the file share from ETL? Here are my environment details: I have 2 nodes in a windows cluster. node 01 and node 02 SQL Server 2014 is installed on both the nodes. Both are Windows ...
3
votes
1answer
61 views

What is the SQLCLR wait type in Activity Monitor within SQL Server Management Studio (SSMS)?

When I check our SQL Prod server, every time I open Activity Monitor I always see SQLCLR on the first list on the resource wait section in the Activity Monitor. It always has a high number. What is ...
2
votes
1answer
35 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 ...
4
votes
1answer
230 views

Partition by ascending and descending performance

I have a table, and for a given set of fields a, b and c, I need to get the first and last rows ordered by d and e, and am using ROW_NUMBER to get these rows. The relevant part of the statement is... ...
0
votes
1answer
55 views

Upgrading SQL Server 2012 Enterprise SP1 (11.0.3000.0) to SP2 or SP3

We need to upgrade our production SQL Server 2012 Enterprise SP1 (11.0.3000.0) to SP2 or SP3. So which one would be more stable out of those 2 service packs and also how could this be achieved with a ...
2
votes
2answers
88 views

How to fix a messed up replication on MS SQL Server

I restored a database from a backup. The database uses replication to publish to a different server. Assuming the database restore would break the replication, I tried to delete the replication and ...
4
votes
2answers
94 views

Is there a way in SQL Server to make a table only able to insert by trigger?

The title is pretty self explanatory but if your'e curious as to why I want this; I want this because I have an archive/log table that stores past values of an active table and due to this I don't ...
1
vote
1answer
66 views

Queries not Running in Prod [duplicate]

I've an update statement which uses 2-3 tables. It runs in less than a second in QA but it takes forever in Prod. The number of records are exactly same and all the indexes are same too. In fact Prod ...
4
votes
2answers
219 views

Why does removing these LOWER calls change the execution plan like this?

While troubleshooting a slow query I was misled for a while by the actual execution plan telling me a sort late in the query where there should be few rows was taking >80% of the query time. At the ...
2
votes
1answer
48 views

SQL Merge Statement Spending Large Amount of Time Sorting on Primary Key

We are looking to use the SQL MERGE statement in SQL Server 2012 to handle data replication for a data warehouse that we have access to from the manufacturer of a 3rd party application we use for data ...
3
votes
1answer
86 views

MEMORYCLERK_XE Memory Usage Will not Clear

We've been having an issue where memory usage from Extended Events grows to over 1 GB and then will not clear - even when we stop all XEvent session on the instance. This hasn't been an issue yet on ...
2
votes
2answers
41 views

Cannot see new drive in SSMS maintenance wizard

I'm trying to setup a Backup Maintenance Plan in SSMS for Sql Server 2012, running in a failover cluster on Windows Server 2012. The current plan backs up to the same cluster volume where the ...
2
votes
1answer
62 views

Difference size DBCC Page and DBCC Fileheader

I'm trying to read the current database size using dbcc command. In my test I execute following commands: DBCC Fileheader('tempdb',1) => size = 988152 DBCC Page('tempdb',1,0,3) => size = 5112 ...
4
votes
1answer
132 views

Cross Apply Fails When DateTime Added

In trying to achieve a Pivot-ed goal for a two row data table into one row, my first thought was to use a Cross Apply. By using the Cross Apply one can identify/generate each of the rows with a ...
1
vote
1answer
56 views

Stored procedure running for a long time

We have a stored procedure which is running for ever, whereas when it is run as script it takes just couple of minutes to complete. When I just declare the params and assign values to them and run the ...
4
votes
4answers
281 views

Easy way to change Column ID (primary key) of a table to Auto increment

I started to use SQL Server recently and I still don't know the best way to do some things. I created all the tables with a column ID as the primary key. Now when I try to insert values I get the ...
1
vote
2answers
63 views

SQL Join problem [closed]

I have a problem trying to join two tables TableA +----------+--------------+ | PersonID | customerId | +----------+--------------+ | 123 | 456 | | 258 | 145 | | ...
1
vote
1answer
32 views

Monitoring decimal values on Perfmon using User Settables

I am currently using sp_user_counter1 to store database information on Perfmon, which can be recovered by AWS CloudWatch, but the values are Decimal, and the sp_user_counter1 parameter supplied must ...
3
votes
2answers
123 views

What is considered for output from an sql server job step?

In short: It seems I only get PRINT messages to my SQL Server job history. What is the definition of valid history output? Background: Just executing a statement like UPDATE product SET ... will ...
3
votes
1answer
39 views

Changing database name based on project environment

I have three environments for our BI project (DEV, QA, PROD) and two databases in each environment, source(EDB) and target(EDM) DEV environment: Source = EDB_DEV Target = EDM_DEV QA environment: ...