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

learn more… | top users | synonyms (2)

3
votes
2answers
749 views

SQL Server NULL Index

I am new here and have a little question. In a customer environment after checking his index fragmentation we see some "NULL" named index that we couldn't rebuilt or reorganize. This was a SQL ...
0
votes
0answers
434 views

How to convert Excel into Star-schema Model?

My end user gave an Excel spreadsheet with 15 columns and 50K rows out of which 5 columns are calculated and 10 are not. My data modeler has converted Excel into a single TABLE on Microsoft SQL ...
1
vote
1answer
882 views

Snapshot agent stuck at 48%

We are having to redo a publication/subscription and while running the snapshot agent it stalls at 48% with the message "The process is running and is waiting on a response from the server." ...
0
votes
1answer
81 views

Can't set up mirroring for a specific database

We have several databases. All of them are mirrored. Until now, we never had issues with this. One database had some problems, and now when we try to enable mirror again, it just stops. Message in ...
4
votes
1answer
2k views

SQL Server 2008 R2: query RECENT locks, NOT current locks

The database had average lock wait time of 30 seconds for a period of about a minute. I didn't run my lock query in time to see what was happening. I can't seem to find anything that shows recent ...
3
votes
1answer
875 views

Timestamp on SSIS update on tsql

I have a couple of SSIS packages that copy the content of several tables to another db. One of the columns updated in the process is a timestamp datatype. From what I read this datatype can't be ...
0
votes
0answers
1k views

SSRS - Report using Stored Procedures, Cascading Parameters and Views

I am looking for a non-trivial Microsoft SQL Server Reporting Services example that shows the efficient use of the following for calculated columns such as: Profit = Revenue - Expense Margin = Profit ...
1
vote
1answer
748 views

How to include categories for which there are no values in an ssrs chart

I've got a query that gets some data points for the last 6 months (prior to current), akin to this: SELECT [DataPoint], [When] FROM Table WHERE [When] >= DATEADD(MONTH, -7, @...
2
votes
0answers
397 views

send custom email when SQL Server job fails

I have setup a profile and account for sending mail using DatabaseMail. I have a SQL JOB. If the job fails, I receive an Email Till this point, everything works fine. This is what the email looks ...
5
votes
3answers
3k views

Encountered unexpected growth Transaction log file size

We are using SQL Server 2008 R2 Standard version, we created Maintenance plan Rebuild Index, Reorganized Index and update Statistic , Scheduled Every day at 1 AM. After Executing this Maintenance Plan,...
0
votes
1answer
257 views

How does the Distributor know you have seeded/initialized your Subscriber using a backup?

Im building my first transactional replication database for our Sharepoint team. The Publisher database is fairly large 330GB+ Because the Publisher database is in a different data center than the ...
0
votes
0answers
1k views

incorrect value from SUM Function

Has anyone seen this before? When doing a simple sum on a table: select sum(column) from table where column is not null I'm receiving the data 1558.39999999999 However, investigation of the ...
0
votes
0answers
274 views

SQL Server 2008 single-line PIVOT/UNPIVOT combo

I have a request to get information from a table with a stored procedure that shows all the necessary data for in one line. This table includes a hierarchyID, so I am trying to use the HID methods in ...
1
vote
2answers
217 views

Backing up my SQL Server System Databases

I'm trying to create a recurring scheduled job to fully backup the master, msdb and model databases, I'm using the Maintenance Plans for SQL Server 2008 R2, in the "Specific Databases to choose from ...
0
votes
2answers
1k views

How can I speed up this query on table with 65 million records

select hash_id, hash_value, substring(T.cri, 1, 7), substring(T.cri, 9, D2.Pos-9), substring(T.cri, D2.Pos + 1, D3.Pos - D2.Pos - 1), substring(...
11
votes
1answer
219 views

How can I interpret the results of these DMVs to help me evaluate our partitioning strategy?

Version: SQL Server 2008 R2 Enterprise Edtn. (10.50.4000) In attempt to evaluate our partitioning strategy, I wrote this query to get the access methods against indexes on partitions (in the broadest ...
0
votes
1answer
171 views

Rebuild Index in system database

I use SQL Server and have multiple indexes in system database whit index fragmentation 30% or more. I don't know that SQL server manage system database index automatically or I want to manage them. ...
1
vote
2answers
85 views

Known vulnerabilities for when a guest executes a stored procedure on SQL Server

I have a production database with crucial business information. Meanwhile, my company is involved in a project with some other company and we have to exchange some information on a regular basis. How ...
2
votes
1answer
56 views

Transaction Log Backups on SQL Server

Running SQL2008 R2 SP2, database is in FULL Mode Is it Possible to shrink the transaction log without putting it into SIMPLE mode? Temporarily cannot take log backus and don't want it to grow until I ...
3
votes
1answer
1k 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 ...
0
votes
4answers
2k views

What can cause Reads to slow down on SQL Server 2008R2?

Our primary DB is 15GB in size. The server has 16GB of RAM. We are using the simple recovery model. Lately we've noticed that our custom apps lookups - just basic reads with adhoc SQL statements - ...
2
votes
1answer
14k views

How to convert sql server table data into MS Access(.mdb) file

I have SQL Database table with 6664055 rows, I want to convert this SQL Server table data to MS Access format(.MDB), How Can I do this?
3
votes
1answer
94 views

Count Data on Third Table Through Link

Suppose I have the following three tables: SupportTicket, Device, and Link. I've created these in SQL Fiddle. I want to display a list of support tickets within a time range that meet specified ...
0
votes
1answer
32 views

JOIN correctly no matter which end is linked [closed]

Suppose I have the following two tables: SupportTicket and Link. I also have other tables that Link can connect between. I've created these tables and a hypothetical third table that can be linked to ...
12
votes
1answer
733 views

SQL Server 2008 R2 index rebuild fails with severity 17

Occasionally during our index maintenance, the job will fail with a SEV 17 error where enough space can not be allocated for the object it is rebuilding. The database is laid out as such: Data_file1 ...
0
votes
1answer
393 views

SQL Server 2008R2 - Stored Procedure with 2 updates causing deadlocks

I have a web api web service that calls an authentication stored procedure. This stored procedure calls a different stored procedure that does 1 update: UPDATE ...
4
votes
1answer
174 views

Drastic performance difference in View Dependencies between SQL Server 2005 and 2008 R2

Background I am a fledgling DBA who was hired in the middle of a migration from SQL Server 2005 x64 to 2008 R2 x64. We also migrated from a local server to a colo virtual server, and moved some of ...
0
votes
0answers
211 views

How to configure log shipping in SQL Server 2008 R2 Standard Edition

Could you please guide me on how to configure log shipping in SQL Server 2008 R2 Standard Edition for same hostname with two different IP's. (Primary and Secondary server) example: Primary machine ...
0
votes
0answers
27 views

SQL Server query used new index until this morning [closed]

I created an index yesterday to fix a slow query where it was spilling into TempDB, this worked great and we had the best performance we have seen in a while, till this morning, now the query is using ...
4
votes
1answer
744 views

Deleting Rows is Increasing Index Size

We have a very large table in a SQL Server 2008 R2 Standard server which I am archiving rows by copying them into another database on a separate disk and then deleting them from the original table ...
-3
votes
2answers
3k views

How to calculate the hours worked by all employees in a day? [closed]

vinay_hours id cico 1 2014-02-20 10:31:00.503 1 2014-02-20 13:00:00.503 1 2014-02-20 15:31:02.503 1 2014-02-20 19:00:02.503 2 2014-02-20 10:00:02.503 2 2014-02-20 18:31:02.503 expected ...
0
votes
2answers
6k views

how to store multiple value in column in sql server [closed]

I am creating job post application. In my first table the columns are This is my master table ID Position jobDescription minExp maxExp LastDate InterviewDate Project HiringManager interviewer ...
3
votes
1answer
417 views

Are there reasons to not use SCHEMABINDING on database objects?

Not using schema binding seems like turning off compiler warnings - you don't find out you broke something until runtime. The benefit is that schema bound objects protects the database from ...
1
vote
1answer
551 views

CASE statement is returning different results within a COALESECE than it does outside it

We're using SQL Server 2008 R2 with database compatibility set to SQL Server 2000, and I've got a table with a few rows from which I'm assigning values to multiple variables. I'm looking to switch ...
3
votes
0answers
4k views

Getting “Attempt to fetch logical page in database” error when running query [closed]

When I run queries from one of my tables I get an error Msg 605, Level 21, State 3, Line 1 Attempt to fetch logical page (1:8687634) in database 7 failed. It belongs to allocation unit ...
8
votes
1answer
3k views

Free space of mdf and ldf does not match with database free space

In SSMS I saw file size related properties and found below details for one database. Here values does not match with other properties. Here size of the mdf, ldf and total size matches with other ...
2
votes
1answer
430 views

Is it possible to replicate wide table in MS SQL?

The reason of me using sparse columns is to overcome limit of 1024 columns in a table (I know that generally it's not a good reason and there are downsides, but in my situation it's good option ...
0
votes
1answer
2k views

HierarchyID--multiple root values in one table

Currently I am trying to work my way around the HierarchyID concept in order to convert a few tables for our programmers. I have the gist of it understood, but one of the cases I'm working on has me ...
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
910 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
179 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
637 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
148 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 ...
5
votes
2answers
31k 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
645 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
19k 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
116 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
644 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 ...