October 21, 2010

Databse design question

CREATE TABLE [dbo].[CustomerMaster]([CustomerId] [int] IDENTITY(1,1) NOT NULL,[CustomerCode] [varchar](30),[CustomerName] [varchar](200),[CreatedDate] [datetime],[ContactNo] [varchar](20) CONSTRAINT [PK_CustomerId] PRIMARY KEY CLUSTERED ([CustomerId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
CREATE TABLE [dbo].[CustomerDocuments]([DocAttachmentId] [int] IDENTITY(1,1) NOT NULL,[CustomerId] [int] NOT NULL,[DocumentType] [varchar](5),[DocumentName] [varchar](200) NULL,CONSTRAINT [PK_CustomerDocs] PRIMARY KEY CLUSTERED ([DocAttachmentId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

ALTER TABLE [dbo].[CustomerDocuments] WITH CHECK ADD CONSTRAINT [FK_DocAttachment_Customer] FOREIGN KEY([CustomerId])REFERENCES [dbo].[CustomerMaster] ([CustomerId])GO
ALTER TABLE [dbo].[CustomerDocuments] CHECK CONSTRAINT [FK_DocAttachment_Customer]GO
Suppose there are 20 rows in customer master and 2 documents for each customer in CustomerDocuments table,what will be the output after the following statement,
TRUNCATE TABLE CustomerDocuments
SELECT IDENT_CURRENT( 'CustomerMaster' )

Answer: Error while truncating
Explanation: TRUNCATE Statement cannot be executed on tables with Foreign key references. AS the CustomerMaster table is referenced by CustomerDocuments table the statement will fail to execute.

TRUNCATE TABLE CustomerDocuments -- Completed successfully
TRUNCATE TABLE CustomerMaster -- Cannot truncate table 'CustomerMaster' because it is being referenced by a FOREIGN KEY constraint.
SELECT IDENT_CURRENT( 'CustomerMaster' ) --1

October 12, 2010

Best practises for being a good DBA.

1.Study, Understand and grasp firmly the database concepts and architecture.
2.Begin with small project and practice, practice, practice. small projects are good to lear the basics.
3.A good DBA means well experienced DBA. A DBA becomes experienced as the time passes. So first of all you need time. It is in your hands to make shorter this time. Databases shows their dirty sides with the large amount of data and under heavy pressure. So if you want to become a good DBA in a short time :
-Try to read a lot (a lot means realy a lot). Mostly read best practises.
-Examine big queries and their effects on the database.
-Try to work on a database which have a large amount of data with a heavy usage.

October 04, 2010

Windows Task Scheduler

By using Task Scheduler, you can schedule tasks such as to automate web testing and site monitoring or system tools like the Disk Defragmenter to run at a time that is most convenient for you. Task Scheduler starts each time you start Windows, and runs in the background. With Task Scheduler, you can: Schedule a task to run daily, weekly, monthly, or at certain times (such as system startup).

On Windows XP and Server 2003 you can access this from the Start Menu and clicking on Settings and then Control Panel to Scheduled Tasks.
Windows administrators can easily manage Scheduled Tasks using command line utility named schtasks.exe which is available in Windows XP/2003/7/2008/2008 R2 operating systems.

Create a scheduled task to run notepad at 8:00AM every day:

C:\>schtasks /create /TN "Run notepad" /TR notepad.exe /SC DAILY /ST 08:00:00

Note: Here in above example, we are mentioning any user account. But the command execution will try to configure scheduled account with current logged on user and prompts you to enter the password.
In case, you want to create a scheduled task with specific user account, pass them as command arguments.

C:\>schtasks /create /TN "Run notepad" /TR notepad.exe /SC DAILY /ST 08:00:00 /RU domain\username /RP xxxxxx

We are done with creating scheduled tasks. Now, let’s see how to list the Scheduled Tasks running in local system as well as remote

Query list of scheduled tasks running in local computer.

C:\>schtasks /query

This lists all the scheduled tasks running in local computer.


C:\>schtasks /query

TaskName Next Run Time Status
==================================== ======================== ===============
GoogleUpdateTaskMachineCore At logon time
GoogleUpdateTaskMachineCore Never
GoogleUpdateTaskMachineUA Never

Run a scheduled task manually:

If we want to run a scheduled task manually irrespective of it’s schedule, we can do it easily like below.

C:\>schtasks /run /TN “Run notepad”

This triggers the command which is configured in scheduled task(notepad in our example).

Modifying scheduled task:

Now we will see how to modify scheduled tasks. We will be able to modify only few parameters using this utility like, program to run, username, password used by scheduled task. Let’s see how we can change the program to run.

C:\> schtasks /change /TN "Run notepad" /TR cmd.exe

In this example, we have changed the program to run from notepad.exe to cmd.exe

Deleting scheduled tasks:

Below are the few examples which describes usage of delete option.

SCHTASKS /Delete /TN * /F
SCHTASKS /Delete /TN "Backup and Restore"

As you can see in above example, /F option forces the delete. This is generally required if a scheduled task is not responding or in running state.

Stop a running scheduled task:

If a scheduled job is running for long time and want to stop it, we can use /END parameter. Now lets see a example how to end a scheduled task

C:\>schtasks /END /TN "Run notepad"

If you want to perform any of the above operations on a remote system, use the parameter /S in the command. For example, to query list of tasks scheduled in remote machine use the below command.

C:\>schtasks /query /S remotehost


C:\>schtasks /query /s Remoteservername

TaskName Next Run Time Status
==================================== ======================== ===============
backup 02:00:00, 10/6/2010
DeleteSQLBackup7old 23:00:00, 10/10/2010
SQLVesrionControl Never
SQLVesrionControl 23:00:00, 10/5/2010
VSSToDev2000 Never