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
189 views

Post mortem on tempdb transaction log after instance restart?

TempDB transaction log filled up and I had to restart SQL Server as a result. After the restart, is there any way to see what caused the transaction log to fill up in the first place?
7
votes
1answer
3k views

Broken tempdb location and can't recover

Made a mistake and mistyped an alter database command for tempdb. Now the instance won't start. I can't start in single user mode using -m as it states the tempdb could not be found. I tried using: ...
5
votes
1answer
778 views

Don't rebuild indexes < 1000 pages - then why bother having them?

Microsoft recommends not bothering to rebuild indexes that have less than 1,000 pages (from memory I think it's because they are so small that they will be held in RAM). If I'm not rebuilding them, ...
10
votes
3answers
213 views

One index or two?

I have the following index created on a table in my database: CREATE INDEX [idx_index1] on [table1] (col1, col2, col3) The server is suggesting the following 'missing' index: CREATE INDEX [...
1
vote
1answer
86 views

Display a List of used Table and Column

Goal: Display detailed information if database A use database B:s table and its column. if yes, the list will display what is the name of the database B:s table and column that is being used in ...
1
vote
1answer
2k views

Finding rows where a column contains [ and ] characters

I need to get results where the column contains a string that includes [ ]. When I use LIKE, it takes the [] as a wildcard sequence, and doesn't work as I need. For example, I want to search through ...
3
votes
2answers
362 views

Prevent SysAdmin from doing a Database Restore

I am a sysadmin on my SQL Server 2008, and I need to be, but I would like to setup my security on the Production server to prevent me from accidentally restoring to the production database. I ...
2
votes
2answers
4k views

Update one instance of Sql Server 2008 R2 Standard to SP2?

We have one server with multiple Sq Server 2008 R2 instances installed. To support an application we need to upgrade to one instance to SP2. Can we only apply the service pack to the specific ...
1
vote
2answers
2k views

High Disk I/0 Issues

Our database has been giving this error occassionally... "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel....
1
vote
1answer
1k views

SQL Server performance for tables having large number of columns [closed]

I have a requirement where I need to create a table with about 600 columns. Out of 600 columns about 200 columns will be XML columns. Other column will be of other datatypes such as int, varchar, ...
1
vote
1answer
406 views

Using OLE DB Source SQL command vs desinging the query using the object tools

In SSIS, we can use the OLE DB Source SQL command to run a T-SQL script on the database and then use the output as the source. For example the a complex query can be wrapped in the OLD DB source SQL ...
4
votes
1answer
2k views

How bad is query plan pollution (too many equivalent plans in cache)?

Question currently affects C#/.NET, DB access based on ADO.NET and SQL Server 2008 R2, but I think it applies to other databases as well. I noticed some old modules of a system have non-optimal SQL ...
0
votes
1answer
2k views

Why are timestamps not always increasing with concurrent inserts?

I'm seeing some unexpected behavior with timestamp (rowversion) columns . I created a test table: create table Test ( Test_Key int identity(1,1) primary key clustered, Test_Value int, ...
3
votes
1answer
444 views

SQL Server: Can Page Verify be set to Checksum on any database, or must I check compatibility?

In Microsoft SQL Server, I have some older databases set to "Torn Page Detection" rather than "Checksum" page verify. Do I need to determine if a given SQL Server database is 'compatible' with the "...
27
votes
3answers
4k 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. ...
2
votes
1answer
207 views

Optimizing query on a partitioned table

Tried to find an answer for this, but couldn't. So bear with me and apologies if this has been answered elsewhere. It may be just me not being able to find it. Here's the setup: SQL 2008 R2 EE. Table ...
0
votes
1answer
76 views

SSMS gives error if column name is “MERGE” where as other keywords works

IN SSMS create table if we include sql keywords in column name then it will be wrapped around square brackets "[]" automatically and the table gets created without any error generally. But if we ...
0
votes
2answers
97 views

Can I improve this query?

SELECT * FROM SameLogTable WHERE ID_Table IN (SELECT ID_Table-1 FROM SameLogTable WHERE <SameCondition>) OR ID_Table IN (SELECT ID_Table ...
2
votes
1answer
416 views

NEWSEQUENTIALID() resets sequence on server restart

MSDN learns me that NEWSEQUENTIALID(): Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting ...
2
votes
1answer
84 views

What is the appropriate SQL method for “Safe to SUM”?

In SQL Server 2008 R2+ we, unfortunately, have some nvarchar columns storing numeric data. In some cases we want to SUM the data when it can be summed. The problem is that ISNUMERIC() returns 1 for a ...
2
votes
1answer
497 views

SQL Server Replication - setting up scheduler issue

We have tasked to set up a scheduler to synchronize data between a publisher and subscriber on daily basis. The subscription was set using windows sync manager and it runs just fine when run manually. ...
1
vote
1answer
297 views

What is the default value of ANSI Padding Enabled, and what should value should I set it to?

When creating a new database, ANSI Padding Enabled is set to False by default. According to this however, the default is ON (which I assume is the same as True). They also recommend to set it to ON ...
1
vote
0answers
781 views

Log file growth due to replication issue

I have one primary database and it's getting replicated to another server. Both servers are SQL Server 2008 R2 with SP1. Replication was running fine till last week. We have transaction log backup ...
1
vote
0answers
4k views

Strange error with restoring database

Some times ago I have done backup my MS SQL Server database and then I restore it (on other computer and server). Today I would make just like two weeks ago. But when I choose file to restore from ...
4
votes
1answer
543 views

Suggest better design pattern for reverse DNS lookups

I'm working on a project where I need to look up the hostnames associated with IP addresses who were logged making HTTP requests. The lookups currently happen as part of a daily ETL job. The current ...
2
votes
1answer
759 views

Checkdb giving error for master database

When i run dbcc checkdb on master database i get error like Msg 5030, Level 16, State 12, Line 1 The database could not be exclusively locked to perform the operation. Msg 7926, Level 16, State 1, ...
2
votes
1answer
567 views

Should I run a delete first before I execute index rebuild to a large table about 15 Million records

I need help for a better approach on how to approach this. Currently we have a table that is huge about 15 million records. We plan to create an index on this table however due to large size, we ...
1
vote
1answer
900 views

SSRS Line Graph formatting

Environment: Windows 8.1, SQL Server 2008 R2 Express database, SQL Server 2008 R2 BIDS Database: Performance counter data collected on client servers. The particular values involved in this question ...
1
vote
0answers
31 views

How to restrict SQL Server database access to application or server running the application [duplicate]

I have a third-party application that connects to a SQL Server database via a SQL Server Native Client 10 ODBC data source. The application requires that each user of the application must have read/...
10
votes
1answer
531 views

Poor query performance

We have a large (10,000+ lines) procedure that typically runs in 0.5-6.0 seconds depending on how much data it has to work with. Over the past month or so it has started taking 30+ seconds after we do ...
2
votes
1answer
11k views

Does “use AWE to allocate memory” do anything on a SQL Server 2008 R2 64bit Windows 2003 system?

After checking the box I noticed that queries that would timeout after 30 seconds would complete execution. The TechNet article says "AWE is not required and cannot be configured on 64-bit ...
0
votes
0answers
545 views

SSPI handshake failed with error code 0x80090308 connection to Sql Server 2008 R2

I have a cross domain situation where the SQL Server 2008 R2 server sits in one domain and the client in another. There is a one-way trust between the domains. The SQL Server runs on a domain account. ...
2
votes
5answers
34k views

Backup and restore database - occured system error 5

In my work I made backup of database and I receive file .bak. Now I would like restore this database in my machine. But I get the following error: Cannot open backup device 'C:\Program Files...\...
1
vote
1answer
7k views

Unexpected EOF during bulk insert

USE [campaigns] DECLARE @FILENAME varchar(400) = 'W:\PlayerListing\List.txt' ,@EMAIL_SOURCE varchar(50) = 'listing' ,@SQLCMD varchar(8000) PRINT (CAST(GETDATE() AS varchar(30)) + ' BULK ...
2
votes
1answer
4k views

SSIS Event Handler at Package Level

I have a fairly simple SSIS package (handful of Execute Task and a few Data Flow task). The end result I am looking to have: I would like to get notified by email when an error occurs anywhere in ...
6
votes
3answers
3k views

Creating a read only replica of SQL Server database for reports

I've faced a problem when my .Net web application is running slow, even when I've optimized all queries. As it turned out another developer, who is making reports runs a bunch of non optimized queries ...
1
vote
1answer
416 views

Cannot grant execute to stored procedure [closed]

When I try to GRANT EXECUTE ON SP_NAME TO [DOMAIN\USER_NAME] on my database, I'm getting the error message below: Msg 8115, Level 16, State 1, Procedure grant_safety, Line 5 Arithmetic overflow error ...
2
votes
1answer
298 views

Logging errors with extended events excluding some users

I use extended events to store database errors, like this: CREATE EVENT SESSION [ErrorCapture] ON SERVER ADD EVENT sqlserver.error_reported ( ACTION ( sqlserver.client_hostname, ...
4
votes
2answers
14k views

DBCC SHRINKDATABASE TRUNCATEONLY 2008R2 vs 2012

Im a bit confused was the TRUNCATEONLY parameter changed in 2012 or is the documentation wrong in 2008R2? 2008R2 Releases all free space at the end of the file to the operating system but does ...
2
votes
2answers
739 views

Creating a partition on my soft-delete indicator column

In SQL Server 2008 R2 I am exploring the use of partitioning tables. We have a soft-delete scheme which is sometimes an IsDeleted bit NOT NULL and sometimes a DateDeleted date NULL I want to focus ...
2
votes
1answer
358 views

sql server NUMA binding to ports

I am trying to allocate a set of CPUs on a 2 node NUMA server as below : Below is from coreinfo that shows physical processors, logical processors and NUMA nodes on the server. Logical to Physical ...
1
vote
1answer
5k views

Some queries are very slow after index rebuild

After finding out almost all databases on my SQL Server had fragmentation over 40%, I decided to do an index rebuild on all tables using a fill factor of 80. After rebuilding all indexes, some ...
1
vote
1answer
253 views

Can I force MSDTC to control only within a single database?

Using MS SQL Server 2008 R2 we have .NET code and we would like to use nested connections to the same database within the same transaction scope. I understand and accept that this will require ...
1
vote
2answers
565 views

Resume log shipping from a specific transaction

Is it possible to force SQL Log Shipping to run from a given .trn file onward? I have two databases, where one ships to another this was working fine. However I had to restore the primary database ...
0
votes
1answer
59 views

SQL Server 2012 Intellisense Support for Extended Events

Using SQL Server 2012 with SP1 for some reason when try to use extended events programmatically using T-SQL. I am not able to see intellisense working? Just trying to check is this something on my end ...
1
vote
2answers
2k views

Delete rows from a large table with joins and aggregating (resulting in 1 % less rows in the big table)

I got a bit stuck on performance, so I thought about asking a bit of help. I have a working query, but unfortunatelly it feels rather slow and I know it's not as performant as it could be made (easily?...
0
votes
1answer
435 views

In Extended Events what is the difference between rpc_completed and sql_batch_completed? [duplicate]

I understand that rpc_completed is defined as the completion of a remote procedure call and sql_batch_completed is the completion of a t-sql batch. However can anyone explain in concrete terms what ...
4
votes
1answer
3k views

Licensing standalone instance of SSIS

Basic background: I have an application on an app server (no MSSQL installed) that I want to allow to call an SSIS package. One Solution: One option seems to be that we can install just SSIS on the ...
3
votes
1answer
734 views

How do I find the longest string in a row amongst various text fields?

Does anyone have an easy query to find the longest string of characters in a data row from ALL alphanumeric (text, nchar, varchar, etc) type columns from ALL tables in a single SQL Server 2008 R2 ...
3
votes
1answer
3k views

Table-valued function runs fast, but slow from another function [closed]

Disclaimer: I'm a senior sysadmin who's trying to help our development team speed up the production database. Our users are hurting, bad. Please don't shoot me for not being great with SQL The ...