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

learn more… | top users | synonyms

8
votes
2answers
5k 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
7k 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
156 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
3k 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
599 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
614 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
164 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
805 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 ...
7
votes
3answers
366 views

Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server

On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: ...
1
vote
2answers
296 views

Installing SQL Server 2008 R2 Service Pack 1

I have SQL Server 2008 R2 installed on my computer. I want to install Service Pack 1 for SQL Server 2008 R2. Do I need to select upgrade option or new installation during installation of service pack ...
4
votes
2answers
6k views

Get Job id OR Job name from within executing job

I have a central DB that I write job outcomes on all my servers back to. I pass 3 parameters via powershell in a SQL job back to a sp on the central server that verifies the job should be running at ...
6
votes
2answers
3k views

What Problems Does an Eager Spool Indicate

Currently running on SQL Server 2008 R2 I am attempting to increase performance of an UPDATE statement. I notice an Eager Spool operation in the showplan popping up. My understanding of spooling ...
1
vote
1answer
96 views

Trying to figure out when a table was dropped? [duplicate]

Possible Duplicate: Determining how a schema change occurred? Using SQLServer 2008, I see in my schema a particular table was dropped. I am trying to figure out when / why etc? Is there ...
3
votes
1answer
481 views

SQL Server 2008 R2 Express - Runs at an average of 2-3% CPU, but gets stuck at 100% sometimes

Edit: Skip to the last paragraph for the summary-version! I am dealing with a SQL Server 2008 R2 Express installation. I have a fairly complex stored procedure. Depending upon the inputs, it may ...
3
votes
4answers
920 views

SQL Server 2008 R2 Standard with SSIS Enterprise

I currently have SQL server 2008 R2 Standard installed with SSIS. I need a SSIS feature that is only available with the enterprise edition. Is it possible to upgrade SSIS only to enterprise while ...
-1
votes
3answers
165 views

SQL Server - Transaction Slowness

Using: SQL Server 2008 R2 I'm seeing some behavior right now in SQL Server, and I can't decide if the server is underperforming or not. I've set up a test to insert an integer into a table, as ...
2
votes
1answer
1k views

SQL Server Maintenance Plan Fails to Rebuild the Indexes

When I run a maintenance plan to reorganize and rebuild tables indexes, it fails. Below the last two rows that displays the fail: Source: Reorganize Index Executing query "ALTER INDEX ...
7
votes
2answers
2k views

Msg 2534 : DBCC CHECKDB allocation errors

We recently moved our intranet production database from an SQL Server 2008 to an SQL Server 2008 R2 brand new server. The backup Job have been failing since migrating, due to DBCC Errors (Check ...
1
vote
1answer
78 views

Where has sysindex.rows gone?

In SqlServer 2000, there used to be a table called sysindex which had a column rows. This table is now: -sys.indexes -sys.partitions -sys.allocation_units -sys.dm_db_partition_stats (see: ...
1
vote
1answer
1k views

Output into temporary table

I am trying to create a procedure that will generate an xml document of a given table whilst updating the information in said table. My procedure does not recognise the use of the temporary table ...
6
votes
1answer
7k views

Is there a shorthand way to 'Auto_Fix' all Orphaned Users in an SQL Server 2008 R2 database?

It's fairly straight forward to fix up a single orphaned SQL user to a login using: EXEC sp_change_users_login 'Auto_Fix', 'user' I could script this, but is there an existing sproc which ...
0
votes
1answer
555 views

SET keyword in SQL Server

I have been using SET keyword to assign the values to parameters declared in SQL Server something like, Declare @StudentName Nvarchar(max) SET @StudentName='ABC' I'm not sure how to write this if ...
6
votes
3answers
8k views

Add article to transactional publication without generating new snapshot

Using SQL 2008 R2 transactional replication with pull subscribers, when we add an article, I'd like to avoid having to create an entire snapshot (the db is ~80 GB, so this takes hours). From this ...
4
votes
2answers
1k views

How do I rescue SQL Agent jobs from a set of detached master, model, mdb files?

I had a MSSQL (MSSQL 2008 R2) cluster that failed in a way that I couldn't bring it back up (long story, servers ended up in BSOD loops, I'm blaming Broadcom and myself). I've SQL backups, and even ...
4
votes
1answer
639 views

How does objectproperty() figure out if a function is deterministic in sql server?

How does sql server determine if a function is deterministic or not? Consider the following function, I think it's deterministic (as it's ultimately an integer operation), but sql server returns 0 ...
2
votes
1answer
203 views

Is there a way to view the body / contents of a system function in sql server 2008

For the sake of leaning and exploring, I'd like to look at how some of the system functions are built. Is there a way to do this in sql server 2008 or R2? I've tried sp_helptext 'Sum' but I get an ...
5
votes
2answers
754 views

Restrict SQL Server 2008 R2 User to restore only one database (but not others)

It is my understanding that a user has to be a "dbcreator" in order to restore databases. However, what if I want a specific user to restore only one specific database, but not gain access or restore ...
1
vote
2answers
429 views

Find databases with no recent activity

Is there some SQL that I can use in order to find out which databases on the Server haven't been used recently? I need to find the ones that are no longer used and detach them.
6
votes
2answers
3k views

Does SQL Server 2008R2 Express actually enforce the memory and CPU limit?

I've been testing SQL Server 2008R2 Express to see if it is limited to 1GB of RAM and 1 CPU. It certainly seems to be using more than one CPU, as I see spikes on all 4 CPUs in Task Manager when I ...
6
votes
1answer
2k views

How much space is one row taking up

I need to find out how much disk space a single row is taking up in a table. I can then use this as an approximate to work out how much disk space I can recover by removing old unneeded data. Would ...
4
votes
2answers
2k views

Extracting a field from RESTORE HEADERONLY in sql server

I'm trying to use 'RESTORE HEADERONLY' to get the date when the backup I'm about to restore was made. The command: RESTORE HEADERONLY FROM DISK = '<path to .bak file>' works fine in query ...
3
votes
1answer
11k views

SQL Server 2008 R2 There is insufficient system memory in resource pool 'default' to run this query

I have a 32-Bit Windows 2003 Enterprise Edition SP2 Server running SQL Server 2008 R2 (10.50.1617.0) Enterprise Edition. The server is a dedicated SQL Server box for our SharePoint 2007 environment. ...
0
votes
1answer
289 views

Affinity Mask and Affinity I/O Mask

Using: SQL Server 2008 R2 Under what circumstances would you set the Affinity Mask and Affinity I/O Mask options in sp_configure in SQL Server? We are currently running on a machine with 16 cores ...
5
votes
3answers
11k views

SQL Server 2008 R2: Problems after computer name change

I'm having a confusing problem after changing the computer name of a remote server hosting a local SQL Server instance. Basically, a remote server was moved from one site to another. In order to ...
3
votes
1answer
1k views

Clustered Index Update on Nonclustered Columns

Using: SQL Server 2008 R2 I am currently stepping through a query execution plan, and have come across an instance of a clustered index update on a table. The issue here is that the columns that are ...
1
vote
1answer
383 views

Giving selective permissions in a database

I have a user [AD\dbusersgroup_limitedrights] that should have on two schema's [ICD] and [XCD] within a database only SELECT and EXECUTE rights, however for each of the users in the ...
2
votes
2answers
367 views

Using Central Management Server to provide convincing performance statistics

Central Management Server The company I work offers dedicated vmware hosted servers to its Windows clients. Among those Windows clients, there are essentially 60 single instance MSSQL servers ...
0
votes
1answer
2k views

Install SQL Server Express r2 2008 using a configuration file

I need to deploy my application with SQL Server Express. There is a way of installing sql express using a configuration file so that when deploying my application it appears as if I am only installing ...
3
votes
3answers
352 views

Semi-Annual archiving of SQL Server database

Twice a year we create a static, archived subset of our production database. The newly created database contains roughly half of the tables as the production db (log/audit tables, etc. are not copied ...
0
votes
1answer
89 views

Checking for ApplicationName in a transaction through a Before Trigger

I would like to be able to build a trigger that checks the ApplicationName coming across on a transaction, and if the ApplicationName is of a certain value, then I want to throw an error, saying the ...
1
vote
2answers
5k views

Single User mode can't be turned off and no users are connected

I have a database that is in Single User Mode. When I run this: exec sp_dboption 'MyDb', 'single user', 'FALSE' It fails, telling me that there is still a user connected. But when I run both of ...
1
vote
1answer
666 views

T-SQL, show a set of rows (specific column) as a field, for a better desc, please see the table relationship

I have been having a problem for quite a while now, that I can show with a sample of 5 tables (I'm actually dealing with more than 5!): -------------------------------- shipments ...