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

learn more… | top users | synonyms (2)

0
votes
0answers
12 views

Create Login on SQL Server 2008 R2

I want to create a login from windows and also set network location. Same like we do in UI i.e. click on search button select location and then add login. Currently, I'm using the following code ...
1
vote
1answer
32 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
242 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
0answers
35 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
44 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
49 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
31 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
77 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
52 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
43 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,...
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 ...
1
vote
3answers
66 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
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 ...
3
votes
2answers
104 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 ...
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
44 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
votes
1answer
171 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
38 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
31 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
34 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
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 ...
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
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, "...
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
1answer
76 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
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 ...
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
36 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
22 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 ...
-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 ...
4
votes
1answer
169 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
35 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
284 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
votes
0answers
22 views

Table imports in sql server_ how to move foreign keys along with tables (sql server 2008 r2)

We received request from Application team to import table from one sql server to other sql server database, we have created one database in same server and imported tables and taken backup and ...
2
votes
0answers
53 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
82 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
49 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
59 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
108 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
61 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
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 ...
2
votes
1answer
30 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
65 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
53 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
133 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 ...