Technology Yogi

db2 restore with automatic storage tablespace

2011-08-19T10:11:00.001-07:00

I was trying to restore a db from inst1 to inst2. The db path (file system paths) in inst1 were different as compared to inst2. The file systems between the 2 have very restricted permissions. Usually when I am restoring a database from one system to another I am used to creating a blank database with correct path and then in the restore I specify REDIRECT keyword. And my assumption in doing so was the restore will happen to the path that is there in the database (not in the backup set). So, when I tried to restore a db from inst1 to inst2, my assumption did not work, received following error message,

SQL0970N  The system attempted to write to a read-only file.  SQLSTATE=55009

I looked at the db2diag.log and found following message.

INSTANCE: inst2                NODE : 000         DB   : {existing db name}

EDUID   : 9933                 EDUNAME: db2loggr ({existing db name}) 0

FUNCTION: DB2 UDB, oper system services, sqlomkdirp, probe:10

MESSAGE : ZRC=0x840F0001=-2079391743=SQLO_ACCD "Access Denied"

          DIA8701C Access denied for resource "", operating system return code

          was "".

CALLED  : OS, -, mkdir

OSERR   : EACCES (13) "Permission denied"

DATA #1 : File name, 78 bytes

/home/inst1/db2fs/inst1/NODE0000/{old db name}/LOGARC/inst2/{existing db name}/NODE0000/C0000001/

DATA #2 : signed integer, 4 bytes

488

DATA #3 : signed integer, 4 bytes

488

DATA #4 : String, 105 bytes

Search for ossError*Analysis probe point after this log entry for further

self-diagnosis of this problem.

Looks like db2 still tries to restore to the path that is there hidden in the backup set. In my case inst2 was trying to restore on inst1's (db) file system paths, to which it did not had access permissions.

So whats the solution ? use following format of restore command.

db2 "RESTORE DB {dbname in image} FROM {backup image file path} TAKEN AT {timestamp} ON {db2fs_path} DBPATH ON {db_path} INTO {existing db name}

db2 "rollforward db {existing db name} complete"

And this the case with "automatic storage tablespace paths" and I guess the DMS should not have this issue.

How to share Windows folder on Ubuntu (guest OS)

2011-07-17T10:43:00.000-07:00

My system environment looks as follows - Windows host with Ubuntu 10 guest OS in VMWare

I followed the below URL from wmware to setup Windows share on Ubuntu. http://www.vmware.com/support/ws3/doc/ws32_running9.html. But this did not quiet work in a single given the fact that I am new to Ubuntu/Window intraction.

Try command "smbmount" if this works that means the smb pieces are install, else install them

if smb components are not install then run "apt-get install smbfs" - ensure that the installation goes correct.

Create a directory that will be mapped to window share "mkdir /home/gvphubli/win_shared"

Here is the last command in this process

mount -t smbfs -o username=win_user,password=win_user_pwd

//win_system_IP/Java_Learning  /home/gvphubli/win_shared

In case of CentOS or Fedora follow these steps,

Test for samba, run this command in terminal "smbd -V" if samba is installed and available the version number will be displayed, else you will see bash error.

If you see error, install samba using following steps, I used my root privileges to do so.

yum install samba-swat samba-client

After successfull installation, use the same command "smbd -V" to verify. If things look good go to next stop of creating a mount, here is he command

mount -t cifs -o username=win_user,password=win_user_pwd //win_system_IP/Java_Learning /home/gvphubli/win_shared

Note: one difference in this command for Ubuntu v/s RH (Fedora and CentOS) is the change from "smbfs" to "cifs"

You should be all set to share files between host and guest OS

...

db2 restore with mix of full and incremental backups

2011-07-08T09:03:00.000-07:00

Following are the steps to restore db2 database when you have mix of db backup with full, incremental and delta are involved.This illustration is based on Windows system.

text in blue are db2 commands
text in green are my comments
text in black are output seen on command line console.

db2 create db zzz_tmp
db2 restore db zzz incremental automatic from C:\ taken at 20110707154108 into zzz_tmp
db2 rollforward db zzz_tmp to end of backup and complete

-- you will see following message, if roll-forward is not complete

SQL4970N Roll-forward recovery on database "ZZZ_TMP" cannot reach the
specified stop point (end-of-log or point-in-time) on database partition(s)
"0". Roll-forward recovery processing has halted on log file "S0000026.LOG".

-- if you see a message as listed above you need to get(copy or restore from TSM)
-- the specified log file to zzz_tmp's active log directory

db2 rollforward db zzz_tmp to end of backup and complete

-- if successfull (you mostly will be) you will see following message

Rollforward Status

Input database alias = zzz_tmp
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read = Log files processed = S0000026.LOG - S0000026.LOG
Last committed transaction = 2011-07-07-22.41.13.000000 UTC

DB20000I The ROLLFORWARD command completed successfully.

-- do some smoke testing, by connecting and possibly quering the db

db2 connect to zzz_tmp

Database Connection Information
Database server = DB2/NT 9.7.0
SQL authorization ID = db2admin
Local database alias = ZZZ_TMP

db2 merge backup - utility test

2011-07-07T11:21:00.000-07:00

I was very excited about "db2 merge backup" tool. As per the documentation this tool is supposed to merge incremental and delta backup and merge it with the most recent full db backup prior to these incremetnal or delta backups. More details about this utility can be found here

So, if you have this tool working you take one full backup and there are after you just do delta backups and keep merging them with you full backup. Here after you never take full db backups again.

Out of excitement I decided to test this tool. Downloaded this tool and set it up on my window workstation. I did my full backup, ran couple of database transactions and took the delta backup. Below is the control file options and the command used and the output as well.

Below are the contents from "SampleMergeCtrlFile.txt" control file used in the test. I used the control file examples from the following link here

MERGE DATABASE ZZZ
PART (0)
START FROM "C:" TAKEN AT 20110707153755
END FROM "C:" TAKEN AT 20110707154108
OUTPUT TO "C:\Merged\"

Below is the my db2 merge command output.

db2mbk -i DB2INST1 -f SampleMergeCtrlFile.txt -s -p 0
MBKI445W Warning: you are still using a temporary license.
you need to enroll DB2 Merge Backup for Linux, UNIX, and Windows within 29 days.
MBKM031I DB2 Merge Backup for Linux, UNIX, and Windows 01.01.000(110505) 32 bits 7/7/2011 (Windows)
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----10----+----11----+----12----+----13--
000001 MERGE DATABASE ZZZ
PART (0)
START FROM "C:" TAKEN AT 20110707153755
END FROM "C:" TAKEN AT 20110707154108
OUTPUT TO "C:\Merged\"
MBKB005I MBK control step start : 16:00:40.287.
MBKB037I Merged backups will not be registered into DB2.
MBKB005I [0] MBK control step start : 16:00:40.302.
MBKB006I [0] MBK control step end : 16:00:40.318.
MBKB006I MBK control step end : 16:00:40.318.
MBKB053I MBK run step start : 16:00:40.318.
MBKB007I [0] MBK inventory step start : 16:00:40.318.
MBKB021I [0] The partition 0 backup image taken at 20110707154108 is involved in the merge (type INCREMENTAL ONLINE DATABASE, device DISK)
MBKB008I [0] MBK inventory step end : 16:00:40.349.
MBKB009I [0] MBK merge step start : 16:00:40.349.
MBKB028I [0] The utility will build the partition 0 backup image taken at 20110707154109 (type FULL ONLINE DATABASE, device DISK)
MBKB021I [0] The partition 0 backup image taken at 20110707153755 is involved in the merge (type FULL ONLINE DATABASE, device DISK)
MBKB010I [0] MBK merge step end : 16:00:41.849.
MBKB054I MBK run step end : 16:00:41.849.
MBKI442I MBK successfully ended: real time -> 0m1.562530s

As per the output the merge was successfull. Then I attempted to restore above merged backup set to a new database. Remember merged backup image is equivalent to full backup. The attempted restore failed and db2 complained that the backup image is corrupt!!

db2 restore db zzz from C:\ taken at 20110707154109 into zzz_tmp
SQL2529W Warning! Restoring to an existing database that is different from
the backup image database, and the alias name "ZZZ_TMP" of the existing
database does not match the alias name "ZZZ" of the backup image, and the
database name "ZZZ_TMP" of the existing database does not match the database
name "ZZZ" of the backup image. The target database will be overwritten by the
backup version. The Roll-forward recovery logs associated with the target
database will be deleted.
Do you want to continue ? (y/n) y
SQL2530N The backup image is corrupted. Restore of database from this backup
image is not possible.

I felt may be my original backups (full and delta) are corrupt. I restored those backups sets instead of the merged and they work just fine. This concluded that the db2 merge utility is doing something funky.

If you find my test is not appropriate, correct me by leaving a comments.

Update: I was testing this on db2 express 9.7.0 on Window XP. Looks like this issue got resolved after I upgraded db2 to FP4. Later I also fond that if you don't want to upgrade db2 you can modify the merge utility configutaion with following file entry "vendor_object = no" in the configuration file located at "DB2TOOLS\MergeBackup11\cfg\db2mbk.cfg"

db2 error SQL10007N - Reason code: "3"

2011-05-25T08:02:00.000-07:00

Have you ever encountered errors like,

SQL10007N Message "-1390" could not be retrieved. Reason code: "3".

or

Error encountered during the Add record action: Failed to lock the registry

I usually encounter these issues when we are managing multiple instances on the same box. So, to set the db2 instance context for commands like db2stop, db2start etc. The way we set context is to change the environment variable to DB2INSTANCE, the command is

export DB2INSTANCE=db2inst1

After setting the instance context using above command, all the instance level command and all the db2 command will be directed to that instance, in our case all the commands like db2stop, db2start, db2 list db directory etc. will be directed to "db2inst1"

However instance name used in export command is case-sensitive. So accidentally if you typed

export DB2INSTANCE=DB2INST1

Then when you run any db2 commands they will complain with error message

SQL10007N Message "-1390" could not be retrieved. Reason code: "3".

You can't even do "db2 ? SQL10007N" to get help. For your reference here is the help text

SQL10007N Message "" could not be retrieved. Reason code:
"".

Explanation:

The requested message could not be retrieved from the message
file. Reason code is one of the following:
1. The environment variable "DB2INSTANCE" is either not set, or is set
to an invalid instance. Correct it and try again.
2. The message file was found, but the file could not be opened because
of permissions. Check the file permissions of the files under the
message directory.
3. The message file could not be found. Either the file does not exist,
or the directory the message file should be in does not exist. Check
that either a default directory or a directory with the same name as
the 'LANG' environment variable exists under the message directory.
4. The requested message does not exist in the message file. Either the
message file is outdated, or it is the wrong one.
5. Either DB2CODEPAGE is set to a code page which the database does not
support, or the client's locale is not supported by the database.
6. An unexpected system error occurred. Try execution again. If problem
persists, contact your IBM representative.
7. Not enough memory. An attempt to get private memory failed. Try
again.

User response:

Reissue the command after verifying the following:
* ensure that the DB2INSTANCE environment variable is set to the
correct literal string for the username attempting this command
* ensure that correct home directory is specified for the username
attempting this command (i.e. in the /etc/passwd file)
* ensure that the LANG environment variable is set to the correct value
for the installed language, or is set to 'C', on the username
attempting this command
* Even if the message file is outdated, you can still find up-to-date
information about the message in the database server documentation.

If all of the above are correct and the error still occurs, reinstall
DB2.

If you do db2ilist you get the instance list along a message..

Error encountered during the Add record action: Failed to lock the registry

These are some of the side effects when you don't set the DB2INSTANCE variable correctly.

db2 drop db error !

2011-04-21T15:21:00.000-07:00

I was trying to drop database but was getting following error,

SQL1035N The database is currently in use. SQLSTATE=57019

So, while I logged in as instance owner, I tried following.

-- first attempt

db2 list applications global

No sessions were listed except my own local connections. Then I tried following set of statements.

-- second attempt

db2 deactivate db myDB

db2 drop database myDB

Still no luck, I kept getting the same message. Then I tried another variation

-- third attempt

db2 connect to myDB

db2 quiesce database immediate force connections

db2 connect reset

db2 terminate

db2 drop database myDB

Finally I tried this,

-- final attempt

db2 terminate

db2 drop database myDB

I was puzzled, why my third attempt did not work and the final one worked?

I thought it might be the sequence of statements. I looked into the "db2 terminate" command documentation on db2 information center, and her is what it says,

Although TERMINATE and CONNECT RESET both break the connection to a database, only TERMINATE results in termination of the back-end process.

It is recommended that TERMINATE be issued prior to executing the db2stop command. This prevents the back-end process from maintaining an attachment to a database manager instance that is no longer available.

What all this means is always use "db2 terminate" if you really want to disconnect your connections/sessions before doing db level options like db drop etc.

db2 luw 9.7 - Event monitors

2011-04-14T10:09:00.000-07:00

Quick steps to setup event monitoring to capture the db2 sql activity and get to the output to some readable format.

-- db2 event monitoring for tracking sql statements etc.

-- first created for troubleshooting PowerDesigner issue.

-- sql statement to create evt monitor

CREATE EVENT MONITOR evtmon_pd_issue

FOR STATEMENTS

WHERE AUTH_ID = 'USER_ID'

WRITE TO FILE '/home/db2admin' -- this is a directory path

MAXFILES 5

MAXFILESIZE 1024

NONBLOCKED

APPEND;

db2 "set event monitor evtmon_pd_issue state 1"

db2 "set event monitor evtmon_pd_issue state 0"

--After stopping the evt monitor you will see a file in "/home/db2admin" which has word "evt" in its name and has sequential number at the end if there are many files created

-- this will format out the evt monitor captured data

db2evmon -path '/home/db2admin' >> evtmon_pd_issue.out

-- in *.out file you see output in readable format (txt)

-- ones done with your experiements, drop the evt monitor(s)

db2 "drop event monitor evtmon_pd_issue"

For more information on event monitors go to

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.mon.doc/doc/c0055386.html

db2 luw monitoring using WLM

2011-03-14T22:51:00.000-07:00

Quick steps to setup WLM (workload manager) and its related tables. This is an extension to event monitoring. In this sample I am using default system WLM service class

-- create workload (uses default system service classes)

CREATE WORKLOAD "WL_POWERDESIGNER" SYSTEM_USER ('APPUSER_ID')

COLLECT ACTIVITY DATA

ON COORDINATOR DATABASE PARTITION

WITH DETAILS AND VALUES;

GRANT USAGE ON WORKLOAD WL_POWERDESIGNER TO PUBLIC;

SET CURRENT SCHEMA = "WLM";

-- following create statements will create all the required tables for capturing the trace output.

CREATE EVENT MONITOR "DB2STATISTICS"

FOR STATISTICS

WRITE TO TABLE

CONTROL (TABLE "CONTROL_DB2STATISTICS", IN USERSPACE1 ),

HISTOGRAMBIN (TABLE "HISTOGRAMBIN_DB2STATISTICS", IN USERSPACE1 ),

QSTATS (TABLE "QSTATS_DB2STATISTICS", IN USERSPACE1 ),

SCSTATS (TABLE "SCSTATS_DB2STATISTICS", IN USERSPACE1 ),

WCSTATS (TABLE "WCSTATS_DB2STATISTICS", IN USERSPACE1 ),

WLSTATS (TABLE "WLSTATS_DB2STATISTICS", IN USERSPACE1 )

AUTOSTART;

CREATE EVENT MONITOR "DB2ACTIVITIES" FOR ACTIVITIES

WRITE TO TABLE

ACTIVITY (TABLE "ACTIVITY_DB2ACTIVITIES", IN USERSPACE1 ),

ACTIVITYSTMT (TABLE "ACTIVITYSTMT_DB2ACTIVITIES", IN USERSPACE1 ),

ACTIVITYVALS (TABLE "ACTIVITYVALS_DB2ACTIVITIES", IN USERSPACE1 ),

CONTROL (TABLE "CONTROL_DB2ACTIVITIES", IN USERSPACE1 )

AUTOSTART;

CREATE EVENT MONITOR DB2THRESHOLDVIOLATIONS

FOR THRESHOLD VIOLATIONS

WRITE TO TABLE

THRESHOLDVIOLATIONS (TABLE THRESHOLDVIOLATIONS_DB2THRESHOLDVIOLATIONS IN USERSPACE1 ),

CONTROL (TABLE CONTROL_DB2THRESHOLDVIOLATIONS IN USERSPACE1 )

AUTOSTART;

-- activate the EM's

-- SET STATE = 1 and STATE = 0 to deactivate

SET EVENT MONITOR DB2ACTIVITIES STATE 1;

SET EVENT MONITOR DB2STATISTICS STATE 1;

SET EVENT MONITOR DB2THRESHOLDVIOLATIONS STATE 1;

Now run some queries on the given database and data will be captured in below tables.

-- following are the table to look at, I used activity_id to link the two.

SELECT * FROM WLM.ACTIVITYSTMT_DB2ACTIVITIES

SELECT * FROM WLM.ACTIVITYVALS_DB2ACTIVITIES

Note the “CREATE WORKLOAD” statement has “WITH DETAILS AND VALUES” clause, which means we capture the statements and also the host variables passed to the statements if any. So, the “ACTIVITYVALS_DB2ACTIVITIES” table will have multiple rows for every row in “ACTIVITYSTMT_DB2ACTIVITIES” table depending on how many variables were passed in the statements.

For more details go to Creating workloads and defining limits

db2 Automation - HADR status check

2011-02-15T10:15:00.000-08:00

Ever wanted to check what's the HADR status, on multiple instances and multiple databases on the same host?

#!/usr/bin/ksh

# file name - "hadr_status_check_v2.sh"

# Crontab entry --------

# 0 6 * * * . $HOME/.profile; /db2_maint_scripts/hadr_check.sh

clear

rm hadr_check.out

db2ilist | while read instance >> hadr_check.out 2>&1

export DB2INSTANCE=$instance

db2 -o terminate > /dev/null 2>&1

db2 list active databases|grep -i 'Database name'|awk '{print $NF}' | while read dbname

echo "Checking HADR Status for - $dbname on Instance $instance"

echo "\n========== HADR Status for - $dbname on Instance $instance ==========\n" >> hadr_check.out 2>&1

db2 get snapshot for database on $dbname | grep -p -i 'HADR Status' >> hadr_check.out 2>&1

done

# email the report

if [ "$?" = "0" ]; then

cat hadr_check.out | mail -s "HADR Status Report for host: `hostname`" "someone@email.com"

else

cat hadr_check.out | mail -s "HADR Status Report errored on host: `hostname`" "someone_else@email.com"

echo "========== Check out the file "hadr_check.out" for actual details =========="

# --- end of script ------

If you run this script as the db2 instance owner or with any user with privileges as db2 sys admin you should be able to run this script successfully.

To get it to work as a scheduled job, make any entry in the crontab as follows - while logged in as db2 instance owner or as user with db2 sys admin.

0 6 * * * . $HOME/.profile; /db2_maint_scripts/hadr_check.sh

run every morning 6 AM at 0th minute every day, every month etc...

If you carefully look at the crontab command, I use ". $HOME/.profile;" to call the profile so that it picks up the db2 profile setting as well. Otherwise the db2ilist and other db2 commands will fail. Other pieces are "db2_maint_scripts" - is where my scripts are located and

"hadr_check.sh" - is the script name.

If other settings like email and privileges are setup as required, you should receive one email every morning with HADR status details for each database on each instance on a given host. Please if the database is in deactivated state the "LIST ACTIVE DATABASES" will not list all databases. Any thoughts on how to get around this?

Apart from HADR status check, this script also demonstrates how to use unix command output to create a loop and this script can be easily adapted to monitor and take some action like failing over to other stand-by database - to trigger a TAKOVER command.

Please post any questions or troubleshooting help in the comments sections.

Cross-node TSM Restore

2010-12-07T01:00:00.000-08:00

Cross-node TSM restore is all about restoring a database TSM backups from some other server on to another server while accessing TSM directly from the new server. If you don't want to deal with all this you can extract the files locally and copy them over to other server.

On your original instance open and check the contents of the following file "/usr/tivoli/tsm/client/api/bin64/dsm.sys" and list your TSM node names

After identifying the node name grant appropriate previlages to the user that you will mostly like use for db restore/recover purposes.

Assuming your node name is "db2inst2x-db2", your instance name owner name is "xyzp01" and your dbname is "DB001P01" - following are are the sample commands

db2adutl revoke user xyzp01 on nodename db2inst2x for db DB001P01

db2adutl grant user xyzp01 on nodename db2inst2x-db2 for db DB001P01

db2adutl query db DB001P01 nodename db21p1x-db2 owner xyzp01

db2 restore db DB001P01 use tsm options "'-fromnode=db21p1x-db2 -fromowner=xyzp01'" TAKEN AT 20101112080432 INTO DB001P01 REDIRECT

Changing you Unix/AIX Profile to enable DB2 commands

2010-05-05T14:09:00.000-07:00

I am writing this article with DB2 newbies in mind and to keep my own knowledge and experiences available when I want it :)

When I log in to AIX/Unix box and if I try to run DB2 command it fails with following message

"ksh: db2: not found"

This is becauase the DB2 user profile is not loaded into your shell environment. To load it automatically whenever you login, you need to edit your ".profile" file. Below are the step for the same. This is very similar to window DOS environment setup with environment variables and set path etc.

type "vi .profile" in the console window. vi editor should open up with the contents of the .profile file. After the vi is open, it's in command mode, so whatever you type it will not be visible on the screen. By chance you some some valid vi command you will see some action else nothing will be happening.

In my case here is how it looks like, just so that you know how the vi editor looks like

You can refer to below URL for vi commands. With the help of vi command you should be able to put the following text into the .profile that you have opened.

# The following three lines have been added by UDB DB2.
if [ -f /home/db2inst1/sqllib/db2profile ]; then
. /home/db2inst1/sqllib/db2profile
fi

Or you may want to just copy above line and just paste it {ESC}i and right click with a mouse(assuming the contents are in the clipboard)

After you are done with editing enter ":wq" command to save and quit the editor.

For this profile change to take effect you need to log-off and log back in. When you log back in, try db2 and it should take you to DB2 prompt.

Hope this serves some help to people who are new to DB2 and coming from windows background like I do.

Universal Database

2009-10-05T16:19:00.001-07:00

There are hundred’s of websites that ask you to register and in the member registration screen / page they usually ask you to fill-in your residential or shipping address details like STATE and COUNTRY. If you go to any ecommerce sites like eBay or PayPal they usually ask for CURRENCY that you like to trade-in. Look at currency exchange site, so may countries are listed with CURRENCY abbreviations. Similarly if you go to sites where you buy and sell used automobiles like www.autotrader.com, there you will see automobile MAKE and MODEL listed, even these two fields are repeated in many other similar sites. These some of the examples that we can easily see over the web, but imagine about the desktop applications in every organization – its mind blowing to think how many copies of this same set of data is sitting and what it might be costing.

Anyways let’s get going, details like STATE, State abbreviations (CT, NY etc), Days (Monday, Tuesday …Sunday), TIME (00:15, 00:30….. 23:45 etc…), MONTH (Jan, Feb, Mar etc) – let me call them as generic for the purpose of writing this article. Below are the examples that I am talking about.

Examples:

DAY:
TIME:
TIMEZONES:
CURRENCY:
CAR MAKE:	**Model depends on Make
COUNTRY

What came next to my mind was, how about having Universal Database? All the above generic information stored and maintained as one single copy by some organization/ person/ entity, of course with all the disaster recovery and availability in place

How do we share this data with the world – may be via Web services or may be via

AJAX. Create JavaScript (AJAX) functions to fill up the combo boxes; the client website will call the JavaScript function or API to fill the dropdown in his page.

Most of the time these fields are used to ensure that the end user does not make typo errors while entering his details and then the details are stored as is. However many a time the data is stored in relational model and there you need these physical (tables with generic data) table itself to generate your reports. If that is also addressed i.e. if the consumers of the generic data is given access to join with these tables with generic data that would solve the problem.

These are just my meandering – don’t take it serious. But these are near future possibilities or may be already in use who knows – at least I don’t know. However I found something interesting – www.freebase.com. This site is trying to gather/ organize all the information and is like wiki, anyone can update the database. This massive database can be queried, watch the video posted over there – it’s cool!

Powershell for SQL Database Administrators

2009-09-03T17:26:00.000-07:00

Here is list of links that can get you started with Power Shell for SQL Database Administrators (DBA)

http://www.databasejournal.com/features/mssql/article.php/3681061/Microsoft-Windows-PowerShell-and-SQL-Server-2005-SMO--Part-I.htm
This is a 3 part article that start from installation and how to interact with Power Shell (PS)

http://www.mssqltips.com/tip.asp?tip=1680 - again this one introduces how to use PS and makes you comfortable with couple of commands

http://www.simple-talk.com/sql/database-administration/why-this-sql-server-dba-is-learning-powershell/
This one directly delves into how to us PS to automate some of the SQL Server admin task, includes good practical samples.

http://itknowledgeexchange.techtarget.com/dba/powershell-sql-server-health-check-script/ - Here is the really working sample that dumps the database information into an excel sheet.

Using the above links you should be able to build a script which can scan all your SQL Servers (listed in a given txt file) and dump the required information into a nicely formatted excel sheet.

But in my case most of the automation that I built around SQL Server was using VB Script, HTML templates and DOS!

I think Power Shell is for new age Database Administrators.

Organizational Changes - why they are painful ?

2009-08-30T06:46:00.000-07:00

Why changes don't happen easily in organizations, why are they painful?

Most common reasons, employees don't like it. As popular saying goes "Change is painful".

My understanding is different. Human being always like change, it makes them feel they are alive and that's why they change their furniture's and personal vehicles and on top of it they pay for it and they enjoy doing so.

So why does it not work in organizations?

First reason I think is - it is because the process “change” starts from Management and flows down to people/employees, so it's not their idea or thought that is being implemented. It's an idea of some manager sitting somewhere in an isolated area. Though the change might be strategically the best one - but people at each desk think differently about the management.

Second reason as per my knowledge is the "process" of change is painful. This can be best explained with typical case. Suppose in an organization a developer is writing his Java programs in a notepad and saving it into a source control application which is totally external (not integrated). He has been doing this from last couple of years, let’s say 7 years and it involves very simple steps. Most of the developers know how to use it and also troubleshoot. Apart from this there are good amount website and forums that talk about this current method of Java development setup. Now organization decided to bring in some Integrated Development Environment (IDE) to improve the productivity of the developers. And they decided to bring in some XYZ IDE. Management decided on this "XYZ IDE" as the comity involved in the decision making decided so. Now developers are asked to use this development environment.

All the assumptions or the predictability that developer had about the old process, over course of time are now invalid. He doesn’t know whom to approach, what to look for. Of course this is common thing with every thing that changes in life. What if that developer is given some training? - Even that will not work any magic. I would say people should be given with learning resources that can help them right when they want it. If this kind of help is not available I am sure the change is going to bring just frustration, no productivity, in fact negative productivity. Individual's productivity loss will further spread into his other job areas.

Here what I think should be done:

Involve as many people as possible while implementing change, make it their own. Involve them in brain storming, testing and evaluation processes. So that knowledge start building in the community before the change is brought.

Secondly I would suggest forming a mentor groups before bringing in change. The individuals affected by these changes should be aware of resources that can help them, when they need it. The resources could be the documentation, on-demand video or a co-worker sitting next to him

If someone commits mistakes, he should not be discouraged, by making it painful to fix it or worst case by a office wide memo publicizing his name. Such events will discourage individuals from exploring the "NEW" tools or techniques. Most of the inventions are brought to the world via accidents (while exploring), not by the dedicated R&D efforts, so exploration is good.

Dear Manager - Please Delegate

2009-06-30T06:49:00.000-07:00

Some managers do not delegate work to their sub-ordinates, why?

Reasons:

Manager has no patience to teach his sub-ordinates on how to do some of the stuffs.
Manager simply doesn’t trust his sub-ordinates.
He thinks he will lose the control of the department, as he cannot keep up with – “what is happening?” kind of stuff
Some managers hesitate to delegate thinking that, what his subordinates will think of him – “he just dumps everything on us and browses eBay all after-noon”
Manager thinks the sub-ordinates are not intellectually capable of handling the work.

While manager is one who has capability, maturity, strong business sense and can play the role of a mentor. If this is what manager is then he should not be worried about any of the above.

As a manager you should trust your sub-ordinates and have full faith in them and depend on them, else your boss will ask you – What is your productivity, with 7-8 members under you? And you will have no answer.

Many a times, you will face situations where you think, its better you do it yourself instead of teaching your subordinate, just because you don’t have time and patience to deal with, you cannot slip away from taking the role of a “mentor”. You have to develop that quality of being patient and teach your sub-ordinates. Teaching and making your sub-ordinates more productive is the best time-investment that can count on.

You should keep tab on what is going on in your department to the extent of what is happening, it’s not necessary to understand how it being done. You simply cannot keep account of every piece that happens within the department. It’s like driving the car at its optimal speed, whenever there is some noise or you suspect some performance issue, you need to open the hood and check it out. As a manger you should be spending more time on thinking how to improve the effectiveness of the team rather than looking each individuals tasks and taking account of them.

Yes manager has to browse the world (not eBay) to gain knowledge and develop new thoughts or ideas. However do not forget your sub-ordinates (your team) have more ready ideas and thoughts that can directly affect your team’s effectiveness. So first listen to them and see if you can materialize them.

Read the above paragraphs above about your personnel development again. You have to give knowledge, capabilities and have to trust your team and that’s the way you can develop some level of independence within the team. Do not paralyze your team by making it dependent on you for every decision that you make for them, soon you will be the bottleneck and you will be in stress.

How to Back Up the Transaction Log When the Database Is Damaged

2009-03-29T20:19:00.001-07:00

This is one of the best and most frequently asked question in the SQL DBA interview especially if you are being interview for "Production Support DBA" role.

"How to Back Up the Transaction Log When the Database Is Damaged" aka "how to perform tail log backup" - you will face this kind of issues when your database which get corrupted and goes in suspect most while in use or when you recover from a server crash mostly in cases like disk failures. When such failures occur your best method to recover the data is to restore your backup (full/diff/t.logs), so when do we need the tail log backups ?

Assume a case like this one - you take a full backup every night and differentials every 4 hrs. and t.log backups every 15 minutes, which most of us do. Suppose your t.log backup was about to start at 11:15 AM and crash happens at 11:13 AM. So, we are left with a gap of 11:00 AM to 11:13 AM transaction - how do we recover from this gap, yes we depend on tail log. There is no gurantee that you will recover best all of the data, but is a "best effort" strategy as a responsible DBA.

Here is the T-SQL command,

BACKUP LOG TestDB TO TestDB_FullRM_log1 WITH NO_TRUNCATE
GO

Notice the keyword "NO_TRUNCATE", why we need it ? - NO_TRUNCATE clause allows the active part of the t.log to be backed up even if the database is inaccessible/offline, provided the transaction log file is accessible and undamaged, this is very important so keep it in mind :) - for interview as well as for real case where you are required to recover the database from a failure.Why it's called tail log ? - because this is the last backup taken from the failed database in order to recover from failures.

SQL Job Status Reporting

2009-03-19T12:06:00.001-07:00

One day I suddenly see that my LiteSpeed DB backup job is running and running forever ! - though it did not fail but it did not complete so its still a failure in my opinion. This can happen due to third part component failure, or due to bug in the Windows OS or in the SQL Server agent. So, for what so ever reason if job/step is hung, its an issue. To overcome this issue I decided to email out the SQL Job status as a summarised report to myself (and other as required).

Here is the script to achieve the same. It will send out an email (assuming you have configure email feature on your SQL Server) with SQL Job status like LastRun time LastRunStatus and TimeTaken for latest execution.

--===================================================

DECLARE @tableHTML NVARCHAR(MAX)

,@SubjLine varchar(500)

SET @tableHTML =

N'<H4>SQL Server Job Status Report:</H4>' +

N'<table border="1">' +

N'<tr><th>Job Name</th><th>is enable ?</th>' +

N'<th>LastRunDateTime</th><th>LastRunDuration</th><th>LastRunOutCome</th>' +

CAST ( (

SELECT

td = J.Name, '',

td = J.Enabled, '',

td = left(Jh.run_date,4) + '-' + SUBSTRING(CAST(Jh.run_date AS CHAR),5,2) + '-' + right(Jh.run_date,2) +' '+

LEFT(REPLICATE('0', 6-LEN(Jh.run_time)) + CAST(Jh.run_time AS CHAR(6)),2) + ':' +

SUBSTRING(REPLICATE('0', 6-LEN(Jh.run_time)) + CAST(Jh.run_time AS CHAR(6)),3,2) + ':' +

SUBSTRING(REPLICATE('0', 6-LEN(Jh.run_time)) + CAST(Jh.run_time AS CHAR(6)),5,2), '',

td = LEFT(REPLICATE('0', 6-LEN(Jh.run_duration)) + CAST(Jh.run_duration AS CHAR(6)),2) + ':' +

SUBSTRING(REPLICATE('0', 6-LEN(Jh.run_duration)) + CAST(Jh.run_duration AS CHAR(6)),3,2) + ':' +

SUBSTRING(REPLICATE('0', 6-LEN(Jh.run_duration)) + CAST(Jh.run_duration AS CHAR(6)),5,2), '',

td = CASE run_status

when 0 then 'Failed'

when 1 then 'Succeeded'

when 2 then 'Retry'

when 3 then 'Canceled'

when 4 then 'Progress'

end, ''

FROM msdb.dbo.sysjobhistory Jh

JOIN msdb.dbo.sysjobs J ON Jh.job_id = J.job_id

WHERE

Jh.Instance_id IN (

SELECT MAX(Jh.instance_id) [Instance_Id]--, J.Job_Id INTO #JobHist

FROM msdb.dbo.sysjobhistory Jh

JOIN msdb.dbo.sysjobs J ON Jh.job_id = J.job_id

WHERE

--Jh.step_id = 0 -- I don't step details, just job outcome

--AND J.enabled = 1 -- enable this condition to filter out DISABLED jobs

J.Name IN (SELECT JobName FROM Monitoring.dbo.SQLJobsToMonitor WHERE ReportedYesNo = 1)

GROUP BY J.Job_Id

)

ORDER BY

CONVERT(DATETIME,

left(Jh.run_date,4) + '-' + SUBSTRING(CAST(Jh.run_date AS CHAR),5,2)

+ '-' + right(Jh.run_date,2) +' '+

LEFT(REPLICATE('0', 6-LEN(Jh.run_time)) + CAST(Jh.run_time AS CHAR(6)),2) + ':' +

SUBSTRING(REPLICATE('0', 6-LEN(Jh.run_time)) + CAST(Jh.run_time AS CHAR(6)),3,2) + ':' +

SUBSTRING(REPLICATE('0', 6-LEN(Jh.run_time)) + CAST(Jh.run_time AS CHAR(6)),5,2)

)

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table><br><h5>Job name: Monitoring - SQL Job Status Report<h5>' ;

SET @SubjLine = 'SQLMail Monitor: '+@@ServerName+': Job Status Report'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'SQLMail_Mail',

@recipients = 'emailid_001;Emailid02',

@subject = @SubjLine,

@body = @tableHTML,

@body_format = 'HTML';

--===================================================

"Monitoring.dbo.SQLJobsToMonitor" is a table which will be holding the list of jobs to be reported, you can add or delete or set the flag "ReportedYesNo" column to 0/1 to filter out the less important jobs from being reported.

Here is the table structure for the same.

--===================================================

CREATE TABLE SQLJobsToMonitor (

PkId INTEGER IDENTITY PRIMARY KEY NOT NULL,

JobName varchar(500),

ReportedYesNo bit default 1

)

--===================================================

Report Sample:

SQL Server Job Status Report:

Job Name	is enable ?	LastRunDateTime	LastRunDuration	LastRunOutCome
SS Maint Job - exec Prc_FutureRevenue @Thread = 1	0	2009-03-14 01:15:17	01:56:31	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 0	0	2009-03-14 01:15:17	01:56:53	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 4	0	2009-03-14 01:15:17	01:57:10	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 9	0	2009-03-14 01:15:17	01:58:15	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 6	0	2009-03-14 01:15:17	01:58:25	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 3	0	2009-03-14 01:15:17	01:58:38	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 2	0	2009-03-14 01:15:17	01:59:18	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 5	0	2009-03-14 01:15:17	01:59:36	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 7	0	2009-03-14 01:15:17	03:22:27	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 8	0	2009-03-14 01:15:17	03:23:24	Succeeded

File Monitoring Using T-SQL

2009-02-18T03:46:00.000-08:00

Ever wanted to check for the files and then take some action? - this is very common situation where some team will be providing a data feed or extract (as some people call it). Which are usually in flat file format. Before consuming those files you may want to check if you have received all the files are not and then start the consumption.

But in my case what happened was the SSIS flow was written in such way, it will start consuming the files and then at the end when it finds that one of the file is missing it used to rollback the operation which used to take 30-40 minutes to realize. So I put a step before that SSIS execution step to check for file and following is my implementation

-- Check if my files are available

DECLARE @return_status INT

DECLARE @myFileMask VARCHAR(50)

SET @myFileMask = LEFT(REPLACE(CONVERT(varchar, GETDATE(), 120), '-', ''), 8) + LEFT(REPLACE(CONVERT(varchar, GETDATE(), 114), ':', ''), 2) + '*.*'

EXEC @return_status = dbo.prc_CheckForFiles

@FileMask = @myFileMask,

@FullPath = 'E:\FlatFilesDump\',

@FileCount = 7

IF @return_status = 0

BEGIN

RAISERROR ('My files are not available yet. Please check !', 16, 1)

END

--consume files if all are available

IF @return_status = 1

BEGIN

PRINT 'Files are available!'

EXEC dbo.usp_Test -- this is just a test SP

END

---===================================================================================

Here is the script for prc_CheckForFiles

---===================================================================================

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

/**

Author: Girish

Purpose: To validate if the set of files exist or not (YES/NO)

Usage: Its takes 3 inputs, root path, file mask and count

**/

CREATE PROC [dbo].[prc_CheckForFiles]

(

@FileMask VARCHAR(50), -- search mask like ab*_.txt etc

@FullPath VARCHAR(1000), -- search path like "C:\DumpFilePath\"... UNC paths are NOT ALLOWED

@FileCount INT -- integer to indicate the number of files to be looked for, based on which success or failure is indicated

)

SET NOCOUNT ON

--PRINT @FileMask

--PRINT @FullPath

--PRINT @FileCount

CREATE TABLE #Tmp(

cmdOutput VARCHAR(500)

)

DECLARE @DateStamp VARCHAR(30)

, @CMD VARCHAR(1000)

, @myRowCount INT

SET @CMD = 'DIR '+@FullPath+''+@FileMask+' /B'

--PRINT @CMD

INSERT INTO #Tmp EXEC master.dbo.xp_cmdshell @CMD

SET @myRowCount = @@ROWCOUNT

IF @myRowCount >= @FileCount

RETURN 1 -- expected number of files were found

IF @myRowCount < @FileCount

RETURN 0 -- expected number of files NOT found

DROP TABLE #Tmp

--end

Please note, since this depends on xp_cmdshell and DOS – it might limit you ability to implement in your specific environments.

Interview Question - DB Mirroring vs Clustering

2008-12-29T03:18:00.000-08:00

Database mirroring and Clustering – both can serve the purpose of high availability and data protection. However Database mirroring is at database level only. Clustering is at server level, all databases on the instance are covered. In case of clustering there is a single copy of your data and log files where in case of mirroring you have multiple copies of files – on primary and secondary server both, which increases your chances of recovery in case of disaster. In case of mirroring you cannot mirror the system databases viz. master, msdb etc. For mirroring to work database recovery model has to be set to "full recovery". I would see mirroring as a sophisticated log shipping mechanism with automatic failover capability.

SQL server - version question

2008-12-17T18:41:00.001-08:00

Most of the people who deal with SQL Server know how to get the current SQL Server version (build number or information in other words). Let me give the command once again

select @@version

The output would look something like,

----------------------------------------------------------------- ---------------------------------------------------------------
2008-11-23 19:13:25.29 Server Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Now, looking at the build number or the version details “10.0.1600.22” you really cannot make out at what patch level or service pack level your SQL Server instance is at. So, here is a handy web page by SQL Security.

http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx

It lists all the version/build number with respective patch number along the side. In this case “10.0.1600.22” refers to SQL Server version 10 i.e. 2008 RTM. Once you apply some service pack this number will increase to something else and sometime its listed in either KB article or in this SQL Security website.

This should help you on - how to figure out the patch level of your SQL Server instance without asking anyone.

How to connect SQL SERVER instance ?

2008-12-17T10:37:00.001-08:00

This article is for those who are new to SQL Server or for those who are new to windows platform.

Quiet a lot of people go to Google and search for these key words “SQL Server”+FREE. Top in the results listed is the Microsoft SQL Server Express edition. Yes- Microsoft SQL Server Express edition is free to user and it comes with certain limitations please read the documentation before you start using it. Curious to learn the SQL Server quickly most of us download and install it. Next is how do I connect and start writing and testing some queries, as it does not come with standard client connectivity GUI tools

There are two methods of user authentication in SQL Server unlike most of the RDBMS products like Oracle, MySQL, Sybase etc. The two authentication methods are know are

Window Authentication and

SQL Server Authentication

In case of “Window Authentication” your connection request is authenticated against the windows operating system. if you are using SQL Server Management studio or another other GUI’s tools and if you are the local admin of your system (which mostly we are all) you need to make following selections in your connection screen >> SQL Server Name and Authentication = “Window Authentication” (or integrated authentication in some cases) no need of password anywhere.

If you are using OSQL or SQLCMD – the command line client utility to manage SQL Server, which is mostly installed when you install SQL Server (any edition). You just need to type following command at command prompt

http://www.paulkovacs.com/InstallSQLServer2005Express/image005.jpg

{Third party GUI Tools}

http://www.sqlfindandreplace.com/images/SQL-Find-And-Replace-Net-Database-Management-Utility-DOC-002.gif

osql -E -S computer_name\instance_name

Where computer_name = your desktop/pc name, no need of keying in any PASSWORD as you have specified –E option i.e. to use window authentication. If you (logged in user) are a valid windows user and have a local admin privileges you will be successfully connecting to SQL Server instance.

In case of “SQL Server Authentication” your connection request is authenticated by the SQL Server itself. In this case you need to pass one more value i.e. password along with user name and also you need to opt for “SQL Server authentication” if there is any in you GUI tool. In this case the OSQL command would be,

osql -S computer_name\instance_name –U <SQL_userId> -P <respective password>

{command line utils}

http://decipherinfosys.files.wordpress.com/2007/12/sqlcmd_1.jpg

NT Event log analysis (for DBA's)

2008-08-09T23:08:00.001-07:00

Have ever looked "deep" into your NT Event logs ? - most of we DBA's will say NO. Here I am describe a simple and little easy way to do that trick. Using this trick you are going to generate a report of type of errors that your server logging into the logs and you will woundering what is all this junk on my server.

We are going to use SQL Server to do this, as it is our strenght !

First pick your box to analyze and export the NT Event logs CSV format. Why CSV - because we are going to import that into a SQL Server and do a "deep" analysis of the issues that are going on you server (sort of a health report). Here are the steps for your convience

Open Event viewer
Right click on the "Application Log"
Choose "Save Log File As" option from the pop up menu
Save dialogue will appear
Type the filename and select file type as "CSV (Comma delimited) (*.csv)"

Simple isn't ? (this is not all that I wanted to share in this article). Similary you an save other logs too if you are interested.

Ok, once we have the NT Event logs saved in CSV format out next step is to load them into a SQL Server table, so that we can query, slice and dice the data in this log(s). We are going to import the CSV file into SQL Server using a linked server. Why linked server, because its easy :), here is the simple command to create a linked server,

EXEC sp_addlinkedserver EventLogs, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\MyEventLogs\',
NULL,
'Text'

"C:\MyEventLogs" is the folder where my all the eventlogs are stored, "EventLogs" is my linked server name. Refer to SQL Books online for specifics of sp_addlinkedserver command, if you are interested.

After linked server is created, run the following command,

EXEC sp_tables_ex EventLogs

This will display a recordset and if you look at the column "TABLE_NAME", you will see that all the CSV files under the folder "C:\MyEventLogs\" will be listed. Lets assume that you NT Eventlogs were save a file named "PefCounter.csv" the file will be listed as "PefCounter#csv". Now let query this "PefCounter#csv" table. Here is a simple query,

SELECT * FROM EventLogs...PefCounter#csv

Was't is simple and neat idea !

Now don't run your analytics directly on the file directly (linked server) it will be slow. Instead import it into a table. Below is the table structure that I use and it mostly works all the time, I mean the datatypes, come take a look,

IF OBJECT_ID('EventLog_BeforeQ2') > 0
DROP TABLE [dbo].[EventLog_BeforeQ2]
GO

CREATE TABLE [dbo].[EventLog_BeforeQ2](
[LogDate] [datetime] NULL,
[LogTime] [nvarchar](255) NULL,
[LogSource] [nvarchar](255) NULL,
[LogType] [nvarchar](255) NULL,
[LogCategory] varchar(5) NULL,
[LogEvent] [INT] NULL,
[LogUser] [nvarchar](255) NULL,
[LogComputer] [nvarchar](255) NULL,
[LogErrDesc] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

SELECT * INTO [dbo].[EventLog_BeforeQ2] FROM EventLogs...PefCounter#csv

At this point your data is sitting in EventLog_BeforeQ2 table. You can run any query again this table now to do any kind of analysis that you want, but my intention of writing this article was about looking "deep" into the event logs. So, first thing I do is

SELECT DATEDIFF(DD, MIN(LogDate), MAX(logDate)) FROM EventLog_BeforeQ2

This will tell how many days worth of logs you have in your table. Try the below query for more detials,

SELECT LogEvent, COUNT(LogEvent)[Count] FROM [EventLog_BeforeQ2]
WHERE LogType= 'Error'
GROUP BY LogEvent
ORDER BY COUNT(LogEvent) DESC

Below is the output that I see on my logs.

LogEvent Count
----------- -----------
6043 2813
4019 823
6013 528
1 381
318 303
17052 217
4045 122

You can also try to find out what specific applications/services are logging most of the erorrs using following query,

SELECT LogSource, COUNT(LogEvent)[Count] FROM [EventLog_BeforeQ2]
WHERE LogType= 'Error'
GROUP BY LogSource
ORDER BY COUNT(LogEvent) DESC

This is how I target to improve my SQL Server's Maintenance, Monitoring and Performance. I will leave rest of the creativity of log analysis to the readers of this article.

DeFragmenting Heap Tables.

2008-05-09T15:44:00.000-07:00

Ever wanted to know how to deal with fragmentation on tables without any index/primary key, the heap tables. Here is a script that one of my friend forwarded to me when I asked him this question.

BEGIN TRAN

        ALTER TABLE [dbo].[XYX_HeapTable] ADD [c] VARCHAR(1) NULL
        CREATE CLUSTERED INDEX [i] ON [dbo].[XYX_HeapTable] ([c]) WITH FILLFACTOR = 100
        DROP INDEX [dbo].[XYX_HeapTable].[i]
        ALTER TABLE [dbo].[XYX_HeapTable] DROP COLUMN [c]

COMMIT TRAN
Where XYZ_HeapTable is a name of the table which in heap (no primary key nor indexes on it). I wanted to test run this script to see the effectiveness of the above script. So I wrote down the following batch of scripts,

USE Tempdb

CREATE TABLE XYX_HeapTable (
        TrackId INTEGER,
        Fname VARCHAR(200),
        Sname VARCHAR(200)
)

SET NOCOUNT ON

DECLARE @iCount INTEGER
SET @iCount = 0
WHILE @iCount < 10000
BEGIN
INSERT INTO XYX_HeapTable VALUES (@iCount, '234234234234232432as34234232432dfasdfasdfasdfasdfasdfasdfasdfasdf', 'asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdf')

SET @iCount = @iCount +1
END

DECLARE @ObjectId INTEGER
SELECT @ObjectId = OBJECT_ID('XYX_HeapTable')

DBCC SHOWCONTIG (@ObjectId,0) WITH TABLERESULTS, FAST

BEGIN TRAN

COMMIT TRAN

DECLARE @ObjectId INTEGER
SELECT @ObjectId = OBJECT_ID('XYX_HeapTable')
DBCC SHOWCONTIG (@ObjectId ,0) WITH TABLERESULTS, FAST

DROP TABLE XYX_HeapTable

Conclusion: I saw good improvement in ScanDensity and ExtentFragmentation after running of the above script, so it works.

CSV or TXT file as a database !

2008-05-09T14:57:00.000-07:00

Do you have a large CSV ( or delimited txt ) file which you cannot open in XL sheet, because of XL's 65K row limit ? Because you are unable to load you are unable to data analysis - here is what I did.

As a SQL Server DBA/Developer I am assuming you have SQL Server installed on your PC or at least a instance is available for you with Sys. Admin previlages on it. Connect to the instance using query analyzer and create a linked server. Use following command..

EXEC master.dbo.sp_addlinkedserver @server = N'txtsrv', @srvproduct=N'Jet 4.0', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'C:\My_CSV_Files\JetDatabase', @provstr=N'Text'

Lets look at each of the parameters specified in above command. @server is pointing to txtsrv i.e. "use text files as database", then use Jet 4.0 as a provider, @datasrv - is the data source name. In this case it is a folder path where my text/CSV files are located, that I want to access or query. Once you add the linked server successfully, try the following command.

EXEC sp_tables_ex txtsrv

The output will show you the list of table(s) available for querying. These tables are nothing but the files that are located in the folder "C:\My_CSV_Files\JetDatabase". In my case the output shows following tables Server_A01#txt, Server_A02#txt. Now lets try querying the data in those files. Try the following command..

select TOP 10 * from txtsrv...Server_A01#txt

Wow, I see the output as if its a real table from my SQL Server Engine. You can run all the sorts of queries on this data(file data) like you do with real table. The performance looks somewhat slow but for adhoc data analysis and processing purpose this is just great.

Backing up MySQL Database

2008-05-02T14:03:00.000-07:00

How is it done:

mingle database is hosted on mySQL. To backup this database we use mysqldump utility which is invoked through a VB script. Earlier we were using "My SQL Administrator" utility, but it lacks some capabilities and due to which we saw that some of tables were missing in the backup. After testing we found that mysqldump is good for our purpose.

Here is how the VB script looks like



'Mingle_Bkp.vbs
on error resume next

Dim TodaysFileName, BatchFileTxt

Set WshShell = WScript.CreateObject("WScript.Shell")

TodaysFileName = DatePart("yyyy", Now) & Lpad(DatePart("m", Now), "0", 2) & Lpad(DatePart("d", Now), "0", 2) & _
     "_" & Lpad(DatePart("h", Now), "0", 2) & Lpad(DatePart("n", Now), "0", 2)  & _
     Lpad(DatePart("s", Now), "0", 2)

BatchFileTxt = "mysqldump -uroot -p" & CryptVBS("#^rT4", "mingle") & " mingle > ""E:\Backups\Mysql Daily\Mingle_"& _
     TodaysFileName &".sql"""


'*** Add delete old backup files using FORFILES cmd
BatchFileTxt = BatchFileTxt & vbcrlf & "forfiles /P ""E:\Backups\Mysql Daily"" /M *.SQL /D -3 /C ""CMD /c del @file"" "


'*** Add delete "myself" step to batch file
BatchFileTxt = BatchFileTxt & vbcrlf & "del backup.bat"


'*** create a batch file with all the step/commands
OpenTextFileForWrite BatchFileTxt, "backup.bat"


'*** invoke the batch file
WshShell.Run "backup.bat"



'*** Function to LEFT pad the number to create fixed length number string. ***
Function Lpad (MyValue, MyPadChar, MyPaddedLength)
 Lpad = string(MyPaddedLength - Len(MyValue),MyPadChar) & MyValue
End Function




'*** Procedure to DECRYPT the string ***
Function CryptVBS(Text, Key)
 KeyLen = Len(Key)
 For i = 1 To Len(Text)
  KeyPtr = (KeyPtr + 1) Mod KeyLen
  sTxtChr = Mid(Text, i, 1)
  wTxtChr = Asc(stxtchr)
  wKeyChr = Asc(Mid(Key, KeyPtr + 1, 1))
  CryptKey = Chr(wTxtChr Xor wKeyChr)
  hold = hold & CryptKey
 Next
 CryptVBS = hold
End Function



'*** Procedure to WRITE the output to a given file ***
Sub OpenTextFileForWrite (TxtToWrite, strFileName)

 Const ForReading = 1, ForWriting = 2, ForAppending = 8
 Dim fso, f
 Set fso = CreateObject("Scripting.FileSystemObject")
 Set f = fso.OpenTextFile( strFileName, ForWriting, True)
 f.Write TxtToWrite & VBCRLF
 f.Close

End Sub

This is how the output batch file would look like..at the end it delets itself!



:backup.bat
mysqldump -uroot -p****** mingle > "E:\Backups\Mysql Daily\Mingle_20080508_111407.sql"
forfiles /P "E:\Backups\Mysql Daily" /M *.SQL /D -3 /C "CMD /c del @file"
del backup.bat

Currently VB script Mingle_Bkp.vbs is located in WINNT system folder on C:\ drive and that is where it creates the backup.bat and triggers it from.

The backup file retention period is set to 3 days, any files older than 3 days will get deleted. We are doing it using *FORFILES* utility. forfiles.exe is part of windows OS & is available on all windows systems(computers).

The script lines are not completely written by me I picked them up from different places and modified them to suite my requirements, but you are free to use it as you like.

Default database file path

2008-04-10T15:25:00.000-07:00

I keep seeing this question in many forums. When you fire "CREATE DATABASE <database name>" T-SQL command without any parameters. The database will get created and its file will be located in some path, lets say "C:\Program Files\MS SQL\Data". But if you want to have the files located else where, lets assume "D:\SQL_Data\" then there are two options that you can think of.

1 - specify the file path in the "CREATE DATABASE <database name>" command or
2- set the default path location in server configuration.

For option one refere to BOL, here is how to workout Option-II: again there are two ways to do this task. one, using GUI (Management Studio/Enterprise manager) - right click on server node, select properties from pop-up menu, look for "Database Settings" in that section there two boxes - one for LOG and other for DATA file. Key in the appropriate values and save it. This change does NOT require SQL Server re-start.
The other way to achieve this configuration change is using T-SQL, as show below,

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\SQL_Data'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'D:\SQL_Data'
GO

Yes, its instance specific registery entry that it takes to set this property. Here the command is using extended stored procedure "xp_instance_regwrite" to do the trick. This type of command can be used in cases where you want to standardise you SQL Server setup and you may want to add this step as a post installation procedure/batch file etc.

Update 12:18 PM 3/26/2009

Couple of people have asked me how to find the database file paths, i.e basically they are asking - how do I know which database is linked to which file. Here is the query,

select * from master.dbo.sysaltfiles --where db_name(dbid) = 'x_db_name'

Above query will display the list of all the database files located on that server. But if you want to to be spcific then use

select * from x_db_name.dbo.sysfiles

To understand more on interpreting the results (meaning of each column displayed) you may want to check documentation in "Book Online" aka. BOL !

Backup Monitoring Across Multiple Servers

2008-02-25T09:26:00.000-08:00

Here is a simple back verification solution. "back verification " I am basically looking to answer following question(s)

1 > When was the last FULL backup was done?
2 > When was the last T.LOG backup was done?.

This solution is very handy if you are monitoring lot of SQL Server(s) in your environment. The solution is split into 3 files as follows,

1 > Batch file
2 > SQL Query file
3 > Server list file

What is in Batch file?

In this batch file I am just calling OSQL with required parameters. The dos "FOR" loop is very fantastic and I found that from the web (thanks to the author).


Cls
for /f "tokens=1,2,3" %%i in (SrvList.txt)  do OSQL.exe -E -S"%%i"   -q -i"BkpDetails.sql" -n -w 2000 >> %random%log.txt

BkpDetails.sql - T-SQL Query


SET NOCOUNT ON
PRINT ' DB Backup details for server ... ['+@@ServerName+'] as on '+ CONVERT(VARCHAR(30), GETDATE())
PRINT ''
SELECT A.NAME [DB_Name], MAX(backup_finish_date) [Bkp Date], MAX(type) [Bkp Type] INTO #Tmp1
FROM master..sysdatabases A
LEFT JOIN msdb..backupset ON a.NAME = database_name
WHERE type = 'L'
GROUP BY A.NAME
ORDER BY [Bkp Date] ASC

SELECT A.NAME [DB_Name], MAX(backup_finish_date) [Bkp Date], MAX(type) [Bkp Type] INTO #Tmp2
FROM master..sysdatabases A
LEFT JOIN msdb..backupset ON a.NAME = database_name
WHERE type = 'D'
GROUP BY A.NAME
ORDER BY [Bkp Date] ASC

select  LEFT(#Tmp2.[DB_Name], 30), LEFT(#Tmp2.[Bkp Date], 30) AS [Last Full Bkp Done At], 
 LEFT(#Tmp1.[Bkp Date], 30) AS [Last T.Log Bkp Done At]
from  #Tmp2
 LEFT JOIN #Tmp1 ON #Tmp2.[DB_Name] = #Tmp1.[DB_Name]
ORDER BY #Tmp1.[Bkp Date] ASC

DROP TABLE #Tmp1
DROP TABLE #Tmp2
PRINT ' ------------------------------ ------------------------------ ------------------------------ '
PRINT ''

What is in SrvList.txt ?

In this txt file I am just listing all my servers that I want to monitor. One server name per line only. In this case I have repeated same server name multiple times to show sample output


LVEPL3KC824\LOCAL
LVEPL3KC824\LOCAL
LVEPL3KC824\LOCAL

Sample Output..


 DB Backup details for server ... [MSSQL_2K5] as on Feb 25 2008  9:37AM

                                Last Full Bkp Done At          Last T.Log Bkp Done At        
 ------------------------------ ------------------------------ ------------------------------
 msdb                           Feb 25 2008  9:33AM            NULL                          
 master                         Feb 25 2008  9:33AM            NULL                          
 model                          Feb 25 2008  9:33AM            Feb 25 2008  9:34AM           
 TestDB                         Feb 25 2008  9:33AM            Feb 25 2008  9:34AM           
 TestDB_Prod                    Feb 25 2008  9:33AM            Feb 25 2008  9:35AM           
 InboxDB                        Feb 25 2008  9:33AM            Feb 25 2008  9:35AM           

 ------------------------------ ------------------------------ ------------------------------

 DB Backup details for server ... [MSSQL_2K5] as on Feb 25 2008  9:37AM

                                Last Full Bkp Done At          Last T.Log Bkp Done At        
 ------------------------------ ------------------------------ ------------------------------
 msdb                           Feb 25 2008  9:33AM            NULL                          
 master                         Feb 25 2008  9:33AM            NULL                          
 model                          Feb 25 2008  9:33AM            Feb 25 2008  9:34AM           
 TestDB                         Feb 25 2008  9:33AM            Feb 25 2008  9:34AM           
 TestDB_Prod                    Feb 25 2008  9:33AM            Feb 25 2008  9:35AM           
 InboxDB                        Feb 25 2008  9:33AM            Feb 25 2008  9:35AM           

 ------------------------------ ------------------------------ ------------------------------

 DB Backup details for server ... [MSSQL_2K5] as on Feb 25 2008  9:37AM

                                Last Full Bkp Done At          Last T.Log Bkp Done At        
 ------------------------------ ------------------------------ ------------------------------
 msdb                           Feb 25 2008  9:33AM            NULL                          
 master                         Feb 25 2008  9:33AM            NULL                          
 model                          Feb 25 2008  9:33AM            Feb 25 2008  9:34AM           
 TestDB                         Feb 25 2008  9:33AM            Feb 25 2008  9:34AM           
 TestDB_Prod                    Feb 25 2008  9:33AM            Feb 25 2008  9:35AM           
 InboxDB                        Feb 25 2008  9:33AM            Feb 25 2008  9:35AM           

 ------------------------------ ------------------------------ ------------------------------

SQL Express for Absolute Beginners!

2008-02-22T16:25:00.000-08:00

Lesson 1: What is a database?
This introduction covers the questions and terminology SQL Server beginners need to create and manage databases, such as: What is a SQL Server database? How is a SQL Server database different from an Excel spreadsheet? What do SQL Server databases do in my Web site or software applications? What's SQL Server Express' relation to Visual Studio Express? What do terms like "ADO.NET" mean? Download the Video

Lesson 2: Understanding Database Tables and Records
SQL Server beginners will learn about tables and definitions of data types, properties, keys, etc. in this second video. Find out how table rows, columns, and fields interrelate and whether columns can be empty. Download the Video

Lesson 3: More about Column Data Types and Other Properties
SQL Server beginners will explore data integrity, learn which data types to use for what, and avoid common data type pitfalls. Download the Video

Lesson 4: Designing Relational Database Tables
Why are relational databases the most popular? SQL Server beginners will learn to differentiate between primary and foreign keys, design relational database tables, and learn about normalization rules and de-normalization.Download the Video

Lesson 5: Manipulating Database Data
SQL Server beginners will learn about ADO.NET objects and execution of SQL statement from ADO.NET. Explore SQL and learn about insert, update, and delete statements.Download the Video and Project

Lesson 6: More Structured Query Language
SQL Server beginners will discover more SQL statements like sub-select and learn stored procedures and their counterparts in C# and VB methods.Download the Video

SQL Express for Absolute Beginners !

2008-02-22T15:51:00.000-08:00

Nice video(s) for absolute beginners, these videos introduce to couple of SQL Terms and Microsoft's Express Edition of SQL Server. SQL Server Express Edition is freely downloadble from Microsoft's website.

Lesson 1: What is a database?

This introduction covers the questions and terminology SQL Server beginners need to create and manage databases, such as: What is a SQL Server database? How is a SQL Server database different from an Excel spreadsheet? What do SQL Server databases do in my Web site or software applications? What's SQL Server Express' relation to Visual Studio Express? What do terms like "ADO.NET" mean? Download the Video

Lesson 2: Understanding Database Tables and Records

SQL Server beginners will learn about tables and definitions of data types, properties, keys, etc. in this second video. Find out how table rows, columns, and fields interrelate and whether columns can be empty. Download the Video

Notification Service Deployment :: Timeout expired

2008-01-28T11:58:00.000-08:00

While working on one of the Notification services deployment I was getting following error message.

An attempt to run a Transact-SQL CREATE PROCEDURE statement failed.
SqlCommandText: CREATE PROCEDURE dbo.<procedure name>

Source: .Net SqlClient Data Provider
Number: -2
State: 0
Class: 11
Server: myServer
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Procedure:
Line Number: 0

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Timeout error for just creation of stored procedure? - I did check the stored procedure, it's T-SQL was 200 line long that; should not matter anyway. However I went ahead and ran the "CREATE PROCEDURE" code in query analyzer. I got created fine, just in slit seconds.

Did some search on web for "Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." - lot of forums were discussing about increasing timeout - command, connection everything, and some counter-argueing why increase timeout parametes its like "passing the buck to next developer " :) which is alo true. Anyway in my case I didn't had a way to change any parameters as NSControl.exe (the one which runs all the T-SQL scripts) is provided by MS and I am not aware of any way to set it. So we just gave a shot to reboot the server and it worked.

I did further research and found that this is a know issue with NSControl.exe. Here is the link http://support.microsoft.com/kb/870657. Per this article "problem occurs if the application databases for the instance of Notification Services are large", but in my case the databases are very small, like 50 subscriptions and 50 members in the list (ofcourse this was TEST environment). Anyway I have decided to apply the patch, with the hope that this issue will not occur again. If it occurs again, I will use my technique - "just reboot".

Caution about Temporary Tables !

2008-01-26T08:26:00.000-08:00

If you run the following statement, it will create a temporary table with Primary key named as "Pk_Col",

CREATE TABLE #Tmp (
col_01 INT CONSTRAINT Pk_Col PRIMARY KEY
)

I will open another connection using same username and run the same statement it should run successfully. However it will fail with following error.

Msg 2714, Level 16, State 4, Line 1
There is already an object named 'Pk_Col' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

From the error message it is very clear that there is a object name conflict not the #Tmp, but the Pk_Col. Unlike #Tmp the Pk_Col is not treated as temporary object.

So to avoide such issue, and in the sprit of "good practice" don't give name to the constraint. If you don't give name to a constraint SQL Server will automatically give some arbitrary name like Pk_Col_A2340 which would never conflict if you rerun the command again and again. So the T-SQL should look like,

CREATE TABLE #Tmp (
col_01 INT PRIMARY KEY
)

Note that I have not given a name, so the primary key was named as PK__#Tmp______________1CF15040

Actually this issue occurred bit differently in my case. I was troubleshooting some T-SQL which was part of schedule job. While troubleshooting I ran the statement and everything looked fine. So went to Management studio and opened the job and pasted this modified T-SQL and executed the job. The job failed! - I looked at the job history and found that its failing due same error as above "Object name conflict". First I was surprise, b'cos both connections are separate and the primary key was on temporary table, but suddenly I realized the issue.

So, best option all the time is NOT to give a name to a constraints used for temporary tables (# tables). This behaviour is seen in SQL 2000 & 2005 both.

Not enough server storage is available to process this command

2008-01-24T08:47:00.000-08:00

Problem:

In one of the deployment procedure we detach the orginal databases and attach them as bak_<Original Name> while doing this we have to rename the file so that we can avoide file name conflict while createing a new instance of databases. In order to rename we use \\<ServerName>\I$ as the access path, where I$ is the administrative share. But for some reason this was not accessable and was showing an on-screen error message as follows.

"Not enough server storage is available to process this command"

And in the System event logs following error message was logged.

"The server's configuration parameter "irpstacksize" is too small for the server to use a local device. Please increase the value of this parameter".

Resolution:

As the onscreen error message was showing "Not enough server storage is available to process this command" we tried rebooting the system once, as the article http://support.microsoft.com/kb/q126401/ suggested there could be possible memory issue, but that did not resolve the issue. Then contacted NT Admin team to trouble shoot this issue. From the update that I got from that team was that - they followed the steps given in aticle http://support.microsoft.com/kb/285089, but still they also don't know the root cause of this behaviour. After this change mentioned in the article was implemented the $ share path was accessable as usuall.

Intrim Solution Steps( This was creative me) :

While NT Admin team was working on this, we decided to run our deployment procedure manually. So were not sure how do we go about it, b'cos our deployment procedure is made up of lot DOS and SQL Scripts and some of these scripts are created as dynamically during run time and executed. So decided to run the steps one-by-one manually, but that was very tedious. Instantaly we got an idea to introduced a PAUSE in one of the CMD files, just before the line where it was erroring out. So when we triggered the process it came until that pause and was waiting to hit some key. Here we went opened dynamically created files and modified the commands in one of the files to use I:\ instead of I$. Then went back to process window and hit enter key to continue. We were able to run the deploy process successfully. This was just an intrim workaround for that day only.

SQL 2008 - Backup Compression feature

2008-01-17T15:20:00.000-08:00

All these days we spent time and resources on identifying the best SQL Backup utility that has compression mechanism. Finally we heard that MS-IT uses SQL Litespeed for its operations and most of the people out there bought LiteSpeed, those who couldn't afford settled with other softwares like Red-Gate etc. Put some time in implementing it with custom code to fire backup commands and to clean the old backup files as these tools are not tightly integrated with SQL Maintenance Plans.

In SQL-2008 MS has introduced "Compressed Backups" - yes all your energy is (that we talked about in above paragraph) is wasted. Instead of saying wasted just assume that you got some knoledge and working of some product like LiteSpeed and Red-Gate etc. Also this "compression of backups" feature is available in "Enterprise Edision" only. And certainly you can resore the "Compressed backups" onto other edition like Standard, & Developer etc.

Now, Lets look what actually is this new feature in SQL 2008 is doing. Ok - it’s the same compression technique, basically it will compress the data in the memory and dump it into a file in that format which reduced the disk space used, plus the time taken to complete the backup - b'cos the disk I/O is less in this case. I don't see encryption feature in here which is mostly available with third party tools. And the other point is that there are couple of restrictions on the having compressed backups, which are,

This feature is only available on SQL 2008 - "Enterprise Edition"
Compressed and Un-compressed backups cannot be in the same media set (i.e SQL 2008 - "Enterprise Edition" will understand the media set, however the older versions of SQL Server will fail to read the media set.)
NTbackups cannot share a tape with compressed SQL Server backups (I am NOT sure why - may be bc'os of the above reason)

So how do we enable this feature on SQL 2008 ? - This is done via sp_configure system stored procedure. By default when instance is installed this value is set to OFF (0), so in order to enable it you need to run

USE master
GO
EXEC sp_configure 'backup compression default', '1'
RECONFIGURE WITH OVERRIDE

Also, here is the command that you can use in case if you don't want to enable the compression at server level, but want only for specific backup command then,

BACKUP DATABASE
        AdventureWorks
        TO DISK='Z:\SQLServerBackups\AdvWorksData.bak'
WITH
        COMPRESSION

After reading all this I was curious to think - is there any change in backupset system table in MSDB. And yes, there is a new column in backupset table named "compressed_backup_size". The value in this column will tell you the byte count of the file (compressed) stored in the disk. If this value is NULL then that backup set is NOT_COMPRESSED.

Performance wise - Certainly compression demands extra CPU cycles, good that MS has provided two more counter to help use deal with performance related to compression. The counter are Device Throughput Bytes/sec (in SQLServer:Backup Device object) and Backup/Restore Throughput/sec counter (in SQLServer:Databases object) with these two I think we should be able to deal with perf. Issue related to backup process involving compression.

Compression Ratio - This is something that depends on couple of factors like is the data repeated too much in that case you can achieve high level of compression. As you migh know binary data cannot be compressed much so in those situation as well as in case of encrypted data character repeation happen very less, so you get less compression ratios.

Capturing PSSDIAG/SQLDump, Right In Time

2008-01-08T10:07:00.000-08:00

Usually when you contact Microsoft PSS for SQL Server related issue they will ask you to run the PSSDIAG if its performance issue or if its SQL Server hung/restart issue they will ask you to capture memory dumps. If the issue is known to occur at certain time or if you can reprooduce the issue its fine you can trigger PSSDIAG or SQLDump well in time. But usually that is not the case. The issues are totally random and unknown in that kind of situation as a DBA you will face one problem - how will you run PSSDIAG or SQLDump right at the time of issue occurance.

Understand the symtoms: If you know the symptoms like heavy blocking, slow down, unresponsiveness etc you can write a VBScript or combination of DOS and OSQL to detect the symptoms. So lets take un-responsiveness as an example. If this is the case I would write an Vscript and ping the SQL Server if the connection time is beyound 3-4 seconds then I will trigger the PSSDIAG or SQLDump whatever MS Support engineer has asked me to capture. This would totally automate the process and no human intervention is required.

In one of my cases what happened was there was an issue with SQL Server and it used to restart automatically. This behaviour was very much random was not sure when would the next re-start would occur. After initial investigation we found that the MS Clustering service was causing the SQL Server to restart. The way MS Clustering service works is - if the resource (in this case SQL service) is not responding (failure of isAlive check) then it will attempt to restart it. After restart attempts exceeds the configured thershold value Clustering service will attempt to failover. Which means the SQL Service will stop & has to start from the other node and hence I used to see the restart event occuring. So what I did was, I whipped out a simple VBScript to detect the SQL Server's situation and trigger the memory dump. Along with this I also changed the threshold timer and number of attempts in cluadmin so that Clustering service will not failover before my script does its job.

Note: Pls evaluate the Pros/Cons or risks before making any change in Cluster Configurations.

So this is how I was successful in capturing the SQLDump right in time and sent it to PSS for analysis. We found that the SQL Server was running out of "worker thread" and due to which applications were failing (new connections) to connect to SQL Server. And even Clustering service was unable to do its "isAlive" checks and it used to interpret this as serice failure.

Similarly like what I explained above you can execute PSSDIAG or any other utility of choice if required.

Who is incharge ?

2008-01-05T15:48:00.000-08:00

All the organs of the body were having a meeting, trying to decide who was the one in charge .

'I should be in charge,' said the brain , 'Because I run all the body's systems, so without me nothing would happen .'

'I should be in charge,' said the blood , 'Because I circulate oxygen all over so without me you'd all waste away .' 'I should be in charge,' said the stomach,' Because I process food and gi! ve all o f you energy .'

'I should be in charge,' said the legs , 'because I carry the body wherever it needs to go .'

'I should be in charge,' said the eyes , 'Because I allow the body to see where it goes .'

'I should be in charge,' said the rectum-Asshole' 'Because I'm responsible for waste removal.' All the other body parts laughed at the rectum And insulted him, so in a huff, he shut down tight.
Within a few days, the brain had a terrible headache, the stomach was bloated, the legs got wobbly, the eyes got watery, and the blood was toxic . They all decided that the rectum should be the boss . The Moral of the story? Even though the others do all the work.

The asshole is usually in charge .

SQL Server 2008 - New Security Features

2008-01-02T16:07:00.000-08:00

Transparent Data Encryption: What actually this means is - in earlier versions of SQL Server anybody could restore or attach and use the data in the database, of course if assuming if the unauthorized person got hold of the physical backup/data files. But SQL 2008 enables encryption of an entire database, data files or log files, without the need for application changes, plus search the encrypted data using both range and fuzzy searches, search secure data from unauthorized users, and data encryption without any changes in existing applications (developers must me happy). So in this scenerio if unauthorised person has to get access to data he needs to have the Master key, not just copy of physical backup/data file. Once encryption is turned on, the data is secured. This is not available in current CTP, soon it might be available to explore. I am expecting some kind of performance degradation on encrypted databases access, as one extra layer of data transformation is involved, but I am not sure.

Extensible Key Management: This feature was there in SQL Server 2005 but was kinda basic solution for encryption and key management. In SQL Server 2008 it even supports third-party key management and HSM (Hardware Security Modules) products, and they say usage of HSM's are much more secured. Hardware Security Module (HSM) is a hardware-based security device that generates, stores and protects cryptographic keys. It provides the foundation for a high-level secure campus certification authority. Certification modules are also available in software, but a hardware device provides a higher level of security. (source: http://www.cren.net/crenca/onepagers/hsm2.html)

Auditing: I am very happy to see this feature since SQL 2005. You can create and manage auditing via DDL while simplifying compliance by providing more comprehensive data auditing. This enables organizations to answer "basic" questions such as "What data was retrieved?".

All these above great features are available for usage via your favourite T-SQL command, isn't it great?

Why use object type identifier ?

2008-01-02T15:56:00.000-08:00

Following is the typical T-SQL statement found in most of the database object deployment scripts. Let me describe the script a little bit - the below script will find the object "[dbo].[fnHasMarketingOptions]" which is of TYPE FN (Scalar function), IF (In-lined table-function) OR TF (Table function) and if FOUND it will drop that function.

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[fnHasMarketingOptions]') and xtype in (N'FN', N'IF', N'TF'))

DROP FUNCTION [dbo].[fnHasMarketingOptions]
GO

I don't understand why use "and xtype in (N'FN', N'IF', N'TF')" this part - because anyway you can't create a TABLE and PROCEDURE/FUNCTION with the same names in a database. Below is the illustration T-SQL statements,

CREATE TABLE Test (col1 varchar(30))

CREATE PROC test
AS
SELECT GETDATE()

Here is the error message….

Msg 2714, Level 16, State 5, Procedure test, Line 4
There is already an object named 'test' in the database.

If two object types (TABLE/PROCEDURE/TRIGGER) cannot be created with same name then why use "and xtype in (N'FN', N'IF', N'TF')" ?

SQL Server Availability Monitoring - Part II

2007-12-29T10:35:00.000-08:00

In this part of the article I will show you how to deploy the solution that I built in the previous article SQLServer Availability Monitoring Part I . The solution was designed to monitor SQL Server in an environment from another monitoring SQL Server. Here I have assumed that I have a dedicated box with SQL Server setup for monitoring. In my case I had a MOM database server which was used only for monitoring purposes. I created my solution specific table(s) in the same server by creating another database called DBA_Benchmark.

Below are the steps involved in the solution deployment.

Create a table to store logging info that comes from Sub.vbs,following is the script for the same. The column names are self explanatory,

3 Tbl_isAlive Table creation script

CREATE TABLE [dbo].[Tbl_isAlive2] (
[RecDateTime] [datetime] NULL ,
[RecInstName] [varchar] (50) NULL ,
[ResSeconds] [numeric](8, 6) NULL ,
[ResDesc] [varchar] (500) NULL
) ON [PRIMARY]

Next create a SQL job that triggers SQLPing.vbs. Instead of calling the vbs directly I have used a wrapper batch file. Batch file actually came into picture because earlier I had an idea of using it as a NT Scheduled job, however I changed my mind and decided to play with SQL Server only.

4 Launch.bat

:Path on my the server where VBS is located
CD "C:\isAlive\UsingVB"
SQLPing.vbs SrvList.txt

In the above DOS command the SrvList.txt is the txt file that holds my entire list of SQL Server one per line. Thecontents as are shown below.

5 SrvList.txt

Sun\SQLSrv_2K
Sun\SQLSrv_2K5
Sun\MSDE

Now, its time to create a job in SQL Server to trigger theLaunch.bat. This layer of batch file can be surely avoided ifrequired.

6 SQL Batch creation script

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'isAliveChk') > 0
PRINT N'The job "isAliveChk" already exists so will not be replaced.'
ELSE
BEGIN

-- Add the job

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'isAliveChk', @owner_login_name = N'sun\girish', @description = N'Job Comments.',
@category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0,
@notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2,
@delete_level= 0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'step_01_01', @command = N'-- run for 3 times in a minute.

DECLARE @iCount INT

SET @iCount=0

WHILE (@iCount < 5)

BEGIN

EXEC master..xp_cmdshell ''D:\isAliveChk\Launch.bat'', no_output

WAITFOR DELAY ''00:00:10''

SET @iCount=@iCount+1

END

-- end

', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Sch_isAlive', @enabled = 1, @freq_type = 4, @active_start_date = 20070607, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

In the job I execute the following T-SQL Code

DECLARE @iCount INT
SET @iCount=0
WHILE (@iCount < 5)
BEGIN
EXEC master..xp_cmdshell ''D:\isAliveChk\Launch.bat'', no_output
WAITFOR DELAY ''00:00:10''
SET @iCount=@iCount+1
END

This is basically I want to check the service availabilityevery 10 seconds and SQL Agent in SQL 2000 does not allow to run any task withfrequency of less than a minute. So I use above code to overcome thatlimitation.

After implementation is over, query the table Tbl_isAliveand see if results are getting logged, at least there should some rows, becausewe query and log results every 10 seconds, unless you have changed it tosomething different.

Troubleshooting: In case if you see that its notworking correctly then disable the SQL Job first and try to launch the batchfile manually by dbl clicking on the file. If it works fine then looks like thepath what you have specified in the SQL Job may not be correct so fix the pathto point at the correct location.

In case there is any other issue, the error details willbe logged in the file called as AppErrorLog.txt. If there iserror connecting to the server that it is logging the results to then you willsee the following kind of error message in the Errorlog,

06/15/2007@ 11:52:08 AM -- Error : -2147467259 ==> [Microsoft][ODBC SQL ServerDriver][DBNETLIB]SQL Server does not exist or access denied.

Otherwise if its having an issue with connecting to theservers to be monitored then entry in log file or SQL Server table would looklike,

06/15/2007@ 11:52:08 AM INSERT INTO DBA_BenchMark.dbo.Tbl_isAlive VALUES('06/15/200711:51:44 AM', 'NonExistant', 12.1875, '[Microsoft][ODBC SQL ServerDriver][DBNETLIB]SQL Server does not exist or access denied.')

Rare 'Jenny' stamp sells for $825,000

2007-12-27T14:35:00.000-08:00

In this undated photo provided by Heritage Auction Galleries of Dallas, a rare 24-cent stamp depicting an upside-down airplane is shown. The 1918 stamp depicts a Curtiss JN-4 'Jenny,' a World War I training aircraft that became an airmail plane. A collector from New York has purchased the rare 24-cent stamp depicting the upside-down airplane for $825,000, according to Heritage Auction Galleries.
(AP Photo/Heritage Auction Galleries)

The rare 1918 24-cent stamp, depicting an upside-down Curtis JN-4 biplane known as "Jenny," was sold privately this week to a Wall Street executive who did not want to be identified.

Heritage Auction Galleries president Greg Rohan, who brokered the sale, said the buyer is the same collector who lost an auction last month in which another "Inverted Jenny" sold for $977,500. Rohan said his client, whom he described as not being a rare stamp collector, was glad to get another chance at the prized misprint.

"I suspect he's going to enjoy owning it and showing it to a few close friends," Rohan said.

The mint condition red, white and blue stamp is one of the finest known surviving stamps from the original sheet of 100 misprints. The original 100 were bought at a Washington, D.C., post office in 1918.

Randy Shoemaker, founder of Professional Stamp Experts, said there are probably fewer than 1,000 collectors in the world with the money and obsession to seriously pursue such a rare item.

"This is the Rolls-Royce," Shoemaker said. "It's the most expensive airmail stamp in the world."

Rohan said Heritage acquired its stamp from Sonny Hagendorf, a dealer from Scarsdale, N.Y. Rohan said the copy sold by Heritage is one of fewer than five to have never been hinged, meaning affixed with adhesive to be placed in a stamp album.

I am saving this image and article just who knows one day this also might fetch some money :)

SQL Server Availability Monitoring - Part I

2007-12-21T10:46:00.000-08:00

The most typical issue that any production support DBA faces is SQL Server "SERVICE" availability. Many times it happens that the 24/7 support team calls up and says XYZ application is down and it shows the message as “Database Error”. I log in remotely and check the service, and service looks like is up and running, and when I check back the application, it's running fine now. So what went wrong in the mean time ?

Usually the SQL Server might be busy and the application might have timed out, either lock or connection timeout issue either way from application's point of view the database service was not available. So the trouble here is

1 - Exactly when the application/database was was experiencing the issue?
2 - Was application connection timeout setting was too low?
3 - Do you have statistics of SQL Server service availability?

Lot of questions will be raised when the issue is escalated and both app and database management team sit for discussions.

To address all such situation I came up with a utility/script "SQLPing". Yes, you got it right I will ping the SQL Server not to kill but tocheck if the service is available or not, so that I have answers to all abovequestions. I have implemented this as a VB Script because of its obvious advantage and I can edit as and when I want to change some parameter. And theother advantage is I don’t have to document it to make other understand what is it all about, have a look at the code and you will get what it is doing.

Let me quickly explain the files used in providing this solution,

1 - SQLPing.VBS

' Script description
'----------------------
' This procedure only reads the SQL Server name from the given(paramer) file
' and launches the sub.vbs per SQL Server found in the file.
' This design was choosed to avoide the delay caused in pinging subsequent server while
' ping scirpt is waiting for current one. Also, all servers can be pinged almost (difference of fraction seconds)
' at the same time.

'CODE STARTS

Set WshShell = WScript.CreateObject("WScript.Shell")
IF Wscript.Arguments.Count = 0 THEN
MsgBox "Server List Missing", vbCritical, "Provide the list of servers."
Wscript.Quit
END IF

PingSQLServer

'*** Procedure to query database and create the HTMLlized content ***
Sub PingSQLServer

Dim conn, rs, Qry_Txt, Qry_01, Qry_02, Qry_03, sTarget, RptFileName
const RptFldr = "C:Girish_PatilisAliveUsingVB"
SET fso = CreateObject("Scripting.FileSystemObject")
SET oFileTargets = fso.OpenTextFile(Wscript.Arguments(0),1)
DO UNTIL oFileTargets.AtEndOfStream
WshShell.Run "sub.vbs " & oFileTargets.ReadLine
LOOP
End Sub

Save the contents of about table to a file and name it as SQLPing.vbs. This file basically when executed, reads the server name from the file (passedas input parameter) and in-turn executes the Sub.vbs

2 - Sub.VBS

' Script description
IF Wscript.Arguments.Count <> 0 THEN
SQLWork Wscript.Arguments(0)
END IF

'*** Procedure to connect to SQL Server ***
Sub SQLWork(SqlSrv)
On Error Resume Next
myTimer=timer
TstTime=formatdatetime(now, 0)
set conn = CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 3
RecDate = formatdatetime(now, 0) ' current time on monitoring system.
conn.open("DRIVER=SQLServer; SERVER="& SqlSrv & ";DATABASE=master;Trusted_Connection=Yes")
if err.number <> 0 Then
WriteToDB "INSERT INTO DBA_BenchMark.dbo.Tbl_isAlive VALUES('" & TstTime & "', '" & SqlSrv & "', " & timer-myTimer & ", '" & REPLACE(Err.description, "'","''") & "')"
err.clear
exit sub
End If
set rs = CreateObject("ADODB.Recordset")
set rs = conn.execute("select getdate() AS [DateTime]")
WriteToDB "INSERT INTO DBA_BenchMark.dbo.Tbl_isAlive VALUES('" & TstTime & "', '" & SqlSrv & "', " & timer-myTimer & ", '" & "Response " & RS(0) & "')"
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub

'*** Procedure to WRITE the output to a given file ***
Sub WriteToDB (TxtToWrite)
On Error Resume Next
err.clear '-- clearing the error from previous sub
set conn = CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 10
conn.open("DRIVER=SQL Server;SERVER=SQL2K5;DATABASE=DBA_Benchmark;Trusted_Connection=Yes")
' Error handler / Timeouts
if err.number <> 0 Then
LogErrors ("-- Error : " & Err.Number & " ==> " & Err.description)
LogErrors TxtToWrite
Err.Clear
exit sub
End If

'run a query to log the results
conn.execute (TxtToWrite)
conn.Close
Set conn = Nothing
End Sub

'*** Procedure to WRITE the output to a given file - for error logging ***
Sub LogErrors (TxtToWrite)

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile( "AppErrorLog.txt", ForAppending, True)
f.Write date & " @ " & time & VBTAB & TxtToWrite & VBCRLF
f.Close
End Sub

Sub.vbs is the one which actually connects to SQL Server and checksif it’s responding, and collects the response time. In case of connection timeout and query timeout it will log the same information.

I will explain how to deploy this solution in my next article SQL Server Availability Monitoring Part-II

Exporting SQL Maintenance Plans & Jobs

2007-12-20T12:51:00.000-08:00

The best way that I have employed is to restore the MSDB database. That will get you all the jobs, maintenance plans and their histories and also backup set details.

However if you say that you don’t want anything except Maintenance plan only then here is the method. Don’t forget that there are jobs attached to maintenance plan as well. So I would import the data from following tables.

sysdbmaintplan_databases - Contains one row for each database that has an associated maintenance plan. This table is stored in the msdb database.

sysdbmaintplan_history - Contains one row for each maintenance plan action performed. This table is stored in the msdb database.

sysdbmaintplan_jobs - Contains one row for each maintenance plan job. This table is stored in the msdb database.
sysdbmaintplans - Contains one row for each database maintenance plan. This table is stored in the msdb database.

For jobs following tables need to be copied/ imported.
sysjobs - Stores the information for each scheduled job to be executed by SQL Server Agent. This table is stored in the msdb database.

sysjobschedules - Contains schedule information for jobs to be executed by SQL Server Agent. This table is stored in the msdb database.

sysjobsteps - Contains the information for each step in a job to be executed by SQL Server Agent. This table is stored in the msdb database.

NOTE: Table definitions are as given in the books online (help files)
So basically we have to move data from following queries to respective tables.
SELECT * FROM sysdbmaintplans
SELECT * FROM sysjobs WHERE job_id in (SELECT job_id FROM sysdbmaintplan_jobs)
SELECT * FROM sysjobsteps WHERE job_id in (SELECT job_id FROM sysdbmaintplan_jobs)
SELECT * FROM sysjobschedules WHERE job_id in (SELECT job_id FROM sysdbmaintplan_jobs)
Table names are self explained.
Similarly DTS packages can also be moved. Check it out here http://www.sqldts.com/204.aspx

Enumerate Disks & Disk Free Space On SQL Server

2007-08-08T12:49:00.000-07:00

Ever cameup with a situation wherein you want to create a databaes on the fly and to decide dynamically where to place your data and log files, yes I faced it and cameup with the following solution.

You have one stored procedure and one function that can help, first xp_fixeddrives and second one is a system function fn_servershareddrives. xp_fixeddrives procedure outputs the list of drives with free space available on each of the drives, but does not tell you which is shared(SAN drive) and which is local. This is important because you cannot create a database on a local drive when SQL Server is running in clustered environment. And function fn_servershareddrives() does not list the free space available and also does not report local drives so combine the results of both and list every thing and pick what you want and create the database, based on isClusterd() server property output. Here is what I did.

SET NOCOUNT ON

CREATE TABLE #DriveSpace (
        Drive char(1),
        MBFree int
)

-- get availabe drives and free space on each of them
INSERT INTO #DriveSpace EXEC xp_fixeddrives

SELECT * INTO #DriveUnion FROM
(
        SELECT A.DriveName COLLATE Latin1_General_CI_AS
        [Drives Available], #DriveSpace.MBFree, 'X'[Shared]
        FROM    ::fn_servershareddrives() A
                LEFT JOIN #DriveSpace on
                A.DriveName COLLATE Latin1_General_CI_AS = #DriveSpace.drive
        UNION

        SELECT Drive COLLATE Latin1_General_CI_AS [Drives Available],
                MBFree, '-'[Shared]
        FROM    #DriveSpace
        WHERE   Drive NOT IN (SELECT DriveName COLLATE Latin1_General_CI_AS
                        FROM ::fn_servershareddrives())
) A

/**
-- if clustered environment eliminate local drives from the list.
IF SERVERPROPERTY('isClustered')=1
        DELETE FROM #DriveUnion where Shared='-'
SELECT * FROM #DriveUnion
**/

DROP TABLE #DriveSpace
DROP TABLE #DriveUnion

Sample Output

'X' indicates shared drive, where as '-' indicates local drive.
Drives Available MBFree      Shared
---------------- ----------- ------
C                50469       -
D                36053       -
E                267942      X
F                39384       X
G                3657        X
H                179584      X

Object Search Window missing !!

2007-05-11T11:43:00.000-07:00

One of my friend asked me is there a way to search for specific object (table,sp, trigger or column) based on key word. Immediately I said yes and tried to look for that search utility within the SQL 2005 Server Management Studio and tried pressing F4 also but nothing popped-up.

I was really surprise and woundering where the Object search window is. Out of frustration I look into Google, not for objects but to know where the Object search window is gone. Then came up with the anwers that Object search functionality is taken out of SQL 2005 tool set. After all this research decided to write my query to do the task, and here is the T-SQL query solution for the problem

/**

SELECT name[ObjectName],
CASE xtype
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'L' THEN 'Log'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'Inlined table-function'
WHEN 'P' THEN 'Stored procedure'
WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K)'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'UQ' THEN 'UNIQUE constraint (type is K)'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END as ObjectType
FROM sysobjects WHERE NAME LIKE '%customer%'
UNION ALL
SELECT object_name(id)+' >> '+name, 'column' FROM syscolumns WHERE NAME LIKE '%customer%'

**/

Here is how the output looks like

Unable to start in debug mode(VS.Net)

2007-05-01T11:46:00.000-07:00

I was working on my project as usuall. And found that one of my database query is not returning any row(s). I checked the query and found that there was a date parameter in WHERE clause due to which I was unable to get the data. So I reset the clock on my system to one month back i.e when it was 01-May it reset it to 01-Apr. After doing this system change I was unable to start the VS.Net project in debug mode. First I didn't realize the fact that it is due to change in my system clock. I kept trying to understand what the heck is wrong. Finally I reset back the date to 01-May, but still I am facing the same issue. If I start the application by pressing F11, I get the following message box.

Then I deleted the folded bin & obj listed under the root of project folder, and thats it things are sorted out.

This behaviour looks like is very specific to C# Console application, I tried to simulate with VB.NET Windows App. I couldn't recreate the issue.

VB.Net String Manipulation

2007-04-26T09:42:00.000-07:00

I was trying to cut the fixed length string to the given specification thought I will post this Vb code as a sample code snippet on my blog.

The output would look as shown below

Database Backup Solution

2007-04-26T06:29:00.000-07:00

As I said in one of my articles, we usually work from remote and don't have a common database server for our development purposes. So one day what happened was one of my developer was doing some db object changes, like almost 20-25 changes. However after he did his changes he didn't backup it up (not a DBA). Next day he was supposed to send the script to me so that I make the same changes on my copy of database and distribute it to others so that every developer and myself are on the same version of database. But unfortunately one of his computer's disk crashed and he lost almost all development work. And database work was totally lost, nor we had any database backup to recover the work from. And most of the developers use SQL Express edition so they might not be well equiped with proper tool

Usually I have seen developers when working in organised manner they usually don't worry about the database server anyway its a DBA job to maintain it. But in the scenerio that I explained, developer is expected to do it himself and protect his work. However SQL Server does not allow physical file backup, so the next best option and the best practise is to backup the database using backup commands.

Here is a simple win batch file for the same,

/********** backup.bat **************************/

date/T >> BackupLog.txttime /T>> BackupLog.txt
osql -E -SSLYSLE-PC\GP_SQL2K5 -Q"BACKUP DATABASE TestDB TO DISK = 'C:\Girish_Patil\DB_Backup\DB_Backup_%random%.BAK'" -w4000 >> BackupLog.txt
time /T>> BackupLog.txtdate/T >> BackupLog.txt
echo "X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~X~" >> BackupLog.txt

/**************************************************/

The parameters are hardcoded, you can modify them as per your requirements. This script logs all the message to BackupLog.txt file if you want to analyze anytime if something goes wrong. I use %random% variable to generate random number and concatenate with backup file name so that there is no overwriting of backup files or any file name conflicts. Now, to automate it further just using Windows Scheduler to run this backup.bat file as scheduled job. Thats it your done with simple database backup solution that meets your purpose.

Database deployment - How to ?

2007-04-25T17:02:00.000-07:00

I was working on one of the projects where I was doing database development and another two guys were working on .Net development. We did't had common DEV server to which we connect and do the work. So in such situation obviously I will have to give a new version of database to each of these developers. So obvious next way is to backup and restore, but I will have to teach the developer how to do it. So I came-up with new option and i.e. batch script. Below is the content of the batch file.

/****** RestoreDB.bat *********************/

::CD "C:\Girish_Patil\Deployment\Restore"
::restoreDB Ultra_V2 C:\Girish_Patil\Deployment\Restore\DB_Backup.BAK C:\Girish_Patil\Deployment\Restore SLYSLE-PC\GP_SQL2K5
echo off
cls
if "%1"=="" goto usage
if "%2"=="" goto usage
if "%3"=="" goto usage
echo.echo Database is being restored pls. wait..
echo =================================
echo.

osql -E -S%4 -Q"if exists(select name from master..sysdatabases where name = '[%1]') drop database [%1]" -w4000 >> RestoreLog.txt

osql -E -S%4 -Q"RESTORE DATABASE [%1] FROM DISK = N'%2' WITH FILE = 1, MOVE N'ULTRA' TO N'%3\%1_Data.mdf', MOVE N'ULTRA_log' TO N'%3\%1_Log.ldf', NOUNLOAD, REPLACE, STATS = 10" -w4000

echo.
echo =================================
echo.
echo [%1] Database is restored and is ready for use !!!
echo.
echo.
echo.
goto end
:usage
echo.
echo usage: RestoreDB DBName BkpFilePath DbPhysicalFilePath SrvName
:end
pause

/******************************************************/

I takes 4 parameter
1=Database name to restore as,
2=DB Backup file name with path,
3=Where do you want the new database files to be located,
4=SQL Server name, with instance name.

In my senario the SQL Server used to be running locally. So this script is ment to work smoothly as is on the local server. However if you want to restore it on remote server, in that case you need to place the DB Backup file on the remote server and provide the path local to it.

Pls. mail/contact me if you need any thing more specific.

Missed opportunity

2007-04-25T13:47:00.000-07:00

Look at this link http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=9152, it says I have won some prize, unfortunately I didn't log to this site for quiet a long period and thats how I miss something.

/************

This is the second month of our new forum contest. Now, anyone who has posted at least once anytime during the month is elgible to win in a random drawing. Below are the winners for June 2005.

To claim your prize, you MUST CONTACT ME, using the e-mail account you used when you registered with the forum. Once I get your e-mail, I will make the arrangements for you to receive your gift. If you don't contact me within 30 days, then you loose out.
Below are the winners, selected at random, along with the gift they have won:
gvphubli: ApexSQL Doc ($249) from ApexSQL Software
billbobcu: SQL Scribe Documentation Builder ($400) from A&G Software
jastone: Book: In the Trenches with SQL Server & .NET: Advanced Techniques & Real World Solutions ($150)

James B UK: DbNetGrid ($599) from DBNetLink
ryanFX: SQLZip ($500) from SQLZip Software
snoopy: myLittleAdmin for SQL Server ($490) from myLittleTools
-----------------------------Brad M. McGehee, MVPWebmasterSQL-Server-Performance.Com

************/

So, keep posting and helping others every alternate day

Search Results

2001-12-20T10:53:00.000-08:00

My Profile

1997-01-19T08:56:00.000-08:00

Girish Patil
Age: 32
Gender: Male
Astrological Sign: Taurus
Zodiac Year: Rabbit
Industry: Technology
Occupation: SQL DBA
Location: Seattle : Washington : United States
About Me
By profession I am a DBA (MS-SQL) with total 10 yrs of experience in IT, out of that 7 yrs as SQL DBA. I have handled Developer, plus production support roles.
Interests
Technology
Driving on country side
Watching action movies
Personal Finance & Investment :)
Favorite Books
A Random Walk Down Wall Street - by Burton G. Malkiel
The Great Mutual Fund Trap - by Gregory Arthur Baer & Gary Gensler
The Warren Buffett Way By Robert G. Hagstrom