Recently had a request to access some DMV's by the Administrators of an application.

I couldn't really think of a reason why not to grant access to the following DMV's:

sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_exec_requests

Supposedly this request was so they could monitor the connections for their applications and ensure things weren't "going wild".  Sounds good to me, a pro-active application team, permission granted.  They may not be sys-admins, but if they see lots of connections from a particular web server, they can go to that web server and reset IIS, saving an alert going to the DBA Team about excessive connections.

Also note that if you don’t have VIEW SERVER STATE permission on the server you will see only the current session, not all executing sessions in the instance of SQL Server.

Always bound to happen, driving happily along, when you push the clutch in, and swoooaaappp, it drops to the floor with the thud that lets you know, your now just a large bread box of an object traveling on the highway at 60mph!

Fortunately it was the morning rush hour and I was in the left lane, so it would be quite easy to navigate to the side of the road to inspect and get a damage report; nothing any Bus Pilot can not easily handle.

Quick review while laying under front of bus, end of clutch cable blown out, clevis pin lost.  Decide to drive and shift with no clutch; there are vw enthusiasts that will tell you if you match the speed of the engine with the speed of the transmission, a 1:1 ratio, you can shift with no clutch....YES IT's TRUE; but damn they make it sound too easy and getting from 3rd to 4th was impossible.

Bus Limps home.

Burned again by the famous combination of Oracle and SQL Server linked servers, and a poorly configured SQL Agent job.

Scenario:

1. Job Runs every 5 minutes
2. Job Runs a standard T-SQL Stored Procedure
3. Stored Procedure makes use of Oracle Linked Server (full 4 part naming and Open query used)
4. Oracle goes down for scheduled maintenance
5. SQL DBA's forget to stop job
6. Job pages on-call (me)
7. Disable job until Oracle maintenance complete
8. Oracle maintenance complete.
9. Connectivity of linked server verified
10. Re-enable job
11. JOB Fails !
12. T-SQL Stored procedure runs from SSMS fine, but won't run as a job

Here is the poorly configured sql agent.  1st off the job output file is set to over-write, so know as I write this I can't quote the exact error, 2nd the system was set at the default job history retention, so I can't go into the job history to grab the error either.

Basically the error was something about reading a row from sysoledbservers or something.

The solution was to Stop and Start SQLAgent; now why SQLAgent, running tsql using a linked server would not work, when the sqm tsql would run just fine from SSMS is beyond me, which is why I wish i had the exact error, as I was going to call pss support, which i didn't do at 1am in the morning.

We have had this problem since SQL Server 2005, all versions, service packs and hotfixes.

A cursor of system databases that randomly skips / leaves out  certain databases depending on how the cursor is declared.

Generally we have maintenance jobs, custom (not maintenance plans), that declare a cursor and loop through each database to perform some task, dbcc, reindex / defragment, backup etc.

The syntax is generally:

DECLARE dbreindex_cursor CURSOR FOR
 SELECT name
 FROM master.sys.databases
 WHERE name NOT IN ('tempdb', 'model','master','msdb')
 AND State <> 1 --not being loaded/restored
 ORDER BY name

THIS IS NOT 100% RELIABLE !!! 

The only way we catch this is we have a job that tracks DBCC's and Backups and then a SSRS Report that shows any database without a dbcc or backup in the past 24 hours.  What we find is that occassionaly it will "skip" a database with the above syntax.  We can never reproduce it on demand, and the only way we can find it happenning is with this report.  Now we do have 100's of SQL Servers and only see it on one or two every couple weeks, but it is maddenning to trouble shooot.

Eventually we changed the cursor to use a different syntax, and it never has happenned on a server using the below syntax ..

DECLARE dbreindex_cursor CURSOR  Local Forward_Only STATIC READ_ONLY TYPE_WARNING FOR
 SELECT name
 FROM master.sys.databases
 WHERE name NOT IN ('tempdb', 'model','master','msdb')
 AND State <> 1 --not being loaded/restored
 ORDER BY name

I've hardle ever seen a refernce to this, we did let our MS Rep and TAM know, but since it is so rare and can not be reproduced never really could figure out what is wrong with the above cursor. 

I have seen one other reference with someone else experiencing the same thing, so I know we are not alone... http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/d4e6549cf10cfe0c/4211b0612a33368f?lnk=st&q=sql+server+2005+cursor+databases&rnum=3&hl=en#

Pay particular attention to how a cursor is declared in sql server 2005....something is more "sensitive"...

   Finally couldn't take the squeaking and hesitation of my 2000 ford windstar, so I got to it and replaced the serpentine belt.

After spending about 15 minutes to detemine the tensioner pulley was located on the very bottom, under the power steering pump (damn why didn't they put that up top ?).  This means I couldn't release the tension and pull the belt off (because I had too lay on my side and reach up under and through the passenger wheel well).  Called the wife and she pulled the belt off from the top.

Review of the belt shows wear, nothing overly wrong with it, but with 115K miles; it's time to get a new one.

Down to local FLAPS (Friendly local auto parts store), get talked into the $40 belt vs $20 (sucker !).

Installation goes as expected, not hard, just have to be patient and work the belt around it's appropriate places (course Ford couldn't be smart and put a picture of the serpentine diagram somewhere in the engine compartment, but I drew a picture before taking the old one off (see i am wiser), and the new belt came with a diagram too).

Getting the thing back on resulted in the same problem as getting it off, it just wasn't possible for me to grab the wrench and release the tension and put the belt back on (see description above). 

Call Wife, she tries to slip belt back on from top, but either doesn't have the hand strength or doesn't want to get pinched putting belt over idler pulley.  I talk her into laying on the ground and reaching up to through the wheel well to the wrench on tensioner pulley....we were done 2 minutes later !

Car is running great no hesitiation, less squeaking; can only assume the hesitation was caused by the belt slipping occasionally and causing the alternator to "jump".  Squeaking still makes let's me know that in the near future a water pump, alternator, power steer pump, tension or idler pulley will be calling out out to me, but that is expected with high mileage.

Total time, 2 hours (including run to FLAPS).

Current Fleet being maintained

69 VW Micro Bus - Sunroof

77 VW Micro Bus - Westfalia camper

2000 Windstar blue

2000 Windstar tan