Assuming this system USED to start up successfully, SOMETHING has changed:

1) check permissions on drives, directories, files that have sql server data.

2) did someone enable windows Data Execution Prevention stuff?

3)...

run sp_whoisactive (search sqlblog.com for latest version of this AWESOME freebie) to check for blocking.

1) gotta ask why 3rd party tools are forbidden for this need - they are the RIGHT and BEST solution

2) is the backup file on same IO as IO you...

Maybe I need another cup of coffee, but I don't see how ROW_NUMBER can help with this classic 'running totals' type of scenario. Best bet for that is actually...

sqlnaive (5/6/2011)

---

I'm not using any UDFs or table variable It's just simple insert as below:

Insert into dbo.TableA

PageId, col1, col2, col3, col4, col5, col6, col7, col8)

select

(row_number()OVER (ORDER BY col1, col2,...

here is a great guide to deadlock troubleshooting (see additional 2 linked posts): http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

serializable isolation, no explicit transaction control, no error handling, GUIDs all over the place, missing SET NOCOUNT ON - wow, where to start with the issues here...

See here and the...

Grant tagged the root cause here I think: you MUST update ALL statistics with a FULL SCAN after upgrading a database from SQL 2000 to SQL 2005+. Doing anything...

You asked for a fast response, so I say the fastest way to figure out what is going on and correct it is to get a professional tuner connected to...

Ninja's_RGR'us (5/5/2011)

---

sqlnaive (5/5/2011)

---

Is around 50 seconds good time to insert around 2500000 records to a physical table A from another table B with 8000000 records ? There are just 8...

day-476284 (5/4/2011)

---

Thank you very much for all your comments.

I agree application queries need to be optimized. Soon, we are going to replace this old application with a newer version by...

Sad but true that SOOOO many entities out there nickel and dime things - right up until they REALLY mess something up. Then they often wind up paying tremendously...

Ninja's_RGR'us (5/3/2011)

---

GilaMonster (5/3/2011)

---

Because I didn't notice and wrote a...

There are so many bad things in that sproc I don't know where to start. But given what I see I imagine there are all KINDS of opportunities for...

Commong problem: the query plans for BOTH conditionals are compile the FIRST time the sproc is executed -> thereby GUARANTEEING one of them will be BAD. Solution is to...