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
66 views

DBCC IND not working

I'm trying to get the pages used by a clustered index on table1 in a SQL2008 R2 DEV box using the below query. dbcc traceon(3604, -1) go dbcc ind (0, 'dbo.table1', 1) go but it still displays ...
2
votes
2answers
317 views

Recommended memory configuration for many SQL Server instances per server

I am currently managing a server cluster for a SaaS system. We currently have 6 SQL servers running MSSQL 2008 R2. Each server is a quad core virtual machine with 16GB of RAM provisioned on a server ...
1
vote
1answer
48 views

Installing SQL Server version 10.50.4000 is failing

I am attempting to install SQL Server 2008 R2 version 10.50.4000 on a virtual machine running Microsoft Windows Server 2008 R2. When I try to install SQL Server, I get an error that, when I look it ...
0
votes
0answers
56 views

Why can't a user in a group with the sysadmin role see any stored procs in SSMS?

A developer who is in a "Developers" group granted the server fixed role "sysadmin" on the development SQL Server installation is unable to see any stored procedures in a database - only the "System ...
-1
votes
1answer
52 views

RESTORE DATABASE execution continues restore completed

Today, I faced a strange problem which didn't cause any error or downtime. I executed a query to restore a database. RESTORE DATABASE Database_Name FROM DISK='PATH\FILE1.BAK', ...
0
votes
1answer
36 views

sqlserver 2008r2 service pack incompletely applied on shared features

I have installed sqlserver 2008R2 service pack 3 on my instance. The installation went through successfully but the instance still shows version number of SP1 in management studio. When I start the ...
2
votes
1answer
89 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 ...
1
vote
0answers
55 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, ...
0
votes
1answer
48 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,...
2
votes
0answers
34 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 ...
1
vote
3answers
69 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 ...
0
votes
0answers
46 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 ...
3
votes
2answers
110 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
52 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 ...
3
votes
0answers
35 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
46 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 ...
3
votes
1answer
310 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
1answer
40 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?
0
votes
0answers
34 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 ...
0
votes
0answers
36 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 ...
-1
votes
1answer
27 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 ...
1
vote
0answers
46 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
51 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, "...
4
votes
2answers
143 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
1answer
84 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 ...
3
votes
1answer
67 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 ...
2
votes
0answers
52 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
37 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 ...
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 ...
1
vote
0answers
24 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 ...
5
votes
1answer
205 views

TDE - Encryption on a large database

I'm in the process of researching TDE implementation on a VLDB and would like to know if anyone has real world experience with enabling encryption on a production server. What kind of problems (if ...
1
vote
1answer
50 views

Wrong Last Execution Time sys.dm_exec_procedure_stats

I am running the following query it shows a Stored Proc that I know ran on Mon 6/6/2016 8:45 AM but the below query shows that the last execution time was 2016-05-19 12:09:23.197. Is there something I ...
0
votes
1answer
40 views

Organizing Stored Procs and Views

I have a database with 112 stored procs, 7 views and 88 tables. The problem is over time I have created tables and stored procs as I needed without taking the time to think about what I might be ...
19
votes
3answers
300 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 ...
2
votes
0answers
61 views

SSDT Syntax Error 80001 on every Select statement starting with “(”

Visual Studio / SSDT marks the following script line as wrong giving "SQL80001 Wrong Syntax next to SELECT" and also "SQL80001 Wrong syntax next to ')'" error. (select a from [tab1]) union (Select a ...
3
votes
1answer
95 views

Creating SQL Server triggers to copy a record from one table and populate another

I want to create a trigger that will copy a record from one table and populate another after an insert or update. Users will enter a date in an inspection table and I want that date inserted or ...
1
vote
1answer
65 views

Howto avoid textdata of trace gets truncated?

I have a trace file including the TEXTDATA column created by a server-side tracing. Some of the traced queries are very long. If I re-open the trace file on Profiler, the relevant long query is ...
0
votes
2answers
65 views

Upgrade a database from SQL Server 2000 to SQL Server 2014

I have database in SQL Server 2008 R2. I took a backup of this database and restored that on a SQL Server 2014 server. However, some of the database queries are not looking compatible to the new ...
3
votes
3answers
110 views

Update Null Values With Other Values

I am new to the SQL Server world. In my example below, I have two companies: Building Blocks Red Cement Only one entry for each company has a value for the columns power1, power2, power3. I need ...
1
vote
1answer
69 views

SQL Server uses more memory than maximum setting [duplicate]

Recently I have added more memory to my VM which has SQL Server installed (now it has 32 GB). I have set the limit in SQL to 24 GB. A few days later I see in my process window that the SQL Server ...
1
vote
0answers
11 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 ...
2
votes
1answer
41 views

Use BCP To Copy When Data Has Apostrophe In It

Anytime my data has an apostrophe in it, the data is automatically split into the next field (which is skewing the result set). Let's say this is my DDL Create Table OneTwoThree ( userid varchar(...
4
votes
1answer
78 views

BCP To Copy Headers

I am using a stored procedure to populate a table with the data that I need. I am then using BCP to copy the data to a .csv file. This process works, the one thing it is missing is that BCP is not ...
0
votes
0answers
74 views

Ola Hallengren backup failed

I have been using on my SQL Server 2008R2 server Ola Hallengren solutions for almost two years. Last night backups failed, both System databases and user databases with error below. Example of ...
3
votes
1answer
67 views

Diagnosing a block in a process that EXECs several layers of stored procedures

Disclaimer: I am a developer. Please be nice to me. I am not the developer that is responsible for what follows. I try to be one of the good ones. I have inherited a support ticket that involves a ...
0
votes
0answers
42 views

Which column to trust about the event order in a profiler result?

Hi I recorded some events using the SQL profiler and I want to work out the exact order of what happened first. But I am having trouble in this basic task, because what ORDER BY EventSequence showed ...
6
votes
2answers
141 views

Transactional replication latency issue

I inherited database systems like this. Currently I have publisher database in SQL Server 2005 compatibility mode on Windows Server 2008 R2 with SQL Server 2008R2 SP2 machine. Distributor is on the ...
2
votes
1answer
69 views

Migrating from SQL Server 2008 R2 to SQL Server 2014

I'm migrating from SQL Server 2008 R2 to SQL Server 2014. Can someone tell me if my approach will work? Current Environment: SQL Server 2008 R2 Windows 2008 R2 Steps to Migrate from SQL Server ...
0
votes
1answer
92 views

Splitting a large table

My scenario is I look after an old system with a VB6 based front end and a SQL back end (good news, the replacement is being developed but in the meantime I need to keep this up). Now the application ...
6
votes
1answer
120 views

What are the “GDR” vs “QFE” hotfixes for SQL Server 2008 R2 SP3?

I'd always assumed that higher patch numbers were necessarily later releases, but now I'm not so sure. Looking at the SQL Server Build List for SQL Server 2008 R2 SP3, there are two possible ways to ...