I can see many warnings with your temp tables and column statistics. Is TempDb configured to auto create statistics?

SELECTname,

is_auto_create_stats_on,

is_auto_update_stats_on,

is_auto_update_stats_async_on

FROMsys.databases

WHEREdatabase_id = 2

I just realised I have been discussing two seperate issues on the same thread, doh.

The month end report was the actual issue on one server. The dodgy server configuration was...

Thanks Gail. I have read your article previously, but I don't think it applies in this case. The update in question is joining on AccountID, an INT, which is the...

It gets better.

Loads of files set to grow at 1MB, including my 30Gb reporting db. The rest on 10%, including a 175Gb store.

They should have asked me to check this...

OK, just discovered an interesting fact.

Our esteemed "senior" (sic) DBA setup the new server.

TempDb has 1 file, on C:, starting at 8Mb and growth of 10%.

#$%#$^$&^#%^&@@$$%

SELECT @@SERVERNAME, login_time

FROM sys.dm_exec_sessions

WHEREsession_id = 1;

matt.gyton (10/30/2012)

---

Indeed I have...it's a bit of a mystery at the moment, the sort is just killing the CPU. I have also checked the indexes are set up the...

matt.gyton (10/30/2012)

---

Just a bit more info for you....

The database was being transferred from a SQL 2005 server to a SQL 2008 R2 server. I have now determined, after trying...

Can you post both execution plans?

Troubleshooting SQL Server: A Guide for the Accidental DBA is a good place to start.

http://www.sqlservercentral.com/articles/books/76296/

This code will get the value when run in a job.

DECLARE @MaxID INT;

SELECT @MaxID = MAX(instance_id)

FROM [msdb].[dbo].[sysjobhistory]

WHERE job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))

AND step_id = 1;

OPENQUERY does not accept variables for its arguments. If you need to build the query dynamically you can use dynamic SQL.

DECLARE @string VARCHAR(MAX),

@sql NVARCHAR(MAX);

SET @string = 'abcd';

SET

Try this

SELECT [September 2012 Billing File] =

CASE

WHEN T2.[Application ID] IS NOT NULL

AND T2.[ServerName] IS NOT NULL

AND SUM([Billed This Month]) <> 0

THEN 'Y'

ELSE 'N'

END

FROM[AEG_DB].[dbo].[TBL_Work] T1

LEFT JOIN[TBL_Data] T2

ON (T1.[Application...

While attempting to reproduce this in another database I discovered the issue. My user table was created with ANSI PADDING off while the temp table I'm loading was created with...