SQL Server 2008R2 is Microsoft's latest public version of SQL Server, released April 2010.

learn more… | top users | synonyms

6
votes
7answers
12k views

Activity monitor is unable to execute queries against server

Is there any fix to get SSMS activity monitor working? Problem: SQL Server Activity Monitor fails with an error dialog: TITLE: Microsoft SQL Server Management Studio The Activity Monitor is ...
6
votes
6answers
26k views

Can't connect to SQL Server 2008R2 on Windows Server 2008R2

I reinstall a server Windows Server 2008 R2, I installed SQL Server 2008 R2 no problem during installation. When I try to connect with "Microsoft SQL Management Studio" via sa account or Windows ...
6
votes
1answer
5k views

How do you get the minimum and maximum memory allocation of a SQL instance using T-SQL?

I'm managing about 90 SQL Server instances and need a query to determine how much memory has been allocated to each instance. Please note, I'm not asking how to set it, just to see what it's currently ...
5
votes
2answers
5k views

How to allow the local Administrator to have access to SQL Server?

One of our clients has an installation of SQL Server Express 2008 R2 that we more or less manage for them. Today I need to create a new database(a fairly rare occurrence). So I go to login as the user ...
5
votes
2answers
974 views

How quickly will SQL Server release RAM when the OS signals a low memory condition?

From the SQL Server 2008 R2 Books Online: Server Memory Options When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free physical ...
5
votes
1answer
729 views

Is it possible to redirect (or create an alias) so that requests for a default instance of SQL redirect to a named instance?

Is it possible to redirect (or create an alias) so that requests for a default instance of SQL redirect to a named instance? Scenario is I have SQL 2008 R2 installed as a named instance ...
5
votes
3answers
56k views

How to completely remove MS SQL Server 2008 R2 x64 which was uninstalled incorrectly?

On my Windows 7 Ultimate x64 I can't do anything with the existing instance of MS SQL Server 2008 R2. It was my mistake to uninstall some components related to SQL Server before running the main ...
4
votes
5answers
22k views

Sql Server 2008 - Cannot connect to local default instance

I recently rebuilt my machine to Windows 7 x64, installed Sql Server 2008 enterprise. I can connect fine to other remote instances via Management Studio (be they 2000, 2005 or 2008), but i cannot ...
4
votes
2answers
2k views

Can I restore a SQL Server 2008 R2 backup to SQL Server 2008 R2 Express?

I have a server that's currently running SQL Server 2005 Express, and I need to restore a database backup that a developer has modified using SQL Server 2008 R2 [I think the full version, but still ...
4
votes
1answer
3k views

How to add a group to the sysadmin fixed server role in SQL Server 2008 R2?

How can i add a group to the sysadmin fixed server role in SQL Server 2008 R2? See my related question for details step-by-step screenshots detailing showing that you're not allowed to add groups ...
4
votes
1answer
1k views

SQL Server Maintenance Plan: Rebuild Indexes and Transaction Log Backup File Size

In my nightly maintenance plan, I was doing everything - reorganising and rebuilding indexes, updating statistics, etc. There's an hourly maintenance plan that does a transaction log backup. I ...
4
votes
2answers
842 views

MS SQL access to restored database without sysadmin role

I have databases from different projects on one server. I can't give all developers administrative (sysadmin) access to the databases. They have to have the permissions needed to create and restore ...
4
votes
1answer
7k views

Difference between “SQL server 2008 standard” and “SQL server 2008 standard for small business”?

There seems to be a big difference in price for these licenses, but after a lot of googling I can't find any info on how the two compare. The blurb for the "small business" version is: "This edition ...
4
votes
1answer
2k views

Installation of SQL Server 2008 R2 hang on Setup-Support files

I'm installing SQL Server 2008 R2 on a customer Windows Server 2003. Setup started ok, installed .NET framework and some Setup files, rebootet. After that I ran setup for a new single-server ...
3
votes
4answers
249 views

Implications of having IIS on SQL Server 2008 R2 machine

an application needs to have IIS on the same box of Reporting Services. Today SSRS and SQL stay on the same clustered box. I need to evaluate the options which for me are: Installing IIS on both ...
3
votes
4answers
1k views

Is there a performance advantage to isolating database logs and data on different volumes (same RAID array) across a SAN?

On my old server, I separated them on to different RAID arrays to improve performance. Using a SAN is new to me, so I'm not sure of the optimal configuration. SAN: 48 drives. 10K 600GB SAS. RAID 50. ...
3
votes
3answers
72 views

Transferring SQL Server 2008 R2 tables/data from dev box to server

I'm nearing the end of site development, and am now facing transferring my table structure and data from my development machine to the live server. SQL Server 2008 R2 is already installed and ready ...
3
votes
2answers
462 views

How to change max server memory in SQL Server

I was trying to change the max server memory for my database server, and I accidentally saved it as "4" instead of the number I wanted to enter. Now I can't even connect to the server. Is there a ...
3
votes
2answers
4k views

How to shrink 40GB LOG file

I have a problem that is my log file in SQL Server 2008 is grown to 40Gb and I wanted to shrink it. The query I am using is ALTER DATABASE DatabaseName SET RECOVERY SIMPLE use DatabaseName GO ...
3
votes
2answers
69 views

Can I create SQL v10.0 databases in SQL R2 server?

Is there a way to create non-R2 databases in an R2 database server? I was hoping to do this in the compatibility options during the database creation, but only options are available for 2005 and 2000. ...
3
votes
4answers
1k views

Log Shipping to an Existing Database

We have a few huge databases we would like to log ship on SQL Server 2008. Unfortunately the link between primary and secondary servers is fast enough to handle log shipping but the initial ...
3
votes
2answers
156 views

Can I use a powershell script to install db/website when the database is separated from the webserver?

My boss is on vacation and I'm supposed to have this installation script done by the time he returns. Unfortunately, I've realized that the script I developed won't work on the target server setup and ...
3
votes
1answer
130 views

Are SQL Collations per Instance or per Database?

I have a single SQL Instance, let's say MSSQLSRV - is the collation per Instance, i.e. every database in the given instance has the same collation, or are collations set per database? i.e. multiple ...
3
votes
3answers
2k views

How do I encrypt SQL Server traffic with IPSEC?

I've found all sorts of documentation on what IPSEC is and how to (in very non-specific ways) configure it, but I can't seem to configure a Windows Firewall With Advanced Security rule that both A) ...
3
votes
1answer
536 views

Is SQL Management Studio 2012 avaliable, without Engine itself?

On my dev machine, I have SQL Management Studio 2008 R2 installed. I tried to upgrade to SSMS 2012 Express, setup finished but nothing happened. Is it possible to get SQL Management Studio 2012 ...
3
votes
3answers
4k views

Does SQL Server 2008 R2 lock memory pages by default?

Does anyone know if the locked pages of SQL 2008 ENT R2 x64 operates as ON as default (I assume it does as my SQL memory usage does not show up in task manager) on Server 2008 R2 x64. And if so, ...
3
votes
4answers
15k views

Cannot login to SQL Server 2008 R2 with Windows authentication

When i try to connect to SQL Server (2008 R2) using Windows authentication: i cannot: Checking the Windows Application event log, i find the error: Login failed for user 'AVATOPIA\ian'. ...
3
votes
1answer
3k views

KB2494088 Always Fails : How to get it to install (or stop trying to)?

I have a problem where KB2494088 (a SQL Server 2008 R2 patch) continually fails to install when I reboot my system. Downloading the manual patch and trying to apply it says: The SQL Server patch ...
3
votes
2answers
909 views

Attempting Unauthorized operation - SQL 2008 R2 install

I've been banging against this for a few days. Keep getting this unauthorized error when trying to install SQL 2008 R2 on a Windows 7 machine. I've changed permissions on the key, does not fix... ...
2
votes
4answers
5k views

Is Sql Server 2008 R2 unsupported by Operations Manager (SCOM) 2007 R2?

I'm performing a test configuration of System Center Operations Manager 2007 R2, on a system prepared with Sql Server 2008 R2. Unfortunately, the Scom 2007 R2 prerequisites verification program seems ...
2
votes
4answers
1k views

Do I really need a 128GB pagefile

I'm running out of space on one of my servers and the only space that I can free in the immediate is re-sizing the enormous pagefile. The server has 128GB of memory and is currnetly set to ...
2
votes
2answers
647 views

SQL 2008 R2 on W2K3 Enable AWE (Address Windowing Extensions)

Hi I have a Win 2K3 with 6 gigs of memory. I had 3 gigs, and then added 3 more. However Windows won't recognize more than 4 Gigs as its a 32 bits machine. What do I have to do to make things work? ...
2
votes
2answers
150 views

How do I force a restore over an existing database?

I have a database, and i want to force a restore over top of it. I check the option: Overwrite the existing database (WITH REPLACE) But, as expected, SSMS is unable to overwrite the existing ...
2
votes
1answer
182 views

Windows 2008 R2 Enterprise SQL Server Enterprise 2008 R2 only using half the memory

We have built a new 2 CPU, 6 core server, with 96GB of RAM. SQL server Enterprise Edition was configured with a maximum memory setting of 92GB. A few days after adding into the load balancer I have ...
2
votes
1answer
65 views

How do I troubleshoot a server I can't get logged into?

This morning I am unable to log into SQL Server because it appears to be busy doing something, so much so that CPU usage is constantly at 98%-99%. I am guessing that something has gone wrong with the ...
2
votes
3answers
1k views

Convert/Upgrade SQL 2005 Standard to 2008R2 Express

I currently have a Server 2003 R2 VM running SQL 2005 Standard. The requirements for Standard have gone away and we can suffice with Express. We also have a requirement to upgrade it to SQL 2008 R2. ...
2
votes
2answers
934 views

Cannot Delete SQL Server Database via Command Line Executable

When running a command line executable, one of the tasks is to drop a database. When this gets run, SQL Server tells me that 'DBName' does not exist or you do not have permission. The database is ...
2
votes
1answer
60 views

How to set up SQL Server to offer high availability during windows updates

We have a load-balanced web farm talking to a single SQL server instance. It is easy to demote web servers, install Windows updates, reboot, and then bring them back into the fold. However, we have to ...
2
votes
3answers
2k 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 ...
2
votes
2answers
4k views

How to update a CLR Assembly without dropping assembly from SQL Server

How can i update a CLR function (or procedure) assembly dll without having to drop and re-create the assembly in SQL Server (2008 R2)? As it stands now if i update an assembly (e.g. to add a new ...
2
votes
2answers
1k views

SQL 2008 to 2008 R2 upgrade failed, can't install R2 now

We attempted to upgrade SQL 2008 to 2008 R2. It passed the checks with flying colors. But midway through the installation we got the dreaded "No mapping between account names and Security IDs was ...
2
votes
1answer
179 views

Why is MS SQL Server Using NTLM Authentication?

Windows Server 2008 R2. SQL Server 2008 R2 installed. MSSQL Service runs as Local System. Server FQDN is SQL01.domain.com. SQL01 is joined to an Active Directory domain named domain.com. The ...
2
votes
1answer
201 views

Electricity gone off while shrinking database, how can I recover my database?

While executing a shrink on a 25GB database, the electricity has gone down, and I can't access my database. It shows as "In Recovery". How can I recover it?
2
votes
2answers
219 views

SQL Server 2008 R2 Cluster with HOSTS file entries to active node on the application servers

I questioned the decision of a colleague concerning the use of a HOSTS file entry on an application server (standard SQL database backed .NET solution), which I think would cause connections to the ...
2
votes
4answers
301 views

Data file size - SQL Server 2008

I am transferring a database that i made onto a server machine, but the size of the database is around 8GB, for some reason. It shouldn't be needing so much space as i only have a few hundred records. ...
2
votes
1answer
499 views

How to Configure SQL Server 2008 R2 Log Shipping

I need to set up a standby server using SQL Server Log Shipping. This is my first time doing this, so I'm following the instructions in the SQL Help step by step. Of course, I ran into a problem at ...
2
votes
1answer
156 views

Upgrade Expired SQL Server 2008R2 Evaluation to SQL Server 2012

We have been working on developing several SharePoint sites as a POC using the evaluation versions of SharePoint 2013 and SQL Server 2008R2 Enterprise Evaluation. POC worked, went into limited ...
2
votes
1answer
275 views

Tables in the SQL Server “master” database, will they cause problems?

Folks, please be kind on me... I'm just an 'accidental' DBA due to our DBA resigned, so I'm totally a newbie in DBA... You see, I have this application, "ESET Remote Administration Server" (ERAS) ...
2
votes
2answers
170 views

Moving SQL Server between domains

If a Windows Server machine hosting SQL Server 2008 R2 is changed to another domain, what steps definitely and might need to be done for SQL Server before and after the new domain is joined?
2
votes
1answer
250 views

Powershell (sqlps) lastbackupdate not changing despite having run a sqlserver backup

I'm using Powershell to check last backup times across all our sqlserver databases. This seems to work really well, but I've got a question If I run this (a cut-down version of the actual script): ...