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

learn more… | top users | synonyms

13
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, ...
7
votes
2answers
6k 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 ...
10
votes
2answers
752 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
1k 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
362 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 ...
4
votes
1answer
386 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
2answers
6k 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
1answer
151 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 ...
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
3answers
742 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' ...
8
votes
2answers
3k 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
482 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 ...
6
votes
2answers
415 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
156 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
2answers
523 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 ...
5
votes
3answers
4k 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
325 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
2answers
601 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
516 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
3answers
386 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
179 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
430 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 ...
2
votes
1answer
107 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
2answers
140 views

How to Select Most Recently Updated Row Based on Sequence ID

I have a primary ID column but also a sequence ID column for each edit of the row (the initial row is 1, each edit increments the sequence ID but the primary ID column remains the same). What is the ...
2
votes
2answers
1k 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
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 ...
2
votes
1answer
2k views

Migrating from SQL Server 2005 to SQL Server 2008 R2 on a different box

We currently have a SQL Server 2005 instance hosting a lot of application specific databases (ArchiveManager, SolarWinds, Kaspersky etc). We are planning to deploy a new SQL Server 2008 R2 instance on ...
1
vote
1answer
344 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
159 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 ...
16
votes
1answer
7k views

SLEEP_TASK Wait Type in SQL Server - What does it indicate?

I haven't seen the SLEEP_TASK wait type before, and today I seem to be getting a ton of them. I'm not the official DBA, just a SQL Server developer who knows some DBA stuff. We upgraded our servers ...
10
votes
1answer
7k views

Stored Procedure to return dynamically created table data

Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
9
votes
2answers
2k views

Restore bak file to smaller mdf and ldf database files

I have a legacy database whose nightmarish lack of design I will not get into here, but the files on the server are (relatively) enormous. I have: MyDatabase.mdf: 24.8GB MyDatabase.ldf: 114.6GB This ...
7
votes
1answer
343 views

Poor query performance

We have a large (10,000+ lines) procedure that typically runs in 0.5-6.0 seconds depending on how much data it has to work with. Over the past month or so it has started taking 30+ seconds after we do ...
6
votes
1answer
386 views

How accurate is the sys.partition.rows column?

The system view sys.partitions has a column "rows" that is the total number of rows in a given partition. For a table that is not partitioned (or only has one partition depending on how you look at ...
6
votes
1answer
725 views

Replicated Database Log File Maintenance

I have a database on the publisher that is involved in replication (publication configured for merge and transaction). Trying to regain control of the log file for this particular database (VLF count, ...
5
votes
0answers
338 views

Extremely Slow StatMan query issue with #SP2010 farm

The issue I’m experiencing is explained in a blog-post I created for this, (easy to refer to and to manage the responses / solutions in 1 location) which can be found here: ...
5
votes
1answer
173 views

SA permissions issues with many nested objects

I have a broker application that's relatively complicated. Today, after I made some changes, I started getting the error: The server principal 'sa' is not able to access the database 'XYZ' under ...
5
votes
2answers
6k views

How export a sql server 2008 diagram to PDF filetype?

I want to have a export from my database diagram to PDF or image types.How can I do this? I worked with sql server 2008 R2.
5
votes
2answers
144 views

Most effective way to determine the minimum set of permissions for user

I have a database that an application connects to using db_owner permissions. How do I effectively determine the minimum set of requirements actually needed by this user (application) to run without ...
5
votes
2answers
711 views

Get a record's current partition

In SQL Server 2008 R2, how would I determine what partition a record currently lives in?
5
votes
1answer
1k views

How can dm_db_index_usage_stats show 0 for user_seeks and user scans but a large number for user_lookups?

I'm probably not understanding something but here is the question. I have done a query on dm_db_index_usage_stats. I've noticed that a couple of my indexes have large numbers (6.5 million and 20 ...
5
votes
3answers
561 views

Save Connections in SSMS?

Can I save some sort of session or profile in SSMS (2008 r2) that will open up connections to a saved list of database servers when I open SSMS? So for example I frequently will open up SSMS and ...
5
votes
1answer
228 views

Migrating large database

What is a quick way of migrating 75G database to the cluster? I tried to test the backup and restore and it took more than 2 hrs. Business owners won't accept the long outage. Any idea how I can make ...
4
votes
1answer
689 views

SQL Server 2008 R2 Upgrade (Express to Standard) fails. No “Select Instance”

Hello there, i got stuck in the progress of upgrading 3 systems from SQL Server 2008 R2 Express Edition --> SQL Server 2008 R2 Standard Edition Problem: The Option "Select Instance" in the ...
4
votes
0answers
294 views

Howto improve performance for queries against table without primary key without changing the query or the table directly? [closed]

UPDATE: I included some updated information in this question, because there was no way of progressing here given the previous constraints. Scroll to the bold-faced paragraphs to see my updates; I ...
4
votes
1answer
958 views

SQL Server Maximum and Minimum memory configuration

What are the correct settings for min-memory and max-memory in this use case? The server has 8GB ram, dual Intel Xeon processors, running Windows Server 2008 R2 / Sql Server 2008 Standard Edition. ...
4
votes
1answer
148 views

Security Question involving Sys tables

We have an application user which has been given read write to the appropriate databases. This user has the ability to query the sys tables by default. I've also heard that there are times when this ...
4
votes
4answers
6k views

Copy Database option not found in SQL Server Management Studio 2008 R2

I am unable to see the Copy Database option in SQL Server Management Studio 2008 R2. Can anyone tell me whats going on please?
4
votes
2answers
2k views

Recover deleted records [duplicate]

Possible Duplicate: How do I get back some deleted records? Our C#.NET application mistakenly deleted all records from all tables of its database. The application automatically takes ...