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

learn more… | top users | synonyms (3)

0
votes
1answer
35 views

Default schema is ignored on SELECT

According to my understanding, a default schema should apply when issuing SQL statements on tables using unqualified table names. My problem is that the following statement fails, because the table ...
2
votes
1answer
63 views

Troubleshoot corruption in SQL Server Database [closed]

I have been asked by my interviewer "How do you troubleshoot corrupted data in SQL in SQL Server 2012?"
0
votes
0answers
33 views

Index Reorganize Generates a Non-Primary Filegroup is Full Error

First chance at a new job to do some much needed index maintenance on a system. 2-node cluster, W2K12R2, 512GB RAM, 64 logical cores, Fusion-IO, NetApp SQL Server 2012 Ent SP1 3.5TB db 4 ...
1
vote
0answers
33 views

Plan guide validation with fn_validate_plan_guide gives false positives

When validating a plan guide for a piece of SQL in a stored procedure that references a temporary table named "#test" the function fn_validate_plan_guide returns the error: Invalid object name ...
2
votes
1answer
235 views

SQL Server: how to troubleshoot login timeout expired error

I have a production SQL Server 2012 (SP1) running on Windows Server 2008 R2 Enterprise edition. A lot of time while connecting to this server from a remote host I to face connection timed out error. ...
0
votes
1answer
78 views

Create an alias for a SQL Server named instance pointing to sql default instance

I have an installation of SQL Server 2012 Std Ed on Windows 2012 R2 running in a default instance, and an app that wants to connect to it using a named instance (can't be changed) E.g. ServerName - ...
0
votes
0answers
61 views

Help diagnosing a single-table, single row deadlock

We're currently using an ORM-controlled, table-based, identity generation scheme to generate the IDs for our entities. The table is as follows: create table id_seq ( tbl varchar(255) not null, id ...
1
vote
1answer
139 views

Find max and min date over multiple fields of the same record

I have a table with 4 distinct data-type fields. For each record, I need to find the max and min date over them. That's easy to do for a set of records over a unique field. But how to do it in my ...
0
votes
1answer
31 views

How do I go about creating a script to compare , update and match structure of two databases

I am comparing two databases using Opendiff which is similar to sqlCompare. I have two databases which should match in structure so as I compare I would like to construct a script such that if the ...
-1
votes
1answer
20 views

cannot consistently view or drop a trigger

I have a database trigger called ddl_refresh_views that provides dynamic binding for views (if you alter view V, it refreshes all the views dependent on V, and all the views dependent on those views ...
1
vote
2answers
39 views

I changed the max memory setting but I don't see memory usage rise at OS level

We have increased the RAM capacity on the OS to 40G and I have changed the max memory parameter to 36G in SSMS. When I select "current value", it shows 36G. However, monitoring memory usage at OS ...
1
vote
1answer
88 views

Copy Data between two databases

I have the following situation. An application writes logs into SQL Server 2012 Standard edition database. The incoming data are too big to leave in the operational DB because the application also ...
0
votes
1answer
27 views

SQL server 2012 Enterprise [duplicate]

Will CDC work with SQL Server Always on Availability groups? Do you need to set up CDC on both/multiples nodes if using Availability Clusters?
2
votes
2answers
35 views

Secondary reporting SQL Server

I have a SQL Server 2012 Std cluster that has a lot of writes going on. We'd like to offload some of the reporting to a separate (2012 Std) VM. It can be around 24 hours out of date. I think log ...
5
votes
1answer
271 views

SQL Server passing variable to a string in a stored proc

I'm trying to concatenate a custom string with a variable that is an int. 'Investigation is pending for ['+ @investigationidout +'] '. When I hover mouse pointer over the first + sign it says: ...
0
votes
2answers
84 views

Split String Function

I have a question regarding this Split String function Aaron Bertrand recommends here. With it you can extract information between two slashes. Now if I want to extract not only one piece of info ...
3
votes
1answer
72 views

How are precision and scale calculated when decimals are multiplied?

When decimals are divided, the precision and the scale of the result value are calculated due to this formula: max precision = (p1 - s1 + s2) + MAX(6, s1 + p2 + 1) -- up to 38 max scale = ...
8
votes
2answers
138 views

Why is TOP operation in SQL execution plan

After searching for a while, I decided to post this question for lack of finding an answer and apologize if there is a similar question/answer out there. When running the query below on two similarly ...
1
vote
2answers
90 views

Is Database Mail worth the risk / a standard practice

I enabled Database Mail XPs and was testing it on my dev machine. I was just wondering if the DB Mail is a bad security choice, and if it more secure (or standard practice) to send this kind of ...
0
votes
1answer
72 views

Database Restore Not Working

I am trying to restore an empty MS SQL database 'Rental' to a new updated version of it. The backup of the database (named Rental_2) was taken from the 2012 version of SQL Server and I am trying to ...
0
votes
1answer
50 views

SQL Server CASE statement dependent on other rows in result set

I have some data that comes out of a cube. I have one row in this order that has gone in as a substitution variance but I need to put it in the quantity variance bucket. The code that decides on ...
1
vote
0answers
67 views

Backing up / restoring a 20GB database with images stored as binary code

Please excuse me as I am a beginner. I am trying to backup and restore a 20GB database from a SQL Server 2012 to another SQL Server 2014, but I have come across the following issues: The developers ...
1
vote
2answers
56 views

Partial backup restore of read-only file group on new database

Let's say, I have a database myModel2015 that contains two file groups - Primary A and Secondary Read-Only B. Then, I am creating a partial backup of the secondary file group. Now, let's create a new ...
2
votes
2answers
101 views

SQL Job is running slow after a few days

I have a simple SQL job that runs a stored procedure that doesn't take any parameters. The job runs once a day and it normally takes less than 3 minutes to complete. What's odd is that this job runs ...
2
votes
1answer
45 views

Backup/Restore Log File Question

My question is in regards to restoring a DEV database from PROD. When I take a backup of my Production Database, the LogFile is about 10GB. However, when it actually restores to DEV, it blows up to ...
0
votes
1answer
73 views

SQL Server Memory Cap reached its maximum state and threw an Out Of Memory Exception

We have set a memory cap limit of 43GB in SQL server instance, so thereafter it keeps 42 GB near about and keeps near about 1 GB for its processing operations. But then we received near about 500,000 ...
1
vote
1answer
34 views

Reseeded Identity not showing in GUI

I am reseeding my Identity Seed with the following T-SQL: USE DATABASENAME; GO DBCC CHECKIDENT ('dbo.DOCUMENT', RESEED, 400000000); GO I am subsequently checking it went through: USE DATABASENAME; ...
3
votes
2answers
39 views

What data type are the numbers in a SQL Server statistics object?

Specifically I'm most interested in: The 'All density' number in the density vector Numbers in the histogram E.g. are they floats / decimals? What precision / scale / length do they have? The ...
3
votes
2answers
55 views

SQL 2012 Autogrow and file extension problem

I had my data and log files set to auto grow by 10%. This has caused the transaction logfile to grow to an astronomical 600 GB in size and led to the error Autogrow of file PFMultilinguaklPOO_log in ...
1
vote
1answer
38 views

Moving FILESTREAM to another database

Our development organization has created an application employing FILESTREAM, which through the pilot has been incorporated into a schema within our Data Warehouse Staging database. Going to ...
2
votes
2answers
83 views

Shrinking log files on development

I've been looking at getting some disk space back on some of our development machines. These are machines that are not being backed up, not being developed on directly, and not being maintained ...
3
votes
1answer
58 views

How to implement table purge with partition on SQL Server 2012?

I've read this question: How to remove a table partition But it's not what I want. I'd like to setup a table, using partitioning on a date/time column to have 1 month partitions. Afterwards, ...
0
votes
2answers
48 views

sql divide by zero error

I have an equation ((P.RealisedConsumption / (NULLIF((PO.ActualQty * P.QuantityPO), 0) / NULLIF(1000000, 0))) - 1) * 100 AS FibreScrapFactor This works for the example order that I am looking ...
1
vote
0answers
40 views

SQL Server 2012 Merge replication not replicating all changes

Having a problem with replicating a database between two servers, both running SQL Server 2012. We have a table where each site that stores the database updates a record every minute with the current ...
0
votes
1answer
61 views

SQL Server 2012 AlwaysOn performance best practice

Is it best practice to have 2 Primary then 1 Secondary for SQL Server AlwaysOn Availability Groups? My purpose for having 2 Primary instances is to balance the database load. (e.g. my db1 and db2 is ...
1
vote
1answer
52 views

How to Properly shutdown and disassemble SQL Database Replication

While testing SQL Server Replication we setup Replication on our ERP Test DB from SQL Server Enterprise to SQL Server Standard. We have since got replication up-and-running on our Live database and ...
2
votes
1answer
46 views

Availability Groups - Can async replica delay failover of sync replica?

Availability Groups SQL Server 2012. Node 1 and Node 2 in primary data centre - sync mode for HA Node 3 in dr data centre - async mode for DR If a manual failover is performed between node 1 and ...
4
votes
0answers
153 views

Performance inner join vs left join involving parallelism

I have a query which takes about 8 hours to finish. Simplified, the query looks something like this: SELECT [Some attributes, inconsequential] FROM RBM.Financial_Events RFE INNER JOIN ...
3
votes
3answers
190 views

Simple yet problematic update query

I have a rather simple update/query, that has been causing me a lot of grief over the years. in simplest form it is : update VillageSemaphore set TimeStamp = getdate() where VillageID in ...
1
vote
1answer
175 views

Execute Dynamic SQL Generated From Select

A business process in SQL can generate a variable to each operation 1-N number of calls to a stored procedure whose purpose is to insert data related to the main process. How does one execute ...
2
votes
2answers
3k views

Conversion failed when converting date and/or time from character string

I have a problem, because I have been using this query without problem... until now: UPDATE T1 SET ORDINAL = DATEDIFF(DAY, T2.Opening_Date, T1.Date) FROM FactTransactions T1 INNER JOIN DimStore T2 ON ...
3
votes
1answer
134 views

SQLCMD stuck on “set textsize 4096”

I am using SQL Data Compare to generate a script that updates data in a QA database (SQL Server 2012). The script is around 500mb and I added a GO every 100 lines. When I start the script with: ...
2
votes
1answer
156 views

Estimated row size and estimated data size is incorrect

I have a 9 million record table that is about 452MB in size (disregarding NC indexes). Below is physical stats and the columns As a simple example, if I dump the table into a temp table, the ...
0
votes
0answers
22 views

RESTORE VERIFYONLY locks file in Azure Blob Storage

I'm using SQL Server 2012 SP2 to backup my transaction logs directly into Azure Blob Storage using the BACKUP TO URL statement. I am then attemtpting to verify my transaction log as follows: RESTORE ...
2
votes
2answers
41 views

Deleting data directly from backup file

Is it possible to update the data directly inside the backup file (*.bak) in SQL Server 2014? The scenario is that I want to automate the process of providing the "smaller" database to some ...
1
vote
0answers
18 views

Best event to track the DML operations being performed on Server along with the hostname performing those operations?

Currently I'm using the XE event sql_statement_completed to track the DML statements that are being executed and the hostname that is performing the same. However this event gets fired for each and ...
4
votes
1answer
90 views

Are there any reasons to avoid using sp_rename for renaming objects?

In the documentation it is clearly said: Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to rename stored procedures, ...
2
votes
2answers
198 views

How to reference only affected rows in AFTER UPDATE trigger

I have this table: And I'm trying to create an update trigger which will update last_updated_on = GETDATE() and last_updated_by = SYSTEM_USER columns whenever an update is performed on step_number ...
0
votes
1answer
49 views

Upgade from SQL Server 2012 cluster Evaluation to enterprise

What I understand is that there is a restriction of upgrading a SQL Server 2012 cluster from evaluation edition to licensed standard edition. Is there a similar limitation for upgrade from cluster ...
2
votes
1answer
63 views

Seeking help with Extended events behaviour

I'm using Extended Events to track the procedures that are being executed on the server, along with the Hostname of its executor. I've used the following XE script for the same: CREATE EVENT SESSION ...