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

learn more… | top users | synonyms (2)

3
votes
1answer
623 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
585 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
584 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
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
79 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
59 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
132 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
2answers
2k 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 ...
1
vote
1answer
518 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
820 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
196 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 ...
-5
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 ...
22
votes
4answers
7k 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 ...
9
votes
6answers
5k 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 ...
25
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.
13
votes
3answers
13k 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 ...
11
votes
2answers
10k 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 ...
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 ...
12
votes
1answer
7k 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 ...
29
votes
5answers
5k 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 ...
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
15k 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
3k 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....) ? ...
5
votes
3answers
20k views

T-SQL query to report disk space allocation and used space for auto-grow SAN databases

We are using 6 databases in total for an application, and we can only share 4TB of space for all 6 databases (via SAN storage). I'd like to write a query (report) for a single database indicating the ...
5
votes
1answer
15k views

SQL Server permissions to create, restore, delete only some databases

On MS SQL Server 2008 R2, I need to have user which can create, restore, DBCC CHECKDB and then drop databases. But he must not be able to access or delete specified databases on server (security ...
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 ...
19
votes
3answers
37k 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
2k 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
944 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 ...
7
votes
3answers
24k views

How do I run a batch file from a scheduled Job via SQL Server Agent

I'm attempting to run a (Windows) batch file from within a SQL Server agent job under SQL Server 2008 R2. In the Job Steps, I've created a new step, set its type to "Operating system (CmdExec)". I ...
6
votes
3answers
3k views

Will the transaction log shrink automagically in SQL Server?

When SQL Server database in a SIMPLE mode, you don't have to care about the transaction log bakcups. But in a SIMPLE mode, the transaction log seems to grow as it does in FULL mode. Does is truncate ...
4
votes
3answers
2k views

How to sync the data between two identical DB

The thing is I've two identical databases, one is in my localhostserver at my laptop and the other one is on the office main server. Is there any automatic way or software that can synchronize the ...
12
votes
3answers
1k views

What's the most effective way to duplicate an instance on SQL 2008?

We've got a simple database instance with just enough data in the databases to keep it useful, and we can use it for demonstration purposes, and I want to put a copy of it on my laptop so I can use it ...
9
votes
2answers
5k views

How to optimise T-SQL query using Execution Plan

I have a SQL query that I have spent the past two days trying to optimise using trial-and-error and the execution plan, but to no avail. Please forgive me for doing this but I will post the entire ...
7
votes
5answers
5k 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 ...
6
votes
2answers
2k views

SQL Profiler - Scripting a trace with HostName filter

I have a stock of scripted trace definitions that I use for different levels of diagnostics, none of which filter by HostName. I needed to filter traffic by host today so: Fired up Profiler Created ...
6
votes
1answer
7k views

Restore encrypted database to another server

I'm using a product which runs on SQLServer 2008. Understandably the company supplying it don't offer SQLServer support. When I installed the product I specified a password to encrypt the database. ...
6
votes
5answers
4k views

What is the most efficient way to alter a column definition in a table with millions of rows

I need to change a column from NOT NULL to NULL in a table that contains millions of rows. I've tried a simple alter table Table1 ALTER COLUMN Column1 XML NULL but it takes forever. So here are my ...
5
votes
3answers
100 views

Is there an advantage to not partition aligning an index?

I have the privilege of managing a large partitioned OLAP table. While reviewing this table I noticed one of the indexes is not aligned with the partitioning scheme. Since the author is not ...
5
votes
2answers
762 views

When was a Database taken Offline

I am trying to find a way to figure out when was couple of my sql databases were taken offline. I checked the the logs but could not find any such info and moreover there is no default trace enabled. ...