This Tag is for SQL Server 2008 R2, major build version 10.50.xxxx

learn more… | top users | synonyms

2
votes
1answer
9k views

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

As the title states, I'm getting the following error when attempting to use a ASP.NET webapp: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed ...
2
votes
1answer
4k views

How to keep the structure of the Tablix when there is no data to show

We have a SSRS report which shows the result of a dataset in a tablix. If the query in the dataset returns no data, the header of the Tablix gets displayed but not the tablix cells below the header. I ...
2
votes
2answers
126 views

force to forget logins

How can I force SSMS to forget the recently used logins? Of course I use "SQL Server Authentication" with Login/Password fields. (It get's annoying, as everytime I open SSMS it shows the "Connect to ...
2
votes
2answers
4k views

Attach SQL Server 2008 R2 on SQL Server Express 2008

I have a database on SQL Server 2008 R2. I want to attach it on a SQL Server Express 2008 instance running on Windows 2003. I set the SQL server compatibility level on the database to SQL Server ...
1
vote
1answer
6k views

How to fix “The OLE DB provider ”MSOLAP“ has not been registered”?

Here's the code I'm using to register a linked server to SSAS: USE [master]; GO EXEC master.dbo.sp_addlinkedserver @server = N'ServerAlias', @srvproduct = N'', @provider= N'MSOLAP', ...
0
votes
1answer
779 views

Unable to drop non-PK index because it is referenced in a foreign key constraint

I have a table named MyTable. The primary key is an identity int column named MyTableID. There is a unique clustered index on the PK column MyTableID named PK_MyTable. I noticed there is an ...
-4
votes
2answers
72 views

Microsoft recommends using Rowversion over timestamp , but i can not find “rowversion” data type inside my sql server 2008 R2 [closed]

MS on this link http://msdn.microsoft.com/en-us/library/ms182776.aspx mentioned that we should use rowversion data type over Timestamps. Now inside my sql server 2008 R2 I want to create a column with ...
11
votes
4answers
8k views

SQL Server database on an SSD - any advantage to a separate file for every table?

I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key ...
6
votes
2answers
9k views

Sql Server - Best Practices for Growing Database Files

I have been monitoring file growth via the data collector in sql server 2008 r2 for two weeks. The database is growing consistently at around 35(MB)/Day. The DB has not yet hit the initial size of 2 ...
27
votes
5answers
5k views

Why is there still a varchar data type?

Many of my databases have fields defined as varchars. This hasn't been much of problem since I live and work in America (where the only language that exists is "American". ahem) After working with ...
13
votes
1answer
4k views

How to write a query which finds all circular references when a table references itself?

I have the following schema (names changed), which I cannot change: CREATE TABLE MyTable ( Id INT NOT NULL PRIMARY KEY, ParentId INT NOT NULL ); ALTER TABLE MyTable ADD FOREIGN KEY ...
12
votes
8answers
2k views

Version control for database objects

The database our developers are working on is too large (have a lot of database objects). We have to control db objects changes (change management). Our company cannot have a person who would be ...
12
votes
3answers
2k views

How often to backup the master database?

The suggestion in BOL is fairly vague: Back up master as often as necessary to protect the data sufficiently for your business needs. We recommend a regular backup schedule, which you can ...
11
votes
3answers
3k views

SQL Server database synchronization

Problem definition Our users need the ability to query a database that is mostly up to date. The data can be stale up to 24 hours and that is acceptable. What would be the lowest cost approach to ...
9
votes
3answers
149 views

What is a deterministic method for evaluating a sensible buffer pool size?

I'm trying to come up with a sane way to understand if the max server memory (mb) setting is appropriate (either should be lower, or higher, or stay the way it is). I am aware that max server memory ...
15
votes
2answers
3k views

Restore database excluding FILESTREAM data

Context We are developing a system with a large-ish database in the bottom. It is an MS SQL database running on SQL Server 2008 R2. The total size of the database is about 12 GB. Out of these, ...
10
votes
2answers
852 views

Optimization: Moving variable declarations to the top of your procedure

While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity. One thing the DBA mentioned was I should ...
9
votes
1answer
2k views

Interpreting SQL Server's Showplan XML

I just rolled out a feature on my site http://sqlfiddle.com that allows users to view the raw execution plans for their queries. In the case of PostgreSQL, MySQL, and (to some extent) Oracle, looking ...
8
votes
1answer
492 views

Should I be alarmed by this NO JOIN PREDICATE warning?

I'm troubleshooting the bits and pieces of a poorly-performing stored procedure. This section of the procedure is throwing a NO JOIN PREDICATE warning select method = case ...
6
votes
2answers
1k views

Generate script to automate renaming of default constraints

Background: Some of our default column constraints were generated without explicit names, so we get fun names that vary from server to server like: DF__User__TimeZoneIn__5C4D869D I would prefer to ...
4
votes
1answer
714 views

CREATE DATABASE permission denied for sysadmin

I'm attempting to create a stored procedure that can be used as a proxy for creating databases where users do not have rights for creating databases. See Restricting Database Access/Functions to User ...
4
votes
1answer
811 views

How is SQL Server returning both a new value and old value during an UPDATE?

We've had issues, during high concurrency, of queries returning non-sensical results - results the violate the logic of the queries being issued. It took a while to reproduce the issue. I've managed ...
4
votes
1answer
2k views

How to force the use of row locks?

I am trying to get my table to use row locks. I have already disabled lock escalation. The isolation level is READ_COMMITTED_SNAPSHOT alter table <TABLE_NAME> SET (LOCK_ESCALATION=DISABLE) go ...
4
votes
2answers
8k views

SQL 2008 R2 creates user/schema when Windows user creates tables

We added a server login and database user that map a Windows Group to a SQL 2008 R2 instance using the following script, with the names changed for anonymity: USE master go CREATE LOGIN ...
3
votes
2answers
1k views

TdsInputBufferError not 0 in LoginTimers errors of RING_BUFFER_CONNECTIVITY of sys.dm_os_ring_buffers

I've got an issue with SQL Server. The value of TdsInputBufferError is not 0 in LoginTimers errors of RING_BUFFER_CONNECTIVITY of sys.dm_os_ring_buffers. This happens randomly, once or twice a week, ...
3
votes
3answers
3k views

How? SQL Cluster with multiple active nodes (not failover)

I'm building a new SQL 2008 R2 (Enterprise) Cluster that is backed by an EqualLogic iSCSI SAN. Instead of building a failover cluster (which I have done), I would instead like to setup several nodes ...
2
votes
2answers
3k views

Should I join datetime to a date using cast or range?

This question is a take-off from the excellent one posed here: Cast to date is sargable but is it a good idea? In my case, I am not concerned with the WHERE clause but in joining to an events table ...
2
votes
3answers
985 views

Working Linked Server Query fails in sp_send_dbmail

Take the following example: EXEC msdb.dbo.sp_send_dbmail @recipients = 'me@whatever.co.uk' , @query = 'SELECT TOP 10 * FROM LINKEDSERVERA.DATABASE.dbo.TABLE' , @attach_query_result_as_file = N'True' ...
10
votes
5answers
4k views

SQL Server 2008 Full Text Index Never Seems to Complete

Our website has a SQL Server 2008 R2 Express Edition database with full-text indexing for our website search. Each time a new record is added or updated in one of the indexed tables, the indexing ...
9
votes
2answers
5k views

Need to understand parallel query execution error

Today we experienced a degradation in performance on our production sql server. Durring the time this occurred we logged several "The query processor could not start the necessary thread resources ...
7
votes
1answer
684 views

Does Detach/Attach or Offline/Online Clear the Buffer Cache for a Particular Database?

A buddy of mine told me today that instead of bouncing SQL Server, I could simply detach and then re-attach a database and this action would clear the given database's pages and plans from cache. I ...
7
votes
2answers
569 views

How should I configure these disks on a SQL Server for a BI configuration?

Assuming constant memory (32gb) and CPU (4), 2 x disk arrays, I have the following disks 2 x 150 (10k) 6 x 150 (15k) They are all local disks. My requirements My DB is 350gb and set to default ...
5
votes
2answers
827 views

An efficient way to compare two large data sets in SQL

Currently, I'm comparing two data sets, that contain unique StoreKey/ProductKey combinations. The 1st data set has the unique StoreKey/ProductKey combinations for sales between begin January 2012 and ...
5
votes
2answers
190 views

'Cleanse' a SQL Server database file created with Instant File Initialization enabled?

I have Instant File Initialization enabled on our SQL Server, so the 'empty' part of the database file is not zeroed before being allocated. My understanding is that this means that the file could ...
5
votes
3answers
6k views

SQL update satement taking a very long time / high disk usage for hours

Yes it sounds like a very generic issue, but I have not been able to narrow it down very much yet. So I have an UPDATE statement in a sql batch file: UPDATE A SET A.X = B.X FROM A JOIN B ON A.B_ID = ...
4
votes
1answer
480 views

Indexes file damaged, was on ramdisk

I have put some of my indexes into a filegroup that contains one file, that file is on the ramdisk, the performance is great, but the problem is that the file was deleted incorrectly (the file ...
4
votes
1answer
912 views

Why can't I read my transaction log backup file using fn_dump_dblog?

I'm using this blog on sqlskills.com as a guide to practice finding the relevant LSN in a transaction log backup for point in time recovery. After backing up the transaction log, I attempt to read it ...
4
votes
2answers
5k views

Transaction and Try-catch in SQL Server Job

We have DML operations in each step of a SQL Server job. To ensure the update/insert will be rolled back in case something goes wrong, I have wrapped the data modifications of each step in TRY CATCH ...
4
votes
2answers
655 views

SQL Server Dynamic Memory management

I am reading a book on SQL 2008 internals and troubleshooting by Christian Bolton, Brent Ozar and a old book written by late Ken Henderson and I am getting confused and curious about SQL server's ...
4
votes
2answers
4k views

SQL Server 2008R2 - Why is my index not used

I have a table defined in the following way: CREATE TABLE [dbo].[MyTable] ( [MyTable_ID] [int] IDENTITY(1,1) NOT NULL, [COLUMN_WITH_DATA] [varchar](128) NOT NULL, [COLUMN_A] [varchar](128) ...
4
votes
3answers
463 views

Does Snapshot Isolation only give you what has been commited? (And General Snapshot Isolation questions.)

So, I have some deadlocking issues. I have seen two options to resolve it. Add read uncommitted to my db or do Snapshot Isolation and add read committed snapshot. After doing some research into ...
3
votes
2answers
358 views

XML queries with case insensitive tag

I have a requirement where i want XML tags to be case insensitive in my xml queries. For example: DECLARE @myTable TABLE ( yourXML XML ) INSERT INTO @myTable SELECT ...
3
votes
1answer
1k views

Licensing standalone instance of SSIS

Basic background: I have an application on an app server (no MSSQL installed) that I want to allow to call an SSIS package. One Solution: One option seems to be that we can install just SSIS on the ...
3
votes
1answer
2k views

Large table and poor performance - what to do next?

We have a Windows 2008 R2 / SQL Server 2008 R2 (Standard) server that is used to host a single database. The database itself mainly consists of a single large table containing both live and historical ...
2
votes
1answer
443 views

Why dropping all foreign keys from a script is not working? [closed]

I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import. So I execute the below code with no ...
2
votes
1answer
182 views

NEWSEQUENTIALID() resets sequence on server restart

MSDN learns me that NEWSEQUENTIALID(): Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting ...
2
votes
3answers
2k views

In Microsoft SQL Server 2008, syntax generates the error “The Parallel Data Warehouse (PDW) features are not enabled.”

I have the following virtual column generated from an aggregate over a sorted partition, MIN(picture_id) OVER ( PARTITION BY [360_set] ORDER BY picture_id ASC ) However, when I execute that, I get ...
1
vote
4answers
12k views

Backup and restore database - occured system error 5

In my work I made backup of database and I receive file .bak. Now I would like restore this database in my machine. But I get the following error: Cannot open backup device 'C:\Program ...
1
vote
1answer
447 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
2answers
255 views

Resume log shipping from a specific transaction

Is it possible to force SQL Log Shipping to run from a given .trn file onward? I have two databases, where one ships to another this was working fine. However I had to restore the primary database ...