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

learn more… | top users | synonyms (2)

4
votes
0answers
39 views

How can we improve this PIVOT operation? ( SQL Server 2008 R2 )

We have this query, that runs in 1:10. We need to improve this. This is just a test query, but we will implement this upgrade on our real query. select top 100 * from ( select codCliente ...
-1
votes
1answer
29 views

Upgrade Database From 2008 to 2008 R2 [on hold]

I 've upgrade my database from SQL Server 2008 to 2008 R2 ,version of SQL Server on other server 10.50.1600.1 , version of my SQL Server is 10.50.1600.1, when restore database from server to my ...
0
votes
1answer
64 views

Is there a way to run this query in all databases ? ( SQL Server 2008 R2 )

How can I use this query for each database? I can easily use sp_msforeachdb but it breaks the results for every database. So I tried to insert the results in a temp table, but it's giving me syntax ...
0
votes
0answers
28 views

Fullbackup + Differential/Copy-only

We have a PROD full backups copied over to support envs daily and we restore to support environments to triage some issues, as the DB is growing big we would want to run the full backup copy only on ...
6
votes
3answers
181 views

Find data size of table for several identical databases on a instance

I have several identical databases with the same schema/table structure within a instance. I need to find the size of one table that they all have. For example, I have 30 databases on a instance and ...
3
votes
0answers
43 views

Permissions on a database object when it does not have any permissions associated to it

I want to find out the permissions of a specific object in the database. I have found many scripts on the internet, and developed my own too. Something like this: select dp.NAME AS principal_name ...
4
votes
1answer
16 views

Problem when add administrator for Analysis Server ( SQL 2008 R2 )

I'm trying to add a user as an administrator in Analysis Server, doing this: 1 - Connect - Analysis Server - Right click on the Instance - Properties - Security - ADD : This number was already ...
0
votes
1answer
20 views

Restore / Create a database with a data file (MDF) from backup the same but with two data files (MDF and NDF)

I must restore/create a db (in a new instance) in one file(mdf) from a backup of the same but with two files of data (mdf and ndf) How can I do it? This is what I try... but it did not work ...
2
votes
1answer
31 views

How to create test environment for lost transactions

I have a problem that I hope you can help me to solve. I'm using SQL Server 2008 R2. I need to create a test environment in which I'll be able to artificially 'create' lost transactions. Due to ...
1
vote
1answer
15 views

dtsinstall.exe does not deploy packages

I created SSIS project solution in VS2008. I created 5 packages and deployed them to SSIS Storage succesfully. I executed 4 packages successfully. I leave SQL server few days alone (holiday!). I ...
-2
votes
0answers
19 views

Upgrade from SQL Server Express Edition to SQL Server 2008 R2 [on hold]

Can I upgrade from SQL Server Express to SQL Server 2008 R2? If there is away can you please provide the steps for me.
1
vote
1answer
46 views

Aggregation by positive and negative values

I have a table called "PROD_COST" with 5 columns: +----+----------+------+-----------+-------------+ | ID | Duration | Cost | COST_NEXT | COST_CHANGE | ...
2
votes
1answer
59 views

Remote access to SQL Server 2008 R2 on Windows Server 2012 R2?

I have SQL Server 2008 R2 (named instance) on a Windows Server 2012 R2: Enabled remote access to SQL Server Enabled shared memory & TCP/IP protocols & named pipes for the SQL Server ...
3
votes
1answer
194 views

Gigantic gaps in IDENTITY column

This question is about strange behavior in SQL Server 2008 R2 SP3 Standard Edition x64. I have read many questions about IDENTITY increments in SQL Server and strange gaps between them. But basically ...
3
votes
1answer
48 views

How to wait for SQL Server replication reinitialization via TSQL

We use transaction and push replication. Every night after the data import 2 replication targets are initialized with the following commands. USE [dbImport] -- Execute at the Publisher to ...
1
vote
0answers
31 views

SQL server on VMWare restart loses five days of data

I am just now in the midst of a strange issue. A client moved their SQL database server from Hyper-V to VMWare on Saturday. Yesterday they called me complaining about performance. In the brief time ...
0
votes
1answer
40 views

Error converting data type nvarchar to float. Oracle Linked Server

Upon executing the following query select value, cast(value as float) float_value from OPENQUERY([ORACLESERVER], 'select 3/5 as value from table') I am getting the following error: Msg 8114, ...
1
vote
1answer
41 views

Query to remove duplicate calculated values

I have a client running SQL Server 2008 R2 SP1, and I can't seem to write a query that has distinct calculated values. I could change the schema to store the value being calculated, but I was curious ...
1
vote
2answers
56 views

Defragmenting Indexes Bloats Log Files

I'm having difficulty "defragging" indexes as a minimally logged operation. Before defragmentation, the database recovery model is switched from FULL to BULK LOGGED. Depending on the fragmentation ...
2
votes
1answer
92 views

how to get the information about sleeping processes that are causing blocking?

I have a procedure that allows me to see what is currently running in my sql server. This procedure does not use sys.sysprocess because it has been deprecated. However, I am struggling to find ...
2
votes
2answers
365 views

Maintaining SQL Server 2008 R2 on Windows 10

I have to run SQL Server 2008 R2 on Windows 10 but it is not supported and so I am not getting any updates. Installing a later version of SQL Server is not an option. I can find all the service packs ...
1
vote
3answers
39 views

Show data and disk use breakdown by table

I have a SQL Server 2008 R2 database being used by several deployed programs. Question: Is there an easy way to display how much space each table consumes, for all of the tables in the database, and ...
2
votes
0answers
67 views

SQL Server 2008 R2 - Two Files Stuck In Restoring Status

Background: SQL Server 2008 R2 mirrored and clustered. We recently failed over the databases via mirror and everything went smoothly. When it was time to do a differential backup, I got an error so I ...
3
votes
1answer
54 views

Page Life Expectancy Wildly Fluctuating

For months now, the PLE on one of our servers has hovered around the 2 million seconds. If varied slightly from day to day but was pretty stable there. This last weekend, we added 12 GB of virtual ...
-4
votes
0answers
16 views

what is the difference between Data Migration and Data Archiving [migrated]

what the difference between Data Migration and Data Archiving.o we have a sharepoint application for document archiving and its database size is nearly 1 TB.So i have plan to reduce the size of ...
2
votes
0answers
23 views

Mirroring won't fail back

Got an interesting mirroring issue. I am using SQL Server 2008 R2. Server A mirrors 8 databases to Server B ideally. Right now the 8 databases are principal on Server B (otherwise healthy). When I ...
0
votes
0answers
11 views

SQL Server 2008 R2 FREETEXTTABLE with FORMSOF(inflectional, string)

Can anyone confirm that using FORMSOF(inflectional, ...) is redundant when querying FREETEXTTABLE in SQL Server 2008 R2? Using FreeTextTable without formsof... gives me the same result list but with ...
8
votes
3answers
160 views

zero-or-one to zero-or-one

How do I model zero-or-one to zero-or-one relationship in Sql Server the most natural way? There is a 'Hazard' table that lists hazards on a site. There is a 'Task' table for work that needs to be ...
1
vote
1answer
36 views

How to audit all DML operations on a SQL Server 2008 R2 database

In order to assess options of implementing indexed views, I want to check the usage of DML operations of the tables. The database is new to me as well as the relevant business knowledge, so I just ...
6
votes
1answer
258 views

Cannot execute as the database principal because the principal “dbo” does not exist [duplicate]

I restored a backup of a database from SQL Server 2008 R2 to SQL Server 2012. When I try to access a particular page of my application, I get this error: Cannot execute as the database principal ...
5
votes
1answer
77 views

Detect if two time periods overlap

I have an HR system and I need to detect if a new leave (time off) request conflicts with an existing one. I have written code to do this based on the entire day; I now need to do the comparison based ...
0
votes
1answer
29 views

How to send an email with embedded values

I need to do a stored procedure that sends an email when a value reaches 100 with this wording in the body: "100 daily file downloaded from the user:USERID" where USERID is the user that reached 100. ...
2
votes
1answer
38 views

Replace/upgrade mirrored server hardware

We are deploying new VMs for a mirrored database that is in high safety mode with automatic failover and also going from SQL Server 2008 to SQL Server 2008R2. Most of the documentation I've come ...
2
votes
1answer
19 views

Unable to change Agent Operators

I am trying to change a SQL Agent Operator in my system and it will not let me. I have sa but I think it's partially due to how I'm doing it. I'm trying to modify the operator with the name NIT and ...
6
votes
1answer
553 views

Dropping a large table

On our production db there is a table 180 GB, around 2 millions records, I want to re-create it as a new empty table in a new filegroup and delete the existing table. What is the best practice to ...
2
votes
1answer
116 views

why this session in particular is suspended? Please see the details

My understanding regarding the suspended status of a session: A query gets suspended when it is requesting access to a resource that is currently not available. This can be a logical resource ...
0
votes
0answers
34 views

Application Taking More Time(Hang)

We are facing issues in our application and database. Our application has an asp.net 3.5 front end and a SQL Server 2008 R2 back end. Based on project requirement, we are doing fields setting for ...
6
votes
1answer
75 views

Size of individual transactions in log file

is there a way to find out the size of transactions from the transaction log by looking at the log file in SQL Server 2008? I do have an unexpected bloat of my logfile from some maintenance jobs which ...
2
votes
0answers
81 views

Why does SQL Server Management Studio crash?

When I open SSMS I get this message: SSMS - SQL Server Management Studio has encountered a problem and needs to close. We are sorry for the inconvenience What can I do? The issue persists ...
5
votes
3answers
164 views

Virtualize SQL High CPU

This is a client environment which needs significant overhaul so all I am trying to do is slow the bleeding. Situation CPU is getting crushed, often 80% or higher utilization VMWARE 5.5, Windows ...
2
votes
1answer
48 views

Does a database backup include all files? [closed]

If I have two files in my database (mdf & ndf), when I do a backup of the database with the statement: backup database mydb; are both the two files are backed up?
0
votes
1answer
21 views

Is there a query to see if all of my logins and password are moved during migration from SQL Server 2005 to 2008R2

I am trying to migrate my database from SQL Server 2005 to 2008 R2 test, I did follow back up and restore method to migrate the database, and I ran the QUERY provided by Microsoft to transfer logins ...
1
vote
2answers
51 views

Turning a multiple row resultset into columns with multiple rows [duplicate]

I currently have a resultset that looks like this: Title Value Start Time              1998-01-04 01:38:00.000 End Time                1998-01-04 01:43:00.000 Product Name         ...
3
votes
2answers
250 views

How does SQL Server perfom lock when using CROSS APPLY?

The best way I think I can ask my question is by starting with an example. Imagine these two queries : SELECT CAR.SerialNumber, TIRE.SerialNumber FROM dbo.tCar AS CAR INNER JOIN dbo.tTire AS TIRE ON ...
3
votes
0answers
21 views

Cluster node upgrade of SQL Server 2008r2 to SP3 Fails when moving resource

I'm trying to update a cluster to SQL Server 2008 R2 SP3. I've installed SP3 on the passive node but when I fail over the cluster it errors when running through it's upgrade scripts. The messages ...
9
votes
4answers
153 views

Is it poor practice to aggregate data from different tables into one?

Background I write a lot of large reports for and generally maintain a large health records DB (write SPs, functions, jobs, etc.). The original schema, and software that uses it, is from a different ...
7
votes
1answer
93 views

Clustered Index Help, where did i go wrong?

I was asked to review a very busy table and identify any areas of improvement. I have very limited power to change the table just the indexes. Table information 240 columns ~5 million rows Read ...
3
votes
2answers
169 views

Latin1_General_BIN performance impact when changing the database default collation

I have set the database collation to Latin1_General_BIN, to make string comparisons case-sensitive. Will this have an impact on performance? Will it have any impact on DML or DDL operations in the ...
2
votes
1answer
61 views

Can't upgrade a SQL Express to SQL Standard

I have a 64 bit SQL Server 2008 Express R2 installed and I cannot upgrade it to a fully licensed 2014 Standard. When I try to update to SP 4, I get this issue: If I try to run the upgrade edition, ...
3
votes
2answers
303 views

Database Schema for Blank Database is large. 20MB

Is it normal for blank databases with just schema to be large? Or is there a way that I can reduce the size of the initial database?