Questions tagged [sql-server-2008-r2]

SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.

Filter by
Sorted by
Tagged with
23
votes
1answer
47k views

Upgrade SQL Server 2000 database to 2008 R2 and enable new features

I have recently upgraded SQL Server 2000 database to 2008 R2. What I did was: Shutdown SQL Server 2000 (express) service on old machine, Move datafiles (mydatabase.mdf and mydatabase.ldf) to new ...
29
votes
3answers
6k 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 (...
39
votes
3answers
8k views

difference in execution plans on UAT and PROD server

I want to understand why there would be such a huge difference in execution of the same query on UAT (runs in 3 sec) vs PROD (run in 23 secs). Both UAT and PROD are having exactly data and indexes. ...
47
votes
2answers
40k views

What is the actual behavior of compatibility level 80?

Could somebody provide me with a better insight about the compatibility mode feature? It is behaving different than I expected. As far as I understand compatibility modes, it is about the ...
7
votes
2answers
3k views

Database defragmentation and autogrowth settings

We do have some maintenance plan for our SQL Server 2008 r2 express. Every month we do de-fragmentation of the database if any table has page count more than 50 for any table and average fragmentation ...
26
votes
7answers
159k views

List all permissions for a given role?

I've searched around all over and haven't found a conclusive answer to this question. I need a script that can give ALL permissions for an associated role. Any thoughts, or is it even possible? ...
14
votes
1answer
8k views

SQL Server Many-to-One replication

I have 8 individual SQL Server 2008 R2 machines, each hosting 1 database. Each database has an identical table structure and schema, and entirely unique data. I would like to establish a reporting ...
8
votes
1answer
26k 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. It'...
37
votes
5answers
222k views

Why is SQL Server consuming more server memory?

SQL Server is consuming 87.5 % of my server RAM. This recently caused a lot of performance bottlenecks such as slowness. I researched this issue. One common solution I could find on the internet is to ...
20
votes
2answers
28k 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 ...
6
votes
1answer
53k views

QUERY - pivot multiple columns, variable number of rows

I have a table that looks like this: RECIPE VERSION_ID INGREDIENT PERCENTAGE 4000 100 Ing_1 23,0 4000 100 Ing_100 0,1 4000 200 ...
50
votes
4answers
15k views

What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?

My company is facing the decision whether to purchase SQL Server 2012 Denali or SQL Server 2008 R2 for a new database server. I am looking for objective reasons to choose one over the other. Our ...
39
votes
1answer
71k views

SIMPLE or FULL recovery model for databases?

When should I use the full recovery model and when should I use the simple recovery model for databases? I always used the full recovery model because it is the default, but today i encountered this ...
23
votes
2answers
65k views

How to partition an existing non-partitioned table

I have an existing table with data: dbo.Test (col1,col2,col3....) ON [PRIMARY] I need to change this table to be partitioned like this: dbo.Test(col1,col2,col3....) ON Ps_Date(Col2) How I can I ...
11
votes
2answers
10k views

How to create an Event Notification that runs a Job/procedure when mirroring state changes

I'm asking this question in the sequence of this one Can I send a string over TCP using T-SQL? Remus Rusanu exposes what it seems to be an optimal solution for my problem, but... I'm too immature to ...
16
votes
2answers
38k 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 ...
57
votes
5answers
40k views

Why does ALTER COLUMN to NOT NULL cause massive log file growth?

I have a table with 64m rows taking 4.3 GB on disk for its data. Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text. I added a a NULLABLE column with data-...
10
votes
2answers
39k views

SQL Server Transaction Timeout

Is there a way in SQL Server 2008 R2 to cause a timeout for a database modification involving a transaction? We have a scenario where our application code hangs or throws an exception and fails to ...
29
votes
4answers
26k 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
10k views

SQL Server 2008 R2 “Ghost Memory”?

We have a dedicated SQL Server 2008 R2 machine that is experiencing some strange memory issues.. The machine itself has plenty of resources including two quad-core processors, 16gb of RAM and 64bit ...
6
votes
4answers
11k views

SQL Server - Alter table vs drop and create

Our database is SQL Server 2008 R2. We have some tables that have some varchar(500) columns that I want to switch to datetime2 or bigint. I can guarantee all the data in the columns to be switched are ...
22
votes
4answers
103k views

SQL server service won’t start after disabling TLS 1.0 and SSL 3.0 [duplicate]

Because of security reasons, we disabled TLS 1.0 and older protocols on our windows, and enabled just TLS 1.1 and TLS 1.2 under the following registry path: HKEY_LOCAL_MACHINE\SYSTEM\...
31
votes
2answers
12k views

I/O requests taking longer than 15 seconds

Usually our weekly full backups finish in about 35 minutes, with daily diff backups finishing in ~5 minutes. Since tuesday the dailies have taken almost 4 hours to complete, way more than should be ...
20
votes
2answers
10k views

Can you explain this execution plan?

I was researching something else when I came across this thing. I was generating test tables with some data in it and running different queries to find out how different ways to write queries affects ...
22
votes
2answers
12k views

LIKE uses index, CHARINDEX does not?

This question is related to my old question. The below query was taking 10 to 15 seconds to execute: SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id] FROM [company].dbo.[customer] ...
9
votes
3answers
59k views

SQL Row concatenation with XML PATH and STUFF giving aggregate sql error

I am trying to query two tables and get results like the following: Section Names shoes AccountName1, AccountName2, AccountName3 books AccountName1 The tables are: CREATE TABLE dbo....
7
votes
1answer
11k views

Creating Users on secondary server in log shipping

I have a production Server say ServerA I have setup log shipping to ServerB which is left in read-only mode. The purpose of this log shipping is to lower the load on production server for some ...
17
votes
3answers
1k views

Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?

I would like to use code that I developed in C# CLR to be used in all of the databases on the system so that I don't have to set each to trustworthy and turn CLR on and keep a bunch of the same code ...
5
votes
2answers
2k views

Can't use msdb.dbo.sp_send_dbmail when in service broker - executes as guest?

I have a procedure TheNotificationProcedure that makes a cross-database call to msdb.dbo.sp_send_dbmail. It gets invoked (indirectly) from a service broker queue: CREATE QUEUE [Blah].[TheQueue] WITH ...
5
votes
2answers
6k views

Searching SSIS packages for the use of specific tables

I have a number of SSIS packages, how can I search them to find the use of specific tables? I need to make tables changes and I am trying to assess the impact of these.
11
votes
5answers
15k views

How to revive SQL Server 2008 R2 performance counters?

I'm running SQL Server 2008 R2 Developer on Windows 7 x64, as the default instance. For some reason, SQL Server's performance counters seem to have disappeared. SELECT * FROM sys....
10
votes
2answers
4k views

Strategies for “checking out” records for processing

I'm not sure if there is a named pattern for this, or if there isn't because it's a terrible idea. But I need my service to operate in an active/active load balanced environment. This is the ...
3
votes
2answers
6k views

How do I get useful SQL Server database performance statistics?

We currently have several shared SQL Server servers and instances in our organization. They are going with a different architecture where business units will get their own SQL Server instances. We ...
16
votes
4answers
8k views

Claiming disk space after removing table field

I am running sql 2008 r2 and the db was working fine and fast for last 3 years untill about 3 months ago we added ntext field on very active and used table. Now we are starting to get out of server ...
4
votes
2answers
4k views

Microsoft SQL Server 2008 Memory

I am running MSSQL 2008 Enterprise and in the configuration of the server the Max memory amount is set to 2147483647 MB. My Question is, the server has 192 GB of ram,the number in the config can't be ...
2
votes
3answers
2k views

Should I shrink my database?

I have a 1.2Tb SQL Server 2008 R2 database which is more than 50% empty, because I migrated a lot of functionality to PostgreSql. The database is still in use, and still somewhat growing, but I do not ...
40
votes
3answers
4k views

Unexpected scans during delete operation using WHERE IN

I've got a query like the following: DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN ( SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL ) ...
24
votes
3answers
7k views

What's the best way to archive all but current year and partition the table at the same time

Task Archive off all but a rolling 13 month period from a group of large tables. The archived data must be stored in another database. The database is in simple recovery mode The tables are 50 mil ...
15
votes
2answers
9k views

SQL Server 2008 - Partitioning and Clustered Indexes

So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario. Comments about how we ...
16
votes
3answers
4k views

Is there any benefit to defragmenting SQL indexes in a SAN environment?

Our SQL server lives on a SAN. It contains dozens of OLTP databases, some with several tables containing over 1m records. We have been running Ola Hallengren's index maintenance scripts weekly, and ...
13
votes
3answers
3k views

Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server

On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: ...
6
votes
1answer
12k views

How to output more than 4000 characters in sqlcmd

I've made a stored procedure restoredatabase that returns code to restore a database. The output script looks like this: restore database [DB] from disk='F:\FULL1.bak' with norecovery restore ...
5
votes
1answer
3k views

How to pinpoint root cause of excessive log file growth

The log file stays pretty small (~500MB) until about 11PM, and at that point it begins growing until no disk space is available. The log file reached it's maximum size at 12:39AM, so the growth took ...
21
votes
1answer
4k 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 ...
13
votes
1answer
1k views

How can I shred this Extended Events XML as fast as possible?

I created an Extended Events session in SQL Server 2008 R2. The session runs, and collects events as they happen, exactly like you'd expect. If I shred the xml when there are relatively few events, ...
8
votes
1answer
154 views

Giving server-role to login breaks stored procedures

I have a login called user1. user1 can execute stored procedure [Test].[SP1] EXEC [Test].[TestSortation] EXEC [TestSortation] Both of the above commands work. Now if I give the user1 the server ...
6
votes
5answers
6k views

Creating temporary table dynamically using condition

I am trying to create a temporary table dynamically using the below code, and I know we can't have two create statements for the same table. Is there a workaround for this to put it in a conditional ...
6
votes
3answers
10k views

Restore a large number of transaction logs

I'm using SQL Server 2008 R2, and I need to restore 36 .trn logs to a database. I have restored the first 4 using the restore transaction log functionality in SSMS (right-click database > tasks > ...
4
votes
1answer
5k 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 ...
3
votes
1answer
9k 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 ...

1
2 3 4 5
9