SQL Server does a great job of logging, but anyone who administers a sql server should adjust some of the "out of the box" defaults for an instance, and create some custom jobs in sql agent to control output of jobs.

By default sql server logs information to ErrorLog.  Depending on your version of SQL Server (7.0, 2000 or 2005) and a default or named instance it is located along a path similiar to the following:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL${Instance}\LOG

The ErrorLog is a great source of monitoring and should be scraped and reviewed regularly, but by default there are only 6 log files kept on disk, and a new log file is started evertime an instance is started or "cycled".  This is not adequate, an administrator applying a patch to a server seems to require 3 reboots and you don't want to find out that information needed in a log file was lost due to not having enough of them.  We run this to 30 log files kept on disk and cycle the error log daily at 12am via a sql agent job.  This is very easy and can be adjusted via gui in SQL Enterprise Manager or in SQL Server Managemetn Studio by right clicking on the SQL Server Logs and selecting "Configure", which will bring up the dialog below, adjust to any number, but definetly 6 is not enough !

It can also be adjusted via TSQL, but requires xp_instance_regwrite and is highly dependent that you know the structure of the registry for your particular instance and version of sql server, this is one case where using the GUI is the best option, but it is possible to do via tsql:

EXECUTE master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE'
,N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer' --****!!!! VERIFY
,N'NumErrorlogs'
,REG_DWORD
,30 -- number of errorlogs you want to keep
GO

After configuring the instance to retain 30 errorLogs, now you need to implement a sql agent job to cycle the error logs daily at 12am.  This is very easily implemented with a job scheduled to run:
Exec sp_cycle_ErrorLog.

Now onto the good one, SQL Server Agent and the output from jobs.

After managing SQL Server Agent jobs for many years it is apparent that the default options for logging of jobs, Overwrite and Append, is not adequate.  Most of the time we default the logs to append, but overtime and depending on the amount of output these log files, they can become very large, difficult to open, difficult to read and waste valuable time when you have to trouble shoot a job.  This applies to sql server 7.0, 2000 and 2005 and is highly dependent on having all your jobs and logging go to the same directory (if they are currently "scattered" you can correct them manually or through tsql in the sysjobsteps table, with a mass update).

We have developed a jobs that we host on every sql agent instance, Log Archive.  It is a two step job consisting of TSQL and Active X Scripting.  One step moves all the log files to a sub-directory for that day.  The 2nd step of the job removes the sub-directories older than x days (we default to 30).  This makes it very easy to find a log file for a particular day, based on the naming convention of the sub-directories.  This job runs daily at 11:58pm.  Example below:

The code for the two steps is attached, they will require editing for your specific directory structure and number of sub-directories to retain.

Archive_Daily_Logs.txt (.7 KB) 

VBScript_Delete_Old_Dirs.txt (.52 KB) 

This creates a great directory tree, easy to maintain, easy to troubleshoot:

The best article I've read in a long time; I wish the focus would have been more on an "Angry American Man", as opposed to "White", and less focus on Hilary, as there are many points that resonate with every American Man, but over-all I enjoyed reading it.

http://www.aspentimes.com/article/2008198091324 

In election 2008, don’t forget Angry White Man

Gary Hubbell
February 9, 2008

There is a great amount of interest in this year’s presidential elections, as everybody seems to recognize that our next president has to be a lot better than George Bush. The Democrats are riding high with two groundbreaking candidates — a woman and an African-American — while the conservative Republicans are in a quandary about their party’s nod to a quasi-liberal maverick, John McCain.

Each candidate is carefully pandering to a smorgasbord of special-interest groups, ranging from gay, lesbian and transgender people to children of illegal immigrants to working mothers to evangelical Christians.

There is one group no one has recognized, and it is the group that will decide the election: the Angry White Man. The Angry White Man comes from all economic backgrounds, from dirt-poor to filthy rich. He represents all geographic areas in America, from urban sophisticate to rural redneck, deep South to mountain West, left Coast to Eastern Seaboard.

His common traits are that he isn’t looking for anything from anyone — just the promise to be able to make his own way on a level playing field. In many cases, he is an independent businessman and employs several people. He pays more than his share of taxes and works hard.

The victimhood syndrome buzzwords — “disenfranchised,” “marginalized” and “voiceless” — don’t resonate with him. “Press ‘one’ for English” is a curse-word to him. He’s used to picking up the tab, whether it’s the company Christmas party, three sets of braces, three college educations or a beautiful wedding.

He believes the Constitution is to be interpreted literally, not as a “living document” open to the whims and vagaries of a panel of judges who have never worked an honest day in their lives.

The Angry White Man owns firearms, and he’s willing to pick up a gun to defend his home and his country. He is willing to lay down his life to defend the freedom and safety of others, and the thought of killing someone who needs killing really doesn’t bother him.

The Angry White Man is not a metrosexual, a homosexual or a victim. Nobody like him drowned in Hurricane Katrina — he got his people together and got the hell out, then went back in to rescue those too helpless and stupid to help themselves, often as a police officer, a National Guard soldier or a volunteer firefighter.

His last name and religion don’t matter. His background might be Italian, English, Polish, German, Slavic, Irish, or Russian, and he might have Cherokee, Mexican, or Puerto Rican mixed in, but he considers himself a white American.

He’s a man’s man, the kind of guy who likes to play poker, watch football, hunt white-tailed deer, call turkeys, play golf, spend a few bucks at a strip club once in a blue moon, change his own oil and build things. He coaches baseball, soccer and football teams and doesn’t ask for a penny. He’s the kind of guy who can put an addition on his house with a couple of friends, drill an oil well, weld a new bumper for his truck, design a factory and publish books. He can fill a train with 100,000 tons of coal and get it to the power plant on time so that you keep the lights on and never know what it took to flip that light switch.

Women either love him or hate him, but they know he’s a man, not a dishrag. If they’re looking for someone to walk all over, they’ve got the wrong guy. He stands up straight, opens doors for women and says “Yes, sir” and “No, ma’am.”

He might be a Republican and he might be a Democrat; he might be a Libertarian or a Green. He knows that his wife is more emotional than rational, and he guides the family in a rational manner.

He’s not a racist, but he is annoyed and disappointed when people of certain backgrounds exhibit behavior that typifies the worst stereotypes of their race. He’s willing to give everybody a fair chance if they work hard, play by the rules and learn English.

Most important, the Angry White Man is pissed off. When his job site becomes flooded with illegal workers who don’t pay taxes and his wages drop like a stone, he gets righteously angry. When his job gets shipped overseas, and he has to speak to some incomprehensible idiot in India for tech support, he simmers. When Al Sharpton comes on TV, leading some rally for reparations for slavery or some such nonsense, he bites his tongue and he remembers. When a child gets charged with carrying a concealed weapon for mistakenly bringing a penknife to school, he takes note of who the local idiots are in education and law enforcement.

He also votes, and the Angry White Man loathes Hillary Clinton. Her voice reminds him of a shovel scraping a rock. He recoils at the mere sight of her on television. Her very image disgusts him, and he cannot fathom why anyone would want her as their leader. It’s not that she is a woman. It’s that she is who she is. It’s the liberal victim groups she panders to, the “poor me” attitude that she represents, her inability to give a straight answer to an honest question, his tax dollars that she wants to give to people who refuse to do anything for themselves.

There are many millions of Angry White Men. Four million Angry White Men are members of the National Rifle Association, and all of them will vote against Hillary Clinton, just as the great majority of them voted for George Bush.

He hopes that she will be the Democratic nominee for president in 2008, and he will make sure that she gets beaten like a drum.

Gary Hubbell is a regular columnist with the Aspen Times Weekly.

All glory goes to Kevin Niederhauser with an assist from Jeff Pickett ;

SELECT sa.full_name, gr_group_name from

[USERS] u, [GROUPS] g, [qcsiteadmin_db].[td].[USERS] sa

where substring(u.us_group, g.gr_group_id + 1, 1) = '1'

and u.us_username = sa.user_name

order by sa.full_name

 

Interstingly found that quotes are necessary around the password for OSQL if the password contains special characters.

Take the following password, spoon12free^$ and create a sql authenticated account with that password and then try to use osql with some simple query, without quotes you receive a login f

osql -S(local) -dmaster -Umy_user -Pspoon12free^$ -Q"select getdate()"

This results in a failure.

I don' know why, but this error suddenly began happenning while trying to start SQL Server configuration manager:

Cannot connect to WMI Provider.  You do not have permission or the server is unreachable.  Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager. 

Invalid class [0x80041010]

This started with every one of my group's DBAs and seems to follow some SMS Push to update something on our desktops, lovely.

One solution from a dba in my group was applying patch 3215, which probably just incidently fixed it.

I researched further and found the following solution:

http://blogs.msdn.com/echarran/archive/2006/01/03/509061.aspx 

Occasionally, during setup, some .mof files don't get installed and registered correctly.  There is a program called mofcomp that is responsible for registering and storing the data associated with .mof files.  If the .mof file information becomes damaged or compromised, or never installed correctly, the problem will result in an error message like below:

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager.
Invalid class [0x80041010]

The solution is to go to a command prompt and then run mofcomp.

C:\Program Files\Microsoft SQL Server\90\Shared>mofcomp "C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof"

The output will look like below

Microsoft (R) 32-bit MOF Compiler Version 5.1.2600.2180
Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.
Parsing MOF file: C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmprovider
xpsp2up.mof
MOF file has been successfully parsed
Storing data in the repository...
Done!

On servers, the .mof file will be sqlmgmprovider.mof.

Been chasing an issue for a long time, where suddenly the connections to a sql server increase exponentially and queries become unresponsive.

Really never made much headway, but found two interesting articles, URL's below:

http://sqlblog.com/blogs/sarah_henwood/archive/2007/07/23/connection-failures-sql-2005-appears-unresponsive.aspx 

http://support.microsoft.com/kb/927396 

Supposedly this was fixed in Service Pack 2.  I'm having the issue with SQL Server 2005, SP2, Cumulative Hotfix 5, version 3215.

Monitor the query results of:

SELECT SUM(single_pages_kb + multi_pages_kb) AS
   "CurrentSizeOfTokenCache(kb)"
   FROM sys.dm_os_memory_clerks
   WHERE name = 'TokenAndPermUserStore'

If it is continuously increasing, than try running:

DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

We have 81+ servers, 100+ instances of SQL Server, and it is only affecting one, of course it is the one under the greatest load, OLTP 5000 transactions per second.  You can see the result of the TokenAndPermUserStore sum growing to 120mb, we now reset it around 40-50mb and have seen no re-occurences of the issue.

I have a case open with Microsoft and am trying to get confirmation that this is the right solution, but since we began running the DBCC statement we have not yet crashed, and previously we could not make it more than 2-3 days without crash, so that there is proof positive of some correlation.

We'll see what Microsoft responds with.

More on plan cache:

http://blogs.msdn.com/sqlprogrammability/archive/2007/01/21/2-0-diagnosing-plan-cache-related-performance-problems-and-suggested-solutions.aspx

This is crap, SQLAgent not starting because a user database is in recovery mode, after restarting sql server.  Usually recovery / checkpointing of user databases on start-up is really quick, but if one runs into an issue it can take a long long time, this particular user database has been recovering for 4+ hours.  Now I have issues with maintenance jobs and reporting services jobs not running because sql agent will not start.

I can understand if msdb or some other system database is in recovery or suspect, but not a damn user database !

SQL Server Error Log:

2008-02-06 09:38:44.07 spid10s     Recovery of database 'ClientAssistant_CY' (20) is 27% complete (approximately 38597 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.

READ THAT, 38000+ seconds, over 10 HOURS, we can't wait that long ! 

SQL Agent Error Log:

2008-02-06 08:44:55 - ? [393] Waiting for SQL Server to recover databases...

This is unreal, i'm going to have to de-attach / interrupt the recovery process; i think, but to be sure i placed a support call to Microsoft.

According to Microsoft, this is a new feature of SQL Server 2005.  If SQL Server Agent starts and not all the user databases on-line / recovered than sql server agent stays in a starting mode and no jobs run.

The way around this is to "interrupt" the services, so you must stop sql server, which requires finding the process id (pid) and killing it via task manager or kill.exe; rename the mdf and ldf of the database taking the extremely long recovery time and then restart the services, everything should come on-line.  Now you can either restore from a backup or re-attach the mdf / ldf and let it start recovering again....

very very bad solution as interrupting sqlservr.exe with a kill of the pid is a high risk operation that could corrupt other databases, so proceed with caution and get a critical support case opened with Microsoft, you do not want to be doing this alone, have them on the phone with you, severity 1.

RESPONSE FROM MICROSOFT (Names removed)>

From: [nameremoved@microsoft.com]
Sent: Friday, February 08, 2008 9:02 AM
To: Horkay, Robert
Cc: 
Subject: RE: Case number SRX080206600293 - SQLAgent will not start on a cluser - Open

Today was marked by a Heather Mead Ale, braggot; inspired by Sacred and Herbal Healing Beers, "Historically it was almost impossible to extract the honey unless the entire comb was removed from the hive and boiled.  In the making of mead from heather honey, this would mean that the entire contents of the hive would be boiled together: angry bees, propolis, pollen, royal jelly honey and wax."  I left out the bees and wax, but got the rest !

This mead, beer or braggot, not sure which it really is, took some time to source the fermentables, of note is the use of real Heather Honey from scotland, royal jelly, propolis and bee pollen...none of these were cheap or easy to find.  My most expensive 5 gallons, I expect fermentation to take 2-3 months, and then 9-12 months in the bottle before a tasting.

Heather Mead Ale (braggot).

10 Good Earth Green Tea Bags (must be good earth w/lemon grass)

3.3 Pounds Marris Otter malt extract; $15.00
1 Pound Dry Malt Extract (Muntons), extra light $5.00
1 Oz Fuggle Hops bitter
1 Oz Cascade Hops finish

4 Oz Heather Tips

6 Pounds Heather Honey (scottish); (approx 1/2 gallon) $80.00
1 Pound Wild Flower Honey
8 Oz. Royal Jelly $45.00
8 Oz. Bee Pollen $5.50
8 Oz. Bee Propolis Powder $26.00

Wyeast Sweet Mead Yeast 125 ml.
Top fermenting yeast which leaves residual sugar after fermentation.

Basic concept was an extract beer formulation, than I strained the hops from the hot wort, added 1 gallon of water to cool the wort a bit, then added the honey and honey products (royal jelly, propolis and pollen) and heather tips.  Stirred vigorously than topped up to a complete 5 gallons. 

11/23/2007 - In the bucket, no sign of fermentation; nasty heather tips floating on top, smells like a combination of mown grass, piney tree sappy and sickening sweet...i'm expecting a very explosive and messy fermentation....blow off hose in place

11/25/2007 - Fermentation progressing nicely, no issues with blowoff or clogging, temperature at low end of range, 65 degrees, turned cold in kansas city, will be hooking up a "brew belt" to warm up the fermentation a bit. Progressing well

11/27/2007 - Too cool, still fermenting well, but placed a space heater in closet with bucket.  Raised temperature up to 72-74 degrees.

12/2/2007 - Racked to secondary.  What an ugly mess.  The mass of heather tips floating on the top with yeast was a unique odor.  Maybe should have filtered out the heather tips before fermentation, but i've read that the combination of yeast and naturally occurring mold on the heather tips add to the medicinal, inebriating and pshycotropic powers of the brew.  Carefully siphoned to secondary, removing most of heather tips, very cloudy at times when siphon either clogged or sucked up stuff from bottom.  Lost a good gallon of product to ensure that too much sediment and trub was not picked up.  Definetly the most trub i've seen, propolis looks to have settled directly out of the wort, looks the same for some of the bee pollen, lots of heather tips and standard trub; the compost pit is getting the most expensive trub i've ever produced !  I plan on letting it sit in the secondary 3 months, at least until Feb 2008.

From the bonny bells of heather,
They brewed a drink longsyne,
Was sweeter far than honey,
Was stronger far than wine.

From Heather Ale, a Galloway Legend
by Robert Louis Stevenson

Suddenly a program is biting me,

Now.Format()  being saved to a string variable, and then passed into a stored procedure, and somehow it is recorded in the database as 12:15 pm, instead of 12:15 am.

I'm not sure if this is because of the stored procedure (implicit conversion of string to datetime for the parameter, very bad), or the Now.Format() in the code net setting up the string variable.

Either way some poor planning and lack of code review, as both these items should have been caught and easily corrected.

So for my own purposes i'm recording the Now.Format and datetime stuff...

i.e.
now.ToString("d");  // "09/27/2006"
now.ToString("D");  // "Tuesday, 27 September 2006"
now.ToString("G");  // "09/27/2006 14:15:39"

I have included my own table mapping Standard Format String to Custom Format string below. MSDN actually has a pretty good table that describe what each item does, and DateTime.ToString() has a pretty good code example that shows what each format string specifier do. Also if you just want samples, MSDN has a "Standard Date Time Format String Output example" here. Because documentation is so good. I won't go into this too much.

Custom Format String

Custom format string gives you the flexibility to build your own formatting. When using a single character format string specifier, you will need to prepend it with a "%", otherwise it will be interpreted as a Standard Format String. Here are the basics for building your own string:

DateTime now = new DateTime(2006, 9, 07, 15, 06, 01, 08, DateTimeKind.Local);

now.ToString();      //"09/27/2006 15:06:01"

Year

now.ToString("%y");   //"6"

now.ToString("yy");   //"06"

now.ToString("yyy");  //"2006"

now.ToString("yyyy"); //"2006"

Month

now.ToString("%M");    //"9"

now.ToString("MM");    //"09"

now.ToString("MMM");   //"Sep"

now.ToString("MMMM");  //"September"

Day

now.ToString("%d");    //"7"

now.ToString("dd");    //"07"

now.ToString("ddd");   //"Thu"

now.ToString("dddd");  //"Thursday"

Hour

now.ToString("%h");    //"3"

now.ToString("hh");    //"03"

now.ToString("hhh");   //"03"

now.ToString("hhhh");  //"03"

now.ToString("%H");    //"15"

now.ToString("HH");    //"15"

now.ToString("HHH");   //"15"

now.ToString("HHHH");  //"15"

Minutes

now.ToString("%m");    //"3"

now.ToString("mm");    //"03"

now.ToString("mmm");   //"03"

now.ToString("mmmm");  //"03"

Seconds

now.ToString("%s");    //"1"

now.ToString("ss");    //"01"

now.ToString("sss");   //"01"

now.ToString("ssss");  //"01"

Milliseconds

now.ToString("%f");    //"0"

now.ToString("ff");    //"00"

now.ToString("fff");   //"008"

now.ToString("ffff");  //"0080"

now.ToString("%F");    //""

now.ToString("FF");    //""

now.ToString("FFF");   //"008"

now.ToString("FFFF");  //"008"

Kind

now.ToString("%K");    //"-07:00"

now.ToString("KK");    //"-07:00-07:00"

now.ToString("KKK");   //"-07:00-07:00-07:00"

now.ToString("KKKK");  //"-07:00-07:00-07:00-07:00"

// Note: The multiple K were just read as multiple instances of the

// single K

DateTime unspecified = new DateTime(now.Ticks, DateTimeKind.Unspecified);

unspecified.ToString("%K");   //""

DateTime utc = new DateTime(now.Ticks, DateTimeKind.Utc);

utc.ToString("%K");           //"Z"

TimeZone

now.ToString("%z");     //"-7"

now.ToString("zz");     //"-07"

now.ToString("zzz");    //"-07:00"

now.ToString("zzzz");   //"-07:00"

Other

now.ToString("%g");    //"A.D."

now.ToString("gg");    //"A.D."

now.ToString("ggg");   //"A.D."

now.ToString("gggg");  //"A.D."

now.ToString("%t");    //"P"

now.ToString("tt");    //"PM"

now.ToString("ttt");   //"PM"

now.ToString("tttt");  //"PM"

 Additional Resources

Now that you understand what Standard and Custom format strings are, here is a table of Standard Format String to Custom Format String mapping:

Year Month Day Patterns:
d      = "MM/dd/yyyy"
D      = "dddd, dd MMMM yyyy"
M or m = "MMMM dd"
Y or y = "yyyy MMMM"

Time Patterns:
t      = "HH:mm"
T      = "HH:mm:ss"

Year Month Day and Time without Time Zones:
f      = "dddd, dd MMMM yyyy HH:mm"
F      = "dddd, dd MMMM yyyy HH:mm:ss"
g      = "MM/dd/yyyy HH:mm"
G      = "MM/dd/yyyy HH:mm:ss"

Year Month Day and Time with Time Zones:
o      = "yyyy'-'MM'-'dd'T'HH':'mm':'ss.fffffffK"
R or r = "ddd, dd MMM yyyy HH':'mm':'ss 'GMT'"
s      = "yyyy'-'MM'-'dd'T'HH':'mm':'ss"
u      = "yyyy'-'MM'-'dd HH':'mm':'ss'Z'"
U      = "dddd, dd MMMM yyyy HH:mm:ss"

All other single characters will throw an exception.