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

learn more… | top users | synonyms (2)

2
votes
2answers
692 views

SQL Server 2008 R2 Database Diagram Error Code

I have sysadmin privileges on a SQL Server 2008 R2 server. I'm working with the development database and want to save a database diagram I created. When I try to save I get the following error message:...
3
votes
4answers
30k views

How to open SQL Server 2008 R2 Configuration manager in Windows 8.1

In Windows 8.1, i can't find Configuration manager of SQL Server 2008R2. I have already followed the steps as specified in this question. But Configuration manager is not showing up even after ...
1
vote
0answers
51 views

SQL Server x64 DBCC Shrinkfile Dies not release Unused space (never ends No errors) [duplicate]

I have a 1.8TB Production database in my test environment on a 2TB drive. Currently there is only 1 .MDF and 1 .LDF (on a separate drive). The Database has had some major cleanup of unused indexes, ...
2
votes
1answer
63 views

Missing .msi files gives error while repairing SQL Server 2008R2

I've got a SQL Server 2008R2 SP1 database installed and I'm trying to run a repair on it using a 2008R2 install binaries. In the middle of the repair loading, the window suddenly shuts down and gives ...
0
votes
1answer
41 views

When and Who created linked server?

I came across a linked server that is pointing to production from test environment with full access. Is there a way to find out who and when this linked server was created? Based on last modified date,...
3
votes
1answer
113 views

How to prevent SSDT publishing from dropping columns

I want to create a publishing profile that DOES a complete schema comparison and publishing BUT does not drop any tables or any columns that have been removed between the old and new version. I am ...
-1
votes
1answer
169 views

SQL query failing because of commented part of T-SQL query

I am executing below query but I am facing issue. Note :SQL 2008 R2 and SQL 2014 running with two diff serv accounts on Win 2008 R2 and Win 2012 R2. TCP Ports, Firewall ports are all enabled along ...
1
vote
3answers
64 views

Find and Remove Duplicate Indexes

I have more then 1000 tables in my database. On few tables I found duplicate Indexes. Now I want to find duplicate indexes in the database and remove them. Means, if a table have 2 or more indexes on ...
1
vote
0answers
31 views

Report subscription deletion failed due to FK constraint

We have many SSRS report timed subscriptions (SQL Server 2008R2 SP4). One of our report developers tried to delete a report subscription, and got the following error: An error occurred within the ...
0
votes
0answers
45 views

Is it possible to inspect data in NORECOVERY mode [duplicate]

We are trying to restore a dataset to our online database that was inadvertently deleted. However we aren't quite sure as to when. Is it possible to restore a test database using NORECOVERY, but still ...
19
votes
3answers
280 views

What's the best way to archive all but current year and partition the table at the same time

Task Archive off all but a rolling 13 month period from a group of large tables. The archived data must be stored in another database. The database is in simple recovery mode The tables are 50 mil ...
1
vote
1answer
449 views

SQL Server 2008 R2 Cluster Service pack 3 fail to failover node [duplicate]

I have a SQL Server R2 cluster on Windows 2008 R2 boxes, and today i was to install SP3. I installed it without any errors and rebooted the passive node. When i then try to failover to the passive ...
4
votes
1answer
2k views

SQL Cluster Resources Disappear After Removing Disk Resource

In the process of removing an usused disk resource from cluster, I would follow these steps: 1) remove dependencies on the disks by the SQL Server resource, 2) take the disk resource offline, 3) ...
3
votes
2answers
101 views

Error with sys.dm_db_index_physical_stats

I am executing a select statement to retrieve fragmentation percentage of indexes and facing below error. SELECT A.NAME,B.NAME,C.avg_fragmentation_in_percent FROM SYS.TABLES A INNER ...
1
vote
1answer
51 views

Running select affects performance of stored procedure called from code

In what scenario would running the same select statement in SSMS as that in a stored SP which is currently timing out start making the SP (which is being called from a service every minute (30 sec ...
0
votes
1answer
117 views

Difference between “Table-Incremental” and “Table-Update” for “Population Type” when scheduling Full Text Index Population

I want to schedule a Full Text Index's population to keep it more up to date rather than leaving it to Automatic and not knowing the next population time as the users are complaining about the data ...
-4
votes
1answer
40 views

Insert and Update a sql table at same time is not working [closed]

I am facing an issue with my SQL DB. I have a stored proc which inserts data into a tables [Table1],[Table2],[Table3]. But when I tried to update some data which already exists in those tables at the ...
-1
votes
0answers
45 views
3
votes
0answers
27 views

SSIS package Job Fails with Unexpected Termination

I scheduled a SSIS package in SQL Server Agent which failes sometime with "Unexpected Termination" without any error message. Any Idea why it happens? Thanks
1
vote
0answers
43 views

ms/write on tempdb data files are dead slow

I don't know why writes on my tempdb data files are extremely slow. ms/write on each of the tempdb data files reaches to more than 800 ms!!! that's even after I moved the 8 equally sized (7GB) tempdb ...
1
vote
1answer
23 views

Non-Admin access to SQL Server Configuration Manager [closed]

Is there a way that access can be granted for a non-admin user for SQL Server Configuration Manager?
3
votes
2answers
2k views

db_owner Member Can't Delete on Tables

SQL server 2008 R2. A user is part of an AD group. That AD group has a SQL account in this instance. That group has the following access to a Database: db_datareader db_datawriter db_ddladmin ...
0
votes
1answer
73 views

How to detect bad execution plans automatically?

I want to automate a way to detect when a stored procedure plan changed for worse. I am planning to do it using a list of critical SPs and checking when their plans changed. Then compare the execution ...
4
votes
2answers
142 views

Execution plan percentages don't add up to 100% [closed]

I am referring to SQL Server query execution plans to take query cost and then optimize the required things by looking at the plan. But the total of individual query costs adds up to more than 100%. ...
0
votes
0answers
30 views

Does having a .ldf for a index file cause issues?

Interesting find today, I have a file group that is used for indexing. It has one file that has a .ldf extension, which as you know for SQL Server, is the extension for the transaction log files. My ...
5
votes
2answers
102 views

RIGHT() works in a SELECT but not in an UPDATE?

I am trying to run a script which does some work including stripping the leading two characters from the string inside each row of a column. To do this I am using RIGHT() and it works fine when I use ...
0
votes
2answers
45 views

SSDT table column order

I realized that the behaviour of SQLPACKAGE.EXE changes depending on what changes have been made to a table schema: Adding a new column at the end of the table: "Table is changed" Removing a column ...
1
vote
0answers
10 views

SQL Server 2008 R2 Express edition limitation [duplicate]

I have website running on SQL Server 2008 R2 Standard edition and on new server we are provided with SQL Server 2008 R2 Express edition. I am sure that there are certain limitations like maximum of ...
1
vote
0answers
42 views

Backup command wait long for completion of CHECKPOINT

I executed simple query to backup my 189GB database on a server with 32GB RAM. BACKUP DATABASE DB_NAME TO DISK='D:\BACKUP\FILE1.BAK', DISK='D:\BACKUP\FILE2.BAK', DISK='D:\BACKUP\FILE3.BAK', DISK='D:\...
0
votes
0answers
33 views

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed

Daily at 5:33:18 AM I am getting this alert , i checked error log and found these entry daily. what is the reason behind this and what is the fix ? Login failed for user 'NT AUTHORITY\ANONYMOUS ...
0
votes
1answer
185 views

Application is hanging when database upgraded from SQL Server 2000 to SQL Server 2008 R2

Presently we are working with SQL Server 2000, the database is perfectly working with our software for retail sales counter application developed with VB 6.0. But after upgraded to SQL Server 2008 R2, ...
-1
votes
1answer
26 views

SA password not working for .\SQLAdmin

Had some issues with jobs not working. To verify it was a security issue, changed the SQL Agent to run under DOMAIN\Administrator. Answered my question about the security issue. Went to change the ...
0
votes
0answers
50 views

How can we decide FILLFACTOR of tables on production database? [duplicate]

I am using SQL Server 2008 R2 E2 x64. on production machine having 8 core processor, 32 GB RAM. My database is being used commonly for OLAP & OLTP both. There are some constraints from Infra, "...
2
votes
0answers
49 views

Select Query with S lock wins on Insert with IX lock in deadlock

Select Query with S lock wins on Insert with IX lock in deadlock in Sql Server 2008R2. Attaching the deadlock xml file.Please help! http://pastebin.com/nY9EXbS8 This is from the error log by ...
3
votes
1answer
66 views

Adding an ID column based on two other columns

I have a query which returns output as follows: [Location] [Reference] [Year] [Int1] [Int2] England 1 2015 13 201 England 1 2015 12 57 England ...
3
votes
1answer
35 views

Resource Governor seems to increase resource usage

We are in the unfortunate position of running a data warehouse on the same instance as our production OLTP system. Recently a "power user" joined our company and has begun running SQL statements ...
1
vote
0answers
21 views

McAfee antivirus and File stream SQL Server 2008 R2 Issue

I am facing file stream issues in SQL Server 2008 R2 and I am not able to correlate things and how to fix them. Below are the event logs. Please let me know if they both the errors (One is Mccafee ...
2
votes
1answer
861 views

SQL Server connection failed for domain user from Mac OS machine

I have SQL Server 2008 R2 SP1. I am using connection string to connect to SQL Server. In connection string I am passing the driver path, server name, TCP port number, user name with which I want to ...
0
votes
1answer
129 views

SQL Server : how to decide if FULL backup or DIFFERENTIAL

The following scenario (on SQL Server 2008 R2 and 2012 servers) A co-worker of mine restored another DB into the one our process was trying to create a differential backup for. As I am currently only ...
0
votes
1answer
432 views

SSRS CROSS Domain Reports Server Authentication

I have a pretty complicated situation at hand, let me try to explain. I have a SQL Server and a Web Server in a separate domain (lets call it domain A). SQL Server has the databases for Reporting ...
1
vote
1answer
149 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 ...
5
votes
1answer
150 views

Cluster node upgrade of SQL Server 2008 R2 to SP3 fails when moving resource

I'm trying to update a cluster to SQL Server 2008 R2 SP3. I've installed SP3 on the passive node but when I failover the cluster it errors when running through it's upgrade scripts. The messages &...
0
votes
3answers
4k views

SQL Server Configuration Manager returns remote procedure call failed

I open the SQL Server Configuration Manager and it returns remote procedure call failed. This is Windows 7 Ultimate version, SQL Server 2008 R2 Express version with advanced services. Previously I ...
0
votes
0answers
33 views

TCP Provider Issue

Error : TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to ...
0
votes
1answer
125 views

How to delay replication start

I'm creating a new publication for replication using the @allow_initialize_from_backup option to avoid the snapshot immediate creation and therefore locking all objects replicated on articles. The ...
-1
votes
0answers
20 views

How to prevent database server from lagging when auto back up of data to backup DB is in progress?

We have a windows 2008 r2 server with MSSQL server running on it as our application's database server (we use it 24 hours). It has auto back up every midnight (as it is the time when less users are ...
2
votes
1answer
119 views

Intermittent errors sending email from SQL Server Database Mail

I've been seeing some SMTP errors in my database mail logs. So I have a set up a test SQL Agent job that runs every ten minutes (it's only action is to SELECT NULL). It is set to send email ...
2
votes
2answers
329 views

Stored procedure working when run manually, not running in SQL Agent Job

When I execute a Stored Procedure through a SQL Agent Job, I get the following error message: Executed as user: NT AUTHORITY\SYSTEM. XML parsing: line 10, character 33, unexpected end of ...
3
votes
1answer
114 views

SQL Server 2008 R2 DB grows overnight and slow

I am running a SQL Server 2008 R2 SP2 DB. I have a DB that was running around 19 GB in size and three days ago grew to 34 for no good reason. We typically have about 100 MB growth in the DB every ...