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

learn more… | top users | synonyms (3)

10
votes
3answers
232 views

Configure RAM for SQL Server and NUMA

Accidental DB admin here. Question: Would you still manually limit max RAM for SQL Server even though the sole purpose of that server is to serve SQL Server database engine only? I have Microsoft ...
1
vote
1answer
38 views

Indexed view doesn't exist on the subscriber

I am configuring transactional replication in SQL Server. The subscription is configured as push from 2008R2 publisher (distributor is the same server) to 2012 subscriber. The object I want to ...
0
votes
3answers
68 views

Query for total downtime of site using datestamps and status columns

what want to find the total downtime for a site. I'm thinking I can subtract the 'down' DateStamp from the following 'up' DateStamp. then add the totals together for all downtime. I want to get a ...
0
votes
1answer
45 views

Optimal SQL Server 2012 Disk Configuration

I will be installing SQL Server 2012 on a new Dell T430 server containing four (4) physical 15K SAS 6Gbps disk drives using a PERC H730P RAID Controller and RAID 1. Since this will be the first time ...
0
votes
0answers
47 views

Sync two SQL Server databases

So I've got a hosting package from hostgator with an MS SQL database and a local SQL Express 2012 database running. I'm trying to investigate syncing the two together, two-way syncing, and I'm unable ...
1
vote
2answers
52 views

With LocalAdmin become sysadmin SQL Server

I am a local Admin on my machine. Same old thing, my account wasnt added when SQL Server was installed, and I don't want to have to reinstall SQL Server. I went through this blog and it worked until ...
3
votes
1answer
62 views

Create Database and LCK_M_S

We are experiencing issues when creating a database in the SSMS GUI in that sometimes it times out complaining that it could not get an exclusive lock on the Model database. Other times the database ...
3
votes
1answer
61 views

Why is the secondary selective index not used when the where clause filters on `value()`?

Setup: create table dbo.T ( ID int identity primary key, XMLDoc xml not null ); insert into dbo.T(XMLDoc) select ( select N.Number for xml path(''), type ) from ( ...
5
votes
2answers
92 views

Does SA account need to be called “SA” or can I rename it safely?

We have some applications on our network (from before my time) that use SA account in their SQL Server connection string. It's hard-coded in the source code, and for whatever reason, we can't change ...
1
vote
1answer
65 views

Full backup at 1am, not started by my maintenace plan

I set up a complete set of Maintenance Plan. Full backup of all databases at 3AM. Differential backup at 6h30, 12h30, 18h30 and 00h30. Log backup at each hour, at 45 minutes (1h45, 2h45...) In my ...
2
votes
0answers
80 views

MEMORYCLERK_SQLOPTIMIZER in SQL 2012 Memory usage extremely high and causes server to crash

I have been having issues with our SQL server for a while now. It seems to run out of memory every few days and when I look at the memory dump, the MEMORYCLERK_SQLOPTIMIZER seem to take over memory ...
2
votes
1answer
38 views

Difference between clustered index and partitioning column

As I understand partitioning, the column that you partition on is how things are divided into partitions. But does the partitioning column have anything to do with order on the disk? (Aside from ...
4
votes
1answer
82 views

Column Store Index over an clustered index (created when primary key is declared) - SQL Server

When a clustered index is created, the table itself becomes the index structure sorted by the index key. Imagine now that we have 5 columns in a table XPTO (a,b,c,d,e) and "a" is the primary key and ...
3
votes
1answer
70 views

Partially contained databases and Windows Logins - best practices?

In versions of SQL Server prior to 2012, in order for anyone to log in to a database using Windows credentials, you first had to create a server-level login, them map that login to a user for each ...
2
votes
1answer
17 views

Retreive a Customized List with input data

I have a database that has about 223 tables and I need to locate the name of the constraint. My request is to add a parameter that is the name of the constraint and you retrieve a list that shows the ...
4
votes
1answer
76 views

SQL Server 2012 Crashes and service doesn't restart

We've been having an issue with our SQL server lately. It seems that the server will run out of memory and then crash. For some reason, the sql service wouldn't restart. I would have to go and ...
0
votes
1answer
98 views

SQL Server Data File/Log File poor performance

I've been asked to look at a database/application server that's "performing slow". No one could really tell me what was performing slow (except for the database backups) so this has been a discovery ...
3
votes
1answer
240 views

SQL Server DOUBLE keyword

There is a reserved keyword DOUBLE in SQL Server, but I know that it's not a data type (other languages' double is float or real in SQL Server); does anyone know why this is a keyword? I've tried to ...
0
votes
1answer
68 views

fn_dblog not showing up deleted table data

I unintentionally deleted 4 rows from a table. I tried to fetch back the deleted data using a stored procedure (got from internet). However, I was able to see my data in the result of stored ...
0
votes
1answer
108 views

CLR Assemblies and SQL Server Availability Groups

I have a SQL 2012 Availability group. On that I also have a database with a .Net assembly that is using PERMISSION_SET = UNSAFE. CREATE ASSEMBLY drawing FROM ...
0
votes
0answers
98 views

Can't connect to an Active Node in an Active/Passive cluster

While being logged into an active node SQL Server 2012 node, I tried connecting to it from within the SQL Server Management Studio but was unable to do so. I tried the fully qualified name and the IP, ...
9
votes
1answer
301 views

What is the different between compression on the PK vs. on the Table?

Data compression can be set on the table: CREATE TABLE dbo.SomeTable( SomeId [bigint] NOT NULL, OtherId [bigint] NOT NULL, IsActive [bit] NOT NULL, CONSTRAINT [PK_Some] PRIMARY KEY ...
2
votes
1answer
54 views

Controlling how tempdb activity is split across drives (tempdb on both solid state and spinning drives)

I'm seeing the following tempdb I/O behavior over a one hour period: There is a fair amount of disk I/O generated by multiple DW workloads executing on the machine, some of which cannot fit in the ...
0
votes
0answers
26 views

2012 Always on (AG only) Where should the Windows Cluster SIT?

2012 Always On and the Windows Cluster, shouldn't the Windows Cluster be on a separate server like the Domain controller/BackupDomainController? With Windows clustering Services on each node ...
0
votes
1answer
35 views

Using * to enforce the need to update both tables together

I am a Java Programmer and definitely NOT a DBA. I am just an "accidental helper" to a DBA as we try to troubleshot an inherited VB.NET application situation. The guy that wrote this MS SQL got fired ...
0
votes
1answer
65 views

Do I need to rebuild indexes when upgrading a DB from 2008 to 2012?

I can't find anything on BOL that indicates I need to rebuild indexes or statistics after the migration and compatibility level upgrade of a database. But I have read a few articles/answers around the ...
1
vote
0answers
239 views

INSERT INTO with TABLOCK taking longer than without TABLOCK [closed]

I'm doing something like below to ensure the insert is minimally logged: insert into AnotherDatabase.dbo.HeapTable with (Tablock) ( <column fields ) select <column fields> from ...
2
votes
2answers
58 views

Why Does Partition Range Right Skip Deletes/Inserts?

I have two example Queries (shown below). Both (drop and) create a database and a partitioned table in it. The table has several partitions, with data in each of them (including the last, unbounded, ...
5
votes
1answer
172 views

Is there any way to monitor execution of xp_cmdshell in SQL Server 2012?

I've been tasked with assessing the security risk involved with allowing a third party vendor to debug their queries remotely within SSMS. This means that the user's account must be in the sysadmin ...
8
votes
1answer
204 views

How to terminate a suspended SQL Server transaction waiting for IO_COMPLETION?

We have a transaction that has been running for more than 5 hours. We are running out of disk space. The session has been killed but it is still waiting for IO_COMPLETION. Actually the wait_type just ...
0
votes
1answer
57 views

Check for duplicates in column and determine which row to keep

I've found myself running in to this on multiple occasions now where I got a query and would need too further refine it. Where one column might contain duplicates and I need to evaluate which row to ...
3
votes
1answer
108 views

Decode and aggregate a BitMask in t-sql

I have a table that contains a bitmask field that stores permissions, where each bit denotes whether a particular permission is granted or not. Here's a simplified example: DECLARE @T TABLE (id ...
0
votes
1answer
81 views

Attaching Database Incorrect name in sys.master_files

I have been moving some databases from SQL 2008 to SQL 2012 using detach and attach method. I correctly map up all the files and the physical and logical names appear correctly in the properties of ...
1
vote
2answers
66 views

How can I Insert when not in table and when max timestamp T-SQL

I am trying to insert rows from one table to another that are not in the one I am moving them to. I also want to only move the ones that have the highest datestamp. (I want to only insert rows that ...
0
votes
0answers
360 views

The Service Broker endpoint is in disabled or stopped state - AoAG - SQL Server 2012 - 15 minute interval

I receive this error message in SQL Server error log every 15 minutes. I have setup AOAG on this server. The database option "Service Broker - Broker Enabled" is turned on for the Blackberry related ...
4
votes
2answers
387 views

Deleting other files during maintenance plan

I have an SQL Server 2012 maintenance plan which does the normal stuff (rebuild indexes and update statistics, back up DBs, deletes .bak files older than X days, etc). Everything is working fine with ...
0
votes
0answers
13 views

After merge replication setup, tables at subscriber not getting any data from publisher

I have created a merge replication setup through Replication Wizard. After sync, it is observed that the subscriber DB gets created with the exact tables in publisher as intended, but on the ...
0
votes
1answer
84 views

System.OutOfMemoryException SQL Server 2012 and 2008R2

I have a 64-bit and 64gb memory for both SQL Server 2012 and 2008R2, when I generate a report w/c has 900K rows, it finished the query smoothly and no problem, but when I'm going to copy the result ...
2
votes
2answers
65 views

Long running backups on small databases

We normally perform backups across the network to a file share on a different SAN. This works very well for all but 1 of our database servers. The problem machine is a new server running Windows ...
1
vote
1answer
28 views

SQL Server 2012 Error 9512 when modifying user login

We are attempting to grant access to a database to one of our employees. We open up his user properties in Security -> Logins, navigate to the User Mappings page, select the database, select db_owner ...
0
votes
1answer
279 views

SSIS package hits “login failed for user” when simultaneous operations occur against the same target database

Quick summary We have been working on migrating our library of SSIS packages from SQL Server 2008 hosted on physical Windows Server 2003 to SQL Server 2012 hosted on virtual Windows Server 2012. The ...
1
vote
0answers
206 views

SSIS Package Deployment Failure using SSDT

SSIS fails almost immediately on the final "Deploying project" step of the Deployment Wizard (Package Deployment Model), using Visual Studio 2010 (SSDT) connecting to a SQL 2012 Server: Failed to ...
4
votes
1answer
91 views

Microsoft SQL linked server to Oracle data not updating

I need to pull data from an Oracle database running on a remote machine and use it on my sql server instance. I need to check these tables periodically for updated or new records. I have created a ...
0
votes
0answers
36 views

Does the transaction log grow during a full backup? [duplicate]

I've been tagged as the occasional DBA in the office. I have minimal experience and it would be great if someone could answer my simple question. I know the trans log grows during transactions, but ...
1
vote
2answers
104 views

Encryption in SQL Server 2012/2014

We are hosting sharepoint DBs with AlwaysOn AGs and FCI. My boss asked me what the options for encryption are on the content database, it's backup files and at the column level? I have read about ...
0
votes
0answers
51 views

SQL 2012 Standard Edition (on Azure VM) + Bitlocker. Good or Bad idea?

I will start this question with a few statements to negate any "Why" questions that are going to be asked. The Head of IT wants the data encrypted. Our profession is such that encrypted data is ...
0
votes
2answers
74 views

sql server transactional - replication - the internal procedures often deadlock with user processes

Very often I have deadlocks on my subscription server, involving the replication internal stored procedures and user processes either ad hoc or procedure queries. For example: Deadlock of the ...
0
votes
1answer
54 views

SQL Server Express 2012 Repair

I have a copy of SQL server Express 2012 database which may be corrupt. When I tried to run a repair on it I was asked for the original installation media. The Options were for an MSI or ISO image. ...
1
vote
1answer
65 views

When Should You Shrink Your Database

I have read conflicting articles about shrinking a databases. Basically, our database grew 100% overnight from 150 gigs to 300 gigs b/c I believe it was with staging tables I put in. However, all the ...
0
votes
0answers
68 views

SQL Server hangs during backup on EC2

Once and then our SQL Server will "hang" during its weekly full backup. When this happens, external connections start to timeout. The symptoms are: Connections timeout The sqlservr.exe process sits ...