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

learn more… | top users | synonyms (2)

2
votes
1answer
1k views

SQL Server 2008 mirroring migration to SQL Server 2012 always on

Quick question, request your input. I am migrating a SQL Server 2008 R2 instance with database mirroring to SQL Server 2012 with always on. Should I : Restore the SQL Server 2008 R2 backup to SQL ...
3
votes
2answers
4k views

Grouping by date range in a column

I have a table with data as: ID Action Date 1 Action1 2014-01-20 2 Action1 2014-01-22 3 Action1 2014-01-28 4 Action2 2014-01-20 5 Action2 2014-01-22 6 Action2 ...
0
votes
0answers
33 views

Post Database upgrade on new instance [duplicate]

I reviewed the SQL Server 2012 technical upgrade guide and also googled for more recommendations. I am migrating a SQL Server 2008 R2 instance to SQL Server 2012 on a separate machine. Here is what ...
2
votes
1answer
867 views

Free space from image column after update

I'm doing some cleanup on one of our database for development purposes on a test server. There are a couple of tables with one IMAGE column. Both tables take roughly 50Gb of space, being the image ...
5
votes
2answers
167 views

Why is an entire index that is infrequently used stored in the buffer pool?

I have been looking at the memory usage of a server and noticed something odd when drilling into the details. One index is eating up ~15GB of the buffer pool. That happens to be the size of the entire ...
1
vote
0answers
621 views

Error: 18456, Severity: 14, State: 11 [closed]

I have a brand new server (VM) just built. I installed SQL Server 2008 R2 and applied SP2. I opened an RDP session in console mode as myself. During installation I had the Windows AD group for the ...
1
vote
1answer
145 views

Create database before restore multiple files

I've several database, most of them have multiple files (both data and logs) and multiple diff and trn backups. I've already a restore script (scheduled and working on other server) that takes all the ...
4
votes
2answers
27k views

How can I dynamically alias columns?

I have a table (not designed by me) which has 20 variably named columns. That is, depending on what type of record you are looking at, the applicable name of the column can change. The possible ...
3
votes
1answer
616 views

Most efficient queries on flags in TSQL: bit field or table join?

Looking for the most efficient database structure for selecting rows from a large table based on one or more true/false values. For example, whether or not a user has opted in to marketing ...
5
votes
4answers
17k views

Changing IP address of SQL Server 2008 R2

I would like to know if there are any dangers or relevant precautions before changing the IP address of a SQL Server 2008 R2 box. We have built a virtual PC with Windows Server 2008 R2 and SQL Server ...
3
votes
0answers
112 views

SQL Server replication - recommendations for stability

We have two SQL Server 2008R2 Standard servers, and we've set up replication of a single database between them. The replication is about as simple as it can get - it's a transactional replication from ...
0
votes
1answer
500 views

Unable to re-install an instance using the original instance name

We are trying to uninstall and re-install a 2008R2 instance. We have uninstalled the database engine but when we tried to re-install it we got the error "Original instance id\name in use". The ...
1
vote
2answers
503 views

DB Locks - How would one handle long transactions and maintain high concurrency?

We're maintaining a POS/WMS Application built in powerbuilder that runs on SQL Server 2008 R2 backend. Recently, we've been having a lot of locking issues and I believe it has to do with how we handle ...
1
vote
1answer
772 views

Can I alter a view being referenced with schemabinding if the change fulfills the binding requirement?

In SQL Server 2008, I have views WITH SCHEMABINDING and I need to alter one. I am changing a column while not changing its alias so consumers will be unaffected. If I were to drop all my other views ...
1
vote
0answers
437 views

Timeout while using table valued parameter

I'm trying to find the cause for this error while calling a stored proc with a table valued parameter: Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target ...
3
votes
1answer
63 views

Single statement or batched delete?

I need to delete 6.3 million records from 25 tables in production. I have the following two queries, but I don't know which one will be best: delete jc from Jtable jc join J1Table j on j.JobId = ...
3
votes
2answers
49 views

Is my Publisher database my actual source database or is it a new database?

I am setting up transactional replication for the first time and am working through my backup policies I need to set. Is my Publisher database my actual source database? Or is it a new database that ...
1
vote
1answer
85 views

Return value formatted similar to if casted as money

My client's system is very outdated, and there is nothing I can do about it. What I'm trying to achieve is to extract values always with at least 2 decimal places. E.g. From 1826.5695600000 ...
1
vote
2answers
1k views

Can SQL Agent job notifications be sent to one operator for success and another for failure?

We have a requirement that some people are sent notification on success, and other on failure. But the UI seems to only allow a combination of 1 operator + 1 condition. So I was wondering if we ...
8
votes
2answers
560 views

Should I nest dependent outer joins in SQL Server?

I have heard mixed information on this and am hoping for a canonical or expert opinion. If I have multiple LEFT OUTER JOINs , each dependent on the last, is it better to nest them? For a contrived ...
3
votes
1answer
297 views

Turn Off Alerts

We have a SQL Server 2008 R2 database instance running on a VM. Nightly, between 10p and 12a, we are getting the following error message: DATE/TIME: 2/3/2014 10:16:37 PM DESCRIPTION: Database ...
2
votes
1answer
273 views

Copy FileGroup to Another FileGroup

we run Microsoft SQL Server 2008 R2 on production server. We need create new tables and load a big amount of data on them (roughly 1 TB!) I am wondering if it is possible to insert this dataset on ...
1
vote
1answer
533 views

Linked Server - login trying to access database that is not default

Trying to create a linked server from server A (2005) to B (2008 R2). Everything worked when created. The login on server B was mapped to two databases: DataBase1 and Database2. Database1 was the ...
6
votes
3answers
2k views

Cube processing either takes too long or fails

I have a SSAS Cube with 35 dimensions and 10 measures. Some of the dimensions are quite large in size. In almost all dimensions data either get updated and inserted. Measures have high volume of ...
5
votes
2answers
2k views

if-then-else gives a boolean headache

I have three statements to combine but I cannot find the correct solution for SQL Server 2008 R2: I need to concatenate three columns into one but in three different ways depending on the result in ...
3
votes
1answer
2k views

sql server service broker queue pass parameter to stored procedure

Stored Procedure Create PROCEDURE [dbo].[InitServiceLog] ( @name varchar(max)=null ) AS BEGIN SET NOCOUNT ON; insert into [AdventureWorks].[dbo].[ServiceBroker_Log](Service) ...
0
votes
1answer
53 views

SQL Server 2008 R2 Resource Managment

I have a server running Windows Server 2008 R2 Enterprise 64Bit, with 256 GB RAM. I am running SQL Server 2008 R2. With normal usage, SQL Server is using about 232 GB of RAM. Occasionally it will ...
3
votes
1answer
187 views

Jobs for cycling error logs

Couple of weeks ago I asked this question Error logs various questions and got the answer I needed. Based on the answer and some readings I want to create the following jobs: cycle the error log ...
1
vote
1answer
52 views

Rebuild the Fulltextcatalog

I have a Database (80GB) that is able to store attachments. These attachments are full text indexed. For some reasons I had to rebuild the fulltext catalog and this 30 hours. Our business users ...
0
votes
0answers
123 views

Replication with no network connectivity

I have SQL Servers on two isolated networks that cannot be connected directly. However, there is a need to keep data in sync between the two systems. Obviously SQL Server replication won't work. It ...
0
votes
1answer
417 views

linked server hanging

We've been using a linked server to an access database for close to a year now with little issue. Now when I run a select query against the linked server the query sits there and never finishes. ...
2
votes
2answers
8k views

Upgrading to SQL Server 2012 from SQL Server 2008

I have upgraded my SQL Server 2008 R2 Enterprise edition to SQL Server 2012. Even after upgrade I can see & use SQL Server 2008 R2 instance without any issue. Did my upgrade went fine? I can ...
2
votes
1answer
2k views

Issue while upgrading SQL Server 2012

When I tried to upgrade SQL Server 2008 R2 to SQL Server 2012 everything went well except below issue How do I fix it? Do I need to uninstall SQL Server 2012 again? EDIT Feature: ...
6
votes
1answer
2k views

How is SQL Server returning both a new value and old value during an UPDATE?

We've had issues, during high concurrency, of queries returning non-sensical results - results the violate the logic of the queries being issued. It took a while to reproduce the issue. I've managed ...
5
votes
1answer
359 views

Numeric data type with variable significant digits

I need to implement a data type which should preserve the last zeros in the decimal points in SQL Server 2008 R2. Example of the data are 1.002 1.210 0.00100 0.1401 My clients get lab measurements ...
1
vote
1answer
5k views

Cannot Start SQL Server Reporting Service [closed]

That's the first time I use SSRS so my apologies if I'm asking something stupid. I have a SQL Server 2008 R2 already installed with SSRS. When I try to start the SQL Server Reporting Service I ...
4
votes
1answer
153 views

In READ COMMITTED isolation, is it possible to read the old value after the update?

I'm trying to solve an issue with SQL Server 2008 R2 and am grasping at straws. Given that data is housed in leaf-nodes of the B-tree, and is also exists in indexes, that someone could read multiple ...
3
votes
2answers
57 views

Calulating savings between two schemes

I've normalized a single table to reduce the number of nullable columns from 3 down to 0. The idea was to save a little drive space but I'm seeing a 45% decrease in space needed to store the same ...
1
vote
2answers
104 views

SSIS via Import Wizard SQL Server 2008 R2

I have a flat file i need to import into SQL. For a lot of boring reasons, I curently don't have access to use BIDS, only the import wizard in SSMS. The flat file looks sort of like: A 2 B ...
1
vote
1answer
601 views

Use @@servername and db_name() on notify operator task

I'm setting up a maintenance plan and on error I added a "Notify operator task" with some text. But I want to be able to add something like: Backup failed on MyDatabase\Instance_name...blablabla ...
2
votes
1answer
3k views

Could I realistically use SQL Server Express as a distributor database for transactional replication?

We have a group of users within my company that are running reports directly against our production database. It has gotten to the point where they are hammering it enough to cause performance issues ...
1
vote
1answer
998 views

Does MAXDOP affect the number of tempdb data files I should create?

From what I have read, best practices for configuring tempdb includes creating multiple data files. The rule of thumb as I understand it is if you should create the same number of data files as you ...
1
vote
1answer
30 views

Change A Database Name When Restoring

I'm going to be moving a database from one server to another. Right now the name makes no sense and I'd like to change it. I found a answer here that makes me think it might be relatively simple. My ...
3
votes
2answers
372 views

How can I most closely monitor this table in production to find why rows are disappearing?

Scenario: We write online banking software and for the time being (due to legacy design decisions) are using Quartz 2.2 right now as a queue system to perform a handful of operatoins long term. ...
4
votes
1answer
7k views

SQL Server 2008 R2 Restore Database From File - “failed: 38(failed to retrieve text for this error. Reason: 15105)”

I'm running a database restore on a backup file that was created up on a clients server, the backup file has been moved to our server (same SQL version 2008 R2) and I'm getting the following error ...
3
votes
3answers
2k views

Is there a way to execute sql statement in sql server based on sql server version?

I would like to create computed index on a table if sql server is 2008 or newer and a simple index if sql server is 2005 or older: -- check for sql server version if (select ...
2
votes
2answers
667 views

Scripting all of available databases to .sql files containing schema+data+users with single command

I have a SQL 2008 Express with 80 databases in it. I want to move these databases completely to another server which is hosting a SQL 2008R2 Enterprise instance. As i'm told it is better to script ...
0
votes
0answers
43 views

unclear SQL Server Issue

I'm attempting to determine root cause for an issue that caused an outage for my company. Essentially the site connections to our database server were timing out after not receiving a response for ...
6
votes
1answer
199 views

2008R2 Cannot Disable Non-Clustered Index

We have identified a handful of superfluous non-clustered indexes and I would like to disable them for a few days before straight dropping them. However when using the GUI in SSMS (right click > ...
1
vote
0answers
116 views

Differential backup job is working but not the Full daily backup

I have a full backup job for my database that runs once daily, I see it posted daily in the folder that it should go to, but when I check the "View History" for this job in SQL Server Agent, there are ...