November 2010 (1)
August 2010 (1)
July 2010 (1)
June 2010 (3)
July 2009 (3)
June 2009 (1)
May 2009 (1)
February 2009 (1)
January 2009 (1)
November 2008 (3)
October 2008 (4)
September 2008 (9)
August 2008 (6)
July 2008 (3)
June 2008 (3)
January 2008 (1)
November 2007 (2)
October 2007 (6)
September 2007 (5)
August 2007 (22)
July 2007 (6)
June 2007 (1)
May 2007 (3)
April 2007 (27)
March 2007 (8)
February 2007 (6)
September 2006 (2)
August 2006 (4)
July 2006 (9)
June 2006 (17)
May 2006 (20)
April 2006 (12)
March 2006 (9)
February 2006 (4)
January 2006 (3)
December 2005 (2)
November 2005 (4)
October 2005 (5)
September 2005 (37)
August 2005 (83)
July 2005 (6)

Active Directory / LDAP (0)
ASP.Net (19)
Blackberry Development (4)
c# (34)
c++ (3)
Code Camp (1)
Excel (1)
Exchange (3)
Front Page 2003 (6)
FTP User Editor (4)
HTML / CSS / DHTML (8)
IIS (146)
IIS - Log Parser (7)
IIS / FTP (12)
IIS / Tools / Administration (42)
IIS / Tools / Authentication (6)
IIS / Tools / Compression (8)
IIS / Tools / Crash & Hang (12)
IIS / Tools / ISAPI Filters (17)
IIS / Tools / Log Files (17)
IIS / Tools / Scripts (28)
IIS / Tools / Security (9)
IIS / Tools / SSL (6)
IIS 7 (3)
Internet Information Server (1)
Me (Chris Crowe) (6)
MIME Types (1)
Misc (72)
Oulook Express (2)
Silverlight (1)
SQL Server (27)
SQL Server CTE (1)
Vista (15)
Vista Gadgets (8)
Visual Studio (11)
Voice over BroadBand (1)
Windows (33)
Windows Powershell (3)
Windows Sharepoint Services (0)
Windows Sharepoint Services (15)
Windows Vista (14)
Wine Cellar (1)
WMI (8)

Archive

July 2009 (3)

Looking for some new sounds – checkout this freebee

Click to download new music, completely free, brought to you by your music loving friends at Windows. You'll see (but not hear) some ads on your screen in addition to the album's cover art, which is how we (they) keep your new tunes from costing you a cent.

Well worth a listen to checkout some new bands and artists….

Listen online, download MP3 or WMA

http://sponsoredsongs.reverbnation.com/windows?utm_source=windows_myspace_page&utm_medium=button_link&utm_campaign=ms_windows&utm_content=thousand_more


An great TSQL index defrag script

I have been doing some more looking at SQL Server indexes lately and came across a great script that may be of use to others as well.

So what can this index defrag script do?

  • Schedule it to run with the default settings; it works “right out of the box” with no additional configuration necessary
  • Run this one script from a centralized database for all databases on a server
  • Run this script for a specific database or table
  • Configure custom threshold limits and the point at which a rebuild should be performed (instead of a reorganize)
  • Defrag individual partitions
  • Log its actions and the duration of the defrag
  • Run in “commands only” mode (@executeSQL = 0, @printCommands = 1)
  • Customize performance parameters such as @maxDopRestriction and @defragDelay to minimize impact on the server
  • Schedule specific indexes to only be defragged on weekends, or every other day

The script source code (very well documented) and author (Michelle Ufford) can be found at http://sqlfool.com/2009/06/index-defrag-script-v30/


Some simple T-SQL code to strip time from a date and return other dates

declare @dt datetime
set @dt = getdate()

 

print 'Now'
print @dt
print ''



print 'Today - no time'
print dateadd(D, 0, datediff(D, 0, @dt))
print ''


print 'Today – 23:59:59.997'
print dateadd(ms,-3, dateadd(D, datediff(D, 0, @dt),1))
print ''



print 'Start of this week - Starts on MONDAY'
print dateadd(wk,datediff(wk,0, @dt),0)
print ''



print 'End of this week - Starts on MONDAY 23:59:59.997'
print dateadd(ms,-3, dateadd(wk,datediff(wk,0,@dt),7))
print ''


print 'Start of this week - Starts on SUNDAY'
print dateadd(wk,datediff(wk,0,getdate()),-1)
print ''



print 'End of this week - Starts on SUNDAY 23:59:59.997'
print dateadd(ms,-3,dateadd(wk,datediff(wk,0,@dt),5))
print ''



print 'Start of Month'
print dateadd(mm,datediff(mm,0,@dt),0)
print ''



print 'Last day of current Month - 23:59:59.997'
print dateadd(ms,-3, dateadd(mm,datediff(mm,0,@dt)+1,0))
print ''

 

Here is a simple break down to how these snippets actually work

print 'Today - no time'
print dateadd(D, 0, datediff(D, 0, @dt))
print ''

First off we return the number of days between the date/time passed and 0 so we get the number of whole days (we have lost the time)

Today is July 14, 2009 (as an example) which using this code

print datediff(D, 0, '2009-07-14 03:34:21')

This will return 40006

We then add 40006 days to 0 and this returns the date 2009-07-14.

print dateadd(D, 0, 40006)

Jul 14 2009 12:00AM

So adding it together we get

print dateadd(D, 0, datediff(D, 0, @dt))

 

You will notice that some of the functions have a –3 as part of the code, this is because we want to return the last actual time that SQL Server can reference on a particular day. SQL Server can store the maximum time as 23:59:59.997. That is because SQL Server is only accurate to 3 milliseconds. If we used 23:59:59.999 it would actually roll over to the next day.

So if we were wanting to return the last time for today’s date/time we can use the following

print DATEADD(ms, - 3, DATEADD(D, DATEDIFF(D, 0, GETDATE()), 1))