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

learn more… | top users | synonyms

1
vote
1answer
469 views

Setting Alerts for Autogrow

Can I set alerts for an autogrow event in SQL Server (2005 or 2008 R2)? I've searched around and haven't found the 'error number' for autogrowth, if there is one. In case this isn't possible, how ...
2
votes
0answers
1k views

Copy database wizard between servers results in Access Denied error. [closed]

I'm trying to copy a database from SQL Server 2008 R2 to another server, using the Copy Database Wizard in SQL Server Management Studio. I get an error telling me to check the error log on the ...
4
votes
3answers
463 views

Does Snapshot Isolation only give you what has been commited? (And General Snapshot Isolation questions.)

So, I have some deadlocking issues. I have seen two options to resolve it. Add read uncommitted to my db or do Snapshot Isolation and add read committed snapshot. After doing some research into ...
3
votes
2answers
193 views

What is the signifigance of a fillfactor of 20?

I have a database for our ticket system in SQL Server express. I was poking around and noticed that a lot of the indexes have a fillfactor set to 20. What is the signifigance of it being set to 20? ...
10
votes
1answer
594 views

Behavior of data in indexes based on fill factor

Let's say you have a database where the default fill factor is 20. Whenever data is inserted, does it only create pages filled up to 20%? From my understanding, when the data is inserted there ...
1
vote
1answer
1k views

Rights needed to see database files in Properties dialog?

I have a contractor setting up a database application. The database is set up, the login for the app is set as the database owner, but has only 'public' at the server level. When the contractor logs ...
8
votes
1answer
2k views

Trouble With Database Permissions for sp_send_mail

I am trying to send database mail but I am getting EXECUTE permission denied on the object 'sp_send_dbmail' database 'msdb', schema 'dbo'.. I code I am running is as follows: SELECT SUSER_NAME(), ...
1
vote
2answers
738 views

SQL Server 2008 R2 Express high insert slowdown possibly due to PAGEIOLATCH_SH

I have a program that insert row to a table though multiple connections. For each insert, the connection would open and then close. When I run the program, the insert were fast. However, once it reach ...
10
votes
1answer
3k views

How can I add a rowversion column to a large table with minimal downtime

Using SQL Server 2008 and later, I want to add a rowversion column to a large table however when I simply ALTER TABLE [Tablename] ADD Rowversion [Rowversion] NOT NULL Then the table is unavailable ...
1
vote
1answer
751 views

Copy SQL table to other server identity becomes int

I just built an Umbraco website. This uses a not-that-small SQL Server db. I copied this DB using the export data function built in in SQL Server 2008. This copies all the tables and their content ...
4
votes
2answers
3k views

Linked servers login failure: Impersonate works, Username/password fails

I'm trying to set up a linked server between 2 instances of Sql Server 2008 R2. The login works perfectly when I select the impersonate option. However when I try to use the remote user/password ...
4
votes
1answer
10k views

SQL Server Activity Monitor - Resource Waits - Logging > 1400 ms/sec, how?

I have an SQL Server 2008 R2 server. I open "Activity Monitor", and inside the "Resource Waits" category, I see that the "Logging" category is taking up more than 1400 ms/sec. That doesn't seem to be ...
1
vote
1answer
241 views

How should I distribute my tables for better performance from fragmentation point of view

I have a database on SQL Server 2008 R2 with about 20 non-indexed tables that are continuously undergoing INSERTs AND DELETEs. There are about 400,000 records @ 4KB/record being inserted or deleted ...
1
vote
1answer
658 views

SQL Server 2008 R2 Express with management tools installation failure

I've been trying to install SQL Server 2008 R2 with tools. I've downloaded thisn version and it just won't work! I've had some problems installing Visual Studio SP1 due to silverlight4 being ...
6
votes
1answer
1k views

Reindex Task Failing Randomly [closed]

We use the built-in maintenance plan to reindex (and backup, etc.) our production SQL Server. The problem we are currently having is the reindex task started failing randomly this week after months ...
2
votes
1answer
773 views

Correct SQL Server linked server connection settings to MySQL that will allow inserts

I'm in linked server hell! I've created a linked server to a remote MySQL database. I'm able to query and do updates. However when I attempt an insert I get an error: "[MySQL][ODBC 5.1 ...
4
votes
2answers
502 views

Computed column with less size than the “referring column”

I have a column with data type nvarchar(max) and I wold like to index this. However, since it's too big it's not possible. So I figured I could create a persisted computed column based on that column ...
1
vote
0answers
464 views

Keep original column names in SSRS report designer 3.0?

I have queries returns a lot of (over a hundred) columns which names contain important underscores, >, < and Uppercase/lowercase characters. However, when creating the reports, SSRS designer (3.0) ...
1
vote
1answer
551 views

DATETIME2 and IDENTITY as composite key

I am currently developing a new database schema with an opportunity to do things right. The purpose of the development effort is to collect data (events) from various sensors that will be recorded ...
2
votes
1answer
716 views

Can a clustered index with low cardinality cause deadlocks?

I'm looking at a deadlock, which happens when two processes execute an UPDATE statement on the same table. Both transactions are implicit, meaning that they span only the current statement only, which ...
11
votes
1answer
12k views

Change length of varchar on live prod table

I have a MS SQL Server 2008 R2 DB server currently in use with a production app. A new enhancement to the app now requires a varchar(100) column in a table to be increased in length. Can the ...
0
votes
0answers
574 views

Update Statistics Maintenance Plan - Ambiguous column name error

Microsoft SQL Server 2008 R2 Web Edition (RTM) - 10.50.1600.1 (Intel X86) Windows 2003 R2 SP2 I am in the process of setting up a maintenance plan to update statistics on a daily basis for all user ...
10
votes
1answer
1k views

DBCC CHECKDB firing every 20 - 60 seconds

I have a dev environment that is rapidly approaching deployment into production and have noticed in the logs that roughly every 20 seconds I see the message: Starting up database 'dbname' CHECKDB for ...
2
votes
1answer
205 views

Views - Indexes/Keys/Execution Plans

I am working with a SQL 2005 database server that contains multiple databases and tables that are replicated from various sources. No one but administrators have access to this server. There is a ...
2
votes
1answer
133 views

General strategy for updating a full text search index

I am using a SQL Server 2008 R2 DB with FTS enabled on it. I applied an FTS index on a table. The table is frequently updated (I mean the CUD operations). Keeping this in mind, I came up with a ...
13
votes
3answers
3k views

Can you explain this execution plan?

I was researching something else when I came across this thing. I was generating test tables with some data in it and running different queries to find out how different ways to write queries affects ...
6
votes
3answers
2k views

How to identify columns order in a table

I need to list columns from a table in the table definition order: select * from syscolumns where id = object_id('MyTable') --order by colid By examining syscolumns tables two columns look ...
4
votes
1answer
292 views

SQL Server Storage sql_variant

USE tempdb ; GO DROP TABLE tbl ; GO CREATE TABLE tbl ( i SQL_VARIANT NOT NULL ) ; GO INSERT INTO tbl (i) VALUES (1) ; GO SELECT i FROM tbl ; GO DBCC IND ('tempdb','tbl',-1) ; GO DBCC ...
9
votes
1answer
2k views

Interpreting SQL Server's Showplan XML

I just rolled out a feature on my site http://sqlfiddle.com that allows users to view the raw execution plans for their queries. In the case of PostgreSQL, MySQL, and (to some extent) Oracle, looking ...
34
votes
4answers
12k views

What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?

My company is facing the decision whether to purchase SQL Server 2012 Denali or SQL Server 2008 R2 for a new database server. I am looking for objective reasons to choose one over the other. Our ...
9
votes
2answers
3k views

Restore bak file to smaller mdf and ldf database files

I have a legacy database whose nightmarish lack of design I will not get into here, but the files on the server are (relatively) enormous. I have: MyDatabase.mdf: 24.8GB MyDatabase.ldf: 114.6GB This ...
2
votes
1answer
301 views

Upgrade to SQL Server 2008 R2 Standard with Mirroring (1 Thread Limit)

I am upgrading a mirroring pair of SQL2005 Enterprise servers to SQL2008R2 Standard which has 1 thread for mirroring limitation. As far as I understand this limitation applies to redo queue: ...
3
votes
3answers
3k views

SQL Server R2 and HyperThreading - Max Worker Threads and performance

SQL Im getting this error: This file is generated by Microsoft SQL Server version 10.50.2500.0 ...
6
votes
1answer
116 views

When you back up a database and scripts are running at the same time, at what point in time does the backup reflect?

If I run a SQL Server database backup via Management Studio and there are scripts running at the same time, does the backup reflect the point in time when the backup was started or when it ended? My ...
8
votes
2answers
6k views

Table not getting created under dbo schema

When creating tables in SSMS, I've realized that if you execute the following statement: CREATE TABLE [tableName]; the table will be created under your own schema (and not dbo). So in order to ...
4
votes
2answers
3k views

What server property makes TransactionScope escalating to MSDTC?

In our application we're using TransactionScope's. We're aiming to not use the MSDTC service because it's a lot slower than lightweight transactions. using (var transactionScope = new ...
1
vote
1answer
52 views

A few questions regarding Sql Server Utility

I just stumbled across the Sql Server Utility (don't ask) and was just wondering what the overhead of the collect and upload process is like? How does this compare with other monitoring/health tools ...
3
votes
2answers
1k views

Is this procedure to Failover to Logshipped secondary and Fail back again procedure correct?

I have a primary database in Amsterdam (AMDB), and a secondary in London (LNDB). I want to fail over from AMDB, to LNDB and then back again, without having to make another full backup (because the ...
2
votes
2answers
3k views

Deadlock on Insert/Update

Using SQL Server 2008 R2: I'm currently attempting to track down the cause of a deadlock issue we have. Not sure where to turn. Here's the setup. TABLE Scores: id INT IDENTITY(1,1), first_name ...
11
votes
4answers
8k views

SQL Server database on an SSD - any advantage to a separate file for every table?

I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key ...
2
votes
1answer
183 views

How does SQL Server control which users get automatically added to a new database?

I re-create my Dev Database with a Visual Studio DB Project twice a week. Today I fixed my permissions to be Active Directory group based rather than giving individuals permissions. After I re-ran ...
5
votes
2answers
4k views

How to Determine the input and output values of stored procedures?

I have hundreds of SPs and I would like to find out: input parameters with type output fields with type (not output parameters) Of course I could manually go through each one and write it down but ...
6
votes
3answers
2k views

Simple SQL queries not finishing

I don't quite know how to formulate the question because we don't know much yet, but I would like to ask sooner than later because this looks like something that should not be neglected. This week we ...
1
vote
1answer
637 views

Why is my maintenance plan save overwriting job ownership?

I'm slowly untying all the system dependencies to the previous DBA's system account and moving them to a service account. The old DBA had ownership of all the jobs in our maintenance plan. In SSMS I ...
5
votes
1answer
1k views

How can dm_db_index_usage_stats show 0 for user_seeks and user scans but a large number for user_lookups?

I'm probably not understanding something but here is the question. I have done a query on dm_db_index_usage_stats. I've noticed that a couple of my indexes have large numbers (6.5 million and 20 ...
0
votes
0answers
31 views

Fix missing performance counters [duplicate]

Possible Duplicate: How to revive SQL Server 2008R2 performance counters? On one machine, I have an instance of SQL Server 2008R2 Enterprise, Visual Studio 2010 and VS 11 Developer Preview. ...
0
votes
1answer
5k views

Remove an instance of “SQL Server 2008 R2” after reinstalling “SQL Server 2008”

I have installed "SQL Server 2008 R2" with an instance name "SQLEXPRESS". Then I found out it cannot restore from my older "SQL server 2008" backup files . So I uninstalled SQL Server 2008 R2 and ...
1
vote
2answers
628 views

Email notifications in SQL Server 2008

I have set up 2 profiles. I can send a test email ok to one profile. I have set up 2 operators. I have a simple job (select coount(*)) which when it completes should send an email notification to the ...
1
vote
2answers
181 views

Is it possible to have indexes for like '%*%' type queries

Now for my thesis project i need to make a lot of queries like the below select * from tblMyTable where ItemName like '%item%' Now my question : is that possible to create indexes for such queries ...
7
votes
1answer
883 views

Is there a way to force Deferred Name Resolution even if the table exists when creating a sproc?

When creating a Stored Procedure in SQL Server you are allowed to refer to tables which do not exist. But, if the table does exist then any column you refer to in the procedure must exist in that ...