Truncate (or Purge) Transaction Log in SQL 2008

Over the weekend one of our critical servers ran out of disk space on the volume which contains the transaction logs & all hell broke loose.  Our DBA was at a wedding and couldn't be reached so it was up to me to get things working ASAP.  I've truncated logs before in SQL 2005 and SQL 2000 (I've even written about it on this blog) with the following:

BACKUP LOG <DataBase_Name>
WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (2,1, TRUNCATEONLY)
GO

However, our dear friends at Microsoft have removed TRUNCATEONLY from SQL 2008. Searching the 'net I found a few fragmented suggestions. But the one that was the bomb and corrected things for me right away was this great post by Otto R. Radke.  There is only one item that has to be customized (highlighted) with the DB name and voila, it fixed by problem by truncating my huge transaction log.  Thanks Otto!

------------------------------------------------------------------------------
-- Otto R. Radke - http://ottoradke.com
-- Info: T-SQL script to shrink a database's transaction log. Just set the
-- database name below and run the script and it will shrink the
-- transaction log.
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- Update the line below with the name of the database who's transaction
-- log you want to shrink.
------------------------------------------------------------------------------
USE <YourDatabaseName>
------------------------------------------------------------------------------
-- Don't change anything below this line.
------------------------------------------------------------------------------
GO
-- Declare variables
DECLARE @SqlStatement as nvarchar(max)
DECLARE @LogFileLogicalName as sysname
-- Alter the database to simple recovery
SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY SIMPLE'
EXEC ( @SqlStatement )
-- Make sure it has been altered
SELECT [name], [recovery_model_desc] FROM sys.databases WHERE [name] = DB_NAME()
-- Set the log file name variable
SELECT @LogFileLogicalName = [Name] FROM sys.database_files WHERE type = 1
-- Shrink the logfile
DBCC Shrinkfile(@LogFileLogicalName, 1)
-- Alter the database back to FULL
SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY FULL'
EXEC ( @SqlStatement )
-- Make sure it has been changed back to full
SET @SqlStatement = 'SELECT [name], [recovery_model_desc] FROM ' + DB_NAME() + '.sys.databases WHERE [name] = ''' + DB_NAME() + ''''
EXEC ( @SqlStatement )
------------------------------------------------------------------------------

Windows 7 RUNDLL32 Shortcuts

Here is a good list of RUNDLL32 shortcuts / commands that can be used in Windows 7 and Windows 2008 (most also work in Vista and Windows 2003).

Add/Remove Programs

RunDll32.exe shell32.dll,Control_RunDLL appwiz.cpl,,0

Content Advisor

RunDll32.exe msrating.dll,RatingSetupUI

Control Panel

RunDll32.exe shell32.dll,Control_RunDLL

Date and Time Properties

RunDll32.exe shell32.dll,Control_RunDLL timedate.cpl

Device Manager

RunDll32.exe devmgr.dll DeviceManager_Execute

Folder Options – Opens to General Tab

RunDll32.exe shell32.dll,Options_RunDLL 0

Forgotten Password Wizard (requires removable disk)

RunDll32.exe keymgr.dll,PRShowSaveWizardExW

Hibernate

RunDll32.exe powrprof.dll,SetSuspendState

Keyboard Properties

RunDll32.exe shell32.dll,Control_RunDLL main.cpl @1

Lock Screen

RunDll32.exe user32.dll,LockWorkStation

Mouse Properties

RunDll32.exe shell32.dll,Control_RunDLL main.cpl @0

Map Network Drive

RunDll32.exe shell32.dll,SHHelpShortcuts_RunDLL Connect

Network Connections

RunDll32.exe shell32.dll,Control_RunDLL ncpa.cpl

Power Options

RunDll32.exe Shell32.dll,Control_RunDLL powercfg.cpl

Regional Settings

RunDll32.exe shell32.dll,Control_RunDLL intl.cpl,,3

Stored Usernames and Passwords

RunDll32.exe keymgr.dll,KRShowKeyMgr

System Properties: Advanced

RunDll32.exe shell32.dll,Control_RunDLL sysdm.cpl,,4

Taskbar Properties

RunDll32.exe shell32.dll,Options_RunDLL 1

User Accounts

RunDll32.exe shell32.dll,Control_RunDLL nusrmgr.cpl

Windows Security Center (Windows 7 only)

RunDll32.exe shell32.dll,Control_RunDLL wscui.cpl

Windows – About

RunDll32.exe SHELL32.DLL,ShellAboutW

Unplug/Eject Hardware

RunDll32.exe shell32.dll,Control_RunDLL hotplug.dll

Windows Firewall

RunDll32.exe shell32.dll,Control_RunDLL firewall.cpl

Wireless Network Setup

RunDll32.exe shell32.dll,Control_RunDLL NetSetup.cpl,@0,WNSW

List of Windows 7 Shell Commands

Windows 7, Windows 2008 (including R2) and Windows Vista shell commands. These can be executed from the "run" command, the "search programs and files" dialog or straight in the address field of Windows Explorer (not Internet Explorer).

shell:AddNewProgramsFolder
shell:Administrative Tools
shell:AppData
shell:AppUpdatesFolder
shell:CD Burning
shell:CSCFolder
shell:Cache
shell:ChangeRemoveProgramsFolder
shell:Common Administrative Tools
shell:Common AppData
shell:Common Desktop
shell:Common Documents
shell:CommonDownloads
shell:CommonMusic
shell:CommonPictures
shell:Common Programs
shell:Common Start Menu
shell:Common Startup
shell:Common Templates
shell:CommonVideo
shell:ConflictFolder
shell:ConnectionsFolder
shell:Contacts
shell:ControlPanelFolder
shell:Cookies
shell:CredentialManager
shell:CryptoKeys
shell:Default Gadgets
shell:Desktop
shell:Downloads
shell:DpapiKeys
shell:Favorites
shell:Fonts
shell:Gadgets
shell:GameTasks
shell:Games
shell:History
shell:InternetFolder
shell:Links
shell:Local AppData
shell:LocalAppDataLow
shell:LocalizedResourcesDir
shell:MAPIFolder
shell:MyComputerFolder
shell:My Music
shell:My Pictures
shell:My Video
shell:NetHood
shell:NetworkPlacesFolder
shell:OEM Links
shell:Original Images
shell:Personal
shell:PhotoAlbums
shell:Playlists
shell:PrintHood
shell:PrintersFolder
shell:Profile
shell:ProgramFiles
shell:ProgramFilesCommon
shell:ProgramFilesCommonX86
shell:ProgramFilesX86
shell:Programs
shell:Public
shell:PublicGameTasks
shell:Quick Launch
shell:Recent
shell:RecycleBinFolder
shell:ResourceDir
shell:SampleMusic
shell:SamplePictures
shell:SamplePlaylists
shell:SampleVideos
shell:SavedGames
shell:SearchHomeFolder
shell:Searches
shell:SendTo
shell:Start Menu
shell:Startup
shell:SyncCenterFolder
shell:SyncResultsFolder
shell:SyncSetupFolder
shell:System
shell:SystemCertificates
shell:SystemX86
shell:Templates
shell:TreePropertiesFolder
shell:UserProfiles
shell:UsersFilesFolder
shell:Windows

Bon Jovi Live in Concert

Watch live streaming video of Bon Jovi concert in State College, PA this Wednesday night at 8:30 PM EST, right here!  FREE.

Smart Phones Directly Affect Bathroom Habits

With the proliferation of smartphones over the past few years time spent in the, ah, rest room has greatly increased.  My wife and kids don't understand this but I'm sure all the men will relate. . .

Amazon Simple Email Service on Windows - How To

Today Amazon announced AWS Simple Email Service (SES) beta.  SES is a great addition to Amazon Web Services, with it an organization can send marketing, transactional, subscription or system notification messages without have to manage their own messaging infrastructure.  And just like all other AWS services it's pay-as-you-go - even free in some instances.

Soon as I received the announcement from Amazon I immediately set out to give it a try.  Since I normally use Windows that's what I used for this.  I ran into a couple of minor problems but was easily able to resolve them and now I'm sending email through SES like a mad man. . .

Summary - Quick Steps
If you're anxious to get started and don't want any fluff here you go.  For everyone else read below.

• Sign-up for SES
• Install Perl
• Install XML-LibXML via Perl Package Manager (instructions below)
• Download AWS SES scripts
• Setup "credentials" file (see below for details)
• Get verified: ses-verify-email-address.pl -k creds.txt -v test@powercram.com
• Send email: ses-send-email.pl -k creds.txt -s "Test One" -f test@powercram.com test@powercram.com

Prerequisites
Sign-up for SES.  If you already have an AWS account you're one step closer, if not you'll have to start there.

SES uses Perl scripts, therefore you must have Perl installed on your Windows machine.  I installed ActivePerl Community Edition.  Soon as that was done I ran the first SES script to get an email address verified but ran into my first problem - c:\perl\bin didn't get added to my path by the installation (for some reason it added c:\perl\site\bin) so I had to do that manually (if you don't know how to do this you probably shouldn't be here anyway. . . )

Amazon Simple Email Service Scripts
Download the SES scripts and save to a useful location - I used d:\aws\ses.  This download includes the following SES scripts:

• ses-get-stats.pl - retrieves statistics about Amazon SES account usage
• ses-send-email.pl - send email using Amazon SES
• ses-verify-email-addresses.pl - verify email addresses to be used with Amazon SES

Next problem - when I ran ses-verify-email-address.pl --help I received the message: "Can't locate XML/LibXML.pm in @INC (@INC contains: C:/Perl/site/lib C:/Perl/lib.) at D:\AWS\SES\ses-verify-email-address.pl line 26.  BEGIN failed--compilation aborted at D:\AWS\SES\ses-verify-email-address.pl line 26."

After tooling around the web for a few minutes I realized I needed to manually install XML-LibXML.  This was easily done by opening the Perl Package Manager off the Start menu.  In there:

• Click the "View All Packages" button or press Ctrl+1
• Type libxml in the search box
• Right-click on XML-LibXML and select Install . . .
• Click File, Run Marked Actions or press Ctrl+Enter

Now when you run ses-verify-email-address.pl --help from the command line you can actually see the help for this command.

Credentials File for SES Commands
All of the SES scripts or commands require authentication to AWS, which makes sense.  You can save your credentials (both AWSAccessKeyId and AWSSecretKey) to a file, creds.txt and pass that to each script with the -k <filename> command.

Example credentials file:

AWSAccessKeyId=022QF06E7MXBSH9DHM02

AWSSecretKey=kWcrlUX5JEDGM/LtmEENI/aVmYvHNif5zB+d9+ct

Verify Email Address for SES
In order to send any email you must add & verify at least one email address to which you have access.  The verification is a two-step process.  First run the command: ses-verify-email-address.pl -k creds.txt -v test@powercram.com.  Amazon will send an email to that address with a link to click to finalize verification.

NOTE: Until you are granted production access to Amazon Simple Email Service you can only send to verified addresses. Click to Request for Production Access to Simple Email Service.  They say it may take up to 24 hours, but my access request was granted in about 30 minutes - now I can send to any address!


View List of Verified SES Addresses
By running the command ses-verify-email-address.pl -k creds.txt -l you are supposed to be able to view a list of verified addresses, however when I run this nothing is returned.  I did discover though that using the --verbose switch, ses-verify-email-address.pl -k creds.txt -l --verbose, I am able to view a list of verified addresses:

<listverifiedemailaddressesresponse xmlns="http://ses.amazonaws.com/doc/2010-12</p><p>01/">
 </listverifiedemailaddressesresponse>
<listverifiedemailaddressesresult></listverifiedemailaddressesresult>
    <verifiedemailaddresses></verifiedemailaddresses>
      <member>test@powercram.com</member>
 <responsemetadata></responsemetadata>



Send Email With SES
Finally we are ready to send email using the command ses-send-email.pl -k creds.txt -s "Test One" -f test@powercram.com test@powercram.com.  Press Enter then you can type the body of your message.  When that's done press CTRL+Z, then Enter.  It will hesitate for a second or two while your credentials, etc. are verified and the message is queued.  Finally, check your email.

Summary
Amazon Simple Email Service looks to be a very useful tool and is a great addition to Amazon Web Services.  In just a few minutes you can be sending email through SES, and it can be adapted for myriad purposes.  Get started today and start emailing (useful messages - NO SPAM or other crap!).

Additional Info

• SES Getting Started Guide
• Amazon Simple Email Service Developer Resources, which includes details about the service and info for each command.

See Also

• Ping Amazon EC2 Instances - How To
• Maximum Drives For Amazon EC2 Instances
  
• Amazon S3 Command Line Utilities for Windows

Roboplow - Awesome robot snowplow

This is how snow should be plowed!

Dude Builds Snow Plowing Robot - Watch more

GNUWin32 Grep for Windows

Grep for Windows 7, Windows 2008, etc.

Grep searches one or more input files for lines containing a match to a specified pattern. By default, grep prints the matching lines.

The general synopsis of the grep command line is

grep options pattern input_file_names

Grep for Windows examples


Download GNUWin32 Grep.

1.78 Million Facebook Users May Die This Year (2011)

Does Facebook really have more than 500 million "active" users?

According to data from digital-legacy planning firm Entrustet, a big portion of these users will soon no longer be considered "active," by any reasonable measure. The company compared Facebook usership data with average death rates from the Center for Disease Control, and discovered that this year, around 480,000 Facebook users may pass away in the U.S., and 1.78 million worldwide. These figures are only likely to grow year to year, especially as Facebook expands pass the 600 million user mark.



So many profiles now fall out of the range of an "active" user. Beyond the deceased, there are endless duplicate accounts--Entrustet found that 150% of 20- to 24-year-olds in the U.S. are on Facebook--not exactly a plausible tally. What's more, a recent Gartner report estimated that in the coming years, roughly one in every ten of your friends on Facebook and other social networks will be nonhuman, meaning they'll be "social bots," automated profiles created by brands and organizations to engage consumers.

Read entire article.

My Favorite Firefox Add-ons

While there are thousands of Firefox add-ons there are a few I can't live without; in fact, I install most of these on almost every computer I touch.

• Video DownloadHelper - DownloadHelper is a tool for web content extraction. Its purpose is to capture video and image files from many sites.  Very useful for downloading videos from YouTube or other video sites.
• Elasticfox Firefox Extension for Amazon EC2 - ElasticFox is a Mozilla Firefox extension for managing your Amazon EC2 account. Launch new instances, mount Elastic Block Storage volumes, map Elastic IP addresses, and more. NOTE: Elasticfox is no longer officially supported by Amazon.  But, thanks to Genki Sugawara Elasticfox-ec2tag lives on and is actively developed.
• New Tab Homepage - A very simple tabbed browsing extension that loads your homepage when you open a new tab. If you have multiple homepages, the first is chosen.
• Session Manager - Session Manager saves and restores the state of all windows - either when you want it or automatically at startup and after crashes. It can also automatically save the state of open windows individually.
• Tree Style Tab - This provides tree-style tab bar, like a folder tree of Windows Explorer. New tabs opened from links (or etc.) are automatically attached to the current tab.
• Web Developer - The Web Developer extension adds various web developer tools to a browser.

Honorable mentions.  These are other great Firefox add-ons.

• Firesheep - A Firefox extension that demonstrates HTTP session hijacking attacks.
• Live HTTP Headers - View HTTP headers of a page and while browsing.
• MeasureIt - Draw a ruler across any webpage to check the width, height, or alignment of page elements in pixels.
• Tab Slideshow - Cycles through tabs as a slideshow.
• Sothink Web Video Downloader for Firefox - A simple, clean and easy-to-use FREE Firefox extension for video download and Flash download. Supports Windows/Mac/Linux operating systems and the latest Mozilla Firefox 3.
• User Agent Switcher - The User Agent Switcher extension adds a menu and a toolbar button to switch the user agent of a browser.