How to remove, delete or shrink Microsoft SQL Server Database Transaction Logs

A database in a SQL server typically has full recovery mode enabled, which creates a transaction log that grows over time. Unless a full SQL backup is made or you have set the growth limit of your SQL database transaction log, this file will continue to consume your storage space.   So what is the function of this file?  SQL Server’s database transaction log is useful for data recovery, as it allows a DBA to recover data  from a certain point of time (from the last time a full backup was made).  And in production and data critical systems, transaction logs are kept in separate partition of SAN and similar storage systems.   But not most database systems needs this function.  So I made this article if you ever find yourself  needing to delete, shrink or disable your database’s transaction log under Microsoft SQL Server

The Burden of Transaction Log Size
Some admins (including me) forgot about this transaction logs after a database for a certain application is created. Some application as well, that auto-creates the database for you typically leaves the recovery model to full recovery, and if you leave it  as it is, and don’t do a full backup regularly, this will grow over time and consume your partition space.

So how do you remove or shrink the size of your database’s transaction log?

1. The safest way is to do a FULL Backup.  This assumes that you still have remaining space to do so.  Doing a full backup is easy, follow this steps from Microsoft :  How to Backup a Database SQL Server (SQL Management Studio) or if you are using a dinosaur SQL Server, here’s the steps for SQL Server 2000.  After your full backup, your transaction log is reset.  If you want to completely disable transaction log, then follow the next step
2. To disable transaction logging, you need to convert recovery method from Full to Simple Recovery.   This is also one way to delete or remove a large transaction log without needing to do full backup (in case free space is a problem).
3.  Login to your server using SQL Server Management Studio.  Right click on the database  you’d like to turned off transaction logging then click properties.  Go to Options.  Change the Recovery Model from Full to Simple. Click Ok.
4. To change the recovery mode of SQL  in  SQL Server 2000, follow the steps  in Microsoft site:  SQL Server 2000 Backup Types and Recovery Models
5. Now we can shrink the transaction file log and recover the enormous disk space it uses .  While your in SQL Management Studio, right click the database you’ve just converted to Simple Recovery, then click Tasks > Shrink > Database.  Tick Reorganize files… then finally click OK.
6. And here’s a link for SQL Server 2000 users from Microsoft website:  How to Shrink a Database using Enterprise Manager

There you go.   With your SQL Server Recovery model set to Simple, your SQL Server’s disk IO is much less busy, without you worrying about the disk space in your server.   Still, a regular full backup is still the right way to go, in making sure the transaction log size is maintain.

How to cold boot, warm boot and hard reset a Motorola Symbol MC55

Motorola MC55 is a ruggedized Windows Mobile Pocket PC from Motorola-Symbol company (now Google-owned).   Typically these devices are used in enterprise applications and so is expensive.

From time to time and when application starts running slowly and crashing, it is good to reset the device.  There are three types of reset:

• Warm boot /reset  – similar to doing a shutdown > restart in your computer (or the infamous CTRL ALT DELETE in DOS and some Linux platform
• Cold boot / reset – a cold boot is similar to powering on your computer from a complete shutoff.
• Hard /Factory reset  – re-image the device to its primal state

How to do a warm reset to your MC55

Doing a warm reset is easy.  Just press and hold down the power button (red button) for at least 5 seconds and release it once MC55 reboots

Perform a cold reset in you MC55

The steps in doing a cold boot for MC55 depends  on the model.  If you have MC55 with the numeric keyboard, press these three button simultaneously:  1, 9 and power button

For MC55 with querty keyboard, the key combination to do a cold reset is Power button, W and C keys.  Press these three simultaneously and it will reset your MC55 device.

How to factory reset your MC55

There may be cases wherein you wanted to hard reset to factory default settings your MC55 (out-of-the-box state).  This erases current configuration and changes you have done to the device.

Do the following steps to factory reset your MC55

• Put the device in the cradle and make sure the USB is connected to your Windows Machine and power plug is also plugged.
• Download the reset software from symbol: https://docs.symbol.com/downloads/55xxw61xxxDBA00001.zip
• Extract 55xxw61xxxDBA00001.zip into a folder in your machine
• On your machine, navigate to your MC55’s my documents folder via the explore function in your ActiveSync (in case of Windows XP) or explore the file/folder from Windows Mobile Device Center (for Vista and W7)
• Copy the extracted files to the my documents folder of your MC5
• Using the File Explorer on your MC55, navigate to the My Documents folder (make sure the handheld is still in the cradle
• Tap and run StartUpdLdr.exe
• After 20 seconds or more, the device will execute an Update Loader boot which will cause the PKGS.LST file deposited on the device to be processed
• Once this is complete, you have a re-image / factory-reset mC55

Linksys WRT54G v7 + PSP wifi connectivity woes

Just bought the lousiest LinkSys WRT54G model which has firmware v7.00.01. Too bad retailers doesn’t allow you to open up the box unless you have bought it. This version uses Atheros chipset while all other version including v8 uses Broadcom. The hardware is a striped model and uses only half the memory of firmware v4 and below.

One incompatibility of its wirelesss connectivity is with PlayStation Portable (PSP) firmware version 3.71 (Other PSP *might* as well be compatible). Me and my cousin who own’s the PSP tried all combination to try to make it work. I later found out the there are issues on this specific Linksys WRT54G model. Checking Linksys main website (www.linksys.com) would not even tell you that this firmware exists (there are listing of version 1 to 8 but skipped on v7). Google intensive search functions help though, and I was able to find that this firmware exist on their UK domain (http://www-uk.linksys.com). Latest official firmware for v7 is v7.00.4 which fixes several issues on this WRT model including the PSP and the occasional drop of connection (every couple of hour or so).

Now we are able to connect the PSP with WPA-PSK (TKIP) mode. Linksys and Cisco quality product seems to be going down and down the drain with this specific new model.

Update:

For us unlucky home users of this version of WRT54G, a new firmware from Linksys has been release that fixes several issues more  specially torrent and dhcp disconnection which are common on Cable providers.  Firmware for Linksys WRT54G Model 7.00.8 is now version 7.00.8.  This maybe the best firmware for this specific model as dd-wrt and open-wrt is not compatible on this specific model.  This release fixes the following issues:

Firmware 7.00.8 (build 8)
- Resolves issue with not being able to block IPSec pass-through.
- Resolves issue with router disconnecting from several particular online game servers.
- Resolves issue with user not being able to get incoming video and audio when video phone is connected to the LAN hosted by the router.
- Resolves issue with WEP 64-bit key not being properly validated when user enters the key.
- Resolves issue with disabling UPnP not being effective until the router is rebooted.
- Resolves issue with Wireless MAC Filter not working properly when there are more than 32 entries in filter list.
- Better handling of TCP packet with invalid option field.
- Resolves issue with router intermittently losing internet connectivity (DHCP) with a particular ISP
- Updated TZO.com DDNS service support.

Windows XP / Server 2003 Installer cannot found any SATA hard drive

Newer motherboards with Intel and AMD-ATI chipset might have a default SATA mode called AHCI (Advanced Host Controller Interface.)

If you can’t seem to install Windows XP or 2003, with the error cannot found any hard drive, the easiest way is to disable the AHCI mode or switch to IDE SATA mode from the BIOS. However, if you insist to use the advance features of AHCI , then you need to load first the SATA controller driver when Windows installer prompts you.

For further info regarding AHCI Mode, here’s a quick info i’ve taken from this site => http://www.techarp.com/showFreeBOG.aspx?lang=0&bogno=316

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
AHCI is the acronym for the Advanced Host Controller Interface. It is a new interface specification that allows the SATA controller driver to support advanced features like Native Command Queuing and Hot Plug. This BIOS feature controls the AHCI function of the SATA controller.

When enabled, the SATA controller enables its AHCI features when the computer boots up.

When disabled, the SATA controller disables its AHCI functions when the computer boots up.

If you would like to make use of the SATA controller’s AHCI features, you should enable this BIOS feature. But please note that enabling this feature requires you to load the SATA controller driver during the Windows XP installation routine.

If you do not intend to use the AHCI features, it’s recommended that you disable this BIOS feature. This allows you to use the native Windows XP driver. You won’t need to load the SATA controller driver during the Windows XP installation routine.

Please note that changing this BIOS feature after installing the operating system may cause a boot failure. You may be required to reinstall the operating system.

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

Repair Volume Shadow Copy Service error in Windows

With the availability of virtual machines from VMWare, i finally found a tool to replicate the Volume Shadow Copy Service error problem that seem to haunt WIndows 2003 Servers and fix it. To all fellow admin with this problem and still doesn’t want to format their Windows Server, this is for you guys:

First, my advice is to try ALL you can find solution from MS website. You can do this by searching goggle with the site:microsoft.com keyword (e.g. search vss repair fix site:microsoft.com)

The VSS Problem cripples your NTBackup and Disk Management MMC, and other backup programs that rely on vss (like symantec veritas backup exec). To be sure this fix is applicable to you, kindly check your Disk Management by Right clicking on My Computer > click Manage > then go to Disk Management. An “unexpected error” popup will appear.

1. First you need a secondary domain controller. If you don’t have one yet, create one Windows Server 2003 virtual machine and promote it as domain controller.
2. If you already have one, transfer the Operation Master Role to the other domain controller (or the virtual machine you created after you dcpromo it as domain controller)
3. Now try to run ntbackup or start > run > cmd and execute from the console “vssadmin list writers” and it will now return proper list of writers unlike before which doesn’t

Based on my 1 month testing, the VSS errors are somehow connected to the trust of other domain controllers on your network. On my situation, I have a Linux Samba implementation that runs as domain master within the same network.

With this new analysis, i can somehow recommend to Windows Administrators to have one windows server virtual machine acting as writable domain controller within each domain you host in your network.  This will give you a safe option fixing creep-tic Active Directory and Windows domain problems that MS seems couldn’t properly fix.

Hope this helps.

Timestamp on Windows world

A colleague of mine asked me how to make a batch file script that will put time stamp on his file/s on a Windows environment. Given that this is easy in Linux systems, I was quite amused in doing it on a Windows system. Flashback of old school scripting in DOS era came as I try to recall the batch file commands and environment restrictions in Windows.

Finally, after about half an hour, I came up with a batch file script that will put date and time stamp to a file. Here’s the short batch file for it…. (copy-paste it on a text editor and save the file with .bat or .cmd extension)

@echo off
for /f "tokens=1-3 delims=:." %%a in ("%time%") do set timestamp=%%a%%b%%c
for /f "tokens=1-3 delims=/." %%a in ("%date%") do set datestamp=%%a%%b%%c
echo >"testfile-%datestamp%-%timestamp%".txt

This code can be integrated on other batch files to stamp log files or rename files with timestamp or datestamp information.

A Windows Admin nightmare

The past weekend is one of the most tiring i had for years. Staying late here in the office to fix what I think, one of the worst problem in Windows 2003. It’s none other than volume shadow copy services (vss) that’s been hunting Microsoft and the NT systems administrators for several years now. The vss error below makes your NTbackup program crippled (as well as other 3rd party backup program that relies on shadow copies to backup open files).

======================
 Event ID: 8194
 Description:
 Volume Shadow Copy Service error: Unexpected error querying for the IVssWriterCallback interface.  hr = 0x8007000e.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
 ======================
 Event ID: 12302
 Description:
 Volume Shadow Copy Service error: An internal inconsistency was detected in trying to contact shadow copy service writers.  Please check to see that the Event Service and Volume Shadow Copy Service are operating properly.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
======================
This is the first time I exhausted the power of google trying to find a working solution. There were a lot of unresolved posts about the same problem; One of the concern is that this started form Windows 2003 but even the latest Vista suffers from the same problem.

I’ve tried most of the like-able solutions suggested by Microsoft Engineers and other professionals but those fixes were unable to repair the problem. The only solution that came to me as i have no other options anymore, was to do a repair-installation (not clean installation) on my Windows 2003 PDC. But that damn idea came up after 3 tiring nights of trying to solve the problem.

Well, hopefully, by tomorrow, the helpdesk team will no longer report another backup failure.