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

learn more… | top users | synonyms

1
vote
2answers
154 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
720 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
329 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
290 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 ...
3
votes
2answers
5k 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
2k 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
474 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
877 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
161 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
1k 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
75 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: ...
0
votes
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
6k 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
480 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
7k 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 ...
3
votes
1answer
551 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
178 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
701 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
400 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 ...
5
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 ...
3
votes
1answer
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
279 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
10k 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
966 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
371 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
347 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
342 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
83 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
625 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 ...
1
vote
1answer
117 views

Pointers to multiple index allocation mappings

In sql server 2008, the sysindexes table contains a pointer to the first page of the index allocation mapping. If a table consists of multiple IAM pages, will the sysindexes table contain a pointer to ...
0
votes
1answer
1k views

sp_dts_getfolder isn't in my MSDB database, any ideas why?

We're currently moving our applications from a SQL 2005 to a SQL 2008 R2 database and everything has gone well(ish) but now one of our applications calls a stored proc called sp_dts_getfolder which is ...
1
vote
1answer
64 views

Reading and writing the common data

I have a couple tables and application service that intensively inserts, updates its. From the other hand some client application have to read data using those tables. Sometimes client application ...
14
votes
3answers
3k views

Painless way to create a clustered index on a huge table?

So we have a customer site that is complaining about some seriously slow performance. I took one look and it's obvious that the problem is because Somebody Else (grrrr) designed a table holding some ...
16
votes
1answer
7k views

SLEEP_TASK Wait Type in SQL Server - What does it indicate?

I haven't seen the SLEEP_TASK wait type before, and today I seem to be getting a ton of them. I'm not the official DBA, just a SQL Server developer who knows some DBA stuff. We upgraded our servers ...
1
vote
1answer
230 views

SQL Server 2008 Analysis Management objects work with SQL Server 2005?

Can SQL Server 2008 Analysis Management objects API work with SQL Server 2005 install?
1
vote
1answer
494 views

Insert 4 tinyint values into varbinary(4)

I'm working on a function to convert the string representation of an IP into its varbinary(4/16) form. Right now, I have this: @stringIP = '192.168.0.3' select CAST(PARSENAME(@stringIP, 4) as ...
4
votes
2answers
616 views

Scrubbing Names via SQL Query/Batch

We have sensitive information (names of people) and we're looking to put into place a script to "scrub" of the names. Has anybody come up with a good algorithm to do this, something other than just ...
0
votes
1answer
262 views

Why is SQL Server 2008 R2 Management Studio making me login again?

I login to SQL Server 2008 management studio each morning, it works all day just fine. When I come in the next morning and try to run a query, it asks me to login again. Where is the setting defined ...
2
votes
2answers
751 views

Running a number of .sql files using SQLCMD

I'm trying to put together a batch file to run a large number of .sql files without doing so individually. So far I have: @echo off echo "Enter Server" set /p SERVER= echo "Enter Database" set /p ...
12
votes
7answers
1k views

Version control for database objects

The database our developers are working on is too large (have a lot of database objects). We have to control db objects changes (change management). Our company cannot have a person who would be ...
2
votes
2answers
1k views

SET LOCK_TIMEOUT, is it session or statement based?

For what period does the LOCK_TIMEOUT LAST? I do a "SET LOCK_TIMEOUT 10" and a "SELECT @@LOCK_TIMEOUT" in one command after a login and get 10 returned. Immediately after this I do a "SELECT ...
3
votes
2answers
279 views

Is it important to release memory back to the server?

I have a SQL Server 2008 R2 instance running on a production system. I'm not a DBA; I'm more a developer, but my client is currently asking me: How important is it to release memory from SQL Server ...
0
votes
1answer
779 views

SQL Server 2008 R2 SQL Server Profiler and “exec sp_reset_connection”

Yesterday i ran the "SQL Server Profiler" tool and outputted the data to a table, now when i look in it i find many "exec sp_reset_connection" rows and thats expected with our data access layer! But ...