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

Shrinking log files on development

I've been looking at getting some disk space back on some of our development machines. These are machines that are not being backed up, not being developed on directly, and not being maintained ...
1
vote
1answer
37 views

Is there an actual problem in my storage IO or is the data incorrect?

Running SP_Blitz indicated that I have a slow read/write performance on my drives. Reading further, I eventually landed on this query to view the actual statistics of IO occurring on my drives: ...
1
vote
1answer
94 views

The process cannot access the file because it is being used by another process

I'm using SQL Server 2008 R2. Absolutely new to DBA role, but I've worked with SQL and VS for a while. I've installed the 2 aforementioned softwares on my home computer. If I have VS2010 open and ...
1
vote
2answers
43 views

SQL Replication across the internet give named pipe error

We've had a db replication working between two servers across the internet. Server A is sitting on our server network (Windows Server 2008 + SQL Server 2008 R2) Server B is sitting on a different ...
1
vote
1answer
87 views

First log backup after FULL taking lots of space

In the backups I'm doing in my database, the first LOG backup I do after the FULL backup is taking too much space, even more than the full backup. For example: 4:50 AM: FULL BACKUP - .bak 7.2GB ...
0
votes
1answer
101 views

Rebuild Indexes on SQL Server Standard edition

We have SQL Server 2008 R2 Standard on a Windows Server cluster. I want to defrag all indexes on a database. What is the best approach to disable all connections and allow the rebuild of indexes? ...
0
votes
1answer
66 views

What or Who is taking SQL Server backups [duplicate]

I have inherited a SQL Server 2008 R2 (SP3) Database. There are backups being taken daily at seemingly random times. The backups are not being saved locally on the server. I see the backup information ...
2
votes
1answer
82 views

Why does SQL Server use this index?

I have two indexes on a table with 786602 rows: CREATE NONCLUSTERED INDEX [IX1] ON [dbo].[O] ([A] ASC, [B] ASC) INCLUDE ([c1], [c2], [c3]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ...
6
votes
3answers
76 views

Query Processing & The Buffer Pool

When SQL Server Server 2008r2 begins to process a query, I know it needs to parse the SQL Text and create the plan, plus a few other steps. It is doing all of that in memory OUTSIDE of the buffer ...
2
votes
2answers
149 views

Create index with a commentary

I would like to add some comments to my indices. So I added some additional information to the index creation script, expecting to see this comment again as soon as I open the script again. But it was ...
1
vote
1answer
50 views

Bringing SQL Server filegroups online

We've experienced an sql injection attack which corrupted our main db. The main db had two partitioned tables into filegroups. The corrupt database entered into Suspect mode, and it's been ...
3
votes
2answers
82 views

Switching between databases with dynamic SQL

I have a process which involves executing various commands between multiple databases - however, when I use dynamic SQL to change DB with 'use @var', then it doesn't actually change the database. ...
0
votes
0answers
33 views

How to Split string into multiple values using best approach [duplicate]

We are trying to save result of a query in database by tokenizing a column but query is taking more than 3 minutes to finish his objective. My question is, do we have any faster way to tokenize a ...
2
votes
1answer
261 views

Restarting SQL Server Agent on a cluster installation

I've just enabled the mail profile in SQL Server Agent and I need to restart the service for it to take affect but the Agent is running as part of a SQL Server cluster and I don't want the cluster ...
3
votes
2answers
278 views

Way to ignore pages in memory for 1 query

Is there some way to ignore both cached query plans and any data pages in memory for a single query or batch? Something like a table hint or other option which can be turned on just for the current ...
2
votes
0answers
22 views

Combine several missing index suggestions with one index?

I investigate on missing indexes recommendations of my sql server engine. Looking at the most useful ones (for a specific table) that are candidates based on their potential usage and potential ...
3
votes
2answers
65 views

How to handle lock information?

Due to analyzing several deadlocks I used sp_blitzIndex to get an overview about locks. There is a high accordance between the tables (and views) affected by the deadlocks and the tables being ...
0
votes
0answers
54 views

SQL Server DB in suspect mode after installing SQL Server 2008 R2 SP3

One of our production DBs is stuck in Suspect mode since we installed SQL Server 2008 R2 SP3. Earlier today we found that most text fields (nvarchar) had the following data appended to it ...
0
votes
0answers
18 views

Dropping historic Partitions that are no longer required while in P2P replication

The Company I work for has a set of databases (largest just below 1Tb) on different servers - 2 in the US, 2 in Europe. We run full peer-to-peer replication per database between the 4 nodes - so they ...
3
votes
2answers
351 views

Connect to SQL Server without specifying port

I am trying to use what appears to be a rather badly written application which connects to an SQL Server database which I specify. I cannot seem to connect to the SQL Server instance I wish to use ...
0
votes
1answer
28 views

Partitioning a database

I have a database that's growing larger by the week (as expected). Key tables are becoming huge and slower to perform queries against. Whilst the indexes are sufficient, I've read the partitioning ...
0
votes
1answer
68 views

SQL Server does not failback to the primary node

We are running SQL Server 2008 R2 on a Windows Server 2008 R2 two-node cluster. My choice of machine is primary, and the secondary is just a low-end machine for contingency planing. We had a ...
3
votes
1answer
199 views

Cannot reinstall SQL Server 2008 R2 after downgrade from Windows 10 to Windows 7

Scenario: using SQL Server 2008 R2 Express under Windows 7 without problems. Then I upgraded to Windows 10 without problems. Then I downgraded back to Windows 7 (due to problems with other ...
-2
votes
1answer
161 views

SQL Server 2014 is not showing

I have installed SQL Server 2014 in my machine (OS Windows 10). I already have SQL Server 2008 R2. Now I have 2 server instances and I also have 2 Management Studios. My problem is that I can not ...
1
vote
0answers
51 views

SQL query using CTE not working after database migration

We have recently migrated an application, and in the process we have migrated from SQL Server 2005 to SQL Server 2008 R2. I have noticed that some SQL queries using CTE do not work anymore since. I ...
0
votes
2answers
63 views

List all sessions from current user

I connect to SQL Server with a given user that is not an administrator. I want to get a list of all my open connections. I've found endless commands to do so but all fall in one of those categories: ...
2
votes
2answers
59 views

Does my table need a primary key and clustered index change?

I have a table holding 1.7 Milion rows. Definition: CREATE TABLE T( [ID] [uniqueidentifier] NOT NULL, [AID] [uniqueidentifier] NOT NULL, [BID] [uniqueidentifier] NOT NULL, [iType] [int] NOT NULL, ...
-3
votes
0answers
24 views

I need the results of 7 day period and sum all point according to 7 day [duplicate]

USE [MLMDB] GO /****** Object: Table [dbo].[payoutPoints] Script Date: 10/15/2015 16:58:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE ...
1
vote
2answers
30 views

Replication fails exactly after a week

We are application developers trying to decode why replication is failing. It fails every week on the same day pretty much same time. This has been happening for about a month now and our vendors ...
0
votes
1answer
26 views

Change SSIS package location (REPOST from SO) [closed]

My SSIS Package are located in shared drive with a long path name. To make the packages easier and not to remember long path name, I use "Map my network drive" option and use X: drive as an alias to ...
1
vote
0answers
130 views

Can't uninstall SQL Server 2008 R2 SP3

I have encountered the error on SQL Server 2008 R2 where Report Builder 3 fails to load with error Microsoft.ReportingServices.ComponentLibrary.Controls.dll, has a different computed hash than ...
-1
votes
3answers
157 views

SQL Server installation errors assembly

I have been trying to install SQL Server and now I am tired of it. I tried to version SQL Server 2008 and SQL Server 2008 R2. SQL Server 2008 gives the following error (.Net Framework installed) ...
0
votes
1answer
32 views

Alert and notification option in job properties

During SQL Agent Job configuration I see the options to define ALERT and NOTIFICATION. I see that notification is what I'd expect, a notification of a job failure for example. But I do not understand ...
0
votes
1answer
113 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 ...
2
votes
1answer
47 views

Missing indexes dmv count varies every day

I have a question to the missig index dmv. BOL says: Information returned by sys.dm_db_missing_index_details is updated when a query is optimized by the query optimizer, and is not persisted. ...
2
votes
1answer
66 views

Change recovery mode and enforce a transaction log backup?

I am going to do a special data deletion operation - once and during a maintenance with no user access (Sql 2008 r2). The result is a large deletion of millions of blob data from a table by ...
1
vote
2answers
203 views

Getting error “Time-out occurred while waiting for buffer latch type 2 for page (1:3564879), database ID 7.” But a database with Id 7 DOES NOT exist

I have a testing environment where I have scheduled jobs running restore tests as well as DBCC checks. These jobs run to: 1) copy database back up from the prod. 2) DROP all existing databases except ...
0
votes
1answer
125 views

Trouble Shooting “Unable to complete login process”

Issue On one of our SQL Server witness servers (This is part of our Database Mirroring setup for SQL Server 2008 R2) we have just started getting the below event in the windows event log. The ...
1
vote
3answers
186 views

Changing Compatibility level from 90 to 120 in sql 2014 migration

I have SQL 2008R2 server to migrate to SQL2014 Server. A side by side upgrade is what we are thinking for few databases in SQL 2008R2 which are having compatibility level of 90 or 80. Can I migrate ...
3
votes
2answers
83 views

How to read an excel file from a network machine using SQL?

I am trying to import some excel file data into a SQL table. I thought of using OPENROWSET for this. The issue is that the csv file is location in a different machine in the same network. How do I ...
0
votes
0answers
18 views

CMS List on Multiple Servers

We have SQL server instances set up in multiple locations (Standard Edition). To aid usage, I have set up a CMS on my local instance, with connections for various development/QA servers that are ...
2
votes
1answer
63 views

sys.dm_exec_procedure_stats missing history?

My boss would like me to find which (of approx 400+) stored procedures are not being used by our website any more. I'm found and reworked a script to give me the details of most recent executions, ...
4
votes
1answer
110 views

sys.dm_exec_procedure_stats DMV contents contradict BOL

I am doing some analysis of long running stored procedures and have come across something of an anomaly, according to books online sys.dm_exec_procedure_stats should only display one row per cached ...
1
vote
0answers
59 views

Sql Server Reporting Services will not connect to sql server 2008r2 using SQL server Connection

I have set up a new SQL Server 2014 default instance. In my Sql Server 2014 Configuration manager I see my 32 and 64 bit SQL Native Client 11.0 configurations and all protocols, pipes, tcp/ip,shared ...
12
votes
4answers
571 views

Claiming disk space after removing table field

I am running sql 2008 r2 and the db was working fine and fast for last 3 years untill about 3 months ago we added ntext field on very active and used table. Now we are starting to get out of server ...
0
votes
1answer
28 views

Check data is the same after restore SQL

I've backed up, transferred to another location and restored a database. Is there anyway to check that the data is the exact same? Would I be best using the CHECKSUM and then the VERIFYONLY method? ...
-3
votes
1answer
61 views

Finding the rows with the minimum value per group [duplicate]

How do I get the min timestamp value of the same Id number with its corresponding Value using SQL? Would anybody help me with this problem? (Please see attached image below) Sample Table Sample ...
-6
votes
1answer
48 views

Minimum value per group [closed]

How do I get the min timestamp value of the same Id number using SQL? (please see below picture) Sample Table Sample Table of what should be the result
2
votes
1answer
54 views

Having Sharepoint on a different Collation than the system Databases?

After installing Sharepoint I realized that the collation of all Sharepoint databases is Latin1_General_CI_AS_KS_WS while my server collation is Latin1_General_CI_AS. From official documentation I see ...
0
votes
0answers
10 views

is there a way to see statistics on when and how often a view is being used in SQL Server? [duplicate]

I was recently handed a request to produce a report on all the views in a given database along with an indication of how frequently they are used, presumably so that the developers can remove views ...