This tag is for SQL Server 2012, major build version 11.00.xxxx.

learn more… | top users | synonyms (2)

6
votes
2answers
2k views

Relating ExecutionInstanceGUID to the SSISDB

The 2012 release of SQL Server Integration Services, SSIS, has delivered an SSISDB catalog which tracks the operations of packages (among other things). The default package execution for solutions ...
0
votes
3answers
2k views

SQL Server 2012, rebuild not lowering avg fragmentation

I have a script identifying what indices to rebuild. select 'alter index ' + name + ' on ' + @dbname + '.dbo.' + OBJECT_NAME(a.object_id) + ' rebuild;' from sys.dm_db_index_physical_stats ...
3
votes
5answers
2k views

How can I tell if a SQL Server backup is compressed?

We have recently upgraded from SQL Server 2005 to SQL Server 2012. Under SQL Server 2005 there is no option to create compressed backups as there is in 2012. If you attempt BACKUP DATABASE ... WITH ...
0
votes
1answer
264 views

SQL Server Express on Windows 8 isn't locking MDF database files

Historically, if I go to rename a database that is currently being used by SQL Server, I get an exception. On this Windows 8 desktop I'm in.. my primary desktop.. I'm able to MOVE SQL Server ...
3
votes
6answers
755 views

Can SQL Server 2000 and SQL Server 2012 CoExist?

We have OLTP Databases on SQL Server 2000 on my client's Prod Server. Planning to create Data Warehouse on the same Server but use SQL Server 2012. Can I install without any issues?
8
votes
2answers
1k views

SQL Server database design for “archived but available” data

We have this large database (>1TB) that we intend to "shrink". The database revolves around one main entity, let's call it "Visit". For discussion, let's say it is a database for a medical practice. ...
1
vote
1answer
2k views

How to connect Powershell to the SQL Server named instance?

When I run # Load the SMO assembly [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo"); $ServerName = "[local\s12]"; #Get a server object which corresponds to the ...
-5
votes
1answer
59 views

SQL server 2012 installation [closed]

i'm installing SQL server 2012 x64 on windows 7 professional x64 and when i install it said to me your operating system doesn't meet the requirements !!
2
votes
1answer
356 views

IntelliSense alternative using SSMS 2012 in a contained database as a contained user?

According to this answer about contained database disadvantages: If you connect to a contained database as a contained user, SSMS will not fully support IntelliSense. You'll get basic ...
2
votes
1answer
608 views

Even when user has 'bulkadmin' role, query says user does not have role - SQL Server 2012

I am facing a weird issue with SQL Server 2012. I have a user (say RS\sqluser) which has bulkadmin role. Now when i run this query Select IS_SRVROLEMEMBER('bulkadmin', 'RS\sqluser') output is 0. ...
1
vote
1answer
4k views

Upgrade SQL Server 2012 Express to Evaluation Edition

Is there a way to upgrade an instance of SQL Server 2012 Express to SQL Server Evaluation Enterprise? I tried using the SQL Server Installation Center and clicking Maintenance, and then Edition ...
0
votes
2answers
372 views

SQL Server sync databases between enterprise - web edition

We have an enterprise version of sql server 2012. we need to synchronize its data to a web edition database. what would be the best option t do this ? further to add on to this we need to provide ...
1
vote
1answer
2k views

Backup stored procedure for full, differential and log backup

I wrote a simple stored procedure for backing up FULL, Differential and LOG backup. This is my stored procedure: Alter PROCEDURE BackupDB ( @BaseLocation varchar(1024), @BackupType ...
1
vote
0answers
432 views

sp_testlinkedserver taking long time to time-out

I'm using the following code to check if a linked server is available before running queries on it: declare @srvr nvarchar(128), @retval int; set @srvr = 'my_linked_srvr'; begin try exec @retval ...
8
votes
2answers
11k views

When is it appropriate to use SQL Server Developer Edition?

Do you use SQL Server Developer Edition on server-class machines in DEV and STAGING environments? I am working on a large project where (if it passes the proof of concept stage) we will have several ...
2
votes
2answers
103 views

How do I write backup procedure

I've read that a decent backup strategy would be: 1) Full backup weekly 2) Differential backups daily 3) Log backups every 5 minutes. Now question is how do I write this stored procedure and create ...
6
votes
1answer
1k views

How do you move a SQL Server 7 database to SQL Server 2012?

I would like to move a database from SQL Server 7.0 to SQL Server 2012. They are not compatible for a backup and restore. Is there a way to export from SQL Server 7.0 and import it to 2012? I only ...
0
votes
1answer
100 views

Accessing a SQL Server Denali CTP1 database with Management Studio 2012 SP1 Express

I had Management Studio 2011 Denali CTP1 installed before but I have uninstalled it. Now I have installed the Management Studio 2012 SP1 Express without any database instances. I have a webserver ...
2
votes
1answer
2k views

Best way to perform SQL Server replication over multiple servers?

My current setup requires me to use three separate SQL Server 2012 instances (installed on 3 separate machines, separated geographically) and have the data be replicated across all 3. Any changes made ...
3
votes
3answers
2k views

SQL Server service shutdown due to 'bad?' SSL cert

This morning it transpired that SQL Server 2012 service running on Win2012 had shut itself down. When I logged in as the server admin and tried to access SSMS, after entering my credentials I got the ...
1
vote
1answer
835 views

Alternative tools to export Oracle database to SQL Server? [closed]

I've got an Oracle database that I need to export (schema and data) to SQL Server. I am trying the Microsoft SQL Server Migration Assistant for Oracle, but it is horribly slow, grossly inefficient ...
15
votes
6answers
7k views

What is a good, repeatable way to calculate MAXDOP on SQL Server?

When setting up a new SQL Server 2012, I use the following code to determine a good starting point for the MAXDOP setting: /* This will recommend a MAXDOP setting appropriate for your machine's ...
4
votes
2answers
1k views

Track grant permission on tables

I'd like to see if there are any changes on the permissions granted for a specific table. For example, I don't want to have the login 'John' have the SELECT permission on the table Invoices. If ...
5
votes
2answers
646 views

In SSIS 2012, what is the purpose of flagging a package as an “Entry Point” package

In the Visual Studio designer you can right click on a SSIS package and designate it as an "Entry Point" package" Doing a search I found this page on MSDN which states: The value of 1 signifies ...
5
votes
1answer
337 views

SQL availability groups log maintenance

I have a AG with 2 nodes in a site in sync mode and 1 offsite in async mode. I'm having trouble understanding how the logspace is maintained in this scenario. For instance, I'm taking log backups ...
2
votes
1answer
420 views

Storing multiple settings for multiple users in a SQL Server table

I'm using SQL Server 2012 to store the account settings for multiple accounts (each user account will have a set of settings). These settings could have any number of fields (has not been completely ...
3
votes
1answer
149 views

Delete a variable number of records by a chronological order

Our developers have been using a cursor within a stored procedure to delete old password history. The number of records to delete is being passed by variable. DECLARE hist_cursor CURSOR LOCAL FOR ...
10
votes
3answers
2k 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 ...
4
votes
3answers
361 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) ...
5
votes
2answers
188 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 ...
5
votes
1answer
4k 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
2k 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
774 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
52 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 ----------- ---------------- ------------- ...
2
votes
1answer
171 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
34 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
525 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: ...
6
votes
2answers
1k 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] ...
7
votes
2answers
3k 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 ...
12
votes
4answers
14k 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
63 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
393 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 ...
5
votes
1answer
648 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
840 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 ...
4
votes
1answer
259 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
142 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
372 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
352 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
533 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
374 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 ...