Questions tagged [sql-server-2008-r2]
SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.
461
questions
23
votes
1
answer
56k
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 ...
- 345
31
votes
3
answers
7k
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 (...
- 69.7k
41
votes
3
answers
10k
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.
...
- 62.1k
48
votes
2
answers
41k
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 ...
- 833
7
votes
2
answers
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 ...
- 3,131
40
votes
7
answers
235k
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?
...
- 831
15
votes
1
answer
9k
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 ...
- 245
8
votes
1
answer
28k
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'...
- 535
43
votes
5
answers
303k
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 ...
- 657
25
votes
2
answers
36k
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 ...
- 2,109
6
votes
1
answer
56k
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 ...
- 133
51
votes
4
answers
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 ...
- 7,310
41
votes
1
answer
78k
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 ...
- 759
31
votes
4
answers
32k
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 ...
- 609
25
votes
3
answers
107k
views
How can I tell how many Cores SQL Server is actually using?
I have two servers running SQL Server.
Server 1: SQL Server 2008 R2 Express (4 core)
Server 2: SQL Server 2012 Developer Edition (8 core)
As far as I am aware SQL Server 2008 R2 Express should only ...
Craig Edmonds
12
votes
2
answers
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 ...
- 781
60
votes
5
answers
42k
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-...
- 703
31
votes
2
answers
14k
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 ...
- 1,465
26
votes
2
answers
72k
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 ...
- 263
22
votes
2
answers
14k
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]
...
- 3,131
20
votes
2
answers
11k
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 ...
- 1,305
19
votes
2
answers
44k
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 ...
- 505
13
votes
2
answers
49k
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 ...
12
votes
3
answers
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 ...
- 223
7
votes
4
answers
15k
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 ...
- 83
34
votes
5
answers
87k
views
Difference between Full backup and Copy-only full backup
I saw in the SQL Server Central thread Does a full backup truncate the log? that full backup does not truncate the log:
No. Neither Full or Differential backups truncate the transaction log. - Lynn ...
- 3,131
23
votes
4
answers
116k
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\...
- 331
23
votes
3
answers
6k
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 ...
- 2,619
18
votes
3
answers
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 ...
- 653
17
votes
4
answers
11k
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 ...
- 173
11
votes
5
answers
17k
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....
- 351
11
votes
2
answers
5k
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 ...
- 341
9
votes
2
answers
72k
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....
- 93
7
votes
2
answers
13k
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 ...
- 1,950
6
votes
2
answers
11k
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.
- 267
5
votes
2
answers
3k
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 ...
- 151
4
votes
2
answers
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 ...
- 183
3
votes
2
answers
7k
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 ...
- 133
2
votes
3
answers
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 ...
- 7,224
40
votes
3
answers
5k
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
)
...
- 1,465
24
votes
3
answers
10k
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 ...
- 24.1k
21
votes
1
answer
7k
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 ...
- 10.6k
16
votes
3
answers
5k
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 ...
- 741
15
votes
3
answers
5k
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: ...
- 263
15
votes
2
answers
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 ...
- 17.9k
14
votes
1
answer
2k
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, ...
- 69.7k
9
votes
5
answers
9k
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 ...
- 193
9
votes
1
answer
37k
views
How to keep the structure of the Tablix when there is no data to show
We have a SSRS report which shows the result of a dataset in a tablix. If the query in the dataset returns no data, the header of the Tablix gets displayed but not the tablix cells below the header. I ...
- 3,674
9
votes
1
answer
5k
views
Trouble With Database Permissions for sp_send_mail
I am trying to send database mail but I am getting EXECUTE permission denied on the object 'sp_send_dbmail' database 'msdb', schema 'dbo'.. I code I am running is as follows:
SELECT SUSER_NAME(), ...
- 2,335
9
votes
1
answer
19k
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 ...
- 405