This tag is for SQL Server 2008, major build version 10.00.xxxx.

learn more… | top users | synonyms (2)

5
votes
1answer
2k views

Why does it take so long to calculate an Execution Plan?

One of our customers has just upgraded to a new server. For a particular stored procedure the first time you execute it, is taking over three minutes to run. Subsequent runs are less than 1 second. ...
5
votes
3answers
838 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. ...
5
votes
2answers
5k views

What is the best practice for storing image varbinary(max) data in a database table?

I have a table that stores images that range in size between 16-100 KB each. Since the images are so small, I've taken Microsoft's advice and not used the FILESTREAM data type. The table is ...
4
votes
3answers
165 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
2answers
4k views

Central stored procedure to execute in calling database context

I am working on a customized maintenance solution using the sys.dm_db_index_physical_stats view. I currently have it being referenced from a stored procedure. Now when that stored procedure runs on ...
4
votes
1answer
612 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
1k 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
557 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
27k 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
2answers
8k views

Automatically restart SQL Server 2008

Is there a "best" way to automatically restart SQL Server on a regular basis? I read that I could create a batch file to net start, net stop the agent and service in a batch file and schedule that, ...
4
votes
1answer
1k views

Why can't I read my transaction log backup file using fn_dump_dblog?

I'm using this blog on sqlskills.com as a guide to practice finding the relevant LSN in a transaction log backup for point in time recovery. After backing up the transaction log, I attempt to read it ...
4
votes
1answer
2k views

need suggestions to improve view performance

I am looking to improve the performance of a view in SQL Server 2008. This view exists in a reporting database that is widely used by not-very-technical-folk to basically denormalize all of these ...
4
votes
2answers
837 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
1k 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
2answers
198 views

Error: Cannot Generate SSPI context

When someone tries to connect to a SQL Server instance, the error shows up: It's not possible do generate SSPI context. Yesterday we had a blackout (don't know how to say this expression in ...
3
votes
4answers
4k 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
1answer
4k 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 ...
2
votes
1answer
2k views

Why dropping all foreign keys from a script is not working? [closed]

I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import. So I execute the below code with no ...
2
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 ...
2
votes
1answer
617 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
2k 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
451 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. ...
2
votes
1answer
882 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
480 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 ...
2
votes
1answer
694 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 ...
1
vote
2answers
2k views

How to find memory usage by table? [duplicate]

I want to discover how much memory is being used by each table. Is this information available in DMVs or elsewhere?
1
vote
2answers
572 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 ...
1
vote
3answers
1k views

sql server 2008 - controlling size of log file

I am getting this error. Msg 9002, Level 17, State 4, Line 2 The transaction log for database 'SalesDWH' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc ...
0
votes
0answers
299 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
5answers
6k 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 ...
-4
votes
3answers
2k 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
1answer
785 views

Slot Array and Total Page Size

I continue to read in many forums and on many blogs that a page is comprised as shown below: Page Size: 16 x 512B = 8192B Page Header: = 96B Maximum In_Row Row: = 8060B This leaves ...
9
votes
3answers
2k 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 ...
8
votes
6answers
7k 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
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
1answer
3k views

noexpand hint for non-enterpise edition and performance

I have to use Indexed views to reach performance. As I can see from this comparison table standard edition does not support indexed views. But BOL says: Indexed views can be created in any ...
7
votes
1answer
15k 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 ...
7
votes
1answer
4k views

SSIS 2008 Package Configurations being ignored

With the change to package configurations in 2008 compared to 2005 when I specify /ConfigFile something.dtsConfig on the command line, variables defined in the package are keeping their design-time ...
7
votes
1answer
619 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

Do indexes consume memory?

I am just starting to learn about memory usage on SQL Server. When using the query in the answer to the question SQL Server 2008 R2 "Ghost Memory"?, I discovered that a single database is ...
7
votes
1answer
9k 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 ...
6
votes
3answers
10k views

sleeping SPID blocking other transactions

I'm really having trouble tracking down some blocking we are experiencing. The root blocking SPID's status is 'sleeping', the cmd is 'AWAITING COMMAND', and the sqltext is 'SET TRANSACTION ISOLATION ...
6
votes
2answers
7k 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 & one log file. They are all located on the same dedicated drive. When I right click on tempdb in SQL Server Management ...
6
votes
4answers
3k views

Checking if 2 tables ( on different servers ) have the same exact data

So it seems that the company who is hosting our SQL Servers has been having some trouble with the replication from the production server to the backup server... I believe some of the tables have been ...
6
votes
1answer
583 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
5answers
6k 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 ...
6
votes
2answers
550 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 ...