SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

3
votes
1answer
56 views

False-failures when mirrored in high-safety mode without a witness

A number of DBs are mirrored in high-safety mode without a witness. I'm trying to understand what might happen in the case that a DB (or DBs) experiences a false-failure while the primary server ...
3
votes
1answer
63 views

Msg 8905: DBCC CHECKALLOC Error Messages

We recently experienced a database corruption and run integrity checks. DBCC CHECKDB returned a lot of consistency errors and 2 allocation errors. We have fixed consistency errors by moving data, ...
0
votes
1answer
37 views

Determining CPU Usage of last week

We got a production issue last week (high CPU spike) on 25-Feb due to which server reboot was performed but ticket landed our DBA queue just today for analysis on CPU spike. Could you pls help me in ...
0
votes
1answer
33 views

Multiple Replication Type Publications on Single DB

I have 2x critical DB's which until recently; I was reporting on as well as using in production. DB1 is a Vendor DB and DB2 an in-house DB used in conjunction with DB1 for custom tables and views (as ...
3
votes
1answer
34 views

Select Permissions Multiple Schema

I have an interesting one that I have not had the pleasure of solving before. I have been asked by a client to implement some views for reporting purposes, the views will be accessed via PowerBI, ...
0
votes
1answer
39 views

Error 823 and no connection

One of our servers tells us it has Error 823 issues: TITLE: Connect to Server Cannot connect to ServerName. ADDITIONAL INFORMATION: Warning: Fatal error 823 occurred at Mar 1 2016 > 10:...
1
vote
0answers
44 views

Why does the 9th Sql Connection in a row fail with a transaction manager error? [closed]

I am debugging an asp.net application on local machine that is connecting to a remote SQL Server database and run into an unhandled execption saying: System.Runtime.InteropServices.COMException: ...
2
votes
1answer
226 views

VMWare VMotion causing performance to increase?

We've been experiencing strange issues for awhile now with our virtual environment running SQL Servers. We randomly get calls from users about very poor performance on the SQL boxes. Sure enough when ...
0
votes
0answers
24 views

Operator Disable enable in sql server via JOB when fail over occured

I want to disable and enable the the operator in sql server when fail over is occurred either manual or automatic. I have created below alert on both principal as well as mirrored server and in ...
1
vote
0answers
62 views

Include failed step's error details in database mail for failed agent jobs

I receive database notification mail whenever a job fails. Error message of the failed job (Step Id=0 in the Job History) is included in the mail; MESSAGES: The job failed. The Job was invoked by ...
3
votes
1answer
156 views

Howto find out details and reasons for idle transactions?

I have started to use the "Idera SQL check" tool. It shows floating bubbles for each process active on the Sql Server system: Those bubbles are surrounded by a red border in case the transaction is ...
7
votes
1answer
108 views

Index SEEK is not used unless OPTION (RECOMPILE)?

(Question moved from SO) I have a table (dummy data) with clustered index contains 2 columns : Now I run those two queries : declare @productid int =1 , @priceid int = 1 SELECT productid,...
3
votes
1answer
75 views

Login issue in Logshipping

I have configured log shipping in my primary serverA and secondary serverB. Everything works fine until I bring the standby database online. Users are not able to login to the secondary serverB ...
1
vote
2answers
74 views

How to estimate REDO phase time during RESTORE

I am restoring database now and get it hang on 100% but still going (doing redo phase). I am thinking how to estimate time of completion for this phase. But because SQL have no build-in way to do it, ...
0
votes
2answers
52 views

Inline SQL To Set Fully Qualified Name [closed]

Why does my variable @FQN not set accordingly? Is this a problem with my syntax? A Problem with using a cursor? What should I do so that my variable @FQN sets appropriately? This is what my print ...
4
votes
1answer
101 views

How do I know that the high number of compilation/sec is because of memory pressure not bad queries?

My server is having a high compilation/sec compared to batches/sec (more than 10%). How do I know that this is because of memory pressure that forces removing cached plans from memory, or because of ...
0
votes
0answers
29 views

How to replicate SQL Server rowversion behaviour for some fields only

SQL Server version 10.50.4000. I have a table: create table people ( national_id char(16), full_name char(255), last_visited datetime ) I want to add a field that will work exactly ...
1
vote
1answer
17 views

execute as login for nonexistent login successful

On Sharepoint database server, we found 18456 state 11: infrastructure error for account Domain\machineA$ every 10 minutes and nt authority\anonymous every hour. I checked syslogins but didn't find ...
0
votes
1answer
43 views

Get Sale Info By Employee

I need a way to get detailed sale information for each employee in the StoreInfo table. Each employeename in that table has it's own table with data structure mirroring the table information below. ...
1
vote
1answer
56 views

How to backup a secondary log shipped standby mode database

We have a secondary transaction log shipped database configured for standby mode. Its primary purpose is for data recovery and for select only queries for reports etc. As the database is very big we ...
2
votes
1answer
63 views

Sync two SQL Server database

Currently we have one SQL Server 2008 R2 database server in location X. All application users are in location X. All instances of the application can add data to database table and which may be ...
0
votes
0answers
82 views

Linked server calling “select * from table” query (ODBC Connection)

Every time I use any command using my Linked Server connection on any table it call first a "select * from table" If I use a simple query like that: INSERT INTO LINKEDSERVER...MYSQLTABLE SELECT * ...
2
votes
1answer
59 views

Will replication commands build up if the distribution agent is stopped?

Assuming I have a transactional publication with a single article. There is a Publisher (PUB1) with two Subscribers (SUB1 AND SUB2), and a separate Distributor (DIST1) box is used. I would like to ...
7
votes
2answers
423 views

Property class Length Annotation PersistedResolvableAnnotation

I am trying to deploy an ssdt project using sqlpackage.exe utility. I get the following error (in German): Fehler bei der Erstellung des Bereitstellungsplans. Die Bereitstellung kann nicht ...
0
votes
1answer
45 views

Intellisense not working for one connection but works for others

I can connect to multiple database servers. Intellisense works on all of the connections except one. It used to work on this connection also until recently. I did the following: Enable Intellisense ...
12
votes
4answers
765 views

Calculate Total Visits

I am trying to write a query where I have to calculate number of visits for a customer by taking care of overlapping days. Suppose for itemID 2009 start date is 23rd and end date is 26th therefore ...
2
votes
1answer
56 views

Identify When Dates Returned Are 1st, 2nd Or 3rd Date in Table

For the 1st 3 orders, a credit is given, so I am needing a way to flag the 1st 3 dates for a customer. My issue that I am having with my query is it flags the first 3 dates returned regardless of if ...
8
votes
4answers
1k views

How are SQL backups different from IT regular nightly server backups?

Our IT department backs up the entire server every night (an SQL Server instance is installed on this server), which should be backing up that server as well as the entire network, in case something ...
3
votes
1answer
100 views

How do I get useful SQL Server database performance statistics?

We currently have several shared SQL Server servers and instances in our organization. They are going with a different architecture where business units will get their own SQL Server instances. We ...
0
votes
1answer
50 views

Grant reference on type

What does GRANT REFERENCES ON TYPE::dbo.myType TO public exactly mean? I found this statement together with every single user defined type we have. I wonder if this is useful or not. AFAIK this ...
14
votes
3answers
1k views

Insert performance increases under load: Why?

I have a piece of code that performs inserts into highly denormalized tables. The tables have numbers of columns ranging from ~100 to 300+. This is SQL Server 2008 R2, running on Windows Server 2008. ...
3
votes
3answers
69 views

Assigning a domain user to run MSSQLSERVER service 2008R2

I have done this on a server (windows server 2008) before, but can not achieve the same thing on another virtual server I want a low level domain user to run the MSSQLSERVER service so that I can ...
3
votes
1answer
54 views

Identity management in Transaction replication with updatable subscription

I have migrated my publisher DB to new server yesterday. For migration what I did was removed subscriptions and publication completely backed up DB on which publication was configured restored DB ...
2
votes
1answer
145 views

Can't recycle the ERRORLOG - error 17049

When we attempted to run sp_cycle_errorlog we get the following: Msg 17049, Level 16, State 1, Procedure sp_cycle_errorlog, Line 9 Unable to cycle error log file from 'G:\SQL_DATA1\MSSQL10_50....
8
votes
0answers
144 views

SQL Server - Handling localization of strings in nested non-deterministic view stacks

While profiling a database I came across a view that is referencing some non-deterministic functions that get accessed 1000-2500 times per minute for each connection in this application's pool. A ...
4
votes
4answers
191 views

Validate three digits before decimal and after decimal point six digits

I have columns called longitude and latitude with nvarchar data type. The user can enter something like (23.23424432),(432423.343), which should not be allowed. Can anyone help me to validate this in ...
1
vote
0answers
49 views

Hundreds of TCP connections opened by sqlservr.exe

We have Tomcat on one machine and SQL Server on another. Our SQL Server machine is Windows 2008 R2 64bit machine with 32GB usable RAM 4 processors, 16 cpus. We use database pooling to keep db ...
5
votes
2answers
190 views

Can I defer a configuration change until the next time SQL Server is restarted?

Let's say that I want to change a SQL configuration setting like MAXDOP. I know that changing this on an active instance will severely impact performance, as it will clear all of the cached query ...
1
vote
2answers
92 views

How can I pass a list of databases to sp_MSforeachdb?

I have the following script that when run gives me a comma delimited list of database names. SET NOCOUNT ON declare @db_list NVARCHAR(MAX) DECLARE @command varchar(1000) ...
10
votes
2answers
244 views

What is the sense and benefit of using SqlCommand.Prepare()?

I came across developer code where SqlCommand.Prepare() (see MSDN) method is extensively used in advance of execution of SQL queries. And I wonder what is the benefit of this? Sample: command....
8
votes
3answers
203 views

Is it possible to disable the remember password checkbox in SSMS?

Some time we are providing same laptop for 4-5 employees in our office for some testing purpose (since a costly licensed tool is exist in that laptop only), also asked them to do not click the ...
4
votes
1answer
56 views

Generating a numeric pattern in query result set

I'm looking to add an artificial grouping to my result set. I can't utilize ROW_NUMBER or RANK. Take the following result from SELECT Col1, Col2 FROM myTbl: Col1 Col2 ---- ---- x y x ...
3
votes
1answer
37 views

Display Data Using Pivot

Is a pivot the best way for me to display this data? I want to show id, uuid on the left then show each event1 across the top with a 1 if exists or a 0 if it does not. So look like this id uuid ...
0
votes
0answers
28 views

Creating Replication from scratch and do not want to change indexes or Procedures on Subscriber Servers

i have a situation here I have replication from Server A (2008R2) Pub to B(2012) Sub for few databases.. I have 10 non-Clust-Indexes on X table on DB1 on A Server and non-Clust-Indexes 100 X ...
3
votes
2answers
94 views

Return values from Left Table Only Once

This is my table structure and a sample query. However, my query returns these results: ID event1 Exists 1 cycle No 1 cycle Yes 2 run Yes 3 walk No As you can see ID 1 is ...
1
vote
2answers
82 views

Slow query that times-out my application, any advice?

I have a query that it seems taking about 50 seconds to execute from SQL Management Studio, but when a user tries to run it as a report from a front end application (that was built in VB), the ...
1
vote
0answers
22 views

SQL Server 2008 R2 Service - large memory consumption [duplicate]

I have a SQL Server instance where the service is consuming 173 GB, the max server memory is set at 60 GB. How do I determine where that memory is or if I have a memory leak?
1
vote
1answer
43 views

Differential backups are 2/3 the size of the full backup

I was investigating why my differential backups were taking almost as long as my full backups and I noticed that the size of the first differential backup after the daily full backup was 2/3 the size ...
0
votes
0answers
46 views

SQL Server 2008 R2: Could not connect to TCP error code 10061

I am having the problem that I'm simply not able to connect to a running SQL Server using Microsoft SQL Server Management Studio. Every time I try to connect to the server I am getting: Could ...
1
vote
1answer
50 views

Does @@FETCH_STATUS have (or need) an equivalent to SCOPE_IDENTITY?

MSDN advises that @@FETCH_STATUS be used with care because it's global: Because @@FETCH_STATUS is global to all cursors on a connection, use @@FETCH_STATUS carefully. After a FETCH statement is ...