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

learn more… | top users | synonyms (2)

4
votes
3answers
4k 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 ...
2
votes
1answer
52 views

Amazing improvement of script execution speed when split - not understood

I have an ETL job that merges data into about 20 columns of a fact table, all integer non-null (default 0) columns. There are 8 merge statements to completely update all the columns and they involve ...
2
votes
1answer
200 views

Deployment script always re-create constraints and indexes

When generating a deployment script from SSDT it always generates code that drops the foreign key constraints & indexes then creates again those constraints and indexes. How to remove the code ...
0
votes
0answers
20 views

Permissions required to add user to fixed server role

We want to take a group out of sysadmin but grant them a role so they can do the following add, change, remove database access on prod servers for users, groups and service accounts. create and ...
0
votes
1answer
26 views

Script to Delete Tables With A Prefix

I am running SQL Serve 2008 and we have a few test tables that were obviously used for testing. The prefix of the tables are Test_, my question is, will this script delete all tables with the prefix ...
0
votes
0answers
58 views

Suggestions for indexes or how to partition a table in SQL Server 2008R2

Table structure and DBMS SQL Server 2008 R2 I have a single table structure (flat) that has summarized sales data (YTD,MAT etc) by unique combinations of "Customer", "Product" and "Territory". ...
0
votes
1answer
18 views

OLEDB provider for linked server reported change of schema version error

I am trying to run a report but its giving this message: An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'ParamYear'. ...
2
votes
1answer
106 views

sys.dm_exec_procedure_stats missing history?

My boss would like me to find which (of approx 400+) stored procedures are not being used by our website any more. I'm found and reworked a script to give me the details of most recent executions, ...
5
votes
1answer
179 views

Property class Length Annotation PersistedResolvableAnnotation

I am trying to deploy an ssdt project using sqlpackage.exe utility. I get the following error (in German): Fehler bei der Erstellung des Bereitstellungsplans. Die Bereitstellung kann nicht ...
3
votes
2answers
566 views

Why not use SQL_Latin1_General_CI_AS for a global system?

What would be some really good reasons to not use the SQL_Latin1_General_CI_AS collation on a system which handles English, German, Japanese, and Chinese data? I'm having a really hard time finding ...
0
votes
0answers
29 views

Incorrect database backup file size in SQL Server 2008 R2

I am getting different sizes of the database backup file from SQL Server 2008 R2 even when backing up at very granular or frequent intervals. Scenario: I have taken database backup at 8:00am = ...
3
votes
2answers
93 views

How to rewrite a query with MAX in WHERE clause

I have a query with a max in the where clause, which is slow. select count(*) from TableName tbl1 where tbl1.id = ( select max(tbl2.id) from TableName tbl2 ...
0
votes
0answers
7 views

SQL Server 2008 R2 Developer edition - upgrade or install SQL Server 2016

Currently I'm working with SQL Server 2008 R2 Developer edition. I have several databases on it more than 500GB. Now I want to upgrade or install SQL Server 2016. What is going to happen to my ...
10
votes
2answers
16k views

Safely moving and creating new tempdb files

Two things I'd like to know, how do you safely move tempdb with minimal downtime? Also, how do you create new tempdb files? Is it 1 per core? So quad-core = 4 tempdb files, creating three new ones?
4
votes
5answers
8k views

Cannot remove unused filegroups

I wanted to remove some unused filegroups/files in a SQL Server Database but am stuck because SQL Server thinks the filegroups/files are still in use. Some background: We had some partioned tables ...
4
votes
5answers
282 views

Does index rebuild time depend on the fragmentation level?

Is the required time for index rebuild dependent on the level of fragmentation? Does the rebuild of a 80% fragmented index approximately take 2 minutes if the rebuild of the same index fragmented ...
3
votes
1answer
37 views

Apply SQLServer Service Pack to a Single Node Cluster

I have to apply a SQL Server 2008 R2 Service Pack to a Single Node Cluster. The other node died and new hardware has been ordered. Is there anything weird to worry about? Normally we'd apply the ...
4
votes
1answer
110 views

Scenario of huge SQL Server Database backup

I use SQL Server and have a huge database with large compressed backup size (about 250 GB). The backup scenario of this database is: Create full backup at the first day of each month. Create ...
3
votes
1answer
36 views

Are indexes bloated if using nvarchar field sizes that are much bigger than needed?

I've been provided with a database where it appears the designer has used much larger field sizes than are needed. Commonly nvarchar (128) fields have been used but the data being stored in them is ...
-1
votes
1answer
54 views

UPDATE statement in a trigger occasionally updates some columns but not others [on hold]

I have a table called NOTATION with the below table structure: ( NOTATION (primary key) TABLE_NAME , TABLE_KEY , NOTE_TIMESTAMP, SUBJECT , COMPANY , ...
1
vote
0answers
14 views

msdb.dbo.backupset.compressed_backup_size is inaccurate?

I thought the value in msdb.dbo.backupset.compressed_backup_size represented the size, in bytes, of the full, differential, or log backup file (that is, the BAK, DIFF, or TRN file). However, although ...
1
vote
2answers
41 views

SQL Server engine in one place with database files on NAS

My office uses accountant programs which run on SQL Server 2008 R2. The SQL Server engine must be installed on one of the laptops (called "server") and the other laptops (called "clients") connect to ...
4
votes
1answer
121 views

how to ignore the SSMS (sql server management studio) sql server ansi settings?

The options in Management Studio On Management studio Tools->Options->Query Execution->SQL Server there are options that are set and these prevail over the same options that are set on database ...
3
votes
1answer
733 views

SSDT project with working linked server references gets SQL71562 warning

I have a VS 2013 solution with two database projects. One of them, let's call it the "worker" is supposed to get deployed to about 10 different production servers. The other one, that is supposed ...
1
vote
2answers
81 views

How can I improve performance on a 3 table query?

I'm attempting to analyze a resource drawdown (the percentage of time individual units are being utilized during a reporting period, e.g. 2-weeks or 1-year). I need the aggregation down to the ...
1
vote
1answer
179 views

Query to get total time logged in per week

I need to find the number of hours each user is logged into an application per week. The table structure is as follows: [ID] [UserID] [Time] (datetime field) [EventType] ("login" or "logout") There ...
-3
votes
1answer
36 views

SQL Server 2008 R2 - Rebuild Log

I'm trying to rebuild a log file for my db and I'm wanting to know if what is happening is normal. This is what I'm doing: use master ALTER DATABASE my_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ...
4
votes
2answers
63 views

Performance of a self join in a temporal table with a single day's difference between end_date and start_date

Title says it all really. A pretty standard temporal table design has a start_date and an end_date where the end_date is the day before the next record's start_date. In this case, there is an ...
0
votes
1answer
10 views

Can't list columns or query in MS Access 2003 linked server in SQL Server 2008 R2 using Microsoft Jet 4.0 OLE DB Provider?

I can however list the tables... If I run Exec sp_tables_ex 'LSTest' I get a result with a list of tables. But if I run Exec sp_columns_ex 'LSTest', '<one of those table names>' I get an ...
1
vote
2answers
72 views

Databases in Suspect state after enabling TDE

A few of my databases are in a suspect state. How can I get them online? I don’t know what info is relevant and what isn’t, but I’ll give as much as I can. Here is what happened: I enabled TDE in ...
-2
votes
3answers
88 views

Split URL string into columns

Given the string: URL:'ID=1!&user=xxx&depart=21&companyId=43&workOrder=67' I need to get the result: Sno ID user depart companyId workOrder 1 1! XXX 21 43 67 ...
5
votes
1answer
349 views

Distributed transaction and Read Committed Snapshot

Distributed transactions are not supported for snapshot isolation level in SQL Server 2008 R2. But what about read committed snapshot?
4
votes
4answers
5k views

Extracting a field from RESTORE HEADERONLY

I'm trying to use 'RESTORE HEADERONLY' to get the date when the backup I'm about to restore was made. The command: RESTORE HEADERONLY FROM DISK = '<path to .bak file>' works fine in Query ...
2
votes
1answer
25 views

SQL Server 2012 In-Place3 Upgrade and Timing of Database Version Change

We are updating several SQL Server 2008R2 instances to SQL Server 2012 SP3 CU2. I know that the database versions will be changed from 100 to 110 during this process (e.g. once finished, the databases ...
7
votes
6answers
19k views

How to find out the content differences between 2 SQL tables and produce sync SQL

How do I find out the differences in data between the two tables that have exact schema, and how to produce synchronisation SQL to get the union results (without duplicates) ? These are the 2 ...
2
votes
1answer
33 views

Permissions on Trigger Problem

I am working with a database in MS-SQL 2008 R2, and I'm running into a problem. The basic setup is this: There are two tables of interest: dbo.Assesments and audit.Assesments. dbo.Assesments is the ...
3
votes
1answer
31 views

Can't set default_schema_name for Windows User Group?

When I try to set the default schema for my Windows Role/Group for Windows Server 2008 R2, it won't allow me to do so...I run: ALTER USER [UCPSAR\Service Provision] WITH ...
2
votes
2answers
666 views

SQL Server 2008 R2 Database Diagram Error Code

I have sysadmin privileges on a SQL Server 2008 R2 server. I'm working with the development database and want to save a database diagram I created. When I try to save I get the following error ...
3
votes
3answers
27k views

Login failed for user Error: 18456, Severity: 14, State: 11

I have an AD group XYZ that I have added to SQL Server security with data_reader permissions. The XYZ group has around 10 users in there who are successfully able to access the SQL Server database. I ...
1
vote
1answer
165 views

Joining two inline functions, slows considerably when filtering on second function

I have two inline functions that run quickly enough alone. When I left join them like this: select * from Function1(-1) F1 left join Function2(-1) F2 on F1.key = F2.key ...I get my result set in ...
3
votes
2answers
1k views

db_owner Member Can't Delete on Tables

SQL server 2008 R2. A user is part of an AD group. That AD group has a SQL account in this instance. That group has the following access to a Database: db_datareader db_datawriter db_ddladmin ...
1
vote
1answer
99 views

Validation of Partitioned table Performance

We are testing partitioning on a table which contains a datetime column (DateColumn). Partition function I am using is: -- 138623 + 16774 records CREATE PARTITION FUNCTION pf_TableName_DateColumn ...
42
votes
5answers
30k 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 ...
3
votes
1answer
3k views

Cannot Connect Using SSMS to Local SQL Server 2008 R2

About six months ago my vendor installed SQL Server 2008 R2 into a clustered pair of servers, DB01 and DB02. I've been logging on, periodically, to DB01 to audit some of the data contained in the ...
0
votes
1answer
103 views

Difference between “Table-Incremental” and “Table-Update” for “Population Type” when scheduling Full Text Index Population

I want to schedule a Full Text Index's population to keep it more up to date rather than leaving it to Automatic and not knowing the next population time as the users are complaining about the data ...
3
votes
4answers
6k views

Can I force a user to use WITH NOLOCK?

Can I force a user's queries to always run with the hint NOLOCK? e.g. they type select * from customer But what is executed on the server is select * from customer with (nolock) This question is ...
6
votes
1answer
540 views

AND conditions within unbracketed ORs work but why?

I have very similar code to the first block below. It is in production in a critical process, and I am not confident that it will always work. I would like to know why it does. It uses or (1=1) to ...
0
votes
1answer
169 views

Application is hanging when database upgraded from SQL Server 2000 to SQL Server 2008 R2

Presently we are working with SQL Server 2000, the database is perfectly working with our software for retail sales counter application developed with VB 6.0. But after upgraded to SQL Server 2008 R2, ...
10
votes
1answer
482 views

High PAGELATCH_* and WRITELOG waits. Are they related?

We are seeing very high PAGELATCH_EX and PAGELATCH_SH wait types along with high WRITELOG waits. I've diagnosed the query causing the PAGELATCH waits and can eliminate them by reducing the insertion ...
1
vote
0answers
30 views

Autocomplete in SQL Server Management Studio showing too much

I'm new to using SQL Server 2008 Management Studio as I just started working with it last week. Yesterday when I was working in one of our databases I remember that I used the auto complete ...