Loading

Tuesday, February 15, 2011

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

Monday, February 7, 2011

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.

Friday, February 4, 2011

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. . .