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

learn more… | top users | synonyms (2)

5
votes
3answers
2k views

How can I write a proper query to JOIN all of these tables without duplicates?

Let's say I have four tables: TABLE: players COLUMNS: id, first_name, last_name TABLE: passing_stats COLUMNS: id, year, passing_yards (several other passing columns) TABLE: rushing_stats ...
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 ...
5
votes
3answers
977 views

Using SQL Server on separate networks

I have a bunch of SQL Servers installed on Windows2008R2. Currently, each windows server has two NIC's, each connecting to separate networks. In other words, each server appears on each network. ...
4
votes
1answer
539 views

How can a blocking_session_id be negative?

I have a case where a query I'm running is taking a long time. When I checked sys.dm_exec_requests the blocking_session_id is a negative value, -2 to be specific. The wait is a LCK_M_X and the ...
4
votes
3answers
192 views

How do you see which database on a Server uses most resources

I have a database server with a couple of databases on it. How can I see where any resource pressure may come from? I would like to get a table of: Database Name Batch Requests per second CPU ...
4
votes
1answer
723 views

Indexes file damaged, was on ramdisk

I have put some of my indexes into a filegroup that contains one file, that file is on the ramdisk, the performance is great, but the problem is that the file was deleted incorrectly (the file ...
4
votes
2answers
2k views

maintaining full text index on large table

I'm on SQL Server 2008 and have a table, for reporting purposes, with 500,000 records that will easily reach the millions. The table will employ a full text index for rapid searching on a handful of ...
4
votes
1answer
675 views

Shared locks taken under Read Committed Snapshot Isolation (RCSI)

I am seeing waits for shared locks (LCK_M_S) when using RCSI. My understanding is that this isn't supposed to happen since SELECTs do not require shared locks when using RCSI. How can I be seeing ...
4
votes
1answer
6k views

How to create a partitioned table based on date?

I'm trying to create a partitioned table where partitioning is determined by OrderDate. I'm trying to create a clustered index on OrderDate and a nonclustered primary key on OrderID. But I get an ...
4
votes
2answers
1k views

SQL Server Dynamic Memory management

I am reading a book on SQL 2008 internals and troubleshooting by Christian Bolton, Brent Ozar and a old book written by late Ken Henderson and I am getting confused and curious about SQL server's ...
4
votes
3answers
6k views

Stored procedure executes too long

We use SQL Server 2008. When I am watching in SQL Server Profiler I found some queries that take for example 20 seconds. The same query (in a stored procedure) when executed in SQL Server Management ...
3
votes
3answers
148 views

How can I make a database backup go faster?

I recently attended an interview for my next job and was asked this: How can I speed up the backup of a database sized close to 1TB or 800 GB in SQL Server? My Answer: We can use Database ...
3
votes
2answers
2k views

Shrinking a SQL Server Log File Multiple Times

Recently ran into a situation where after a log backup and shrinkfile, the LDF file remained the same size. DBCC LOGINFO clearly showed only one active VLF and SSMS said that 99% of the space in the ...
3
votes
1answer
36k views

How do I add minutes to a time data type?

I have a stored procedure which inserts two records into a table, the difference between the records is that the time column of the second record is @MinToAdd after the first: CREATE PROCEDURE ...
3
votes
1answer
5k views

Security permission to only read SSAS Cube with Excel

I have an SSAS database set up on a SQL Server 2008 server. I have a Role set up with a group of users that I want to be able to access the Cubes in that database. I set up "Read definition" and ...
3
votes
1answer
555 views

Is staggering reindexing jobs a good strategy?

I have inherited a server with 35 databases and a job that reindexes all tables on all databases (using sp_MSForEachTable). It's taking over 14 hours to complete and is now blocking other processes. ...
3
votes
1answer
981 views

What is the best practice for backing up a database in Simple mode?

I am trying to create a SQL Job for backing up my database. I want to have: A weekly full backup A daily differential backup Something that will clean up backups that are older that 14 days I ...
2
votes
5answers
9k views

Copy a SQL Server database from one server to another on demand

I have two servers, Prod and Test, both running SQL Server 2008 RTM. Is it possible to have a PowerShell- or VBScript that could copy a database from one server to another? The copy should of course ...
2
votes
3answers
1k views

Is transaction log lost after backup on SQL Server?

I have database that has regular nightly backup (full recovery, full backup). I noticed some irregularities in data and wanted to check out transaction log to see what happened. I restored earlier ...
2
votes
1answer
780 views

Prevent users from using power pivot excel connections to a database

A couple of weeks ago my boss connected to our production database via excel and was able to query the database. Because of how we're doing security to the database (AD groups) he should have access ...
2
votes
2answers
3k views

how to use database mail from SSIS

I try to use gmail in database mail. I have no problem to send out email from it. But how I can configure my send mail task to send out email by using database mail. Thanks
2
votes
1answer
2k views

Performance Inserting and Updating Millions of rows into a table

We have a CSV the user imports into the Application. After inspecting the CSV, validating and applying some business logic, we got Inserts and Updates operations we should perform into a ...
2
votes
3answers
1k views

Changing a column's data type fills the transaction log

Running this code: ALTER TABLE npidata ALTER COLUMN npi varchar(20) Gives this error: Msg 9002, Level 17, State 4, Line 2 The transaction log for database 'SalesDWH' is full. To find out why ...
2
votes
1answer
968 views

How to Backup and Restore Filestream Data

I am trying to backup and restore my database which includes a filestream filegroup. I backed up the database without the filestream filegroup and was interested to know what happens when you restore ...
2
votes
1answer
574 views

SQL Server 08: Union over while

I've done some reading on this, and I keep seeing people say it's a bad idea, but hear me out! I have a stored procedure which needs to find a series of timeslots over a number of days. Due to the ...
1
vote
1answer
123 views

how to get History of queries executed with username in SQL

I can get executed queries by using: SELECT deqs.last_execution_time AS [Time] ,dest.TEXT AS [Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ...
1
vote
2answers
670 views

Can I send a string over TCP using T-SQL?

I've set up some alerts, based on this article Now I would like the Job that is triggered by the alert to send some information to a c# application (this application, can be running in the same ...
0
votes
0answers
834 views

Two instances sharing same system database?

I have a situation that I encountered with one of my clients, that I have never seen before. They are running SQL Server Express 2008 R2. There are two instances of SQL Server installed - a default ...
0
votes
2answers
81 views

Is there a way to do a textual search through all stored procedures in a table?

I've just inherited a database with a number of tables and stored procedures. I use Microsoft SQL Server Management Studio. One, or possibly more, stored procedures update a specific table. If the ...
0
votes
2answers
939 views

migrate SQL Server geography data to PostGIS geometry data

I'm trying to use SSIS to transfer point data from a Geography column in SQL Server 2008 R2 to a geometry column in a Greenplum (Postgres 8.2.15) database with PostGIS. I know there's a way to do the ...
-5
votes
3answers
3k views

How database administrators can see my requests to SQL Server?

I'm a SQL Server 2008 user. I have access to some tables. I need to request few columns from table as I usually do. But I need to do it once (for example) in 5 seconds and system administrators ...
9
votes
2answers
2k views

How does using separate schemas affect SQL Server 2008 performance?

I want to use separate schemas for objects with different purposes in our SQL Server 2008 database. Right now we use a fairly mind-numbing naming convention to indicate the purpose of a table or ...
8
votes
6answers
11k views

SQL Server in a state of confusion: does the database exist or doesn't it?

Got a really weird, annoying problem.. Somehow the instance of SQL Server 2008 R2 running on our server has gotten somewhat corrupted. First, we noticed that the database we created yesterday was ...
8
votes
1answer
21k views

SQL Server 2008 R2 high Buffer I/O causes

Am trying to troubleshoot performance on a SQL Server that we have for our document management server and today performance has hit an all time low. Looking at the Management Data Warehouse (MDW) I ...
8
votes
5answers
9k views

Does SQL profiler affect server performance?

I am facing a problem where the sql server 2008 fails for some high load. I need to find that load case and need to optimize the code so that it can handle the load. I have found on the internet that ...
8
votes
1answer
11k views

Breaking and Restoring a mirror

I have to do some maintenance on a secondary node of a mirror. Since this is the result of the site becoming slow while the mirror re-established after a brief network outage I want to be safe and ...
7
votes
3answers
8k views

SQL Server 2008 R2 SP1 - Tempdb “Available free space” showing negative value

Please help as this issue is driving me nuts... Tempdb has four data files and one log file. They are all located on the same dedicated drive. When I right click on tempdb in SQL Server Management ...
7
votes
1answer
716 views

CATCH block is triggered when it should not

On my neverending quest for shooting myself in the foot with save transaction I seem to have found even more ways to fulfill the quest purpose. The save transaction clause itself this time is out of ...
7
votes
3answers
2k views

Dealing with CXPACKET waits - setting cost threshold for parallelism

As a follow-up to my previous question on perf troubleshooting a Sharepoint site, I was wondering if I could do something about the CXPACKET waits. I know the knee-jerk solution is to turn off all ...
7
votes
2answers
2k views

Database locking issues?

I have run into a transaction related issue on a SQL Server 2008 production database. A brief overview is that we have a website that has numerous concurrent users around the state, who do GUI type ...
7
votes
2answers
1k views

Using totals on aggregates to improve performance

I have two tables: details and totals of these details. Details (Slow solution): select OrderId = r.OrderId , TotalQty = SUM(r.Quantity) , ...
6
votes
3answers
3k views

Cannot rebuild index, but there's no reason why not?

I've created a process whereby I am able to only rebuild indexes that need rebuilding(the process takes an hour and a half if I rebuild them all), and while it works beautifully, it gets stuck on one ...
6
votes
1answer
595 views

Why am I missing some output?

Archiving to avoid ID Exhaustion We are about to run out of IDs in one of the tables of our OLTP logging system. It's my job to find a way to 'archive' the existing row data in the full table and in ...
6
votes
3answers
31k views

“lock request time out period exceeded” Error When Trying to See DB Hierarchies

I'm having problems with a database. I can run basic queries, albeit much slower than normal. When I attempt to view the hierarchy trees for tables, views, or procedures in SSMS Object Explorer, I ...
6
votes
1answer
674 views

Attempting to tune a Sharepoint site using SQL Server Waits and Queues

I'm pretty new to this performance tuning with Waits and Queues thing - fascinating, but also not always all that intuitive... Right now, a customer of mine has a SQL Server 2008 64-bit Enterprise ...
6
votes
1answer
1k views

How to get online the database which has missing/corrupted FILESTREAM files?

I've corrupted the files related to FILESTREAM in a database. The .mdf and .ldf files are still intact, but when I try to get the database online, it complains, saying that the files related to ...
6
votes
2answers
641 views

Why select max partitioned column is much slower than indexed one?

I have a big partitioned table (300+M rows, 70 active partitions). The table has clustered PK of (K0, K1) and it's partitioned on column K1. And K1 has very low selectivity. However the following ...
6
votes
2answers
4k views

SQL Server Profiler is available for SQL Server 2008 Dev Edition?

I just upgrade from SQL Server 2008 Express with advance services to Dev Edition. I wonder whether SQL Profiler is available for the Dev Edition? Please let me know how to access that if it is ...
6
votes
2answers
785 views

How can I run multiple concurrent versions of my application against the same database?

Background: I have multiple concurrent versions of my application running in my production environment. Depending on the user account that's used, the user will have access to a different version of ...
6
votes
2answers
11k views

How can I backup & restore a single FILEGROUP in Sql Server 2008

Previously, on ServerFault I asked a question about backing up and restoring a Sql Server 2008 Filegroup. Today, when I tried to RESTORE one of these FILEGROUP backups, I got the following error:- ...