Manually trucate and shrink the transaction log for SQL 2005

In some situation, the transaction log grow too big until the harddisk space become unavailable and backup cannot be performed to truncate the log immediately.

 

How to truncate and shrink the transaction log manually?

Run the below 2 SQL query:

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

use databasename
DBCC LOGINFO('databasename')
go

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

Note down the fileID
==========================
Use databasename
DBCC shrinkdatabase (N'databasename', fileid, truncateonly)
Backup LOG databasename WITH TRUNCATE_ONLY
DBCC shrinkdatabase (N'databasename', fileid, truncateonly)
GO
========================================

 

Please perform full database backup immediately after the above SQL statement.

 

Deal with the heart of NetBackup (get into the castle)

I hope that is not too long to continue my NetBackup journey. Before I tell you about how I get into the castle, I need to SPECIAL THANKS to my brother, wolf. (because he is the first warrior to get into this virgin place, lol)

After the journey begun, I was not too long to say bye to my different mother's brother. Ha, so inside the "SQL anywhere" box, we need to take out the "Sybase Central". Look at these figures, aware of the redbox and advise. Don't blame me if you going too fast and bang the wall without reading them.

Deal with the heart of NetBackup (a journey begin)

NetBackup uses ASA (Adaptive Sybase Anywhere) Database since version 6.0. It actually consolidates all the media, server, volumes information into a centralized place called EMM (Enteprise media manager) server.
It actually ease the administrator daily task and no longer required to maintain a whole list of media servers.

However, Symantec remains it as a myth to all the users that how it actually contains in the EMM database. Not even a document to show you this. I did look help from my different mother's brother (google) and he seems blur too. For every page that related to modify the EMM database, it will ask you to consult the symantec PS team (luckily it is dissolved). hahaha!

So.... first thing first. By understand this "unexplored" part, you must know how to open the door first.
Firstly you need to equip yourself with a weapon called "SQL anywhere". I sure your different mother's brother can help you much on this.
Secondly, go into your NetBackup Master server and look for a file called "server.conf". A parameter called "Local only" and set it to "NO". Then you already very near to enter this new world!!

How to create solaris disk slices corresponding to the Veritas volume manager subdisks.

For the users who deal with Solaris and Veritas VxVM, I believe this is important to know of and lots of people will miss out this steps. For miror the OS disk under VxVM control, we will first encapsulate the boot disk and this will preserve the Sun partition as it is then create the private & public region accordingly.
Consequently, we will initialize another disk using VxVM then use vxrootmir to mirror the root (/) partition. Then use vxassist mirror to mirror the rest of OS partition (var, usr, opt, etc).
Looks perfect? but it actually misses out one steps. Where?

When you use vxassist mirror, VxVM will only create the respective subdisk in public region and update the records in private region. Hence no Sun partition will actually be created (E.g. slice 5, 6, 7). This will prevent the Solaris to mount back the original OS partition when disaster happen and you can't boot up the OS with VxVM startup. Even you revert back the /etc/vfstab.prevm & old /etc/system, it still unbootable because the SUN partition slices are not exists. For the primary copy of OS disk, it doesn't have this problem because we run it using encapsulation.

vxmksdpart [-f] [-g diskgroup] subdisk sliceno [tags flags]

According to the fmthard manual, the tags and flas are as below:

tag       description

0x00 UNASSIGNED
0x01 BOOT
0x02 ROOT
0x03 SWAP
0x04 USR
0x05 BACKUP
0x06 STAND
0x07 VAR
0x08 HOME

flag      description
0x00 MOUNTABLE
0x01 UNMOUNTABLE
0x10 READ-ONLY

Assume we have below config, root partition at s0, swap at s1 and var at s4. No need to run for root partition because vxrootmir will create the slice 0.
dg rootdg default default 0 1036078650.1025.pegasus
dm rootdisk c0t0d0s2 sliced 6463 17683680 -
dm rootmirror c0t1d0s2 sliced 6463 17683680 -

v rootvol - ENABLED ACTIVE 13423200 ROUND - root
pl rootvol-01 rootvol ENABLED ACTIVE 13423200 CONCAT - RW
sd rootdisk-01 rootvol-01 rootdisk 0 13423200 0 c0t0d0 ENA
pl rootvol-02 rootvol ENABLED ACTIVE 13423200 CONCAT - RW
sd rootmirror-01 rootvol-02 rootmirror 0 13423200 0 c0t1d0 ENA

v swapvol - ENABLED ACTIVE 2100000 ROUND - swap
pl swapvol-01 swapvol ENABLED ACTIVE 2100000 CONCAT - RW
sd rootdisk-02 swapvol-01 rootdisk 13423200 2100000 0 c0t0d0 ENA
pl swapvol-02 swapvol ENABLED ACTIVE 2100000 CONCAT - RW
sd rootmirror-02 swapvol-02 rootmirror 13423200 2100000 0 c0t1d0 ENA

v var - ENABLED ACTIVE 2100000 ROUND - fsgen
pl var-01 var ENABLED ACTIVE 2100000 CONCAT - RW
sd rootdisk-03 var-01 rootdisk 15523200 2100000 0 c0t0d0 ENA
pl var-02 var ENABLED ACTIVE 2100000 CONCAT - RW
sd rootmirror-03 var-02 rootmirror 15523200 2100000 0 c0t1d0 ENA

First create the swap on the secondary disk (e.g. c0t1d0),
# /usr/lib/vxvm/bin/vxmksdpart rootmirror-02 1 0x03 0x01
 
Then create the var on the secondary disk,
# /usr/lib/vxvm/bin/vxmksdpart rootmirror-03 4 0x07 0x00
 
At this point of time, you have the slices created. You may also want to update the changes on /etc/vfstab and /etc/vfstab.prevm.
 
# /usr/lib/vxvm/bin/vxprtvtoc -f /tmp/v1  /dev/rdsk/c0t1d0s2
# cat /tmp/v1 > /etc/vtoc

How to salvage your CVM cluster node if one node is crash?

Background: This is the real experience that the node is crashed and unbootable after the kernel patch activity. Unfortunately, no backup is performed and no mirror is break before the activity. This cluster is running Veritas VCS and CVM for NFS file sharing purpose.

Solution overview : Clone the OS disk from working node and modified the configuration to revert back the host information.

Detailed solution:
1. Use ufsdump to perform clone from the working node to a new harddisk. Also touch the install-db to prevent the VxVM startup, modify the /newdisk/etc/system & /newdisk/etc/vfstab, remove the root-done file.
2. Unplug all the network & FC connection on the crashed node.
3. Boot up the crashed node with the new harddisk. However it is exactly like the working node therefore hostname & IP, etc must be changed.
Some files must change to revert back the corrent host information.
/etc/hostname.[interface]
/etc/nodename
/etc/hosts
/etc/llttab
/etc/llthosts
/etc/gabtab
Other files if neccessary.

4. Start up VxVM manually.
    # vxconfigd -m disable
    # vxdctl hostid [crashnode]
    # vxdctl enable
    # rm /etc/vx/reconfig.d/state.d/install-db
5. Disable whatever rc2.d and rc3.d script (SFHA) to prevent the applications you don't want them to be started up on next reboot. Then reboot the crashed node.
6. Connect back the FC cable.
    # format (you should see the SAN storage)
    # vxdctl enable
    # vxdisk list (you should see the SAN storage)
7. Freeze the crashed node (running commands on working node)

    # haconf -makerw
    # hasys -freeze -persistent [crashnode]
    # haconf -dump -makero

8. Connect the network cable (include heartbeat) and perform ping test.

9. copy the /etc/VRTSvcs/conf/config/main.cf from working node to crash node.

10. Bring up the llt & gab.
     # /etc/rc2.d/S70llt start
     # /etc/rc2.d/S92gab start
     # lltstat -vvn
     # gabconfig -a (should see port a and membership should join correctly)
11. Unfreeze the cluster
     # haconf -makerw

     # hasys -unfreeze -persistent [crashnode]
     # haconf -dump -makero
12. Bring up the HA
     # hastart (perform on crash node, the crash node should back to running)
13. Bring up the CVM
     # hagrp -online cvm -sys [crashnode]
     the CVM group should become online.
     # vxdctl -c mode (the crash node should be slave)
14. Bring the rest of resource groups.

Email archiving - how to deploy Symantec Enterprise Vault? Part 3

Once all the pre-requisite is met then please get your license key and the location for vault store ready. During the EV installation, you will be asked for license key, fix registry key, provide index location, provide shopping location, provide EV cache location, and furthermore the location for your vault store.

Many peolple confused that where the EV data stored, they might think that all datas are stored in the SQL Database. In fact, only EV software configuration, deduplication fingerprint, metadata of archived items, audit & monitoring trail are stored in the SQL databases. The index, and actual archived items are stored in the EV server itself (most commonly NTFS SAN disks). Symantec adopt alta-vista technology to provide fast and efficient index searching and it supports deduplication since EV version 8. De-duplication is become a MUST for symantec product for their word "dedupe everywhere" and Netbackup 7 also support client source dedupe.

OK. Installation for EV is pretty simple by just launch the setup.exe from the DVD and follow by "Getting Start Wizard" after the installation. I will talk more about here for MSCS cluster setup for EV.

Prior to install the EV software, the cluster group must be created in MSCS and included the following resource: physical disk (this will store your index, cache, shopping, vaultstore), IP address (EV must be configured using virtual IP), Network Name (EV must be configured using Virtual name), Message Queuing (MSCS will automatically put it into shared disk). Good to go!! just launch the setup.exe on the primary node but without launch any wizard after installation finish.

Launch the Enterprise Vault configuration wizard through Start button.

Select the Cluster group will be added for EV services.

Create new EV directory database i.e. new EV environment

At this point of time, The EV configuration consider almost done. You may just follow normal way to configure the EV Vault Store, etc.
Just put a screenshot for MSCS.

OK. EV is ready for you.... cheers!

Veritas NetBackup - SAN switch migration activity

This post is to summarize the SAN switch migration activity I performed for Telekom Malaysia.

Some background information : TM has more than 150 media servers and running their master server in AIX environment. I involved in the netbackup portion when customr is actuallly perform the SAN switch migration for their ACS tape library which has 44 FC tape drives.

Tasks performed and the useful commands:
1) Gather the existing nbu information. De-activate policies and make sure no jobs are running.
2) freeze the Netbackup cluster group persistent.
    - haconf -makerw
    - hagrp -freeze -persistent
    - haconf -dump -makero
    - hastatus -sum
3) remove tape devices from master server.
    - nbemmcmd -deletealldevices -machinename -machinetype master
4) bring down netbackup services.
    - bp.kill_all
    - kill remaining nbu processes.
5) customer perform SAN switch migration
6) Perform AIX scan after SAN switch migration.
    - /usr/openv/volmgr/bin/scan (to check whether all drives serial number are detected)
    - found some drives are missing, after confirm the SAN zoning is done correctly. reboot the AIX server and re-run the cfgmgr again.
    - lsdev -Cc tape, /usr/openv/volmgr/bin/scan
    - other commands like: lscfg -vl , /usr/openv/volmgr/bin/scsi_command -d /dev/rmtxx -inquiry, odmget -q "/dev/rmtxx" CuAt, etc are also helpful to verify the drives.
7) verified the cluster is in partial status now. Unfreeze the cluster and bring up the netbackup service group.
   - you may hit "unable to initialize device mapping, status=1" error if running scan command. This is due to the virtual IP and netbackup services are not up.
8) Launch the NBU console and use the device configuration wizard to scan the robotic/drives for master server only.
9) Once master server have all drives configured in NBU. Then you proceed to use device configuration wizard to scan for other media servers too. But please run the nbemmcmd -deletealldevices in advanced.

Email archiving - how to deploy Symantec Enterprise Vault? Part 2

What's next after setup the SQL database and Vault service account?
Then you should start to check whether the Enterprise Vault is ready to be installed on the server. The easiest and faster way to check is to use the Enterprise Vault Deployment scanner.

Login the server using Vault service account. Browse into your EV software CD and access the folder called deployment scanner. Then just run the msi file inside to install. (Normally I install into D: drive) Once installed completed, Launch the deployment scanner and fill in the SQL server full name and the EV server name.
Rest assured, the deployment scanner will tell you what is lacking and you just try to fix them up until all necessary items are passed.

Common items that you need to fix
1) MSMQ location - use add or remove program to add this windows components (you will need i386 to install it). Check "application server" and then the IIS, ASP.net, and message queuing inside. (refer installation guide for detail how to do it). Once you install MSMQ, then right-click "My Computer" and select "Manage". Expand the tree and you see the icon called message queuing, right-click it and select properties. So you may change the location now to SAN disk and it will ask to reboot.
2) .NET - you require .NET 1.1 SP1 & .NET 2.0 SP1. Both are needed, Enable IIS first then install .NET. For E-Discovery deployment, you also need to install .NET 3.0.
3) Registry entries - you no need to fix it right now. you will able to fix it easily during installation.
4) IIS - Access the IIS manager and see the web extensions, you must see both .net 1.1 & .net 2.0 are allowed.
5) Exchange full admin rights - if you are doing Exchange archiving, most of time customer will forget to give you "receive as" & "send as" permissions. Grant the permissions in exchange server using system manager.

OK. to be continued...

Enterprise Vault - how to manually create audit database if the selection is missing from the admin console

Normally we enable audit for enterprise vault by right-clicking the directory icon in the admin console. However, I face a problem that the selection "enable audit..." is not shown after I re-install the EV software. Worst thing is I also deleted the EnterpriseVaultAudit database from SQL server. I can't find any document or technote how to create back the audit database.

1) Shutdown all EV services.
2) Manually create a database called 'EVaudit'. and select the path you want to locate the database and transaction log. Click 'OK'.
3) Explore to the \Enterprise Vault. Search for the file called 'audit.sql'. Double-click to open it.
4) In the SQL management console, Execute the audit.sql for 'EVaudit'. This will create required tables in the database. (This script will fail if the database name is not 'EVaudit'.)
5) Open regedit and verify HKLM\software\KVS\admin\auditing is available or not. If not, rename the file audit.reg.txt (in EV installed folder) to audit.reg and double-click to run it.
6) Now open the ODBC (from start -> administrative tools) and go to systemDSN tab. select the EVaudit and click 'configure'. click 'Next' Until you see "change database default to" and select the 'EVaudit' this time. click 'Next' again until finished.
7) Start all the EV services.

You may verify it by
1) Perform some action in the admin console.
2) Launch the auditviewer.exe to perform the search.
3) If the search fail, check the settings whether point to correct SQL server.

Note: You still need need to rename the Database from "EVaudit" to "EnterpriseVaultAudit" Due to the EV reporting services required the database name as such.
How to do it?
1) Shutdown all EV services.
2) Open SQL management studio and rename the database name to "EnterpriseVaultAudit".
3) In the EV services, Open the ODBC wizard. Select the System DSN entry 'EVAudit' and click configure.
4) Follow the instructions but only change the databasename point to "EnterpriseVaultAudit".

Email archiving - how to deploy Symantec Enterprise Vault? Part 1

Email archiving becomes popular recently in malaysia. Many organizations face challenges in maintain their MS exchange servers efficiently and start to looking for archiving solution. Symantec Enterprise Vault is one of the leader product (6 years in a row in gartner quadrant). By implementing Enterprise Vault allows customer to move rarely used emails into lower tier storage. Meanwhile provide seamless experience to the end user (mailbox user). This will significantly save the capacity in first tier storage and hence reduce the load on MS exchange servers. After some times, IT department may find that they able to reduce number of exchange servers in their environment, and end user will forget when is the last time they need to perform housekeeping due to overquota. :) Win-win situation, isn't it?

So... how can i bring joy to the technical engineer? I will show how to deploy this Symantec EV in a neat guide thus save your life in reading manuals. What is the first thing you need to do is to create the Vault service account. Vault service account is used to run EV services and access target servers, and it has the most powerful permission rights. I will call it VSA.

Bear in mind that Symantec EV is running on windows environment with Active Directory.
1) Create a domain user account (Domain admin account will also work)
2) Assign VSA into local admin group in EV server, MS SQL server and target exchange servers. (Please do this even customer has already give domain admin account sometimes)
3) Assign VSA for Exchange Full administrative rights (this is for exchange server archiving).
4) Assign dbcreator and sysadmin rights for VSA in MS SQL server. (please refer installation guide if you don't want to assign sysadmin rights)
5) VSA account must be enabled and password never expired.

Yeah. I did mention MS SQL server. It is required to save the EV configuration data and metadata. I will normally install the SQL software with reporting services and workstation component.

Then you require a system mailbox in the exchange server.
1) one exchange server require one system mailbox.
2) system mailbox must have unique name.
3) system mailbox cannot be hidden.

To be continue....