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

learn more… | top users | synonyms

0
votes
1answer
2k views

Upgrade older SQL 2008 to 2008 R2 on active/passive cluster

I have an active/Passive SQL Server 2008 cluster in production that I need to upgrade to SQL Server 2008 R2. I just want to make sure I understand all the steps before starting this in production. ...
6
votes
1answer
918 views

Visual Studio 2010 deployment script creates objects in wrong order - trigger after stored procedure

I created a view that selects from multiple tables in a complex query. Due to the complexity, I created insert/update/delete INSTEAD OF triggers to handle these operations on the view. I also ...
4
votes
2answers
2k 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 ...
5
votes
3answers
547 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
2answers
6k views

Unable to delete SQL Server 2008 R2 login

I had created a SQL Server login some days back. Now I need to delete the same. So I went into SQL Server Management Studio => Security => Logins and selected the login that I want to delete. It ...
0
votes
2answers
803 views

Cannot Attach Database to SQL Server Management Studio 2008 R2

I installed SQL Server 2008 R2 just now and wanted to attach my previous databases. Attaching a Database gives an error. Attached is the image of the error. Any Suggestions and help will be ...
2
votes
2answers
298 views

Rebuilding Index - sort in temp db

What is the significance of SORT_IN_TEMPDB when working with indexes on a table of significant size (20GB plus)? Where else does sorting take place?
11
votes
2answers
385 views

To maximize DB performance, which commands should be run after loading large amounts of data into SQL Server 2008 via SSIS

I have written an SSIS package to load test data into an empty database. Some of the tables are very large (~700 million rows). Once the SSIS package has completed are there any commands I should run ...
3
votes
1answer
242 views

What load does SQL Server Reporting Services (SSRS) have on the server?

Does SQL Server Reporting Services use a lot of server resources? Is it okay to install SSRS on the same server as the SQL Server instance? What should I take into consideration? Is the SQL Server ...
10
votes
2answers
746 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 ...
5
votes
2answers
3k views

How to delete a Backup from a backup set in SQL Server 2008 R2

The title pretty much sums it up. We are running out of space on our backup disks, and need to remove old backups out of the set. I can't seem to find any information on this.
5
votes
2answers
144 views

Is is possible to manage SQL Server 2008 databases via R2 Management Studio?

Is it possible to manage SQL Server 2008 databases using SQL Server 2008 R2 Management Studio?
2
votes
2answers
252 views

SQL Server recovery with LVM snapshot as base

Is it possible to fully recover a database from a snapshot plus transaction log backups? By snapshot I mean a crash-consistent clone of the Server - in this case an LVM snapshot that is shipped ...
7
votes
2answers
188 views

Can I use transaction logs to rebuild a database?

We've had some data loss and don't have a reliable backup it seems. We do have HUGE transaction logs and there was some thought that we might be able to use these to get back to a certain point. Is ...
1
vote
1answer
66 views

Any general reasonable performance tests for DBs on application server and DBs on separate server?

My company are planning to move the DBs (SQL Server 2008 R2) from the application server to another server (random distance). All servers are Windows 2003 x86. Are there any general performance tests ...
3
votes
2answers
844 views

Multiple charsets and Collations for a Multinational Database

After decades of business speaking with companies that adhere to the general Latin1 Collation, my company is facing the issue of storing information in a different charset and collation: Greek. So, ...
5
votes
3answers
1k views

Run several commands against data in text file (SQL Server 2008 R2)

I'm new to this website so forgive me if I'm asking a rudimentary question and/or one that's been asked 100 times before. I wasn't able to find an answer during my searches here. I'd like to have my ...
0
votes
2answers
260 views

Is command BACKUP asynchronous operation

I got strange delays when backed up a group of databases (20 more) in one scope (TSQL) to the same file location. Is it possible that multiple backup operations are running at the same time? I have ...
5
votes
1answer
6k views

Receiving “The SELECT permission was denied on the object” even though it's been granted

I'm a programmer, not a dba... I know just enough to be dangerous. I've inherited a database with a legacy user that is a db_owner for the database. We can't adjust this user's permission for ...
5
votes
2answers
2k views

Connection To R2 Named Instance

Will preface this by saying I am not at all convinced yet this is not the vendor doing something wrong on their application server. Just looking for new ideas Instance X is a named instance of SQL ...
2
votes
2answers
885 views

AUTO_UPDATE_STATISTICS and FULLSCAN in SQL Server 2008 R2

Is it possible to force FULLSCAN when statistics are updated automatically by SQL Server 2008 R2? If not, is a planned UPDATE STATISTICS WITH FULLSCAN the best way to keep statistics up-to-date? ...
3
votes
3answers
3k views

How to move data from big table

There is a big table (5-6 million records). I have to move 90% of old records to other database(table). Solution?
7
votes
2answers
182 views

Meeting with DBAs regarding server upgrade project - Common concerns

I'm more of a network/windows admin and I've been tasked with overseeing a SQL server upgrade project. I need to meet with the DBAs and discuss their needs / wants regarding the upgrade. I don't want ...
4
votes
2answers
697 views

SQL 2008 R2 Datafile Too Large To Empty or Remove

Our primary database has three datafiles: 1TB, 400G and 500G. The 1TB file is just too dang big and isn't really even needed any more, so I want to empty it and get rid of it so we can use that drive ...
12
votes
3answers
1k views

Slow deletion of records when a trigger is enabled

Thought this was solved with the link below - the work around works - but the patch doesn't. Working with Microsoft support to resolve. http://support.microsoft.com/kb/2606883 Ok so I have an ...
4
votes
3answers
654 views

Is there an easy way to trigger automatic failover of a single mirrored database?

I have 3 mirrored databases on a single instance of sqlserver2008-R2 in a high safety with automatic failover config. Is there a way to trigger automatic failover of just 1 database? I want to ...
2
votes
1answer
261 views

SQL Server 2008 R2 DETA Error: Can not get minimal database information in allotted time

I have a 18,6 GB SQL Server 2008 R2 profiler trace file. Trace file recorded at tuning profile. When I submit this trace file to database engine tuning advisor I get this error: Error: Can not get ...
4
votes
1answer
603 views

How to restore a SQL Server 2008 R2 database

Here's my issue. I have a server created from a windows image that includes SQL Server 2008 and two specific windows users, these windows users are a part of the sysadmin role and are users for the ...
2
votes
1answer
82 views

How to get dependencies order

I have a Database Called Total and I have about 40 tables in it.I am trying to move the tables in the database into a different server So I have prepared all the create table scripts but the problem ...
3
votes
1answer
486 views

Is it possible to change the Access data source to SQL Server?

I'm currently managing a "db" in Access (meh). A complex structure of masks and VBA scripts was built over this "db". What I'd like to do is change the Access datasource from Access itself to SQL ...
4
votes
4answers
864 views

Where to look for a bottleneck after elimitating CPU, memory, and disk

I have a database job that runs each night to create a warehouse table. Using my new database server and SAN I have gotten the process down from 4 1/2 hours to 35 minutes. I have optimized it to run ...
9
votes
3answers
1k views

SQL Server 2008/R2 recovery model

Pretty much all of our databases on certain servers do not require the Full Recovery model (we don't do transaction log backups) and the default should always be to create databases and specify the ...
2
votes
3answers
5k views

sql server 2008 r2 mirroring

I am trying to set up mirroring between two servers behind the same firewall on the same subnet. I backed up mainserver and restored to mirrorserver. I'm using the wizard on "mainserver" and ...
3
votes
2answers
1k views

SQL Server 2008 R2 - Error 18456 State 12 - nothing I find helps me

First off let me state that I'm not a DBA, I don't have access to a DBA, I'm just a programmer who has to get a CMS working. I'm generally pretty good at getting random servers working, but this is ...
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 ...
4
votes
1answer
6k 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 ...
0
votes
1answer
1k views

Reindexing Partitioned Table Indexes

Historic Reporting Server running SQL 2008 R2. I'm trying to tweak the fill factor. I have a job that records the fragmentation when the jobs runs in the early hours. For every table that had high ...
3
votes
1answer
725 views

Replication slows down on massive deletes

First of all, I'm not a DBA, but I'd like to get advice how to deal with the following issue. We have few database servers based on SQL Server 2008 R2 SP1. They have been organized as shown below, so ...
4
votes
4answers
292 views

How to measure the number of database writes on SQL Server 2008 R2

How would I measure the number of database writes on SQL Server 2008 R2 For example, how many updated rows and how many new rows in a database over a period of time (1 day, 1 week or 1 month)?
3
votes
2answers
681 views

What is the best way to mirror an ASPState database?

I'm not having issues with the actual mirroring part, but because of the use of temporary tables, IIS is throwing a bit of a wobbler if a failover occurs (I'm still testing a few things, so it's me ...
5
votes
5answers
1k views

log shipping to multiple-read servers

The SQL Server 2008 R2 Web Edition product marketing site has a benefit listed on its High Availability section: Benefit from log shipping, which helps automatically back up data on separate ...
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 ...
27
votes
5answers
4k 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 ...
3
votes
4answers
533 views

Cannot reproduce client SQL 2008R2 error: subqueries are not allowed

EDIT: My question is not "how do I work around the failure?", my question is "why is the failure occurring?" We have a client that reports error "1046 subqueries are not allowed in this context. Only ...
4
votes
1answer
1k views

SQL Server 2008 R2 reindex job reorganise getting stuck

I am currently experiencing this job getting stuck in my nightly maintenance solution - ALTER INDEX [PK_ThirdPartySettleCalled] ON [evo_archive].[dbo].[ThirdPartySettleCalled] REORGANIZE WITH ...
3
votes
1answer
1k views

Getting error on SQL Server

I am getting these error messages one after the other every week on Win 2008 and SQL 2008 R2. MAX DOP is set at 1. Any idea how I can solve this? Thanks. DESCRIPTION: A fatal error occurred ...
2
votes
2answers
891 views

Instant File Initialization | Lock Pages in Memory Issues

I have a server that I'm coming onto performance tune and for the life of me I can'g get these rights enabled. So I ran PS SQLSERVER:\SQL\NOFAULT2010\DEFAULT+sa\> whoami nofault\administrator ...
7
votes
1answer
2k views

Understanding SQL Server permissions

I am rooting around in SQL Server Management Studio against my instance of SQL Server 2008 R2 Express Edition. I am trying to understand how the permissions work. What I can see is (via the ...
17
votes
3answers
4k views

Adding columns to production tables

What's the best way to add columns to large production tables on SQL Server 2008 R2? According to Microsoft's books online: The changes specified in ALTER TABLE are implemented immediately. If the ...
12
votes
3answers
1k 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 ...