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

learn more… | top users | synonyms (2)

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 ...
4
votes
1answer
199 views

Truncate log file mirrored database

In SQL Server 2008, I have a mirrored database with a log file that has increased to 80GB, which is not normal and I have serious problem. I would like to decrease the log file size by truncating it. ...
3
votes
1answer
3k views

Concatenate one-to-many field in single result?

Say I have the following query: SELECT * FROM AppDetails, AppTags WHERE AppDetails.AppID = '1' AND AppDetails.AppID = AppTags.AppID Which gives the following results: AppID AppName ...
3
votes
4answers
4k views

SQL Server Min Server Memory

using SQL 2008 R2 with SP2 with a server with 64 GB of RAM. I have set max server memory to 58GB considering SQL Server is limited to only database engine related service only. I have a question ...
3
votes
2answers
7k views

Query (duration) different between data value change

I am trying to write a query that would get me the duration of a status in a table. This query needs to work in SQL Server 2008. Say I have the following table: Key Value RecordDate 1 1 ...
3
votes
2answers
426 views

Migration to databases Domain Users do not have access

I migrated databases to new servers, however the applications that were previously used with the databases are failing to load. I have changed the connections and etc. The jobs also seem to be ...
3
votes
2answers
1k views

Can I get notification when event occur?

SQL Servers has Traces and XEvents. These are used to capture and analyze what is going on with our SQL Server Instances. Events are stored in the stack for later analysis. For example, If I decide ...
3
votes
1answer
1k views

Multi-tenant database using SQL Server 2008?

I am designing a database using a shared schema containing the "security views" to which all logged tenants will be directed. I read the msdn article on Multi-tenant Data Architecture. I understand ...
3
votes
1answer
844 views

Microsoft SQL Server 2008 - Catching user role changes with SQL Server Profiler?

I'm trying to figure out why one of my SQL server users keeps losing sysadmin role when I uninstall a particular program. Can I use the SQL Server Profiler to track down the action that causes this to ...
3
votes
1answer
654 views

Transaction log growing on a mirrored principal

I have a pair of SQL Server 2008 R2 servers hosting a synchronously mirrored database with no witness. This weekend, we had to do some network maintenance so I paused the mirroring. Upon resuming, the ...
3
votes
4answers
3k views

migrating tables from Postgres to SQL Server 2008

(I fear what I wish to accomplish shall not be easy, but a lot of my googling around about it yielded rather dated results, so here it goes...) I have a Postgres 8.4.x database with over 100 tables. ...
3
votes
1answer
10k views

Why TRY CATCH does not suppress exception in trigger

I have a trigger on a table (source) that data should be copied to the other one (target) in other database. I am trying to implement custom synchronization process for data: I want that target ...
3
votes
3answers
5k views

SQL Server: How to call stored procedure without schema name?

SQL Server 2005std/2008web edition db=user=schema=owner='John' I'm moving website from one server to another. This piece of code works well on old server with SQL Server 2005. Dim sqlCmdVehicle ...
3
votes
1answer
600 views

SQL 2008 Server agent cant see perfmon counters

I am trying to create a new SQL server agent alert to capture "SQLServer:General Statistics|Processes Blocked||>|5' but it looks like alert creation GUI is not able to see any perfmon counters as in ...
3
votes
2answers
613 views

Should I create a maintenance plan for my database or use jobs?

I tried creating a Maintenance Plan on my SQL Server 2008 database and was impressed by some of the things that are available out of the box. However, after it was created, it seemed difficult to ...
2
votes
2answers
3k views

High cpu usage on SQL Server

We have a customer server containing SQL Server 2008R2 with about 50 databases. The issue is that the CPU usage reaches 100% (or close to it) and it lasts for a couple hours. This typically happens in ...
2
votes
2answers
5k views

Attach SQL Server 2008 R2 on SQL Server Express 2008

I have a database on SQL Server 2008 R2. I want to attach it on a SQL Server Express 2008 instance running on Windows 2003. I set the SQL server compatibility level on the database to SQL Server ...
1
vote
2answers
85 views

Convert date and time

I have a column with about 100 records in datetime (for example 2004-08-09 12:20:41:000) and I would like to convert all records or update same time just date only with format mm-dd-yyyy. Thank you. ...
1
vote
2answers
62 views

Making a Database Online [closed]

I am so confused with this issue, I have vb.net application and it has a sql database . Now, I want this application to access the sql server from anywhere , because there are many clients sharing ...
1
vote
4answers
154 views

Backup from Server1 and restore it to Server2?

IF we have two Servers installed with SQL Server 2008 instances, Server1 has three drives C, D and E with 100, 500 and 500 GB respectively and Server2 has three drives C, D and E with 100, 300 and ...
1
vote
1answer
622 views

SQL Server agent option in SQL Server Express

I am using SQL Server 2008 Express. I don't have SQL Server Agent option in it. How can I enable it? I have searched on internet, but couldn't find anything. Do I need to install anything?
1
vote
2answers
858 views

sql server-percentage calculation

Sample data : LINE_NO E_FIELD F_FIELD G_FIELD HSA_STATUS FAMILY CACHE_FAMILY Count 23053B 00000 00000 00000 S SUMMIT WER 43 23053B 00000 00000 00000 T ...
1
vote
3answers
3k views

SQL services wont start due to incorrect drive letter after windows restore

SQL services wont start due to incorrect drive letter after windows restore Had a server crash, was successfully able to reinstall windows and restore the server SQL and all the necessary services ...
0
votes
3answers
5k views

How to select the first 10 records for each table in the same database using sql

I have 50+ tables in a database. I don't want to use the following SQL on each table in a database. select * from {table_name} So, is it possible to display the first 10 records for each table ...
-1
votes
1answer
197 views

Validate each parent intermediary is also a parent to itself

I want to validate where each parent intermediary is also a parent to itself. So this query should return invalid parents in SELECT if table has any. I need to do is first of all get all parents ...
-4
votes
2answers
2k views

Microsoft recommends using Rowversion over timestamp , but i can not find “rowversion” data type inside my sql server 2008 R2 [closed]

MS on this link http://msdn.microsoft.com/en-us/library/ms182776.aspx mentioned that we should use rowversion data type over Timestamps. Now inside my sql server 2008 R2 I want to create a column with ...
26
votes
2answers
6k views

Find Queries that Are Causing Waits

Given a certain kind of wait, how do you find which queries are causing those waits safely on a production SQL 2008 R2 Server? In this particular case I am wondering about async_network_io.
22
votes
4answers
8k views

SQL Server “empty table” is slow after deleting all (12 million) records?

I have a SQL Server 2008 instance with approximately 150 columns. I have previously populated this table with approximately 12 million entries, but have since cleared the table in preparation for a ...
14
votes
3answers
17k views

When a previously-fast SQL query starts running slow, where do I look to find the source of the issue?

Background I have a query running against SQL Server 2008 R2 that joins and/or left-joins about 12 different "tables". The database is fairly large with many tables over 50 million rows and about 300 ...
9
votes
6answers
7k views

Estimate projected database growth

I recently started working with SQL Server 2008 as a DBA trainee. I need calculate the size of the database but also estimate its growth over recent months and the predicted growth for the next 12 ...
11
votes
2answers
11k views

Performance difference between Clustered and Non Clustered Index

I was reading Clustered and Non Clustered Indexes. Clustered Index - It contains Data Pages. That means the complete row information will be present in the Clustered Index Column. Non Clustered ...
11
votes
4answers
14k views

Hard and Fast rule for include columns in index

Is there any hard and fast rule to decide what columns and in which order it should be put in Included in non clustered index. I was just reading this post Why use the INCLUDE clause when creating an ...
10
votes
3answers
2k views

SQL Profiler tutorials for newbie

The MSDN documentation tough to understand. Are there any online tutorials available to learn SQL Profiler? If possible, videos or simple blog posts would be preferred.
17
votes
3answers
2k views

Is it OK to blindly add missing indexes?

I often use SSMS to test my slow stored procedures for missing indexes. Whenever I see a "Missing Index (Impact xxx)" my kneejerk reaction is to just create the new index. This results in a faster ...
13
votes
4answers
9k 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 ...
13
votes
4answers
3k views

Make SqlClient default to ARITHABORT ON

First things first: I'm using MS SQL Server 2008 with a database at compatibility level 80, and connecting to it with .Net's System.Data.SqlClient.SqlConnection. For performance reasons I've created ...
12
votes
1answer
8k views

How do you ORDER BY a parameter?

Just wondering if I could solicit some feedback on a stored procedure I'm running and whether there's a more efficient way of handling the scenario (I'm pretty sure there will be!). Basically I have ...
31
votes
5answers
6k views

Why is there still a varchar data type?

Many of my databases have fields defined as varchars. This hasn't been much of problem since I live and work in America (where the only language that exists is "American". ahem) After working with ...
21
votes
2answers
19k views

What is the purpose of the database 'owner'?

Today while troubleshooting a service broker problem, I discovered that the database owner was the Windows login of an employee who had left the company. His login had been removed and thus the query ...
12
votes
3answers
6k views

Is there a way to determine if SQL Server queries are running in memory or going to disk?

I came across a set of stored procedures in an application today that are called repeatedly within a long running process. Within each procedure I found multiple different select statements, some ...
8
votes
3answers
16k views

How to prevent transaction log getting full during index reorganize?

We have multiple machines where we have pre allocated the size of the transaction log to 50gb. The size of the table that I am trying to reorganize is 55 - 60 gb but is going to continuously increase. ...
6
votes
2answers
4k views

Is it possible to get execution call stack in a trigger?

I have 10 stored procedures and each of them does INSERTs into one tableX. Is it possible in a trigger body of tableX to get what object causes modification of tableX (stored proc1 or sp2 or....) ? ...
22
votes
1answer
39k views

How to force drop database in SQL Server 2008

I'm trying to force drop a database, but after dropping the database, when I try to recreate the database, I'm getting the error cannot create file C:\Program Files.....[databasename].mdf because ...
21
votes
3answers
4k views

Efficency of stored procedures vs raw queries

I have read much on both sides of this debate: is there a signficant performance gain to be had by using only stored procedures over raw queries? I am specifically interested in SQL Server but would ...
20
votes
3answers
39k views

How to change SQL Server collation

How could I change the SQL Server 2008 R2 Express Default Collation for the whole server and a particular database? Is there a way to do it using visual interface of SQL Server Management Studio? In ...
15
votes
3answers
3k views

Anybody using HierarchyId in production? Is it reliable?

Is anybody using HierarchyId in real production with tables of reasonable size, more than a few thousand rows? Is it reliable/performant? So far I have not found anyone not affiliated with the vendor ...
14
votes
3answers
3k views

Why might a table's data space take up 4x the size of the raw data?

I have a table with 490 M rows and 55 GB of table space, so about 167 bytes per row. The table has three columns: a VARCHAR(100), a DATETIME2(0), and a SMALLINT. The average length of the text in ...
10
votes
2answers
3k views

Is data retrieved from SQL Server compressed for transmission?

Is data retrieved from Microsoft SQL Server compressed? If this is controlled by the connection string, is there any simple way to tell if any particular app is using it? I'm examining analysis ...
9
votes
1answer
1k views

alert when any job in a job category fails

Is it possible to set up an alert in SQL Server 2008 that will send an e-mail anytime a job in a specific category fails? I am wondering because I would like to set up an e-mail anytime an SSRS ...
9
votes
6answers
3k 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 ...