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

learn more… | top users | synonyms

4
votes
3answers
526 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
444 views

Efficently Remove 90% of a table's data

I am working on a process to remove 90% of a table's data because only 10% is needed for testing. The best way I have found includes storing 10% of the table's rows into a temporary table. Current ...
3
votes
1answer
358 views

Execution Plans Differ for the Same SQL Statement

As a background to this question the details can be found at this link. In a nutshell: I have 2 SQL statements that are almost identical except for the RIGHT table. The LEFT JOIN is the same and the ...
3
votes
2answers
589 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
2k 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
3answers
3k 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 ...
3
votes
1answer
2k views

How to take partitions or filegroups out of service

I'm an experienced SQL Server DBA but new to partitioning, and I have a couple of questions. Using SQL Server 2008 R2 Enterprise Edition. I've inherited a large customer metrics database that grows ...
3
votes
2answers
274 views

How to prevent SQL Log from being too full

I have a database that generate a huge amount of transaction per seconds, something like 50-100 transactions/second. Because of this, the log files grows very quickly. The thing is though, that even ...
2
votes
1answer
1k 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
291 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 ...
1
vote
4answers
20k 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
551 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 ...
1
vote
1answer
3k views

Read only/Standby database - Set user permissions

Example database: WarehouseDB Example user: reporting Database state: Read only/Standby mode How do I give the user 'reporting' access to only WarehouseDB without giving them sysadmin privileges? ...
0
votes
2answers
389 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 ...
11
votes
4answers
21k views

How export a sql server 2008 diagram to PDF filetype?

I want to have an export from my database diagram to PDF or image types. How can I do this? I worked with SQL Server 2008 R2.
10
votes
1answer
16k 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 ...
10
votes
2answers
4k views

Good way to call multiple SQL Server Agent jobs sequentially from one main job?

I've got several SQL Server Agent jobs that should run sequentially. To keep a nice overview of the jobs that should execute I have created a main job that calls the other jobs with a call to EXEC ...
9
votes
1answer
433 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 ...
7
votes
1answer
682 views

SQL Server not using available memory

SQL Server 2008 R2 Standard Edition (64-bit) on Windows 2008R2 Enterprise Server has over 300 GB of memory but total memory use in control panel never goes over 86 GB Have SQL configured to use ...
6
votes
1answer
1k 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
952 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: ...
6
votes
1answer
2k 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, ...
6
votes
2answers
169 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
1answer
7k 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. ...
5
votes
1answer
1k views

Partitioning / indexing an extremely large table

I'm working on indexing and partitioning a single data warehouse table that weighs in around 500 GB. The table is a heap, has over a hundred TEXT columns, and the TEXT_IN_ROW option is enabled. I ...
5
votes
1answer
253 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
1k 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
1k 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
9k views

Migrating from one SQL Server 2008 R2 instance to another

I just started a new dba job and have been given a new server and told to move everything from the old server to the new one, brand new windows and SQL Server installations. I know I have to transfer ...
5
votes
1answer
269 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
199 views

SQL Server 2008 R2 Readable Mirror

I know that SQL Server 2012 and 2014 offer AlwaysOn Availability Groups which provide this functionality, but I'm still stuck on SQL Server 2008 R2 for a while. I recently came across this AWS ...
4
votes
1answer
2k views

SQL Server SSL connection - What am I missing?

I am trying to set up my SQL Server 2008 R2 instance so that it only accepts SSL connections. My understanding is that once this is set up, any client machine should need to have a certificate ...
4
votes
1answer
2k 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
1answer
183 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
5answers
3k views

Automatically Execute Stored Procedure After Any RESTORE DATABASE Event

Is it possible to have SQL Server 2008 R2 Standard automatically execute a stored procedure in any given database that is restored or attachced to the instance? I've got close to a solution by ...
4
votes
3answers
1k views

How can I make my SSIS process consume more resources and run faster?

I have a daily ETL process in SSIS that builds my warehouse so we can provide day-over-day reports. I have two servers - one for SSIS and the other for the SQL Server Database. The SSIS server ...
4
votes
2answers
3k views

Best Way to Grant Read All Permission in SQL Server 2008 R2?

What is the best method to grant read access to all current and future databases to a user (AD Group in this case)? Currently I have a flaky scheduled job that does this using (going to start ...
3
votes
1answer
3k views

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values

One of INSERT INTO script is written as following. INSERT INTO tableName (Column1, Column2,....) VALUES (value1, Value2,...), (value1, Value2,...),.... Following is the error we are facing on ...
3
votes
1answer
520 views

How to find XXth day of previous month in SQL server?

I have 2 parameters, an input date (DATETIME) input day of month (TINYINT) If I enter 11 MAR 2014 as the DATETIME and 26 as the input day of month, I would like to select 26 FEB 2014 as the ...
3
votes
3answers
996 views

Track changes in database structure

There has been a change in our DB and one of the SPs has been deleted. We have no idea who did it or when. Is there any way to find it out? Or enabling an option to be able to track this in future?
3
votes
1answer
255 views

Are there reasons to not use SCHEMABINDING on database objects?

Not using schema binding seems like turning off compiler warnings - you don't find out you broke something until runtime. The benefit is that schema bound objects protects the database from ...
3
votes
2answers
1k views

Database defragmentation and autogrowth settings

We do have some maintenance plan for our sql server 2008 r2 express. Every month we do defragment of the database if any table has page count more tahn 50 for any table and average fragmentation more ...
3
votes
5answers
511 views

Extract data to another computer

I have SQL Server 2008 R2 Express edition, and I want to export the data in that database to another computer so I can import it again in that computer. How please? What I have tried Right-click ...
3
votes
2answers
1k views

Can I trust the Database Engine Tuning Advisor recommendations?

Our database has become large, and we are trying to optimize performance. Can we follow the recommendations provided by the Database Engine Tuning Advisor by supplying it a .trc file generated with ...
3
votes
1answer
1k views

Database locked

I canceled a query which took too long time. After that, the SQL-Server database was locked. After I restarted SQL Server, the mode of my database was "in recovery". Now I can't access my database ...
3
votes
2answers
16k views

SQL Server 2008 R2 Management Studio : “Evaluation period has expired”

I've spent several hours trying to fix this issue so far with no success. I had installed SQL Server 2008 Evaluation Edition. Out of fear it might expire & create problems for me I installed SQL ...
3
votes
2answers
2k views

Does adding page compression to a table also compress existing indexes?

I am creating a series of new tables that will have page level compression enabled. Will this affect the indexes as well, or will I have to also specify compression when creating the indexes? create ...
3
votes
1answer
1k views

Multi-tenant database using SQL Server 2008?

I am designing a database using a shared schema containing the "security views" to which all logged tenants will be directed. I read the msdn article on Multi-tenant Data Architecture. I understand ...
3
votes
2answers
8k views

Why database diagrams are not working when domain account is attached to database owner?

I was trying to use Database diagrams is SQL Server and got following error: Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first ...