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

learn more… | top users | synonyms (3)

0
votes
1answer
46 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
36 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
51 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
59 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
49 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
58 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
43 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
28 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
30 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
29 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
59 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
34 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
82 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
76 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
40 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
61 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
130 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
276 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
120 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: ...
0
votes
0answers
21 views

Is there a way to get alerted for stats going stale?

It came to our notice that applications running slow when there is bulk update/insert operation on few tables, randomly on AD-HOC basis. NO fixed date when data will be updated. Thus we see the ...
0
votes
1answer
34 views

Generate backup script of SQL Agent Jobs using c#

I am using C# code to read all the SQL Agent Jobs: static readonly string SqlAgentJobName = "SqlAgentJob_123"; static ServerConnection conn; static Server server; static Job job; try { conn = ...
-4
votes
1answer
32 views

calculate a column based on existing columns [closed]

Simply I want to calculate a formula on specific columns in my table to populate an empty column in the same table using SQL Server.
2
votes
3answers
155 views

How can I create 3 tables with references On SQL Server

I started using SQL Server recently and I think this is simple to resolve. I need to create one table with a country list, and two tables that will use the country list in a column. Here is the ...
1
vote
2answers
53 views

Can't create a table with the same name I deleted before

I started to use SQL Server recently and I think that its simple to resolve I did: 1 - Create table table_nome 2 - Drop table table_nome 3 - I'm trying to create a table with the same name, and ...
1
vote
1answer
32 views

linked servers between availability group to a server

I had three servers(server1, server2, server3) in availability group. And the listener name is "Production". I have a server name "Acceptance". I need to run a script that which will copy the tables ...
2
votes
1answer
41 views

SQL Server spatial geometry properties on index

Is there a way to add geometry.STSrid(object property) to a spatial index or is it already added when I create a spatial index in SQL Server 2012. I have mixed SRID table and I was wondering if it ...
5
votes
3answers
234 views

Shrinking database which is not having any insert/update

We have a process of archiving wherein we take the backup of current database and restore it as xxx archive database. Now this database contains previous data and will not have any ...
0
votes
1answer
23 views

Point in time restore fails - backup file from before the restore point not available (why does this matter)

I have tried to do a point-in-time restore of a database through the management studio gui, the point-in-time being yesterday morning (2016-04-12). This fails with the error "...cannot open ...
0
votes
0answers
23 views

installed 2012 SP3 after fixing missing cached installer files, @@ version shows up as sp1

I was to upgrade stg server running SP1 2012 standard, upon getting downtime on the first try I received multiple errors about missing installer cached files (.msp), which was fixed using Microsoft ...
1
vote
0answers
34 views

SQL Server dynamic inserts based on another table's rows

I've derived the following pseudo-coded script as a generic insert with variables that I want to replace for each row in another table (I can assume that the columns are always the same for the insert ...
0
votes
1answer
80 views

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '*%text%*4' to data type int

I have a database that holds information linking customer data to forms that are filled out by them, or on them by someone else. Our UI runs reports but can only run 1 form at a time. I am trying to ...
4
votes
1answer
69 views

Get a listing of snapshot data files

I need to programmatically drop a database with all of its snapshots and then recreate the database and create a new snapshot. I'm having trouble locating where the snapshot files are located from ...