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)

0
votes
1answer
212 views

Where to look for Agent history in Replication?

In SQL server 2008R2 replication configuration, "agent history cleanup distribution" job by default runs every 10 minutes and deletes any historical data older the 48hrs. If I increase the retention ...
0
votes
0answers
5 views

Restrict connection to SQL Server By IP [duplicate]

On a QA server I need to give access to specific login from just one IP to log to this server. I wrote This login trigger Create TRIGGER tr_CheckIP ON ALL SERVER FOR LOGON AS BEGIN ...
10
votes
2answers
305 views

SUM of DATALENGTHs not matching table size from sys.allocation_units

I was under the impression that if I were to sum the DATALENGTH() of all fields for all records in a table that I would get the total size of the table. Am I mistaken? SELECT SUM(DATALENGTH(Field1)) ...
6
votes
1answer
128 views

DBCC Check DB issue

I have database of 99GB on this database when I am running DBCC Checkdb('IssueDB') It gives error as: Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was detected while ...
1
vote
2answers
70 views

SQL Server Service does not start

I'm using Windows 8.1. I have successfully installed SQL Server Management Studio, but services can't be started in "Sql configuration Manager". I search on google about these errors,and try all ...
1
vote
1answer
32 views

How can I create a role to create,update,delete stored procedures and views

what permissions do I need to give in order to achieve the above task to get users the capacity to create,update,delete stored procedures and views to a database role.
0
votes
0answers
13 views

SQL-Server Full-Text Index Auto-Population Stops Working

From time to time auto population stops working (SQL Server 2014 11.0.6518). I have to use ALTER FULLTEXT INDEX ON [table] RESUME POPULATION to make it work again. In the SQLFT*.LOG there is no ...
0
votes
1answer
16 views

SQL Server 2012 - Powershell Agent Job Hangs

I have a SQL Server Agent Job on one of my 2012 SQL Servers which runs a powershell script inside the job step, where the 'Type' is set to powershell, and is running under the SQL Server Agent Service ...
0
votes
0answers
10 views

How to pass parameters to a cmd.exe using Servcice Broker External Activator

I am trying to use SQL Server Broker External Activator to launch C:\Windows\System32\cmd.exe application and pass commands to it. For example: echo Hello World x 2 >C:\Test\cmd1executed.txt To do ...
3
votes
4answers
5k views

How to make SSMS upper case keywords

I recently started using Management Studio 2012. When using MySQL Workbench, a handy feature was that I could stay all in lower case and any reserved word (like SELECT, INSERT) would convert to upper ...
4
votes
2answers
109 views

How to optimize this tricky SQL self-join?

I want to perform a complex self-join on a table. I know that this could in theory be done very efficiently (see below), but I have trouble getting SQL (on Microsoft SQL Server) to do so. My question ...
5
votes
2answers
3k views

Why isn't the objectname and indexname populated in deadlock graph?

I got a request to check out some errors on a 2008 sp3 server (10.0.5500.0 - Enterprise Edition (64-bit)) and see if I could find deadlocks that were happening earlier. Traceflag 1222 wasn't enabled ...
0
votes
1answer
109 views

Can we have OLAP cubes with same OBJECT NAME and ID at different database?

I have olap database for both production and test. Fortunately I noticed that cube under both production and test olap database has the same object name and Id. But DataSource for production and test ...
0
votes
0answers
10 views

Enabling the use of OPENROWSET in Sql Server [duplicate]

I am trying to make my DEV Server allow the use of OPENROWSET. Though I can use it on my production but not in my DEV. I cannot use the Bulk insert because the excel file is dynamic (Column changes ...
0
votes
0answers
18 views

Issues with the @query parameter in sp_send_dbmail

When I use the following: select * from table A where repID in ( select repID from table B where id = 5 ) as the string I'm assigning to the @Query parameter. ...
2
votes
1answer
133 views

Store extended properties and apply to another copy of the database

I'm using SQL Doc to document a development copy of a database. SQL Doc uses extended properties to store user entered values. At some point, I would like to copy those extended properties to the ...
2
votes
2answers
250 views

MS SQL Included Columns in an Index: is there something like “too many”?

We have a table in one of our databases with approximately following structure: Field1 int primary key Field2 int foreign key A Field3 int foreign key B Field4 int foreign key C Field5 ... Field70 ...
0
votes
0answers
23 views

Resource Governor - user in classifier function using NULL pool rather than stated pool

I have a problem with transaction log backups for an AlwaysOn HA database causing latch contention. It's not a problem when we run backups on the replica but we're not licensed for that, so I'd like ...
-2
votes
0answers
45 views

Want to setup full database in sql server 2012 [on hold]

I need to setup full database let say want to create tables, stored-procedures, cursors, functions etc. I have one xml file and all the related scripts. I need to know is there any way that I can ...
0
votes
0answers
43 views

Equalize datafile size [on hold]

I have a DB (Microsoft SQL server 2008 R2 (10.50.1600.1)) that has 28 datafiles with all different filesize. Some are 10GB, some 15GB, some 27 GB, etc. The difference in filesize is a historical fact. ...
0
votes
0answers
10 views

Changing MaximumErrorCount when executing maintenance plan

I have defined a Maintenance Plan in Microsoft SQL Server 2012. It doesn't execute and it says DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) ...
2
votes
1answer
30 views

SQL AlwaysOn & System Center Configuration Manager TP 4 1601

Recently Microsoft released Technical Preview 4 for Configuration Manager (1601). Per TechNet, "Configuration Manager now supports using a SQL Server AlwaysOn availability groups to host the site ...
8
votes
2answers
253 views

Shrinking a SQL Server Transaction Log

I’m currently having to deal with a SQL Server transaction log that has got out of control. Disclaimer: I’m not a dba and this isn’t my area of expertise so please bear with me. Currently I have a ...
-1
votes
0answers
23 views

What are the Pros and Cons of modelling a ready only query system with Multiple databases vs Multiple schemas?

I have the following scenario where an SQL Server database (D1) is being replicated to D1_p (on-premise SQL Server). I have another on-premise oracle database (D2) being replicated to D2_p (on-premise ...
3
votes
2answers
69 views

SQL SERVER 2014 Standard - High Availability Listener solution

The company has SQL Server 2014 Standard licenses and we're standing the servers up in a VMware environment. My initial plan is to create 2 instances on each DB server and run Instance1 on SQL1 as ...
0
votes
1answer
30 views

Pros and cons of DBCC SHRINKDATABASE operation

I have read MSDN - Microsoft Manual according to this issue, but I still do not have clear understanding, when it is useful to apply this option and when it is not. Please advise.
0
votes
0answers
9 views

Full text search error: Invalid locale ID was specified

Query involving full text search is generating the following error message after restoring the database to a new server: Invalid locale ID was specified. Please verify that the locale ID is ...
3
votes
3answers
104 views

Install Express/Standard side-by-side

I've reviewed these two existing questions: Install SQL Server 2008 Express on same machine as normal SQL Server 2008 install? Installing SQL Server Express in parallel of Standard ...and I ...
2
votes
0answers
17 views

AlwaysOn Multi Subnet Configuration

Environment Two Nodes A & B A - Site/Subnet 1 B - Site/Subnet 2 File Share - at Site 3 We have an AG listener that has two IP addresses spanning the two subnets (1 up, the other down ...
7
votes
4answers
228 views

In what cases a transaction can be committed from inside the CATCH block when XACT_ABORT is set to ON?

I've been reading MSDN about TRY...CATCH and XACT_STATE. It has the following example that uses XACT_STATE in the CATCH block of a TRY…CATCH construct to determine whether to commit or roll back a ...
3
votes
1answer
108 views

SQL Server - monitor changes on linked server

We have a local SQL Server database which we have full control over, and in this local server we are linking a remote SQL Server database, to which we have only read access (no schema changes, no ...
6
votes
3answers
272 views

The fastest way to get the most recent records

I am looking for the fastest way to return the most recent record for each reference number. I quite liked the solution from BrentOzar.com, but it does not seem to work when I add a third condition ...
2
votes
0answers
69 views
+50

How to Replicate data between a hosted database and a local database

I have a Cloud hosted Database, maintained by a different company. Here is my scenario: Cloud Hosted Database SQL Server 2012 - Need to connect through a VPN. We setup a local Server with with ...
0
votes
0answers
26 views

One bitmask column vs multiple bit columns [duplicate]

I have many tables with a column storing a set of days of week. I used to use MySQL and data type "set" worked well. For example: [worker] id weekday_mask (data type: ...
4
votes
2answers
184 views

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

I have an Operations server running Windows Server 2012R2 and SQL Server 2014 Enterprise back end. This server is used to deploy new code to other production servers via cmd file called by a SQL ...
0
votes
0answers
21 views

Suitability of sqlite vs sql server compact or local db [on hold]

I am currently using sqlite with my C# application. The issue why I need this is for simple deployment using the clickonce. I could not use mysql here is cause of steps needed for its depolyment. I ...
2
votes
0answers
30 views

Very high avg_write_stall_ms for tempdb [duplicate]

What could be causing (and how to fix) my tempdb files (8 equal size) to have [avg_write_stall_ms] of 7300ms and the next highest data file is 47ms. System is Hyper-V fail-over cluster, SQL 2012 ...
6
votes
1answer
446 views

What does SOS in SQL Server stand for?

I am reading SQL Server 2012 Internals book now. There are several phrases with SOS acronym, such as "SOS Scheduler" (in chapter about the scheduler), "SOS Memory node" (in chapter about the free ...
4
votes
1answer
71 views
+50

Can't get databases or its objects with powershell

I have Powershell installed both in my server and development pc. In my dev machine apparently have a most recent version of PowerShell installed or some its components of SQL Server 'sqlps'. My ...
6
votes
1answer
59 views

Does SELECT INTO Reserve the #Object Name in TempDB Prior to Runtime?

Putting together a quickie proc to help with debugging, I ran into a what seems to be a error in the compiler. create proc spFoo @param bit as begin if @param = 0 begin select * ...
0
votes
2answers
58 views

How many log files does a database usually have? [duplicate]

I'm studying SQL Server. I have read that a database can have several data files and one log file, but recently I was heard that a database can have more than one log file. Is this true?
2
votes
1answer
114 views

CheckDB error 9003 SQL server 2014

Currently I am restoring SAN snapshots to another server nightly and running weekly checkdbs on that test server. The checkdb is throwing the following errors. The log scan number (105217:402:0) ...
36
votes
4answers
27k views

Index Seek vs Index Scan

Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan. What is the difference between and index seek and an index ...
5
votes
3answers
5k views

“TASK MANAGER” process is taking over a single-user mode database. What is it?

This is a duplicate of the question I asked on stackoverflow, but I was advised, that someone here could have a better idea what is happening. I have a sporadic problem, when upgrading SQL Server in ...
-1
votes
3answers
190 views

Speeding up insert in SQL Server

I have a stored procedure which inserts some records in a few tables. In couple of tables at least , the inserted records number 10000+. Not more than 15K though. Takes 3-5 mins for this procedure to ...
1
vote
3answers
192 views

Can FOR XML PATH return NULL for an empty resultset?

I would think this query select null for xml path('foo') would return NULL, but actuality <foo/ > is returned. Is there any way to achieve this behavior? To clarify, the behavior I would ...
2
votes
3answers
1k views

What are alternatives to Replication to distibute data (Microsoft SQL Server)

Is anyone knowledgeable of alternatives to SQL Server replication? I have a scenario where separate databases will share a lot of the same data which complex queries will match against. We want to ...
0
votes
1answer
22 views

Partitioned view with dedicated “routing” column

I have an application (Microsoft Dynamics NAV to be exact) that splits up data for different companies in tables with the exact same structure. I can not change that behavior, so I built VIEWs that ...
1
vote
7answers
2k views

Do SQL Server or MYSQL contain JSON supporting functions?

Do SQL Server or MYSQL contain JSON supporting functions? If so, then please tell me the name of the functions or where else I can find it? I am new in this stuff and I want to implement JSON with ...
1
vote
1answer
21 views

Synchronous SQL Server Replication only for specified subscribers

I would like to create database replication between multiple localisation. There will be one publisher and multiple subscribers. Is it possible to define that synchronisation is synchronous only for ...