tempDB, size it right; it is important.

use -T1118.

1 data file for every logical processor, 1 log file.  This seems excessive for a 4 way Quad core machine, 16 temporary database data files, each one at 2gb.  We'll see how this runs today.

Read the Microsoft white paper, it's got all the information.

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx 

WorkingWithTempDB.doc (301.5 KB) 

One Unexpected side effect of this is uncontrolled log file growth.  We have a 12gb log file and it was filling up quick, 2gb an hour.  While monitoring this you could see the log file growing continuously, if the log file were to fill up, it could cause an aotogrowth, an autogrowth in tempDB when you doing 1,000's of transactions per seconds will crash the server (ask me how i know!).  To keep this from happenning we implemented a job to checkpoint the tempDB Database every 30 minutes.  Hopefully when we ren't under such load it will ease up and begin checkpointing properly on it's own.  We did verify there are no uncommitted or long running transactions.

A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this.

Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop or clear the data (which can be a good thing or a bad thing; remember "release early"?). The tempdb transaction log is less impacted than with #temp tables; table variable log activity is truncated immediately, while #temp table log activity persists until the log hits a checkpoint, is manually truncated, or when the server restarts.

In an OLTP system that uses tempDB extensively it is important to monitor both the user and tempDB very closely.   

This could go under so many names:

theory meets reaility

Quad core vs dual core

8 procs vs 16 procs

Numa or non-numa

today the question was answere, 16 processors, quad core showed it could handle the load, though we crashed on cmemthread suddenly, hot fix 3 or 5 to the rescue.

4 - Way Quad Core, 16 Processors, DONE.

Tonight is the fail over from 4 way dual core to 4 way quad core.

Interesting suttle changes as well, the dual core is AMD NUMA Architecture, the Quad core is Intel - NON NUMA.

Interesting to see, how the machine handles the load.  The current SQL Server is maxed out, all cpu's hitting 100% for sustained times, 4-6 hours.  Very little locking and blocking or disk i/o, all waits are on the SOS_SCHEDULER_YIELD.

Very difficult to recreate this issue or test in the lab, so the results are due tomorrow !

exhaustion.

Been fighting this issue where a SQL Server 2005 instance hosted on polyserve would not allow the password policy enforcement.

Something of a fix:  http://support.microsoft.com/kb/926642/en-us

SQL Server 2005

AMD 4-way dual core, so 8 procs.

Maxed out.

1000-1200 concurrent connections

2200-2500 Transactions per second

Minimal pagine 2-5 pages / sec

4 gb of ram still available out of 32gb possible

Do Disk Queue, % disk time less than 10 %, spikes of course.

100 % cpu utilization, i'll see 400-500 runnable spids with a wait type of SOS_SCHEDULER_YIELD.

Tuning like crazy, everything we can find, even resorting to NoLocks in many places and dropping referential integrity.

New server on the way, Intel 4-way Quad core, hopefully it will help.

Under heavy load, does the default trace cost ?  I'm about to find out, as I'm turning it off.

SQL Server 2005, one instance, 64 bit EE, 32 gb of RAM, 4 Dual Core procs (so 8 procs), HP 585; big box, right ?

I'm running at 80-90 % cpu; across all 8....so I'm squeezing anything I can and part of that squeeze is turning the default trace off...

Also have change the max degree of parralellism to 4.

select * from sys.configurations where configuration_id = 1568

select * from ::fn_trace_getinfo(0)

sp_configure 'default trace enabled', 0

reconfigure with override

We'll see if it makes a difference today, i'd like to keep the cpu's out of the 90's !

Didn't help, maybe didn't hurt either, all CPU's (8 of them) pegged most of the day 94-100%

The fun of supporting 1000's of installations of SQL Server 2000 and 2005, is you get too see some fun stuff; 12 hours of research shows that when you have SQL Server 2000 and 2005 installed together and 2000 is installed after 2005, the SQL Server browser service does not work properly due to permissions issue in the registry.

For this particular problem, we are dealing with two instances of MSDE and an instance of SQL Express, installed as:  MSDE, SQL Express, MSDE.

After this install the following message is seen for the sql server browser service:  “The configuration of the instance {} is invalid” ,substitute your instance name in {}. pic:

Local connections using the shared libraries still work, but remote connections do not.  There is a MS KB Article that documents how installing SQL 2000 after 2005 affects the registry permissions causing the SQL Server browser service to be unable to read the registry.

Microsoft KB Article:  http://support.microsoft.com/kb/905618 

  Spring Fornicator brewed up...

Not sure the name is appropriate for an Easter Brew, but it's definetly time to get working on those Lagers that take 3 months.

John Bull Marris Otter Extract 3.3 lbs

BierKeller Light Extract 3.5 lbs

12 Oz. Carapils Crushed Malt (mash 30 minutes 158, 1 gallon of water).

2lbs Clover Wildflower Honey (last 15 minutes)

1 Oz. Tettnanger Hops (bittering)

1 Oz Saaz Hops (finishing)

White Labs Zurich Lager yeast (swiss lager).

Standard 1 hour Boil and hopping schedule.

Not sure how the Zurich yeast will work, but it is a lager yeast and wanted something a bit different.  I expect this to take 6 weeks of fermentation (2 weeks primary, 4 weeks secondary) and at least 4-6 weeks of lagering in the refridgerator.  Plan on 1 week in primary at 50 degrees F, than cranking down to 40 degrees.  Will raise temp day before bottling to 55 degrees to "wake up" yeast, hold bottles at 55 degress and crank down temp every other day 2 degress until 45 degrees than hold there for 2-3 weeks and then work down to 40.

So long to wait...

Transferred 12/29/2007.

Bottled 1/19/2008 - Beer smelled great, sampled great, did not clear much at all, cloudy (protein haze from honey?), who needs clear beer ? I have a liver! 

My latest "finds" are now working.

I couldn't help my self recently, noticed two items in the trash of a house that was recently vacated by the occupants; snow blower and lawn mower.

Both looked good, Toro Snow blower, litle 3hb job, nice; Troy Built 6.5 hp self-propelled mower, a beauty both side and rear discharge, why would it be in the trash ? ; even if didn't work, i can always use parts.

Snow Blower, no-start, 10 minutes of tearing apart, cleaned carb and spark plug, shot of starter fluid in the carb, starts right up, very nice.  Electric starter is broken, but properly cleaned plug and carb, fires right up with the old fashioned pull rope.  These 2-stroke engines foul easy with lack of maintenance, oil-gas mixture goes bad quickly, snow blowers are rarely used (even in the midwest, most only see use 4-5 times a year), people rarely drain the gas out in the spring and few bother to start them occasionally.  I always like to have fun and tinker; once a month I start everything I have, generators, snow blowers, mowers, tractors, weed wackers, chain saws and leaf blowers...that's fun ! 

Troy built mower, looks brand new, but diagnosis shows lack of basic care.  Carb fouled, spark plug fouled, electric cut-off safety device broken and underneath the mowing deck a side peice of sheet-metal tore off and impacted the blade (what the hell were they mowing, the african bush ?).  Hoping the engine isn't shot from the impact; so I clean 'er up enough to start, bend the metal underneath out of the way, rig-up the safety so it's ignored !, 6 pulls later she runs like champ, what a great mower.  Cut-out the metal under the mowing deck, fix the safety, this mower is better than the one I got, what a find !

Of course the "boys" helped the entire time, they love fix'n stuff too !

"People know the cost of everything and the value of Nothing", Unknown quote I heard from somewhere;  Nowhere is this more true than here, I can sell either of these peices of equipment for a few hundred bucks; absolutely nothing wrong with them except lack of care.

Another man's trash is another man's treasure.

First off it's always important to have lots of logging on for your dts packages.  These packages are difficult to troubleshoot, even with the logs.  So SET LOGGING ON, in the data pumps, in sql jobs, everywhere (don't forget you may need to have some clean up scripts to keep your log files from growing out of control).

Recently ran across a package, failing in production, ran fine of course in development and test.  Now this wasn't the greatest package, but unfortunately the DBA doesn't always get consulted on the design of things, but of course, it became the DBA Groups problem to fix!

The package has quite a few tasks, data pumps from Oracle to SQL and even runs an external c# program to pull data from Active Directory \ Exchange.

Nothing obvious for the error (it seems obvious to you realize it's bogus !), the log file is below.  Finally after researching and research could find nothing for why the "incorrect syntax error happenned".  To this day I beleive that to be a "bogus" error.  

I focused on that provider error of 170 (AA), remembering back to my ancient days of programming, I looked the error up:  AA        170 BUSY: The requested resource is in use  .  I began to throttle the parrallell processes to lower the amount of resources simultaneously in use by the package, ultimately I had to set it to 1.  It's run fine every day since.  I'm not sure if the Oracle provider interface on this machine is outdated and caused the error, or if the "requested resource in use" was some other item !

Don't forget those error codes... http://www.lifeasbob.com/Code/ErrorCodes.aspx

DTSRun OnError:  DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005)
   Error string:  Error at Destination for Row number 1. Errors encountered so far in this task: 1.
   Error source:  DTS Data Pump
   Help file: 
   Help context:  0
 
Error Detail Records:
 
Error:  -2147467259 (80004005); Provider Error:  170 (AA)
   Error string:  Line 1: Incorrect syntax near 'OFFICE_ID'.
   Error source:  Microsoft OLE DB Provider for SQL Server
   Help file:  
   Help context:  0
 
DTSRun OnError:  DTSStep_DTSDataPumpTask_2, Error = -2147213206 (8004206A)
   Error string:  The number of failing rows exceeds the maximum specified.
   Error source:  Microsoft Data Transformation Services (DTS) Data Pump
   Help file:  sqldts80.hlp
   Help context:  0
 
 Error Detail Records:
 
Error:  -2147213206 (8004206A); Provider Error:  0 (0)
   Error string:  The number of failing rows exceeds the maximum specified.
   Error source:  Microsoft Data Transformation Services (DTS) Data Pump
   Help file:  sqldts80.hlp
   Help context:  0

Error:  -2147467259 (80004005); Provider Error:  170 (AA)
   Error string:  Line 1: Incorrect syntax near 'OFFICE_ID'.
   Error source:  Microsoft OLE DB Provider for SQL Server
   Help file: 
   Help context:  0
 
DTSRun OnFinish:  DTSStep_DTSDataPumpTask_2
DTSRun:  Package execution complete.

Error: 2008-01-10 21:03:38.38
   Code: 0xC002F210
   Source: Create DB_Stats Worksheet Execute SQL Task
   Description: Executing the query "CREATE TABLE `DB Stats` (`StatDate` DateTime, `DayTotal` Long, `DBA` Long, `master` Long, `msdb` Long, `EAM` Long, `ASPState` Long, `EAM_Load` Long, `tempdb` Long)" failed with the following error: "Too many fields defined.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
End Error
Warning: 2008-01-10 21:03:38.49
   Code: 0x80019002
   Source: Create DB_Stats Tab
   Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
End Warning

Anyone ?

Prepared my first wine today, Green Apple Riesling from Wine Experts, supposed to be light and sweet.

It was very easy, hoping for this to be ready for the wife this spring.

Updates as I progress...

December 3, 2007

    Racked into secondary.  Fermentation was nice steady bubbling.  Little bit cloudy.

December 20, 2007

   Cleared up nicely, Added finings and F-Pack

January 6, 2008

  Bottled.  Very clear, tasty - though I'm not a wine fan; bottling wand gave me a hard time blowing the tip off, ended up filling bottles via gravity drop on/off valve from bottling bucket, PIA.  Bottled in 750ml and 12 Oz beer bottles to allow enjoyment on a smaller scale. 

Chasing 4 problems.

1. Polyserve failing / restarting SQL Instances due to no network traffic.
2. Incorrect durations reporting in SQL Server traces (very large numbers).
3. Information message logged in SQL Server 2005 log file, The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.
4. Alert from Monitoring on, Windows cannot obtain the domain controller name for your computer network. (An unexpected network error occurred.). Group Policy processing aborted.
5. I've been chasing these errors, without much support from network or other engineers, basically the same as the mechanic who tells you the engine light is on, but all is ok !

Well finally found a customer advisory from HP and all 4 problems roll up to the same issue:

----------------------------

-------------------------------------

http://support.microsoft.com/kb/931279/en-us 

Using Polyserve clustering technology from HP, you need to ensure to have Automount off.

This is done using diskpart from a command prompt.

DiskPart

Automount disable

If this is not disabled you end up with "phantom" drives, and it can cause problems with everything from virus scanning, back up software and windows itself !  Basically what happens is the SAN Administrator zones new storage, somewhere the server reboots (got to love that!), than you have a new drive letter, but basically it is a "phantom" and netbackup, virus scanning or some other program attempts to access the new volume through it's drive letter, and the software abends....nice !

Removing the drive letter is usually a straight forward process of using Disk Management or Veritas Enterprise Disk Manager and removing the drive letter, but I've had issues with this and had to drop do a command prompt and use DiskMount to remove it.

Message
Executed as user: {removed}. Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "{removed}". [SQLSTATE 42000] (Error 7346)  OLE DB provider "OraOLEDB.Oracle" for linked server "{removed}" returned message "ORA-01403: no data found". [SQLSTATE 01000] (Error 7412).  The step failed.

Once again, my most favorite.

This is from a sql agent job, in sql server 2005, that calls a stored procedure that uses a Oracle Linked server.  Now what really is strange, is that i can take that stored procedure and run it from a query window and it runs fine !  BUT NOT AS A JOB !

Stopped SQL Agent, still Fails.

Finally had to stop and start the ENTIRE INSTANCE.  I think this is overkill, but I didn't have hours to research and I was still in the maintenance window.  Not sure if this is related to the MS DTC Service or an Oracle issue.

This is on an HP 585, Windows 2003, 64 bit, Oracle 10g client.

%^&$#$#%$%

Nothing more annoying than something that should, but doesn't work.

Often we allow users to view jobs (not necessarily administer), but just the ability to go view them etc.

Generally this was granted through the TargetServerRole in the msdb database.  Works fine in sql server 2000 enterprise manager, but with SQL Server Management Studio connecting to a sql server 2000 Instance, it does not work.

Best we can tell this is related to a "Phantom" 208 error.  A 208 error is generated when one compiles a stored procedure which declares and uses a temporary table.

http://www.mcse.ms/printthread.php?threadid=1265640

Though if you run a trace profile everything completes fine, but some how ssms "sees" the 208 error and "throws" an error.

Unfortunately many users are not adept enough to run the commands through TSQL, they need a GUI.

So far the only solution was to install sql server 2000 client tools, which we were hoping to only have to install sql server 2005 client tools.