This tag is specifically for SQL Server 2012
8
votes
3answers
162 views
Unique Identifier with Extra Characters Still Matching in Select
We are using SQL Server 2012 with a unique identifier and we've noticed that when doing selects with additional characters added onto the end (so not 36 chars) it still returns a match to a UUID.
For ...
2
votes
1answer
182 views
How can I calculate time spent at work in a single column?
I would like to calculate the amount of time spent working from rows of data that contain a single date/time field.
The first occurrence on a given day can be considered the start time, with the ...
4
votes
3answers
142 views
Backup strategies for zero data loss
I am working on one project and it has around 100K records. We can afford a downtime of 1 day but we need the data to be always available to us in latest state.
I've gone through two options:
1) ...
3
votes
2answers
80 views
Is it possible to enforce unique values in a column, excluding nulls?
I have a business requirement to have a FK field on TableA referencing TableB. If there is a value in TableBID, it should be unique, but it's not mandatory, so there could be multiple records with ...
2
votes
0answers
329 views
Log Shipping - RESTORE WITH STANDBY - on SQL Server 2012 keeps breaking
We are using log shipping and RESTORE WITH STANDBY on SQL Server 2012 in order to restore the database in read-only mode for reporting purposes. However, the log shipping setup keeps breaking after ...
5
votes
2answers
353 views
RSExecRole missing
I have multiple servers and 4 of them have reporting services installed with the ReportServer & ReportServerTempDB databases.
A few days ago I was setting up security for a new login and I found ...
2
votes
1answer
138 views
How can I configure SQL Server 2012 to use multiple NICs?
How can I configure SQL Server 2012 to use multiple NICs?
I've a server with 4 NICs. I want to use 2 NICs to query my databases, and 2 NICs to retrieve results from them.
I want them to work in load ...
4
votes
1answer
38 views
Can I query data_space names when not in context of the database for multiple databases?
I have a data set pulled from sys.master_files. The data set looks something like this:
database_id filename physical_name data_space_id
----------- ---------------- ------------- ...
1
vote
1answer
49 views
MDW performance_counter_instances table running out of identity values
My MDW database's performance_counter_instances table has run out of identity values.
Has anyone ever encountered this?
Is it safe to change the data type of the identity field ...
0
votes
0answers
32 views
Performing a Transaction Log Backup on a Live Database [duplicate]
I have a server 2012 database that has a transaction log that is growing at a rapid rate. i have a job that runs once a day after production hours which backs up the log file and shrinks it.
The ...
2
votes
1answer
189 views
How to grant permission for am administrator account in SQL Server 2012? [closed]
I installed SQL Server 2012 on my laptop. I am trying to run a script to create a simple database.
SQL Server version: 11.0.3128
SSMS starting parameters:
server type: database engine
server name: ...
2
votes
1answer
188 views
Is there a better option than Union All for multiple selects from the same row?
Example Schema:
CREATE TABLE [dbo].[Base](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Option1ID] [int] NULL,
[Option2ID] [int] NULL,
[Option3ID] [int] NULL,
[Option1Name] [varchar] NULL,
[Option2Name] ...
6
votes
2answers
335 views
How might a corrupt partition in TempDB result in DBCC CHECKDB reporting no issue?
One of our SQL Servers reported the following error recently:
DATE/TIME: 2/25/2013 9:15:14 PM
DESCRIPTION: No catalog entry found for partition ID 9079262474267394048
in database 2. The ...
9
votes
4answers
1k views
SQL Server 2012 slower than 2008
I migrated a large website and database from an older server (Windows 2008 / SQL Server 2008 / 16 GB RAM / 2 x 2.5 GHz Quad Core / SAS disks) to a newer, much better server (Windows 2008 R2 / SQL ...
0
votes
2answers
55 views
some tips on setting up sql server database server
I am currently setting up a new sql server database server with databases. It is supplied by a third party company.
I need to request access for them around ports etc.
I would like to be able ...
0
votes
2answers
141 views
Is there a sortable ('order by') alternative to CURRENT_TIMESTAMP
I'm in the process of looking for a way to find a sortable alternative to a column that is populated with CURRENT_TIMESTAMP and I'm stumped at the moment.
I have a web based form with multiple ...
3
votes
1answer
138 views
Unrecognised system process saturating CPU
We have switched to our backup DC today as primary site Bandwidth has gone.
We are getting issues with the most powerful DB server; it is spawning the following process for several db's on the ...
4
votes
1answer
203 views
Specifying the failover partner when using Availability Groups
We have a situation where we are using replicated databases in an environment that's making use of Availability Groups. As we wanted no manual intervention when failing over, we decided to set up ...
3
votes
0answers
92 views
What are the best practices for backing up a secured SQL Server 2012?
I want to state right off the bat that I'm not looking for information on how to backup a SQL Server. I've got that taken care of.
I'm looking for some form of documentation that will tell me the ...
1
vote
1answer
87 views
Set up AlwaysOn in virtual environment: How to add nodes to domain? [closed]
On a beefy server, I have a few Virtual Machines (VMs) running. On one of those, I have Domain Controller (DC) with a domain name (ex:SQL.AD.COM) running successfully. Now, I need to add the other VMs ...
4
votes
1answer
111 views
Query records that chain together based on date gaps
I have a table in SQL Server 2012 with the below simplified schema and I need to define a query that grabs the most recent record and then returns the chain of records that have less than 30 days of a ...
3
votes
1answer
106 views
SQL Server 2012 defrag job runs interactively, but not as a job - why?
I have recently started using the dba_indexDefrag_sp stored procedure from SQL Fool (http://sqlfool.com/2011/06/index-defrag-script-v4-1/) against my production databases running on SQL Server 2012 ...
3
votes
1answer
103 views
Are there problems with using SQL Server AlwaysOn Availability Groups with Windows 2008R2?
[Cross post from StackOverflow]
Brent Ozar published this webcast (slides, video with free registration) entitled "Real-Life SQL Server 2012: AlwaysOn Lessons Learned", in which he repeatedly states, ...
2
votes
1answer
68 views
AlwaysOn Availability Groups: Confusion with some concepts
In Sql Server 2012, I am trying to install Failover Clustering to configure AlwaysOn Availability Groups. When I run Cluster Validation test wizard, it comes back with a lot of errors. One of them ...
2
votes
2answers
102 views
Resetting a SQL Server 2012 sequence
I'm in the process of testing and populating a specific table that leverages the SEQUENCE object. In this process I'm testing populating the table with tens of thousands of insert lines (as I'm ...
4
votes
2answers
237 views
Restore SQL Server database from .sql files
So we had an encrypted database that puked and killed our whole SQL Server setup. Sucks about our data, but we were smart enough to have our data structures / stored procedures / functions in Git
The ...
3
votes
1answer
177 views
LIKE character length limitation
The background to this question can be found here.
The following part of my stored procedure does not work. Is there a character length limitation for the LIKE clause? t1.ProductList sometimes can be ...
1
vote
2answers
101 views
Using SQL Server 2012 for Business Intelligence Features Only, No Database Engine
I would like to implement SQL Server 2012 purely for the Business Intelligence Features: SSIS, SSAS and SSRS on its own server.
I current have SQL Server 2008R2 Database Engine installed on another ...
2
votes
1answer
97 views
Should I use Asynchronous Mirroring?
I plan on setting up mirroring on a SQL 2012 database which is currently in production. My concern though is how much it will affect the performance of the principal database.
I am using mirroring ...
4
votes
1answer
119 views
Error in SQL Server 2012 OUTPUT clause
Given the following SQL code against the AdventureWorks Database:
begin transaction;
drop trigger Sales.iduSalesOrderDetail;
alter table Sales.SalesOrderDetail
add LineNumber int null;
go
with ...
0
votes
1answer
75 views
Parameter triggers different execution plan, but why?
I have a table
CREATE TABLE [dbo].[Numbers]
(
[Date] [date] NULL,
[Time] [time](3) NULL,
[Value] [char](10) NULL
)
and the table has > 10 Billion rows, therefore it ...
1
vote
1answer
108 views
Commit Insert but Hold Lock
I have implemented what is effectively a reliable queue through the following:
CREATE TABLE Queue (MessageID UNIQUEIDENTIFIER, Message varchar(255))
--Enqueue Command
DECLARE @MessageID ...
1
vote
2answers
404 views
32-bit ODBC driver on 64-bit SQL Server [closed]
I am unable to use an ODBC 64-bit driver with SSMS 2012 running on a SQL Server 2012 64-bit instance. However, I believe that the 32-bit driver works.
My question is can I use SSMS 2005 32-bit to ...
1
vote
0answers
78 views
SSMS produces error if query returns too many rows
We are having an issue with a server that we linked to our sql server 2012 instance.
The server that we linked through an odbc or oledb connection is Pervasive SQL.
Selecting from the linked server ...
7
votes
1answer
211 views
selecting more fields causes catastrophic failure
I have a connection to a linked server from sql server 2012 to pervasive sql.
When I do select field1, field2, field3 from mytable everything works!
However, when I do select field1, field2, field3, ...
3
votes
1answer
53 views
Integration Services Catalogs - Version Description
In Integration Services Catalogs I can see what versions of a package/project are available and if necessary revert to an older version. On this dialog I see a field "description" however I found no ...
0
votes
1answer
77 views
How can I install a provider for linked server?
I have a provider for a linked server called PervasiveOLEDB.11.0 installed on a sql server 2005 machine. It always me to have a connection to a pervasive sql database.
I would like to use this same ...
1
vote
1answer
112 views
How can I change SQL Agent Log file to use unicode format?
I've created a database with tables that heavily leverages nvarchar for most of the columns. One of the challenges I'm running into is that when I go to view the SQL Server Agent Log files, I get the ...
2
votes
0answers
101 views
SQL71561 Error on Schema compare after initial import from database
I have created a new sql server project with SSDT 2012, then I went to import an existing database and it created all my scripts. After that I did a schema compare between the project and the database ...
3
votes
3answers
2k views
Can SQL Server Management Studio Express be installed standalone?
I was wondering if the SQL Server Management Studio Express (mainly for Microsoft SQL Server) be installed on a standalone machine (without any of the SQL Services and stuff) to connect to a remote ...
2
votes
0answers
98 views
TFS 2012 - Database Project - User with NO LOGIN map to Windows - best practices
I have a SQL Server Database Project (.NET 4.5) in TFS 2012. As a DBA, i'm looking for a mechanism whereby the developers can create 'CREATE USER' sql scripts and assign their permissions within the ...
9
votes
2answers
216 views
Best way to defrag/compact a database for archival purposes
We've got an SQL Server instance that's used for email archiving (courtesy of a 3rd party archiving package). Every so often, the software is rolled over to a new empty database. We've done this ...
3
votes
1answer
375 views
Change data capture - how to know who made the change?
Tracking who made the change identified by CDC.
Along the lines of my datetime hack I tried the same approach by adding suser_sname as a new field with default value on the cdc change track table. ...
0
votes
1answer
62 views
Installation of SQL Server 2012 does not contain Virtual Interface Adapter?
I have installed the entire Enterprise edition of SQL Server 2012 and have noticed that I do not have the Virtual Interface Adapter.
What is the cause of this?
1
vote
2answers
164 views
Query Performance Tuning on a huge table
I have the following table Transactions. The table looks like this:
CREATE TABLE [dbo].[Transactions](
[TransactionID] [bigint] IDENTITY(1,10) NOT NULL,
[PlayerID] [bigint] NOT NULL,
...
1
vote
0answers
294 views
SQL Server Create Access Denied even after adding MSSQLSERVER user permissions
I am aware that SQL Server instances need write access not for my user account (which is in fact irrelevant to that service's context) but for the user account or security principal under which the ...
1
vote
0answers
31 views
when can AG primary replica be a different host to WFC primary?
To set up an Availability Group with two servers, the servers both participate in a Windows Failover Cluster.
I've seen cases after certain failover modes where the primary IP address in the cluster ...
2
votes
1answer
158 views
Error: 9245, Severity: 16, State: 1. / During the last time interval XXX query notification errors were suppressed
I've got this sequence of error messages showing up regularly in my server error log:
Error: 9245, Severity: 16, State: 1.
During the last time interval 257 query notification errors were ...
1
vote
2answers
473 views
Removing Databases in Recovery Pending Status after AG group was removed
I have removed a High Availability Group, disabled the option in sql configuration manager and restarted and I have also removed the Failover cluster components from windows and restarted.
...
2
votes
1answer
118 views
Inside SQLOS – Scheduling
Using SQL Server 2012 Enterprise Edition SP1 running on Windows Server 2008 R2 SP1
For each logical processor core on SQLOS has a scheduler assigned to it. Those scheduler can be viewed as status ...
