All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

3
votes
0answers
784 views

SQL DatabaseMail smtp error 5.7.57 at one site, email works at another with the same details

I have several SQL databases on different servers. I have used a script to set up Database mail. EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'Emailer' ,@description = 'Email out ...
3
votes
0answers
103 views

How to set ALL the replication to DEADLOCK_PRIORITY LOW?

I have just got this email here: Then looking at the sql server log I find this deadlock was caused by one of our procedures (that was the victim) against the replication. O.K. in this case in ...
3
votes
0answers
25 views

TDE prep: key/certificate backup for restores

I am working in a dev environment to better understand TDE encryption. I have it working along with backups and restores on another server. Had a few questions, I know I need to backup the certificate ...
3
votes
0answers
100 views

Very High wait time?

We currently have one production SQL Server 2012 Standard Edition , running on Windows server 2008R2 standard ( 32 GB RAM , 16 cpu). there are around 22,000 connection. the Max worker thread is ...
3
votes
0answers
174 views

SQL Server replication - error when creating a subscription - The article '(null)' does not exist

When trying to add a subscription to an existing publication use [Product] exec sp_addsubscription @publication = N'Product', @subscriber = N'REPLON1', @destination_db = ...
3
votes
0answers
51 views

Database Maintenance Planner greyed out (disabled)

I have to look into some SQL Server 2000 maintenance plans, but Database Maintenance Planner option is greyed out in Tools menu of Enterprise Manager. SQL Server Agent seems enabled and running, ...
3
votes
0answers
68 views

Need a way to prevent log shipping from kicking users out

We have SQL Server 2012. A service we use requires us to use log shipping, however we are tired of having users being kicked offline every 15 minutes. Are there any creative ways to get around this ...
3
votes
0answers
74 views

Trigger/Event that fires when a Transaction finishes

I have a query that pulls from about 15 different tables. I am looking to materialize this into a table that stores it in xml/json. (To improve performance.) The problem I have is that these tables ...
3
votes
0answers
128 views

SQL Agent Jobs Automatically Disabled After Modifying Maintenance Plan

I modified a maintenance plan yesterday, to find out this morning (by using the last modified date from sysjobs) that all jobs related to the maintenance plan are disabled. I can't figure out why ...
3
votes
0answers
111 views

Index Optimization Error in SQL Server 2012

I have an Index optimization job set on the database SWHSJ having two data files with unrestricted growth in the J drive with 174GB free, as you can see below: ...
3
votes
0answers
130 views

Is it possible to have different authentication mode for SQL Server Database Engine and corresponding SQL Server instance?

I have installed the x64 SQL Server 2008 R2 Express with default settings and run MBSA 2.3 Microsoft analizer (using default settings too). Tried on clean Windows 8 installation (not 8.1) and Windows ...
3
votes
0answers
309 views

Upgrading SQL Server from 2008 to 2012 doesn't respect existing install / instance paths

Basic Information : Running Microsoft Windows Server 2008 with MS SQL Server 2008 (version 10.0.5869), trying to upgrade to SQL Server 2012. Currently working in a cloned VM of our production server ...
3
votes
0answers
106 views

Change Tracking across several Tables

I'm looking into providing DB level change tracking within my system. I need to be able to track changes at an entity level, rather than just individual tables. Right now, we have triggers on each of ...
3
votes
0answers
529 views

How to insert values into Bridge Table in star schema?

I am modelling my database with respect to star schema with bridge tables. My master Table looks like below. I need to convert this format to star schema. MasterTable StudentNumber | Subject1 | ...
3
votes
0answers
153 views

ASSIGN value for cmd in sysprocesses? What does it mean?

We're seeing some strange blocking behaviour in a production (SQL Server 2008 R2) system that we're investigating, and it seems to have one consistent and novel element: in the sysprocesses table, the ...
3
votes
0answers
7k views

how to resolve intermittent SQL backup issue?

We have this issue on aprox 100-300 SQL machines each month and it's never re-occuring. If you re-run the backup it will complete. The problem exists in both win2k8 and win2k12 machines. Backup ...
3
votes
0answers
567 views

DTUTIL to package store

I am trying to deploy a package to a project in my SSIS catalog and am using the following command: dtutil /FILE C:\Changes\ReceivedEDI.dtsx /DestS MARSQL12 /COPY ...
3
votes
0answers
1k views

Once more about SQL Server virtual accounts

Let me reboot my question on SQL Server virtual accounts permissions The (same) MSDN article on the same version 2012 of SQL Server "Configure Windows Service Accounts and Permissions" states: ...
3
votes
0answers
169 views

SQL Server 2012 Resource Governor and memory used by CLR

In SQL 2012 memory used by CLR is now part of what is controlled by "MAX Server Memory" a) Does Resource Governor in SQL 2012 Enterprise (MAX_MEMORY_PERCENT for resource pool) governs CLR memory? ...
3
votes
0answers
226 views

How can I search values, considering occurences, on more than one term in SQL Server CE?

I have a rather complex SQL query, that I actually received help from this SE site to come up with parts of. This query is designed to take a search term and search all relevant values in a database ...
3
votes
0answers
2k views

Severity 016 Backup virtual device fail

My backup on one of my servers is failing once a week. This is the alert I'm getting: DESCRIPTION: BackupVirtualDeviceFile::PrepareToFreeze: failure on backup device ...
3
votes
0answers
198 views

custom population of full text index

Is there any way to populate a full text search index without storing the backing information in the database? I don't mean using remote-blob-storage or FILESTREAM types. The backing information is a ...
3
votes
0answers
172 views

merge replication too big

I have multiple handhelds which sync their data via merge replication. from time to time, new core data is imported on the server-database. for some reasons I keep the old data in the databse and set ...
3
votes
0answers
1k views

Preferred Methods Of Indexing MS SQL Server Audit Data To Splunk

What is your preferred method for indexing MS SQL Server for Splunk? I am collecting audit data in various environments by various means (profiler, app logs, and extended events) but I hope to ...
3
votes
0answers
190 views

Date dimension changes between Test and Production Server

I have a cube deployed into a Test environment and Production environment. I've noticed something strange happen with the date dimension. When deployed into our test environment, the Date Key comes ...
3
votes
0answers
829 views

SSIS Recordset Destination error

I have an SSIS package targeted at SQL Server 2012. I have it deployed into the Stored Packages in Integration Services and then have a SQL Server Agent job which executes it. The first part of the ...
3
votes
0answers
637 views

INSERT BULK in SQL Profiler without values

I started SQL Profiler to catch actions that are doing on server, and among them I found one row where is stated: SQL BatchStarting: insert bulk table_name <column_list> but there is no ...
3
votes
0answers
194 views

Key Lookup and Full-text index

I have the following query: SELECT T2.Title FROM TitleTable T1 INNER JOIN TitleTable T2 ON T2.FKID1 = T1.FKID1 WHERE T1.FKID2 = @ID_PARAM1 AND T2.FKID2 = @ID_PARAM2 AND ...
3
votes
0answers
785 views

SQL Server Database Hot Backups: Which Technique(s) to Use for Multi-Level Replication?

I'm reading the many other posts here about mirroring vs. replication, but I'd still appreciate it if someone with some experience would weigh in. My small development team needs to have local ...
3
votes
0answers
563 views

SqlDependency permissions

The db is SQL Server 2012. All data tables are in the dbo schema. All data access is done via stored procedures in a different schema (user rt). I want to add query notifications for some tables, so I ...
3
votes
0answers
320 views

Are the following statements concerning sys.dm_exec_requests true?

Parallel plans mean that cpu_time can be greater than total_elapsed time. Therefore, per-query wait time is not possible to determine accurately from DMVs on servers with more than one CPU thread. ...
3
votes
0answers
325 views

SQL Server Event Alerts Lack Enough Details

I setup SQL Server Alerts to send me emails whenever certain events (by severity) occur. Below is an example of en email I received for a "014 - Insufficient Permission" severity event of Type "SQL ...
3
votes
0answers
575 views

Tables not shown in SQL Server Enterprise Manager

For some reason 3 tables which are clearly in use by the application are not listed in the Tables view in SQL Server Enterprise Manager. It's SQL Server 2000. When generating a diagram, a popup ...
3
votes
0answers
137 views

How To Diagnose Slow sp_mssetupbelongs

I am using merge replication with SQL 2012. When performing a clean sync using the merge agent I am getting a timeout on this stored procedure, sp_mssetupbelongs I am wondering what the best way to ...
3
votes
0answers
107 views

MSMerge_contents Table Populated After One Sync

I am running merge replication with SQL 2012. When I first create my publication the MSMerge_contents table is populated with a large number of records with the colv1 being set to 0xFF. When I run ...
3
votes
0answers
205 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. ...
3
votes
0answers
285 views

SQL Server 2008 R2 - Merge Replication - Can the Distribution Database size cause replication problems?

Setup: We have a SQL Server 2008 R2 instance running on a central server. The server is a merge-replication publisher. We have 'N' subsribers running SQL Server 2008 Express. 'N' can grow to any ...
3
votes
0answers
94 views

Merge Replication Optimization

I am running Merge Replication on Microsoft SQL Server 2012 through web sync. Is there a way to recognize when regenerating a new snapshot and applying it would be more efficient than running a ...
3
votes
0answers
824 views

SQL Server Database Mirroring : strange client application behavior upon failover

I am putting a HA SQL server environment up based on three SQL Server 2008 R2 machines and database mirroring. I'll name them : principal.company.intra mirror.company.intra witness.company.intra ...
3
votes
0answers
408 views

Upgrading Remote Distributor in SQL Server Replication

I'm upgrading my remote distributor from 2005 to 2008r2, it is performing transactional replication only. this is a prerequisite to completing an upgrade of the Publisher to 2008r2. Script ...
3
votes
0answers
568 views

some SQL Server MSX targets show as Normal, Blocked

I've centralized my farm's jobs in MSX, covering about 70 instances of SQL Server. Seemingly randomly, at least once a week, several instances will stop accepting instructions. Within the MSX window ...
2
votes
0answers
34 views

Cross database - Table valued Stored Procedure

I have the same table type in two databases (DB1 and DB2), DB1 has an stored proc (sp1) which has the above type as an input parameter. Now, I want invoke this SP from DB2. But, I am getting this ...
2
votes
0answers
27 views

Create File,secondary FileGroup then move a table and restore database with 'Primary'

i created secondary FileGroup , linked a File then moved a table there and restore database with 'Primary' however the table name still there and when I use the select statement I got an error that it ...
2
votes
0answers
47 views

SQL Server Check Constraint Lock Order

I'm having problems with SQL Server 2012 filetable. Filetable defines an check constraint on a path_locator field with definition as: /*ParentsAreDirectories*/ ...
2
votes
0answers
36 views

Max Degree of Parallelism for SQL Server 2008

Microsoft have recommendations around setting MAXDOP on various versions of SQL Server, but this (old) article raises some issues and recommends setting MAXDOP = 1 generally for SQL Server 2005 and ...
2
votes
0answers
12 views

Installing SQL Server 2012 Standard on a machine with more than 64GB RAM

I’m in the process of doing the specification for a new server that will host SQL Server 2012 including SSIS, SSRS and SSAS I’m aware SQL Server 2012 Standard has a limit of 64GB. However from what ...
2
votes
0answers
32 views

Is there impact on query execution as more partitions are added to a table?

We are looking to heavily use partitioned tables in our data warehouse environment. We are looking to take advantage of fast partition switching as well as partition elimination during analytical ...
2
votes
0answers
45 views

Shrinking a physical transaction log file on a mirrored database

I have 2 SQL Server 2008 Standard Edition instances that are mirrored. Both instances are running Service Pack 1. I run Full backups daily and transaction log backups every half hour (through ...
2
votes
0answers
36 views

DB snapshot goes into SUSPECT mode (SQL 2014 on WIN2K8R2)

I was hoping someone here might have encountered this sort of issue and if so what did you do to fix it. Here's some details: DB server: SQL 2014 Enterprise (12.0.4100.1) OS: Windows 2008R2 ...
2
votes
0answers
27 views

SQL Server - Debugging - How to show parametervalues as HEX

I am writing a script which sets Parameters in the Registry for SQL Server. The following works on some of my servers but not all. DECLARE @Parameters AS VARCHAR(MAX) SET @Parameters = '-T1118' ...