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

learn more… | top users | synonyms (3)

4
votes
1answer
124 views

Scenario of huge SQL Server Database backup

I use SQL Server and have a huge database with large compressed backup size (about 250 GB). The backup scenario of this database is: Create full backup at the first day of each month. Create ...
6
votes
2answers
175 views

sys.dm_sql_referenced_entities fails to provide used columns list when joining with temp table

I think I need some help as I run out of ideas. In my database I have a simple table like this: CREATE TABLE dbo.Invoices ( CashDiscounts MONEY, Cost MONEY ) GO and a stored ...
0
votes
1answer
158 views

“Unspecified Error” when executing SSIS Package

I am executing an "Initial Load" SSIS package, which truncates all ETL tables first, then re-populates them by pulling data from a remote server. I get the dreaded "Unspecified Error" at a different ...
1
vote
2answers
100 views

How many deadlocks am i really getting? system health or deadlocks/sec?

I'm trying to look at deadlocks on a troublesome server, and when I look at the system_health xel file, I see 80 records for "xml_deadlock_report" in about 16 hours. I assume these are all different ...
0
votes
1answer
194 views

SQL Server 2012: Users cannot see database properties

I migrated a database from SQL Server 2008 to 2012 and also the users. Now the weird thing is that if the user connects to SSMS on the same server as the SQL Server engine, he can display database ...
4
votes
2answers
198 views

Help with query

I have following data. I am trying to get the number of days the project stayed in a particular status. Example below of raw data, I need to be able to get the result shown. Can I get some ...
0
votes
1answer
89 views

DENY VIEW DEFINITION TO PUBLIC

I have a SQL account with no permissions other than having the public role. I have run the following command to deny permissions to the system views: use master DENY VIEW ANY DEFINITION TO ...
0
votes
1answer
93 views

Exploring async DB IO - what is the cause for “The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement”?

Please, observe the following code: static async Task<Result> IOAsync(string duration) { using (var conn = new SqlConnection("server=localhost;database=abc;uid=xyz;pwd=123;Max Pool ...
5
votes
1answer
68 views

Online clustered index rebuild using more log space than expected

I have a table TableA ID PK BIGINT TrxID BIGINT CardBalance INT AccountBalance INT SP_SPACEUSED 'TableA' output: name rows reserved data index_size unused TableA 904054184 ...
2
votes
2answers
1k views

AppDomain 2 (mssqlsystemresource.dbo[runtime].1) is marked for unload due to memory pressure message in SQL Server Log

From last two days I can see following in SQL Server errorlog. AppDomain 2 (mssqlsystemresource.dbo[runtime].1) unloaded. & AppDomain 2 (mssqlsystemresource.dbo[runtime].1) is marked for unload ...
1
vote
0answers
38 views

Disable the Task action button

How can I disable the "Tasks ->" button in SQL Server Management Studio? I need to prevent users scripting objects.
1
vote
0answers
39 views

Date picker is dynamic in SSDT but not in SSRS

Running SQL Server 2012 SP1 and SSDT from same distribution. (VS2010) When I display a report in SSDT the date selector on a date parameter is dynamic or responsive. When you click on the date ...
1
vote
2answers
31 views

Selecting Values That Differ From Section Average

I'm attempting to select data from a table that's already been made containing records of file size as taken every twelve hours. The table looks something like this: surrogate_id | partition_id | ...
2
votes
1answer
78 views

Generate Database Creation Script Within A Stored Procedure

I'm looking to automate a wipe of our test environment to bring it up to be in-line with production at the end of every sprint. As it stands this is currently being completed by the test enviroment ...
0
votes
2answers
815 views

How to replicate data between two SQL Server 2012?

I have two SQL Server 2012 databases running on two different Windows Server instances, I want the Server A (DB1) to be in full sync with the new Server B (DB2). How can I make this connection ...
1
vote
1answer
55 views

storing as smallint, querying as int

A multi-billion-row fact table in our database has 10 measures stored as int columns. The value ranges for some of these columns won't ever be above the +/-32K range of a smallint. To save I/O, we're ...
2
votes
0answers
83 views

Every time I run a stored procedure it takes longer to complete until I restart the SQL Server [closed]

I need to optimize a critical stored procedure running on SQL Server 2012. The idea was to measure how long it normally took to run this procedure, make a few changes, measure the improvement and ...
1
vote
0answers
49 views

Long Transaction On File Upload Locks Strategies

I need to upload files to some SFTP or FTP servers (using libcurl) and register them in database. For this I have several tables -- locations to store files - some SFTP and FTP servers CREATE TABLE ...
4
votes
1answer
119 views

transaction across multiple databases - what is the overhead?

I run the following update, which is one single transaction across 2 databases. Please note I have not committed the transaction. --================================================================ ...
0
votes
1answer
57 views

Sql Server 2012 Failover Replica in Synchronous Mode between Primary-Secondary on VPN is very slow

I have installed SQL Server 2012 Clustered HA with Replica in Sync mode between two nodes connected by an high-speed VPN. The DB is encrypted. Everything works fine, but to do the replication, the HA ...
2
votes
1answer
116 views

Should you encrypt the system databases?

A finding from a STIG evaluation, CCE-19832-5 reports that the [master] database should be encrypted with a Database Master Key using an encrypted password that meets DoD standards. This a result of ...
5
votes
1answer
430 views

CDC system tables missing from backup

I am migrating a database that uses CDC from SQL Server 2008 R2 to SQL Server 2012. There are CDC-related tables under the "System Tables" branch in Object Explorer: cdc.ddl_history ...
0
votes
1answer
143 views

how to calculate the next time a job will run - by each schedule

I have a script that shows me when it is the next time a job(s) will run, and other things, the dates are formatted, etc. the script is here, and it works fine: USE msdb Go set transaction ...
1
vote
1answer
42 views

putting together contents of XML column - from all rows of a table - how to get it done without a “while”?

I have a table called sizeguide with the definition you can see below. I am interested in the SizeGuideXML column, which is type XML. I had a mission the objective was, for each SizeGuideDesc, get ...
9
votes
1answer
1k views

“truncate log on checkpoint” option in SQL Server

Long story but our long term consultants (former employees) wrote a custom script years back (2006 or so) to interface with Tivoli Storage Manager and it appears to be checking for a SQL Server DB ...
3
votes
1answer
162 views

Always fetch 3 records in a particular order

I have a query which gets 3 books (ABC1,ABC2,ABC3) and their details. I need them in a particular order for pivoting so I have a Row number column for the same. People read none/some/all books. ...
1
vote
0answers
90 views

how to minimize the effect of updating statistics on the plan cache?

The issue I am trying to address: After updating statistics there are slow downs in running many of our processes here. then following the scary dba on this link below: ...
0
votes
0answers
49 views

Recovered database files can't be attached

I have recovered my hard drive with my databases mdf and ldf files. When I want to attach them I get a 9004 error: Error: 9004, Severity: 21, State: 1. An error occurred while processing the log ...
9
votes
1answer
267 views

Recursive sql query performance issue [closed]

This is the sixth time I'm trying to ask this question and it's the shortest one too. All the previous attempts were resulting with something more similar to a blog post rather than the question ...
0
votes
0answers
416 views

Reporting Services Dataset - Login failed for user - SQL User

I have two SQL Server 2012 Development Edition machines setup for testing purposes. One is a reportserver and one is a database server. I've configured the report server such that it's database is on ...
7
votes
0answers
168 views

SQL Server 2012 Compatibility with New TLS 1.2 Standards [duplicate]

I'm trying to switch off TLS 1.0 on my SQL Server 2012 server in order to comply with PCI standards. Initially I had some trouble with the SQL Server service not starting. I found help for this ...
1
vote
1answer
103 views

dynamically creating query in a loop [closed]

I"m looping through a table trying to create a dynamic sql query. It appears to work fine except when I'm concatenating results to make the actual statement. Psudo code follows WHILE (Data is in the ...
6
votes
4answers
476 views

How can I Debug a Buffer Issue?

I have a production "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)" that is showing weird buffer and page life expectancy (PLE) symptoms. I am running this every minute on my server (to track ...
0
votes
1answer
426 views

The ALTER TABLE statement conflicted with the CHECK constraint

I need to check datecom is less than datelivr create table Commande ( Numcom int identity primary key, Datecom date, Datelivr date, Codecli int foreign key references clients (Codecli) ) alter ...
2
votes
2answers
71 views

Moving database from one server to another

I have a virtual machine running SQL Server 2012 in Azure and have also generated a script of my database to be run on the SQLSERVER2012 VM on Azure, however I am getting an error "C:\Program ...
0
votes
1answer
1k views

Cannot remotely connect to SQL Server 2012 on azure

I have a SQL Server instance running on a virtual machine in azure, I have tried to connect to it using the public virtual IP address however I get an error saying The server was not found or not ...
2
votes
1answer
198 views

SQL Server AG databases went into 'Synchronizing/In recovery' after upgrading to 2014

We have 3 replica AG setup: 2 replicas are in sync/automatic failover the other(DR Server, different subnet) in asynchronous/manual mode All these replicas were on SQL Server 2012. Recently we ...
1
vote
1answer
107 views

Permissions for sys.database_mirroring in SQL Server

I need to assign permission to use sys.database_mirroring and of course I want to give the minimum amount of privilege. When I looked at microsofts homepage it says: To see the row for a database ...
9
votes
1answer
1k views

Could a null column be part of a primary key?

I'm developing a SQL Server 2012 database and I have a question about a One-to-Zero-Or-One relationship. I have two tables, Codes and HelperCodes. A code could have zero or one helper code. This is ...
1
vote
1answer
358 views

Geo-clustering using SQL Server 2012 Enterprise with AlwaysOn

We are trying to take our current SQL 2012 Enterprise edition 3 node cluster (OS 2012) and add a 4th node to the cluster that will be at another location. And everything I find is telling me to use ...
0
votes
0answers
2k views

Oracle:Test connection failed because of an error in initializing provider. ORA-12170: TNS:Connect timeout occurred

We have Migrated SQL server 2008R2 to SQL server 2012 from Windows 2008R2 onto Windows 2012R2. Everything went well , except for the Oracle database connection: We have a SSIS package created on SQL ...
3
votes
1answer
94 views

Help with SQL SERVER 2012 Licence

I am planning to buy SQL Server 2012 for development and I have gone crazy with Microsofts complicated licencing policies.I had been searching the internet for valid answers from last few days and ...
3
votes
1answer
52 views

Two Sequences on one column

You can use 1 or 2 sequence. Let's say I have a table tblOrder and 2 columns in it, OrderID and Company. I only use two companies in my table, IBM and Airtel. What I want is when Company = IBM then ...
5
votes
2answers
202 views

Buffer Size Changing?

I have a production database that is experiencing wildly fluctuating Page Life Expectancy (PLE) issues. (It crashes to zero at random times.) I have been researching the PLE issue and have found ...
2
votes
1answer
652 views

Why has my database connection failed?

Recently I have developed software using SQL Server 2012. Now when I try to connect, I get this error message: Cannot connect to AAFI\SQLEXPRESSA network-related or instance-specific error ...
1
vote
3answers
57 views

Need help with SQL Server configuration

I was planning to buy the SQL Server 2012 standard edition for developing reports and SSIS packages. My company does not have a client-server architecture and the developers are working on their ...
0
votes
0answers
45 views

SQL Server 2012 , TDE key rotation for a database in Availabilty groups

I need to do TDE Key Rotation for a database in availability groups in SQL Server 2012 for PCI Compliance. Need help please on procedure to follow .
0
votes
1answer
67 views

Need to maintain transational consistency of the data across both servers, need to achieve these goals without manual intervention

I got this question about MS SQL Server 2012 administration, two production servers in the same data center. You need to ensure that database remains available if a catastrophic server failure or a ...
1
vote
1answer
253 views

Issue with FMTONLY in SQL Server 2012

I support an application that uses CodeSmith and NetTiers templates to generate C# code. CodeSmith inspects the database and uses the SET FMTONLY ON setting to determine the columns for which code ...
1
vote
3answers
1k views

List all tables from all user databases

sys.tables lists all user tables from a single database, But it doesn't have their schema name. If I know existing databases when creating the code, I can use USE to browse all databases, query ...