Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Thursday, January 29, 2009

Mountpoints are fun and easily can solve issues with more than 26 drive letters (don't ask), but recently we ran into several issues with monitoring the mountpoints.  In particular we have a report that is based on past growth history of a database, disk size and disk free space, and it estimates when a drive will be at 80% capacity and when it will fill up.   With 100's of database servers this report can prioritize and pro-actively identify which server will encounter a problem next and when.  Of course the limitation is in bold, drive. 

Report Example:

When we began using Mountpoints the report was not as accurate and it needed to be adjusted.  We have some internal services that collect the drive size and free space to a central DBA database.  Review of this monitoring reveals it is using a WMI Query, a quick review of the WMI SDK shows another call that will pick up mountpoints, Select * from WIN32_Volume.  Life is good.

Not so quick after hurdling from drives to drives and mountpoints, a problem was revealed where the WMI call failed on two servers.  Englightenment.  These two sql servers have also been giving us odd issues with SQL Management Studio (SSMS), which is highly dependent on WMI, SMO, .NET and probably some other stuff.  Fixing WMI on these two servers fixed the issue and corrected the SSMS issues.

Steps to fix WMI (Thanks to our Windows Team for the steps below):

1. net stop winmgmt
2. del %SystemRoot%\System32\WBEM\Repository /s /q
If that does not work, then I
1. remove all rights from %system32%\wbem\Repository\FS
2. disable the "Windows Management Instrumentation" service
3. reboot
4. add rights back to %system32%\wbem\Repository\FS
5. delete the contents of %system32%\wbem\Repository\FS
6. set the "Windows Management Instrumentation" service back to Automatic
7. start the "Windows Management Instrumentation" service

Here is a short quick Visual Basic Script (VBS) you can save to a text file with a .VBS Extension to see the call to WMI to check disk space for mount points or drives.  It filters out certain mount points for Polyserve as we don't want to monitor those, also for some reason z:\ is mapped in our environment and this wmi script pulls that with nulls, so you need to test for those.  You can also use Powershell, but it is using a wmi call underneath the hood as well, and we have yet to install powershell on all our servers.

Set DiskSet = GetObject("winmgmts:{impersonationLevel=impersonate}!//BCCMAPP02")_
    .ExecQuery("Select * from Win32_Volume")
For Each objItem In DiskSet
    Ignore = False
    if len(objitem.name) >= 51 then
        If UCase(Left(objitem.name,51)) = _
            UCase("C:\Program Files\PolyServe\MatrixServer\conf\mounts") Then
            Ignore = True
        End If
    End If

    If Ignore = False Then
        msgbox(objItem.Name & vbCrLf & "Percent Free = " & _
            round((objItem.freespace/objItem.Capacity)*100,2) & _
            " = " & objItem.freeSpace & " = " & objItem.Capacity)
    End If

Thursday, January 29, 2009 12:33:07 PM (Central Standard Time, UTC-06:00) |  | General Technology | Polyserve | SQL Server#
Saturday, January 24, 2009

Never enough beer, never enough time.

Now is the time to start thinking about brewing for spring and summer.

While not traditional spring or summer beers, i had to have some Animal Stout and Dust Bunny Ale.

Easy to make and quick to brew, should be ready in 4-6 weeks.



Saturday, January 24, 2009 9:04:17 AM (Central Standard Time, UTC-06:00) |  |  Beer #
Monday, January 19, 2009

The server could not load DCOM. [SQLSTATE 42000] (Error 7404).  The step failed.

Began receiving this error from failed sql server jobs on a sql server 2000 box and a windows 2000 box, after a reboot.

We didn't spend too much time researching the issue, as we were still in a maintenance window, so we performed another reboot and things worked fine.

I've seen many dcom errors over the years, but not this particular one from SQL Server, most likely com+ or ms dtc sub-systems (services) did not start or failed to start on the reboot.  There were no system or event log entries that were immediately helpful, though there were other odd entries that seemed related to dcom not working:

The server {73E709EA-5D93-4B2E-BBB0-99B7938DA9E4} did not register with DCOM within the required timeout.

The NetBackup SAN Client Fibre Transport Service service failed to start due to the following error:

The service did not respond to the start or control request in a timely fashion.

Timeout (30000 milliseconds) waiting for the NetBackup SAN Client Fibre Transport Service service to connect.

BMSS failed to bind to a network interface card (NIC) because none was specified in the registry. Check the registry to make sure the network bind policy is correct.

Monday, January 19, 2009 8:50:57 AM (Central Standard Time, UTC-06:00) |  | General Technology | SQL Server#
Wednesday, January 14, 2009

Altering a table column from varchar to datetime is pretty straight-forward in the SQL Server Management Studio (SSMS), until you look at the tsql generated.  For many operations SSMS will generate a tsql script that will:

  • create a temporary table
  • drop all the foreign keys
  • copy the data to the temporary table
  • create the new table with the correct data type
  • copy the data to the new table
  • drop the temporary table
  • add the foreign keys back

That is a lot of operations and on a really large table of millions of rows may take a very long time to complete.

SQLCricket comments that it is possible to change the options in SSMS to warn on table operations, i think this is only in sql 2K8.

SQLPuma comments that it is possible to change a varchar() to a datetime via tsql with an alter table alter column command.

In this particular case we were modifying a varchar(10) to a datetime.  All the data was in a valid format.  The easiest method is to alter the column, another method to complete this is to:

  • rename the existing column (tmp_varchar etc)
  • add a new column with the correct name
  • update the new column (in batches if necessary)
  • drop the original column (now with a tmp_name)

This is very quick, much safer operation and is much "nicer" to the database log file.  Example of tsql is below:

CREATE TABLE dbo.testing
(test_id bigint NOT NULL IDENTITY (1, 1) primary key,
some_dt varchar(10)

insert into testing (some_dt) values ('2009-01-01')
insert into testing (some_dt) values ('2009-01-02')
insert into testing (some_dt) values ('2009-01-03')
insert into testing (some_dt) values ('2009-01-04')

EXECUTE sp_rename 'dbo.testing.some_dt', 
    'Tmp_some_dt', 'COLUMN' 

Alter Table testing
Add some_dt DateTime Default('1900-01-01') NOT NULL

Update testing
    Set some_dt = Convert(DateTime,Tmp_some_dt)

Alter Table testing
    DROP Column Tmp_some_dt

select * from testing

--drop table testing
Wednesday, January 14, 2009 9:13:13 AM (Central Standard Time, UTC-06:00) |  | SQL Server |  SSMS#
Tuesday, January 06, 2009

I twisted my head around a select statement that wasn't returning what I wanted.  Eventually I focused in on the column list, that had column names with spaces, so it required using brackets [].  These brackets when mixed in the column list can lead to tsql parsing that is not expected.  In this case I mixed some columns with brackets and some without, ultimately leaving a comma off.

You'd expect the tsql to not compile, but it does and runs with no errors.  Once tsql encounters a column list with brackets, it parses based off the brackets and commas ignoring the additional field i wanted to include in the select list..


create table #testTable     (tableid int identity(1,1) primary key,         [login name] varchar(20),         [is_password_validation] int,         [is_password_expiration] int,         [login type] varchar(20)) Insert into #testTable ([login name],[is_password_validation],     [is_password_expiration],[login type] ) values ('User 1',0,0,'AD') Insert into #testTable ([login name],[is_password_validation],     [is_password_expiration],[login type] ) values ('User 2',0,0,'AD') Insert into #testTable ([login name],[is_password_validation],     [is_password_expiration],[login type] ) values ('User 3',0,0,'AD') Insert into #testTable ([login name],[is_password_validation],     [is_password_expiration],[login type] ) values ('User 4',1,1,'SQL') Insert into #testTable ([login name],[is_password_validation],     [is_password_expiration],[login type] ) values ('User 5',0,1,'SQL') select * from #testTable -- good select [login name],     is_password_validation -- NOTICE THE MISSING COMMA     [login type]     From #testTable
Tuesday, January 06, 2009 3:33:54 PM (Central Standard Time, UTC-06:00) |  | SQL Server#

UPDATE - 01/29/2009 - I just received a solution to this issue, that does not require a temp table, which was my wimpy solution, thanks to Shawn who left this great solution in the comments:

Select * 
    From #checkDates
    where convert(datetime,case isdate(rptDate) when 1 then rptDate else null end)
        '02/27/2009' and '02/28/2009'

The isdate() function does not work properly (or at least as I expect it would) when it is used inside a derived table or a Common Table Expression.  This is really frustruating as it changes the way I think of derived tables and CTE's.  I always assumed that the inner query or CTE was resolved first, but if you follow the TSQL below, you can see that is not the case.

This may affect other functions, isnumeric() or even custom functions, so test them carefully.

I was working on a report issue that was generating an invalid derived date from a varchar, 02/29/2009.  Very simple to filter out, but due to the way the where clause is resolved / short circuited in tsql, it was going to be necessary to use a derived table or a CTE, but neither would work.  I ended up resorting to using an additional temporary table (wimpy, wimpy), I don't like this solution and would like to find one that does not require the temporary table.

Here is the problem:

create table #checkDates
    (chkDtId int identity(1,1) primary key,
        rptDate varchar(10))

Insert into #checkDates (rptDate) values ('02/26/2009')
Insert into #checkDates (rptDate) values ('02/27/2009')
Insert into #checkDates (rptDate) values ('02/28/2009')
Insert into #checkDates (rptDate) values ('02/29/2009')

-- This fails below with invalid date
Select * 
    From #checkDates
    where convert(datetime,rptDate) Between
        '02/27/2009' and '02/28/2009'

-- put the isdate function as part of where clause
-- select statement still fails
Select * 
    From #checkDates
    where isdate(rptDate) = 1 and
        convert(datetime,rptDate) Between
        '02/27/2009' and '02/28/2009'

-- switch to a derived table, the thought being to
-- remove the invalid dates from the inner select 
-- somehow in my mind i think the inner query should be resolved first,
-- but this is not the case !
Select * from 
    ( Select * 
    From #checkDates
    where isdate(rptDate) = 1 ) x
    where convert(datetime,rptDate) Between
        '02/27/2009' and '02/28/2009'

-- wimpy solution is to select the "good" records into 
-- a temp table and then perform the next select, yuck
Select * into #InterimResults 
    From #checkDates
    where isdate(rptDate) = 1 

Select * from #InterimResults
    where convert(datetime,rptDate) Between
        '02/27/2009' and '02/28/2009'
WITH goodDate_CTE AS (
    Select * 
    From #checkDates
    where isdate(rptDate) = 1 
FROM goodDate_CTE
where convert(datetime,rptDate) Between
        '02/27/2009' and '02/28/2009'
Tuesday, January 06, 2009 3:24:16 PM (Central Standard Time, UTC-06:00) |  | SQL Server#

Recently ran into an issue with a linked server between sql server and an as400, using the iSeries access driver via ODBC. 

The linked server was not returning all the rows and it was not throwing any errors either.

There is an advanced settings option on the iSeries as400 setup that needs to be "unchecked", by default the option is checked and can lead to missing records.

Once it is unchecked, the query returns the correct counts or rows.

Tuesday, January 06, 2009 10:00:00 AM (Central Standard Time, UTC-06:00) |  | SQL Server |  Linked Server#

Where did the add remove programs go in windows 2008 ?

Of course, it was renamed to "Programs and Features", still under control panel.

Also from Start --> Run --> appwiz.cpl


Tuesday, January 06, 2009 7:46:43 AM (Central Standard Time, UTC-06:00) |  | General Technology#
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Agent will not start when a use...
SQL Instance will not fail back to ...
Hash Join vs. Merge Join
Recent Posts
May, 2019 (1)
April, 2019 (1)
March, 2019 (1)
February, 2019 (1)
January, 2019 (1)
December, 2018 (1)
November, 2018 (3)
October, 2018 (1)
June, 2018 (1)
May, 2018 (3)
April, 2018 (2)
February, 2018 (3)
January, 2018 (3)
November, 2017 (1)
October, 2017 (1)
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Admin Login
Sign In