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

learn more… | top users | synonyms (2)

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
669 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
40k views

IF NOT NULL then UPDATE else keep the value of the field

I think I am somehow close to get it work, but for some reason I still get errors. I have the following UPDATE query: UPDATE DeviceAttribute SET Details = CASE Name WHEN 'Accessories' THEN ...
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
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 ...
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
131 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
4answers
8k views

SQL Server 2008: how much RAM memory should SQL Server use in a 8GB RAM server?

We have SQL Server 2008 and 8GB RAM in a customer, hosted in a Windows 2008 R2 SP1, and consider the following amount of free memory: and consider the following running programs: Scenario: ...
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
35k 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
547 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
952 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
752 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
966 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
566 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
2answers
657 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
790 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
80 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
926 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 ...
10
votes
3answers
3k views

How to change the firing order of Triggers?

Realy I rarely use triggers. So I met a problem at first time. I have a lot of tables with triggers (2 or more for every table). I would like to know and change the order of firing triggers for every ...
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
10k 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
20k views

High Buffer I/O SQL Server 2008 R2 causes?

Am trying to troubleshoot performance on an SQL server that we have for our document management server and today performance has hit an all time low. Looking at the MDW I setup recently I can see a ...
8
votes
5answers
8k 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
707 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
575 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
1answer
663 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
634 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
747 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
10k 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:- ...
6
votes
2answers
660 views

SQL Server configuration / specification recommendations and advice!

I am about to deploy a new server at work as follows: Dell T610 2 x Xeon 2.53 Quad core 24 GB RAM 1 x Perc H700 RAID controller with 1GB Non Volatile memory 8 x 300GB 15k SAS Hard drives I am looking ...
5
votes
2answers
139 views

Can sp_executesql be configured / used by default?

I am looking at an application that uses highly dynamic sql queries against SQL Server. Looking at the queries that are constructed in very weird and complicated ways, but that's a different story, I ...
5
votes
3answers
703 views

SQL Server- Table partitions on SSD

I have a table in my database that has about 250m rows. I'm new with databases but I have read up about partitioning the table to increase performance. One of the important points that I seem to ...
5
votes
3answers
9k views

Quickly change NULL column to NOT NULL

I have a table with millions of rows and a column that allows NULL values. However no row currently has a NULL value for that column (I can verify this fairly quickly with a query). However when I ...