This tag is specifically for SQL Server 2012
2
votes
1answer
332 views
SMO v11 (SQL Server 2012) not scripting key definitions, indexes, and constraints
I have a .ps1 PowerShell script that runs on a Central Management Server. The script goes out to all databases in our environment, and scripts out all of of objects. For tables scripts, the output ...
2
votes
1answer
174 views
Merge replication SQL Server 2008 publisher with SQL Server 2012 subscriber
is this somehow possible to have SQL Server 2008 publisher with a SQL Server 2012 subscriber for merge replication?
3
votes
1answer
130 views
How to link users and logins in an Availability Group?
I've got an Availability Group going and need to map some logins to database users on an active secondary replica. (Node2) Unfortunately, when I try to exec sp_change_users_login or alter user with ...
5
votes
2answers
199 views
case versus isnull versus coalesce
I am just reading through the documentation for the SQL Server 2012 exams and I saw the following point:
case versus isnull versus coalesce
Now, I know HOW to use each one but I don't know WHEN ...
1
vote
1answer
145 views
Can't grant role permissions under sysadmin login
I'm trying to execute this script from Management Studio:
GRANT ALTER ON ROLE::[AdaptTemplate] TO [AdaptAdmin] AS [AdminSapr]
Execution fails with this message:
Msg 15151, Level 16, State 1, ...
1
vote
1answer
310 views
SQL Server Full-Text Indexer with stoplists/stopwords
As I'm working on a graduate project (Textmining with SQL Server 2012 Semantic Search) I run into a situation where I need to post a question on this website, hoping someone can help me.
This ...
1
vote
0answers
106 views
SQL Server: Collation of the resource database
I'm trying to use stopwords so that some words are skipped by the Full-Text Indexer. I can add those stopwords in the table sys.fulltext_stopwords.
When trying to get a list of stopwords and ...
5
votes
2answers
310 views
How to handle sql server database logins when using AlwaysOn. (they aren't syncing)
We have 2 servers in an AlwaysOn group.
While the user accounts within each syncronized database exist on both servers, the database instance level logins only exist on one of the servers. Ie ...
3
votes
1answer
160 views
Cross Database transactions - Always on
Recently we are working on a POC to get Always on work and happened to see this article in BOL
http://technet.microsoft.com/en-us/library/ms366279.aspx
This article suggests that there would be ...
2
votes
1answer
2k views
Access denied when trying to run an agent job
Can someone please point me in the right direction. I keep getting the below error message when I try to run a job that executes a package I created and saved on the SQL Server.
12/03/2012 ...
3
votes
0answers
83 views
Uninitialized Merge replication subscription
I have 3 separate SQL Server 2012 databases running on 3 VM's. I have successfully configured one pull subscription to the distributor but when I add a 2nd subscription on another SQL Server the ...
3
votes
1answer
200 views
Performance effects of database file size and available space
I have set the autogrowth and maxsize properties to:
By 10 MB, Unlimited
When I look at the Properties -> General Tab by right-clicking the database in SSMS, I see the file size is 2249MB with 6.1 ...
3
votes
1answer
181 views
Error migrating a big table from mySQL to SQL Server 2012
I'm trying to convert several mySQL tables to Microsoft SQL Server 2012 tables using the SQL Server Migration Assistant for MySQL. The small tables get converted but when it comes to the big one ...
3
votes
1answer
197 views
Agent job will not run
I created a JOB to run a simple package that runs a query and saves the results to an Excel file. If I try to run the job In SQL Management Studio I get an error saying:
SQLServerAgent is not ...
11
votes
1answer
202 views
Is it possible to record incoming parameter values in a procedure call while tracing in SQL Server Profiler?
Using SQL Server Profiler (I'm on SQL Server 2012), I'm trying to generate a useful trace that shows the parameter values, not just the SQL with variable names. The stored procedure walks through a ...
1
vote
2answers
167 views
SQL Server 2012 Edition Upgrade Error
I know I can't have a direct answer to this previously written somewhere, because this is somehow an special situation.
I installed a SQL Server 2012 Business Intelligence last month. I have found ...
0
votes
2answers
74 views
Getting notification when log truncation is delayed
Is there any way to notify DBA proactively when transaction log truncation has been queued multiple times however for some reason (it could be an open transaction, replication, currently running full ...
2
votes
4answers
529 views
limiting sql server backups inside .bak file
I have a job scheduled to run a few times everyday. The job backups my databases, like:
BACKUP DATABASE [databasename]
TO DISK = N'd:\sqlserver\backup\databasename.bak'
I can track the backups in ...
2
votes
1answer
165 views
Divide by Zero Causing Error transaction to fail with Arthimetic Abort and ANSI WARNINGS FALSE after upgrade
This is a strange issue..
We are receiving Divide by zero errors that are causing SQL Server to terminate transaction and crashing the test website.
Arithmetic Abort is set to FALSE along with ANSI ...
3
votes
1answer
185 views
SQL Server Dynamic Memory management
I am reading a book on SQL 2008 internals and troubleshooting by Christian Bolton, Brent Ozar and a old book written by late Ken Henderson and I am getting confused and curious about SQL server's ...
2
votes
1answer
172 views
SELECT multiple sensor values in one query
Background
I have a couple of devices, each with a couple of sensors. I log these every now and then and stores them in a table described below. When someone requests a web page, I fetch a couple of ...
6
votes
1answer
170 views
DBCC TRACEON (652) does not disable read ahead reads
Refering to this StackOverflow question: http://stackoverflow.com/questions/13307485/sql-server-clearing-cache-has-no-effect-on-io-stats
I used the trace flag 652 which had, at the time disabled the ...
25
votes
2answers
2k views
Are SQL Server in-place upgrades as ill advised as they used to be?
I have been working with SQL server on and off since SQL Server 6.5, the old advice that still rings in my head was never to do an in-place upgrade.
I'm currently upgrading my 2008 R2 DEV and TEST ...
7
votes
2answers
464 views
How To Profile Stored Procedures
I am using SQL Server 2012 and was wondering how to profile stored procedures
For instance can profiler capture each individual SQL statement in a stored procedure, what it is, and how long it takes ...
3
votes
2answers
125 views
Does the Query cost in Sql Server account for buffering?
If I am comparing 2 queries by checking their relative "Query cost" as a high level indicator of performance, does the buffer status have any effect on the values shown in the execution plan?
4
votes
1answer
81 views
Preferred node for read on AlwaysOn in a multisite configuration
We are going to set up a multi (at least 2) site configuration, and we want to achieve both HA as well as a good resource usage.
The sites will be interconnected through a high speed, dedicated fiber ...
6
votes
4answers
1k views
What is the fastest way to export a table to a text file
I will have a SQL Server 2012 database and a table with 3 million rows and maybe 50 columns. What will be the fastest way for an unattended background .net process (maybe issues some SQL or Powershell ...
5
votes
2answers
379 views
SQL Server Management Studio 2012: How to have folders in the SSMS project?
I'm using SSMS 2012 to talk to the SQL server 2012 and Azure SQL servers we use. I admit I'm not a SQL expert so I've been saving most of my SQL scripts for future reference. I've quickly run into 20 ...
4
votes
5answers
454 views
using IDENTITY column with increment -1
I need to run a sql server logging db with 2 main tables in 2 seperate datacentres writing to both at the same time.
I had the idea of restoring the db to the new datacentre and then reseeding the ...
2
votes
0answers
78 views
How To Analyse Index Scans
I am using merge replication with SQL Server 2012.
During a sync using a merge agent there are a large number of index scans. I can see this by running a profile capturing SCAN:Started and ...
3
votes
4answers
365 views
Is it advantageous to scale out using multiple SQL Server instances on the same server?
For a use-case of 9 databases spread across 3 time zones, is it advantagous to spread the databases onto multiple SQL Server instances on the same server?
What will I gain and what will I lose? ...
0
votes
0answers
28 views
Copying tabular models from production to DR
I have a Production server with several SSAS Tabular Models on it.
What is the best way to automatically copy the meta-data for these models to a DR server at end of day?
2
votes
1answer
160 views
Why 2 allocation units during online index rebuild to different file group?
I'm in the process of rebuilding the clustered index of a big table to a different file group. I'm monitoring the rebuild process using sys.system_internals_allocation_units. I'm 3h in but not done ...
3
votes
1answer
209 views
Move Primary Key to Filegroup (SQL Server 2012)
How can I move a clustered primary key to a new filegroup? I already have found a possible "algorithm" but it is horribly inefficient:
Drop non-clustered indexed (requires them to be resorted and ...
2
votes
0answers
173 views
SQL Server connection with multiSubnetFailover fails, claims mirroring is enabled
I have two SQL Server instances in an availability group. They are not mirrored.
I'm trying to connect to the AG using the MS JDBC drivers (4.0) with a connection string including ...
7
votes
1answer
3k views
Is SQL Server 2012 Management Studio “Express” any different?
Are there any differences from the version of SSMS that comes as part of the SQL Server 2012 Enterprise Installer and the SQL Server 2012 Express Management Studio?
2
votes
0answers
78 views
SQL Server fulltext_semantic_languages
When trying to install a semantic search example, I'm running into a problem.
There are some steps in the example to check if the 'new' Semantic Search and Full Text Search are installed.
...
1
vote
2answers
57 views
How much work actually happens on SSMS and how much on the server itself?
Suppose I run the following query:
declare @i integer
set @i=1
while @i < 2000000
BEGIN
insert into t1 (ID,DESC,date) values (@i%200,concat(NEWID(),newid()),GETDATE())
set @i = @i +1
END
Is the ...
0
votes
1answer
464 views
Restore msdb from SQL Server 2008 R2 to SQL Server 2012 instance
I'm moving from a SQL Server 2008 R2 instance to a SQL Server 2012 instance on a separate box. I want to restore msdb to get all my agent jobs and the like and I'm getting the following error:
...
1
vote
1answer
137 views
Connect to SQL Server 2012 Database from 2008 Agent Job containing 2008 SSIS Package
I'm planning the rollout of SQL Server 2012. My client has a number of SQL Server Integration Services (SSIS) packages currently deployed to SSIS 2008 and consumed by SQL Server Agent Jobs defined on ...
1
vote
1answer
54 views
Force encryption flag doesn't save
I'm trying to set the server to force encryption when I hit apply the message saying that it will take affect after restarting pops up, but when I open the box again its back to NO as nothing was.
...
3
votes
1answer
372 views
transactional replication, failing with communication link failure half way through bcp
I am trying to replicate to a 4th subscriber on a remote site. 3 subscribers work fine locally. the other difference is this is a 2012 subscriber from a 2008 r2 publisher and remote distributor.
When ...
2
votes
1answer
168 views
TempDB size issue after applying Indexes
I added several indexes to a SQL Server 2012 database to help with performance a couple of days ago. Today our partition with TempDB on it ran out of space. TempDB went from less than 10 GB to 38 GB. ...
0
votes
1answer
85 views
seeking a confirmation — SQL Server backup performance [duplicate]
Possible Duplicate:
Is SQL Server Backup single or multi-threaded?
Does SQL Server uses a separate writer thread for each backup device (backup file) or separate thread is used only when ...
3
votes
1answer
181 views
Lock Timeouts with low Lock Waits
On a production SQL Server 2012 database we are getting some odd locking statistics that I don't understand.
Lock Waits/Min: 0.2
Lock Timeouts/Min: 197.4
Deadlocks/Min: 0.0
@@lock_timeout is set ...
5
votes
3answers
322 views
Storing terabytes of data in SQL Server
I need to develop a production system capable of storing a 100 kb textfile and a serial number for every piece produced. I know the approximate output over 2 years, the time the data has to be stored, ...
1
vote
1answer
436 views
sp_dboption is not available in SQL Server 2012
The system stored procedure sp_dboption is not available in SQL Server 2012 anymore. How could it be replaced?
2
votes
1answer
81 views
Execute job every time database changes - execute job on each change-event
I have a transactional-replication running in my database, and I need to execute a service on my website that refreshes the cache every time the database changes.
So i guess i have two options to ...
4
votes
2answers
310 views
SQL Server Concise List Of Trace Flags
I did my 5 minute research and have not come up with a concise list, or even a list really except for Trace Flags (2012) which is a bit lacking.
Looking at this query which uses 8691, I was wondering ...
1
vote
1answer
361 views
Query performance of a NULL vs an empty ('') varchar in SQL Server 2012
I have 3 indexed fields in a query: int, int, and varchar(250).
The query performs well when all 3 conditions are specified with real values. The int columns always have values, but there are plenty ...
