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

learn more… | top users | synonyms (1)

5
votes
2answers
515 views

Window functions cause awful execution plan when called from a view with external parametrized 'where' clause

I had this issue long time ago, I found a workaround which suited me and forgot about it. But now there's that question on SO so I'm willing to bring this problem up. There's a view that joins few ...
5
votes
5answers
5k views

Pros/Cons Using multiple databases vs using single database

Working on a new project which has the requirement to use 7 databases. Arguing performance, stability, optimization is easier implemented. While I don't agree I'm having trouble collecting good ...
4
votes
3answers
2k views

Test if any fields are NULL

I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column. I need something like SELECT * FROM ...
3
votes
2answers
4k views

Get Job id OR Job name from within executing job

I have a central DB that I write job outcomes on all my servers back to. I pass 3 parameters via powershell in a SQL job back to a sp on the central server that verifies the job should be running at ...
3
votes
2answers
2k views

Problem Reaching VM SQL Server Instance

Here's what I'm working with right now. I have a host OS which includes a SQL Server 2008 Enterprise instance. I have a virtual machine running on this host which has a SQL Server 2008 R2 Express ...
14
votes
2answers
467 views

Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?

I would like to use code that I developed in C# CLR to be used in all of the databases on the system so that I don't have to set each to trustworthy and turn CLR on and keep a bunch of the same code ...
8
votes
1answer
3k views

Handling exceptions in stored procedures called using insert-exec blocks

I have a stored procedure that is called in an insert-exec block: insert into @t exec('test') How can I handle exceptions generated in the stored procedure and still continue processing? The ...
8
votes
3answers
1k views

Does SSRS lock the table when querying?

My senior DBA told me that SQL Query execution by default doesn't lock the table. I was having some issues with my SQL Server Reporting Services (SSRS) report which seems to be getting some issues ...
8
votes
2answers
3k views

Why does “SELECT POWER(10.0, 38.0);” throw an arithmetic overflow error?

I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns. As part of the check I compute the size of the data type's range for every IDENTITY column; I use ...
8
votes
2answers
343 views

Mirroring the Distributor

Has anyone had success with mirroring the distribution database? We have a dedicated server as a Distributor. It handles all our push replication from prod to reporting. We'd like to build an ...
7
votes
2answers
401 views

Capturing datetime of change in SQL Server CDC

So we've started exploring using change data capture on one of our production databases. We'd like to know the datetime of each change. Reading through walkthrough's and tutorials etc it seems that ...
7
votes
6answers
627 views

Scheduled backup task doesn't always backup all databases despite always saying job successful

I have a job in SQL 2008 that runs a stored proc to backup all databases. This runs daily via sql server agent job. It quits with success every day but some days it quits with success only after ...
6
votes
2answers
290 views

Help with this query

I have a person table. I also have a car table and books table. The car and book table both have a foreign key back person. I am trying to count the number of books and cars each person has: I try: ...
6
votes
1answer
514 views

How to back up a small database in SQL Server 2008 R2 Express Edition

I will shortly be migrating a database from SQL Server 2000 (part of SBS 2003) to SQL Server 2008 R2 Express Edition The database is small, and there are only a few hundred short transactions per day ...
5
votes
1answer
92 views

grant permission

i've got a database-role, -login and user CREATE LOGIN dummyLogIn WITH PASSWORD = 'dummyPassword', CHECK_POLICY = OFF; CREATE USER dummyUser FOR LOGIN dummyLogIn; CREATE ROLE "dummyRole"; with ...
5
votes
1answer
855 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
6answers
5k views

SQL Server log shrinking Issue [duplicate]

Possible Duplicate: Why Does the Transaction Log Keep Growing or Run Out of Space? I have a 300 GB database log in our SQL Server. I want to shrink all the logs but it is not allowing me to ...
3
votes
2answers
2k 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
1answer
480 views

Backing up file groups with restore to earlier backup

I have a task: I have created a database with a filegroup lamb that has a few tables. I need to extract a csv into a table in a filegroup named lamb and back up that filegroup. Then extract another ...
3
votes
2answers
842 views

SQL Server media belongs to which edition

Could anyone please help me on below question? Without installing SQL Server media 2008 and 2012, how can we know that the media belongs to which edition (like developer, standard or ...
3
votes
1answer
330 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
3answers
3k 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
431 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 ...
2
votes
1answer
146 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? Please ...
2
votes
2answers
227 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 ...
2
votes
2answers
7k views

SQL query to return a column per date in date range?

Let me explain what I've got and what I'm trying to achieve. Let's say I have Table A: BookingsPerPerson Person_Id ArrivalDate DepartureDate 123456 2012-01-01 2012-01-04 213415 ...
2
votes
4answers
2k 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. ...
2
votes
2answers
4k 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 ...
2
votes
2answers
476 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 ...
1
vote
2answers
45 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
98 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
342 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
1k 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
1answer
180 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 ...
23
votes
2answers
4k 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.
20
votes
4answers
5k 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
3answers
1k 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
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.
16
votes
3answers
1k 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 ...
9
votes
4answers
6k 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 ...
27
votes
5answers
4k 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 ...
11
votes
1answer
4k 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 ...
6
votes
2answers
2k 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....) ? ...
18
votes
3answers
3k 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 ...
12
votes
3answers
2k 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 ...
7
votes
2answers
6k views

Sql Server - Best Practices for Growing Database Files

I have been monitoring file growth via the data collector in sql server 2008 r2 for two weeks. The database is growing consistently at around 35(MB)/Day. The DB has not yet hit the initial size of 2 ...
7
votes
2answers
5k 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 ...
5
votes
3answers
15k 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 ...
4
votes
3answers
9k 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. ...
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 ...