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

learn more… | top users | synonyms (2)

2
votes
2answers
748 views

parallelism repartitions, ordering, and hash matches

I am just starting to delve into query plans and recently learned that "hash matches" are bad. I know it's rare for something to be so absolute, though, and I want to determine if I have a bad hash ...
1
vote
1answer
192 views

Impact of miss-ordered fields in a composite, clustered primary key?

I have tables where the primary key is something like this: -StoreID -BusinessDate Typically, there is one row for every store (~100) for every single day. One row for each store is inserted on a ...
0
votes
1answer
7k views

sql server - could not allocate space for object

I am trying to retrieve the first 1000 records of my view and am getting this error message: Msg 1105, Level 17, State 2, Line 2 Could not allocate space for object 'dbo.SORT temporary run ...
9
votes
6answers
4k views

Tables with hierarchy: create a constraint to prevent circularity through foreign keys

Suppose we have a table that has a foreign key constraint to itself, like such: CREATE TABLE Foo (FooId BIGINT PRIMARY KEY, ParentFooId BIGINT, FOREIGN KEY([ParentFooId]) REFERENCES ...
4
votes
2answers
994 views

Performance degradation after rebuilding indexes

I am optimizing some server application task that uses database (querying, complex calculations, data insertions...). Execution of this task spends about 16 minutes (I have tested it 3 more times) and ...
6
votes
5answers
47k views

How to install SQL Server 2008 R2 Profiler [duplicate]

SQL Server Management Studio doesn't have it on the Tools menu in my case, as is reported in some blogs and forums. Apparently you somehow either got it or not, but how did you get it, if you did, is ...
4
votes
1answer
655 views

How to change database owner for login and database created inside stored procedure?

I have a signed stored procedure that creates a new database, a new login and assigns the new user to be the owner of the new database. Well my SProc creates the new login and new database but will ...
3
votes
3answers
916 views

Database growing like crazy - what do I do?

I have an Epicor 9 ERP database that I recently was put in charge of (new job). Server is running Server 2008 R2 with SQL Server 2008 R2 64bit. The main production database started at 16gb 7 months ...
4
votes
2answers
3k views

Get failed SQL Server agent job?

How to get a list of failed job run last night? I only find the following powershell script. What's the SQL equivalence? dir $psPath\Jobs | % { $_.EnumHistory() } | ? { $_.RunStatus -ne 1 }
1
vote
2answers
2k views

Should I put the error handler inside the loop, or the loop inside the error handler?

When a procedure is looping over a statement that could raise an error condition, should I put the loop inside the error handler, or put the error handler inside the loop? I want the procedure to run ...
2
votes
1answer
2k views

Activity Monitor Showing Long Duration for WAITFOR

Using Activity Monitor, I am consistently seeing the following query showing an average duration of over 300 seconds: WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, ...
2
votes
2answers
8k views

How to lock a Row while I'm using it

I want to be able to Lock a row, select it, increment its value, and then release the lock. (without lockin the other rows, so that other connections can work with the rest of the table) I've found ...
1
vote
2answers
704 views

Create multiple files for each file group for table partitioning?

I am partitioning a table by date (month). The server has four drives. The partition scheme is defined as CREATE PARTITION SCHEME ByDate AS PARTITION ByDate TO ( [PRIMARY], [fg2], [fg3], [fg4], ...
2
votes
2answers
351 views

Found errors in table after executing DBCC CHECKTABLE command

I am trying to alter table to add primary key ALTER TABLE GeoCity ADD CONSTRAINT PK_GeoCity PRIMARY KEY(locId) and got this error message Msg 1750, Level 16, State 0, Line 1 then I run ...
1
vote
1answer
83 views

Where and with what should I be looking for a good hardware backup tool? [closed]

Until now, I was doing my SQL Backups using USB Hard disk, and copying that to my local computer. After that, I went zipping and burning onto DVDs. Now I want to push that on more professional and ...
0
votes
1answer
733 views

How to load more than one MS-Access-db tables with data in SQL-Server-DB

I'm just getting into SQL-server 2008 R2 ssis, so forgive me if this is a basic question. I'm building an application using integration service. One of my computer folders contains more than one ...
5
votes
2answers
556 views

Is there a setting to force an error when asigning a varchar longer than a variable?

I would like to get an error when assigning a longer varchar to a variable and no silent truncation. Are there any settings? This truncates silently: declare @code varchar(10) set @code = 'This is ...
5
votes
1answer
1k views

Using MSDTC in SQL Server 2008

I used Hyper-v to install one windows 2008 server machine, and copy the image to create another one and change the computer name, both of them are in the same domain, and I use a domain user as ...
0
votes
3answers
213 views

Alternative of replication in internet disconnection period of time [closed]

I'm looking for an alternative of merge replication that work without internet connection, something like red gate sql data compare but not in uniq direction.
11
votes
5answers
8k views

What's a good tool for visualizing SQL Agent jobs? [closed]

I find the built-in MS SQL Studio tools for managing Agent jobs to be a little frustrating. What tools out there have you found helpful? Offhand, there are three things I'd like to see in such a ...
1
vote
1answer
80 views

Is it safe to change the name of a database?

I have a SQL Server database which has data for several customers/devices. We are changing the prefix of our website (asp.net) and our application dynamically loads the proper connection string based ...
3
votes
2answers
3k views

What is the difference between a SQL Login and a Database User in terms of security?

I am currently working with a consulting agency to set up a new production environment. They have set up a separate SQL login for each database. However, there is no corresponding database user ...
4
votes
1answer
89 views

Issue with CAST using linked servers (possible bug?)

So I've got a some SQL Server 2008 DBs arranged in a star schema. Server A is the one users submit queries to. On Server A there is a DPV called Detection that's basically a UNION of table Detection ...
2
votes
2answers
12k views

SSIS SQL Server 2008 errors

I just want to know how to import data from source to SQL Server 2008 database using SSIS. I have a simple txt file (actually it's from this blog : ...
0
votes
1answer
47 views

Copying a subscribed database to the publisher server

I have two servers. I have installed a merge replication in the publisher (Server A) and I have added two subscriptions with the same database name. - One on the publisher itself and the seconde one ...
2
votes
3answers
4k views

Reference a newly created computed column in the same query where we create it

SELECT (SUBSTRING(Column1,1,3)) AS ComputedColumn WHERE ComputedColumn !="dec" INTO Table2 FROM Table1; This is an example where I have a computed column (which is essentially created by using an ...
2
votes
2answers
6k 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 ...
13
votes
4answers
10k 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 ...
1
vote
1answer
544 views

Database Tuning Advisor can't find tables across two databases

I've got a Python script which accesses two SQLServer 2008 databases. If I run it and take a trace to capture all events that occur as a result of the script, and then put that trace through the ...
2
votes
1answer
425 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
3answers
2k views

After upgrade of SQL Server 2000 database to SQL Server 2008 the view is not sorted on the group by column

I have two tables Location Master ([LocatioNo],[LocationName],[Description]) Location Data ([LocationNo],[LocationCode],[DDate],[BillNo],[Model],[Quantity],[SNo]) and a view involving the 2 ...
20
votes
2answers
37k views

How to find the SQL statements that caused tempdb growth?

The tempdb of a server (SQL Server 2008) increases to 500GB+ several times every month. Is it possible to find out which SQL statements caused this problem? The problem is usually not caused by create ...
2
votes
1answer
2k views

Error attaching database after deleting NDF file

I have deleted .ndf file by mistake from a SQL Server 2008 database. While trying to attach a previous copy, SSMS raises this error: Attach database failed for Server 'MDSWIN34'. ...
5
votes
2answers
2k views

SQL Server backup, attach to BAK file?

I have a SQL Server database with a full text index. When I created the app that uses it, I made a backup of the database and I ship the .BAK file with the app. As part of the app install it does a ...
0
votes
1answer
119 views

sql server maintence plans. What account to use?

When setting up a maintenance plan specifically for backups, what account should you use as the owner. Currently we use the domain account that sql server runs as. Is this best practice or should we ...
7
votes
1answer
140 views

Insert triggers to eliminate sensitive information

A vendor system (i.e. outside of my control) is inserting a row into a table into a database that I do control. One column in this insert will have sensitive information that I do not want in my ...
6
votes
2answers
3k views

Speed up INSERTs

I need to have a way to insert to a table, fast, synchronous, with minimal duration. What I've tried ("blind") is : Have no index on the table whatsoever Switch to simple logging (from full logging) ...
1
vote
1answer
340 views

sql server 2008 email notifications

I have set up a profile and operator within SQL Server 2008. I also have an operator that is a group in MS Outlook. When I use the single user address as the notification address when a job ...
4
votes
1answer
124 views

Should the host machine always be restarted after upgrading an SQL Server 2008 instance?

My organization recently upgraded all its production SQL Server 2008 instances to Service Pack 3, Cumulative Update 1. It's my responsibility to make sure that our development database environments ...
3
votes
2answers
465 views

Archive a single giant table from a SQL 2008 database - filegroup backup?

I have an extremely large table (400GB data size, inside a 2.5TB database) that is no longer needed. Before we drop it, however, our client contract says we need to archive it to tape in case in needs ...
4
votes
2answers
3k views

How to obtain SIDs of Sql Server installed NT Service Accounts

I am using WMI to obtain members of server local groups, as well as the SIDs of the members. The WQL queries work for members that are local users, local groups, AD domain users and AD domain groups. ...
18
votes
1answer
27k views

Table-Valued Parameter as Output parameter for stored procedure

Is it possibile to Table-Valued parameter be used as output param for stored procedure ? Here is, what I want to do in code /*First I create MY type */ CREATE TYPE typ_test AS TABLE ( id int ...
1
vote
1answer
916 views

Create Index with a WHERE clause

I've read the documentation on the MSDN, but it doesn't answer what I'm after. If I create an index on specified columns using a WHERE clause for certain IDs, this will limit the index to those IDs ...
5
votes
5answers
6k views

User Windows login name has been changed in AD yet session in SQL 2008 Profiler shows the old Windows login name

Background: a user had her name changed in Active Directory from Domain\oldname to Domain\newname and successfully logged into the network with Domain\newname and yet Domain\oldname appears in ...
2
votes
1answer
392 views

Mirroring multiple source databases to a single destination in SQL Server 2008

How can we maintain multiple source databases data in a single destination server database in SQL Server 2008? They all are at different networks, if that makes any difference.
0
votes
1answer
8k 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
1answer
420 views

Design SQL Server 2008 new install … which collation?

A customer has a SQL Server 2005 instance, collation SQL_Latin1_General_CP1_CI_AS, with 8 databases ... for a new application, we have been asked to install a SQL Server 2008 with collation ...
5
votes
1answer
237 views

Does the transaction isolation level affect indices as well?

Here's one for the SQL Server gurus: if I set my SQL Server 2008 transaction isolation level to READ UNCOMMITTED, does that also affect the index pages? E.g. using ISOLATION LEVEL READ UNCOMMITTED, ...
-2
votes
1answer
1k views

Query database by date, month, year

How can I query order by date. Like this: And the same for week, month, year...?
2
votes
1answer
2k views

Save Transaction: Is the name local to the stored procedure or not?

Some foot shooting just occured on a production server. I fixed the problem, but I'm now rather confused. There is a subset of stored procedures that preserve state on failure. That is, if the ...