Support Ends for the list of MS SQL products

http://support.microsoft.com/lifecycle/search/?sort=PN&alpha=sql

Products Released	Lifecycle Start Date	Mainstream Support End Date	Extended Support End Date	Service Pack Support End Date	Notes
SQL Server 2000 64-bit Edition	11/30/2000	4/8/2008	4/9/2013	7/11/2002
SQL Server 2000 Desktop Engine	11/30/2000	4/8/2008	4/9/2013
SQL Server 2000 Desktop Engine Release A	1/29/2003	4/8/2008	4/9/2013
SQL Server 2000 Developer Edition	11/30/2000	4/8/2008	4/9/2013	7/11/2002
SQL Server 2000 Enterprise Edition	11/30/2000	4/8/2008	4/9/2013	7/11/2002
SQL Server 2000 Reporting Services Service Pack 1	9/22/2004	Not Applicable	Not Applicable	7/11/2006
SQL Server 2000 Reporting Services Service Pack 2	4/22/2005	Review Note	Review Note		Support ends 12 months after the next service pack releases or at the end of the product's support lifecycle, whichever comes first. For more information, please see the service pack policy at http://support.microsoft.com/lifecycle/#ServicePackSupport.
SQL Server 2000 Service Pack 1	6/12/2001	Not Applicable	Not Applicable	2/28/2002
SQL Server 2000 Service Pack 2	11/30/2001	Not Applicable	Not Applicable	4/7/2003
SQL Server 2000 Service Pack 3a	1/7/2003	Not Applicable	Not Applicable	7/10/2007
SQL Server 2000 Service Pack 4	5/6/2005	Review Note	Review Note		Support ends 12 months after the next service pack releases or at the end of the product's support lifecycle, whichever comes first. For more information, please see the service pack policy at http://support.microsoft.com/lifecycle/#ServicePackSupport.
SQL Server 2000 Standard Edition	11/30/2000	4/8/2008	4/9/2013	7/11/2002
SQL Server 2000 Windows CE Edition 2.0	12/16/2002	1/8/2008	1/8/2013
SQL Server 2000 Workgroup Edition	6/1/2005	4/8/2008	4/9/2013
SQL Server 2005 Compact Edition	2/19/2007	4/12/2011	4/12/2016	7/10/2007
SQL Server 2005 Developer Edition	1/14/2006	4/12/2011	4/12/2016	7/10/2007
SQL Server 2005 Enterprise Edition	1/14/2006	4/12/2011	4/12/2016	7/10/2007
SQL Server 2005 Enterprise Edition for Itanium-based Systems	1/14/2006	4/12/2011	4/12/2016	7/10/2007
SQL Server 2005 Enterprise X64 Edition	1/14/2006	4/12/2011	4/12/2016	7/10/2007
SQL Server 2005 Express Edition	6/1/2006	4/12/2011	4/12/2016	7/10/2007
SQL Server 2005 Express Edition with Advanced Services	7/16/2006	4/12/2011	4/12/2016	7/10/2007
SQL Server 2005 Service Pack 1	4/18/2006	Not Applicable	Not Applicable	4/8/2008
SQL Server 2005 Service Pack 2	2/19/2007	Not Applicable	Not Applicable	1/12/2010
SQL Server 2005 Service Pack 3	12/15/2008	Not Applicable	Not Applicable	1/10/2012
SQL Server 2005 Service Pack 4	12/13/2010	Review Note	Review Note		Support ends 12 months after the next service pack releases or at the end of the product's support lifecycle, whichever comes first. For more information, please see the service pack policy at http://support.microsoft.com/lifecycle/#ServicePackSupport.
SQL Server 2005 Standard Edition	1/14/2006	4/12/2011	4/12/2016	7/10/2007
SQL Server 2005 Standard Edition for Itanium-based Systems	1/14/2006	4/12/2011	4/12/2016	7/10/2007
SQL Server 2005 Standard X64 Edition	1/14/2006	4/12/2011	4/12/2016	7/10/2007
SQL Server 2005 Workgroup Edition	1/14/2006	4/12/2011	4/12/2016	7/10/2007
SQL Server 2008 Developer	11/6/2008	7/8/2014	7/9/2019	4/13/2010
SQL Server 2008 Enterprise	11/7/2008	7/8/2014	7/9/2019	4/13/2010
SQL Server 2008 Express	11/11/2008	7/8/2014	7/9/2019	4/13/2010
SQL Server 2008 Express with Advanced Services	11/22/2008	7/8/2014	7/9/2019	4/13/2010
SQL Server 2008 R2 Datacenter	7/20/2010	7/8/2014	7/9/2019	7/10/2012
SQL Server 2008 R2 Developer	7/20/2010	7/8/2014	7/9/2019	7/10/2012
SQL Server 2008 R2 Enterprise	7/20/2010	7/8/2014	7/9/2019	7/10/2012
SQL Server 2008 R2 Express	7/20/2010	7/8/2014	7/9/2019	7/10/2012
SQL Server 2008 R2 Express with Advanced Services	7/20/2010	7/8/2014	7/9/2019	7/10/2012
SQL Server 2008 R2 Parallel Data Warehouse	11/9/2010	7/8/2014	7/9/2019		Hardware products will receive 5 years of support following Microsoft’s end of sales date for the Major Product version. Minor Product releases follow the Support Lifecycle of their respective Major Product versions.
SQL Server 2008 R2 Service Pack 1	7/12/2011	Not Applicable	Not Applicable	10/8/2013
SQL Server 2008 R2 Service Pack 2	7/26/2012	Review Note	Review Note		Support ends 12 months after the next service pack releases or at the end of the product's support lifecycle, whichever comes first. For more information, please see the service pack policy at http://support.microsoft.com/lifecycle/#ServicePackSupport.
SQL Server 2008 R2 Standard	7/20/2010	7/8/2014	7/9/2019	7/10/2012
SQL Server 2008 R2 Standard Edition for Small Business	7/20/2010	7/8/2014	7/9/2019	7/10/2012
SQL Server 2008 R2 Web	7/20/2010	7/8/2014	7/9/2019	7/10/2012
SQL Server 2008 R2 Workgroup	7/20/2010	7/8/2014	7/9/2019	7/10/2012
SQL Server 2008 Service Pack 1	3/31/2009	Not Applicable	Not Applicable	10/11/2011
SQL Server 2008 Service Pack 2	9/24/2010	Not Applicable	Not Applicable	10/9/2012
SQL Server 2008 Service Pack 3	10/6/2011	Review Note	Review Note		Support ends 12 months after the next service pack releases or at the end of the product's support lifecycle, whichever comes first. For more information, please see the service pack policy at http://support.microsoft.com/lifecycle/#ServicePackSupport.
SQL Server 2008 Standard	11/6/2008	7/8/2014	7/9/2019	4/13/2010
SQL Server 2008 Standard Edition for Small Business	11/6/2008	7/8/2014	7/9/2019	4/13/2010
SQL Server 2008 Web	11/6/2008	7/8/2014	7/9/2019	4/13/2010
SQL Server 2008 Workgroup	11/6/2008	7/8/2014	7/9/2019	4/13/2010
SQL Server 2012 Business Intelligence	5/20/2012	7/11/2017	7/12/2022	1/14/2014
SQL Server 2012 Developer	5/20/2012	7/11/2017	7/12/2022	1/14/2014
SQL Server 2012 Enterprise	5/20/2012	7/11/2017	7/12/2022	1/14/2014
SQL Server 2012 Enterprise Core	5/20/2012	7/11/2017	7/12/2022
SQL Server 2012 Express	5/20/2012	7/11/2017	7/12/2022	1/14/2014
SQL Server 2012 Parallel Data Warehouse	7/12/2013	10/9/2018	Review Note		SQL 2012 PDW appliance hardware and the server software configured and running on the appliance hardware shall receive five years of support from General Availability of SQL Server Parallel Data Warehouse 2012.
SQL Server 2012 Service Pack 1	11/7/2012	Review Note	Review Note		Support ends 12 months after the next service pack releases or at the end of the product's support lifecycle, whichever comes first. For more information, please see the service pack policy at http://support.microsoft.com/lifecycle/#ServicePackSupport.
SQL Server 2012 Standard	5/20/2012	7/11/2017	7/12/2022	1/14/2014
SQL Server 2012 Web	5/20/2012	7/11/2017	7/12/2022	1/14/2014
SQL Server 4.2 for OS/2	Not Available	7/1/1999	Not Applicable
SQL Server 6.0 Standard Edition	Not Available	3/31/1999	Not Applicable
SQL Server 6.5 Enterprise Edition	3/1/1998	3/31/2004	Not Applicable
SQL Server 6.5 Service Pack 1	12/31/1998	Not Applicable	Not Applicable	1/1/2002
SQL Server 6.5 Service Pack 2	9/15/2000	Not Applicable	Not Applicable
SQL Server 6.5 Service Pack 3	1/15/2000	Not Applicable	Not Applicable
SQL Server 6.5 Service Pack 4	Not Available	Not Applicable	Not Applicable	3/24/1999
SQL Server 6.5 Service Pack 5a	12/24/1998	Not Applicable	Not Applicable	3/31/2004
SQL Server 6.5 Standard Edition	6/30/1996	1/1/2002	Not Applicable
SQL Server 7.0 Enterprise Edition	3/1/1999	12/31/2005	1/11/2011
SQL Server 7.0 Service Pack 1	5/25/1999	Not Applicable	Not Applicable	3/31/2004
SQL Server 7.0 Service Pack 2	3/20/2000	Not Applicable	Not Applicable
SQL Server 7.0 Service Pack 3	Not Available	Not Applicable	Not Applicable	7/26/2002
SQL Server 7.0 Service Pack 4	4/26/2002	Review Note	Review Note		Support ends 12 months after the next service pack releases or at the end of the product's support lifecycle, whichever comes first. For more information, please see the service pack policy at http://support.microsoft.com/lifecycle/#ServicePackSupport.
SQL Server 7.0 Standard Edition	3/1/1999	12/31/2005	1/11/2011
SQL Server Compact 3.5	2/19/2008	4/9/2013	4/10/2018	10/13/2009
SQL Server Compact 3.5 Service Pack 1 for Windows Mobile	8/11/2008	Not Applicable	Not Applicable	7/12/2011
SQL Server Compact 3.5 Service Pack 2	6/29/2010	Review Note	Review Note		Support ends 12 months after the next service pack releases or at the end of the product's support lifecycle, whichever comes first. For more information, please see the service pack policy at http://support.microsoft.com/lifecycle/#ServicePackSupport.
SQL Server Compact 4.0	4/13/2011	7/12/2016	7/13/2021
SQL Server Notification Services 2.0 Enterprise Edition	11/26/2002	1/8/2008	1/8/2013
SQL Server Notification Services 2.0 Standard Edition	11/26/2002	1/8/2008	1/8/2013

details of FULL_LOG_Transactional_Differential Backups.

--Get Full Database Backups
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'D'
ORDER BY backup_set_id DESC
GO
--Get Transaction Log Backups
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'L'
ORDER BY backup_set_id DESC
GO
--Get Differential Backups details
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'I'
ORDER BY backup_set_id DESC
GO
--File\File Group Backups
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'F'
ORDER BY backup_set_id DESC
GO

node_cluster _details

SELECT SERVERPROPERTY('ServerName') as 'Server\Instance',
  SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as 'Node',
  (select left(af.filename,3)from sys.sysaltfiles af where af.name like '%MSDB%' and af.fileid = 1) as 'Drive',
  ec.local_net_address as 'IP Addr',
  ec.local_tcp_port as 'SQL Port'
from sys.dm_exec_connections ec
where ec.session_id = @@SPID

getting Serial Number thru cmd

to get the serial number of the laptop/machine
type in cmd
wmic bios get serialnumber

Grant rights to user

declare @sql nvarchar(4000)


declare @db sysname ; set @db = DB_NAME()

declare @u sysname ; set @u = QUOTENAME('')

set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +

QUOTENAME(ROUTINE_NAME) + '' TO ' + @u + ''' FROM INFORMATION_SCHEMA.ROUTINES ' +

'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'



exec master.dbo.xp_execresultset @sql,@db

Installation SQL

http://blogs.technet.com/b/seanearp/archive/2008/07/01/installing-sql-server-2005-on-windows-server-2008.aspx
List of known issues
http://support.microsoft.com/kb/936302

MS SQL downloads

Management Studio Download link..

http://www.microsoft.com/download/en/details.aspx?id=22985

MS SQL Express ..

http://www.microsoft.com/download/en/details.aspx?id=23650

SQL Server Script to Display Job History

select job_name, run_datetime, run_duration from ( select job_name, run_datetime, SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' + SUBSTRING(run_duration, 5, 2) AS run_duration from ( select DISTINCT j.name as job_name, run_datetime = CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4, run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6) from msdb..sysjobhistory h inner join msdb..sysjobs j on h.job_id = j.job_id ) t ) t order by job_name, run_datetime

DISK I/O Latency

http://sqlskills.com/BLOGS/PAUL/post/How-to-examine-IO-subsystem-latencies-from-within-SQL-Server.aspx

SELECT
--virtual file latency
ReadLatency = CASE WHEN num_of_reads = 0
THEN 0 ELSE (io_stall_read_ms / num_of_reads) END,
WriteLatency = CASE WHEN num_of_writes = 0
THEN 0 ELSE (io_stall_write_ms / num_of_writes) END,
Latency = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE (io_stall / (num_of_reads + num_of_writes)) END,
--avg bytes per IOP
AvgBPerRead = CASE WHEN num_of_reads = 0
THEN 0 ELSE (num_of_bytes_read / num_of_reads) END,
AvgBPerWrite = CASE WHEN io_stall_write_ms = 0
THEN 0 ELSE (num_of_bytes_written / num_of_writes) END,
AvgBPerTransfer = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE ((num_of_bytes_read + num_of_bytes_written) /
(num_of_reads + num_of_writes)) END,
LEFT (mf.physical_name, 2) AS Drive,
DB_NAME (vfs.database_id) AS DB,
--vfs.*,
mf.physical_name
FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS vfs
JOIN sys.master_files AS mf
ON vfs.database_id = mf.database_id
AND vfs.file_id = mf.file_id
--WHERE vfs.file_id = 2 -- log files
-- ORDER BY Latency DESC
-- ORDER BY ReadLatency DESC
ORDER BY WriteLatency DESC;
GO

Backup History

We can get all the information for this task out of below msdb System Table: 

      msdb.dbo.backupset.  - Provides information concerning details of the backup process .The important columns of note for this object are itemized below. 
§  database_name - name of the database the backup file pertains to.
§  type - Type of backup process performed:
·         D = Database
·         F = File or Filegroup
·         G = Differential File
·         I = Differential (database)
·         L = Transaction Log
§  backup_finsh_date - time when the backup process completed.
§  backup_start_date - time the backup process was initiated.
§  name - name of the backupset
§  user_name - user performing the backup process
§  expiration_date - date the backup expires

SQL Scripts 1

SELECT
      sys.sysdatabases.Name AS DATABASE_NAME,
      COALESCE(CONVERT(VARCHAR(12),MAX(msdb.dbo.backupset.backup_finish_date), 101),'NEVER') AS LAST_DATABASE_BACKUP_DATE
FROM
      sys.sysdatabases
      LEFT OUTER JOIN msdb.dbo.backupset
      ON msdb.dbo.backupset.database_name = sys.sysdatabases.name
GROUP BY
      sys.sysdatabases.Name

SQL Scripts 2

SELECT
      msdb.dbo.backupmediafamily.physical_device_name AS FILE_LOCATION,
      msdb.dbo.backupset.database_name AS DATABASE_NAME,
      CAST(CAST(msdb.dbo.backupset.backup_size / 1000000 AS INT) AS VARCHAR(14))+ ' ' + 'MB'  AS BACKUP_SIZE,
      CAST(DATEDIFF(second, msdb.dbo.backupset.backup_start_date,      msdb.dbo.backupset.backup_finish_date) AS VARCHAR(4)) + ' '+ 'Seconds' AS BACKUP_TIME_TAKEN,
      msdb.dbo.backupset.backup_start_date AS BACKUP_DATE_TIME,
      CASE msdb.dbo.backupset.[type]
      WHEN 'D' THEN 'Full'
      WHEN 'I' THEN 'Differential'
      WHEN 'L' THEN 'Transaction Log'
      END AS BACKUP_TYPE,
      msdb.dbo.backupset.server_name AS SERVER_NAME
FROM
      msdb.dbo.backupset
      INNER JOIN msdb.dbo.backupmediafamily 
      ON msdb.dbo.backupset.media_set_id = msdb.dbo.backupmediafamily.media_set_id

SQL Scripts 3

SELECT
      ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(Backup_finish_date)))), 'NEVER') AS DAY_LAST_BACKUP_TAKEN,
      master.dbo.sysdatabases.name AS DATABASE_NAME,
      ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') AS LAST_BACKUP_DATE
FROM
      master.dbo.sysdatabases
      LEFT OUTER JOIN msdb.dbo.backupset
      ON msdb.dbo.backupset.database_name = master.dbo.sysdatabases.name
      AND msdb.dbo.backupset.type = 'D'
GROUP BY
      master.dbo.sysdatabases.Name

SQL Scripts 4

SELECT
      database_id AS DATABASE_ID,
      convert(varchar(25), sys.databases.name) AS DATABASE_NAME,
      convert(varchar(10), Databasepropertyex(name, 'status')) AS DATABASE_STATUS,
      (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = sys.databases.name AND type_desc = 'rows') AS DATAFILES ,
      (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = sys.databases.name AND type_desc = 'rows') AS DATA_FILE_SIZE_IN_MB,
      (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = sys.databases.name AND type_desc = 'log') AS LOG_FILES,
      (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = sys.databases.name AND type_desc = 'log') AS LOG_FILE_SIZE_IN_MB,
      CASE compatibility_level
      WHEN 60 THEN '60 (SQL Server 6.0)'
      WHEN 65 THEN '65 (SQL Server 6.5)'
      WHEN 70 THEN '70 (SQL Server 7.0)'
      WHEN 80 THEN '80 (SQL Server 2000)'
      WHEN 90 THEN '90 (SQL Server 2005)'
      END AS COMPATIBILITY_LEVEL,
      CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS DATABASE_CREATION_DATE,
      CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE ' ' END AS FULL_TEXT,
      CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE ' ' END AS AUTOCLOSE,
      CASE WHEN is_read_only = 1 THEN 'read only' ELSE ' ' END AS  READ_ONLY,
      CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE ' ' END AS AUTOSHRINK,
      CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE ' ' END AS AUTO_CREATE_STATISTICS,
      CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE ' ' END AS AUTO_UPDATE_STATISTICS,
      CASE WHEN is_in_standby = 1 THEN 'standby' ELSE ' ' END AS STAND_BY,
      CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE ' ' END AS CLEANLY_SHOUTDOWN,
      user_access_desc AS USER_ACCESS,
      recovery_model_desc AS  RECOVERY_MODEL
FROM
      sys.databases

SQL Jobs - TSQL

select name,
case notify_level_eventlog
when 2 then 'write to application eventlog'
else 'not writing to eventlog' end as Notify_level_eventlog,
case enabled when 1 then 'enabled'
else 'disabled' end as Enabled
from msdb.dbo.sysjobs

DISK I/O findings.

script to find the read/write (DISK I/O) on a particular database in 5 minutes duration.

SET NOCOUNT ON

DECLARE @IOStats TABLE (
[database_id] [smallint] NOT NULL,
[file_id] [smallint] NOT NULL,
[num_of_reads] [bigint] NOT NULL,
[num_of_bytes_read] [bigint] NOT NULL,
[io_stall_read_ms] [bigint] NOT NULL,
[num_of_writes] [bigint] NOT NULL,
[num_of_bytes_written] [bigint] NOT NULL,
[io_stall_write_ms] [bigint] NOT NULL)
INSERT INTO @IOStats
SELECT database_id,
vio.file_id,
num_of_reads,
num_of_bytes_read,
io_stall_read_ms,
num_of_writes,
num_of_bytes_written,
io_stall_write_ms
FROM sys.dm_io_virtual_file_stats (NULL, NULL) vio
DECLARE @StartTime datetime, @DurationInSecs int
SET @StartTime = GETDATE()
WAITFOR DELAY '00:05:00'
SET @DurationInSecs = DATEDIFF(ss, @startTime, GETDATE())
SELECT DB_NAME(vio.database_id) AS [Database],
mf.name AS [Logical name],
mf.type_desc AS [Type],
(vio.io_stall_read_ms - old.io_stall_read_ms) / CASE (vio.num_of_reads-old.num_of_reads) WHEN 0 THEN 1 ELSE vio.num_of_reads-old.num_of_reads END AS [Ave read speed (ms)],
vio.num_of_reads - old.num_of_reads AS [No of reads over period],
CONVERT(DEC(14,2), (vio.num_of_reads - old.num_of_reads) / (@DurationInSecs * 1.00)) AS [No of reads/sec],
CONVERT(DEC(14,2), (vio.num_of_bytes_read - old.num_of_bytes_read) / 1048576.0) AS [Tot MB read over period],
CONVERT(DEC(14,2), ((vio.num_of_bytes_read - old.num_of_bytes_read) / 1048576.0) / @DurationInSecs) AS [Tot MB read/sec],
(vio.num_of_bytes_read - old.num_of_bytes_read) / CASE (vio.num_of_reads-old.num_of_reads) WHEN 0 THEN 1 ELSE vio.num_of_reads-old.num_of_reads END AS [Ave read size (bytes)],
(vio.io_stall_write_ms - old.io_stall_write_ms) / CASE (vio.num_of_writes-old.num_of_writes) WHEN 0 THEN 1 ELSE vio.num_of_writes-old.num_of_writes END AS [Ave write speed (ms)],
vio.num_of_writes - old.num_of_writes AS [No of writes over period],
CONVERT(DEC(14,2), (vio.num_of_writes - old.num_of_writes) / (@DurationInSecs * 1.00)) AS [No of writes/sec],
CONVERT(DEC(14,2), (vio.num_of_bytes_written - old.num_of_bytes_written)/1048576.0) AS [Tot MB written over period],
CONVERT(DEC(14,2), ((vio.num_of_bytes_written - old.num_of_bytes_written)/1048576.0) / @DurationInSecs) AS [Tot MB written/sec],
(vio.num_of_bytes_written-old.num_of_bytes_written) / CASE (vio.num_of_writes-old.num_of_writes) WHEN 0 THEN 1 ELSE vio.num_of_writes-old.num_of_writes END AS [Ave write size (bytes)],
mf.physical_name AS [Physical file name],
size_on_disk_bytes/1048576 AS [File size on disk (MB)]
FROM sys.dm_io_virtual_file_stats (NULL, NULL) vio,
sys.master_files mf,
@IOStats old
WHERE mf.database_id = vio.database_id AND
mf.file_id = vio.file_id AND
old.database_id = vio.database_id AND
old.file_id = vio.file_id AND
((vio.num_of_bytes_read - old.num_of_bytes_read) + (vio.num_of_bytes_written - old.num_of_bytes_written)) > 0
ORDER BY ((vio.num_of_bytes_read - old.num_of_bytes_read) + (vio.num_of_bytes_written - old.num_of_bytes_written)) DESC
GO

MS SQL Server Interview info

http://www.scribd.com/doc/22171697/ms-sql-server-interview-questions1

SQL Server Cluster

Windows Clustering Configuration
Before you can install SQL Server 2005 clustering, you must first install Windows Server 2003 clustering services. Once it is successfully installed and tested, then you can install SQL Server 2005 clustering. In this article, we take a step-by-step approach to installing and configuring Windows 2003 clustering. In a later article, we will learn how to install SQL Server 2005 clustering.

Before Installing Windows 2003 Clustering
Before you install Windows 2003 clustering, we need to perform a series of important preparation steps. This is especially important if you didn't build the cluster nodes, as you want to ensure everything is working correctly before you begin the actual cluster installation. Once they are complete, then you can install Windows 2003 clustering. Here are the steps you must take:
• Double check to ensure that all the nodes are working properly and are configured identically (hardware, software, drivers, etc.).
• Check to see that each node can see the data and Quorum drives on the shared array or SAN. Remember, only one node can be on at a time until Windows 2003 clustering is installed.
• Verify that none of the nodes has been configured as a Domain Controller.
• Check to verify that all drives are NTFS and are not compressed.
• Ensure that the public and private networks are properly installed and configured.
• Ping each node in the public and private networks to ensure that you have good network connections. Also ping the Domain Controller and DNS server to verify that they are available.
• Verify that you have disabled NetBIOS for all private network cards.
• Verify that there are no network shares on any of the shared drives.
• If you intend to use SQL Server encryption, install the server certificate with the fully qualified DNS name of the virtual server on all nodes in the cluster.
• Check all of the error logs to ensure there are no nasty surprises. If there are, resolve them before proceeding with the cluster installation.
• Add the SQL Server and Clustering service accounts to the Local Administrators group of all the nodes in the cluster.
• Check to verify that no antivirus software has been installed on the nodes. Antivirus software can reduce the availability of clusters and must not be installed on them. If you want to check for possible viruses on a cluster, you can always install the software on a non-node and then run scans on the cluster nodes remotely.
• Check to verify that the Windows Cryptographic Service Provider is enabled on each of the nodes.
• Check to verify that the Windows Task Scheduler service is running on each of the nodes.
• If you intend to run SQL Server 2005 Reporting Services, you must then install IIS 6.0 and ASP .NET 2.0 on each node of the cluster.
These are a lot of things you must check, but each of these is important. If skipped, any one of these steps could prevent your cluster from installing or working properly.

How to Install Windows Server 2003 Clustering
Now that all of your physical nodes and shared array or SAN is ready, you are now ready to install Windows 2003 clustering. In this section, we take a look at the process, from beginning to end.
To begin, you must start the Microsoft Windows 2003 Clustering Wizard from one of the nodes. While it doesn't make any difference to the software which physical node is used to begin the installation, I generally select one of the physical nodes to be my primary (active) node, and start working there. This way, I won't potentially get confused when installing the software.
If you are using a SCSI shared array, and for many SAN shared arrays, you will want to make sure that the second physical node of your cluster is turned off when you install cluster services on the first physical node. This is because Windows 2003 doesn't know how to deal with a shared disk until cluster services is installed. Once you have installed cluster services on the first physical node, you can turn on the second physical node, boot it, and then proceed with installing cluster services on the second node.

Useful SQL Server Websites and Blogs

http://madhuottapalam.blogspot.com/
http://vyaskn.tripod.com/
http://experiencing-sql-server-2008.blogspot.com/
http://www.extremeexperts.com/SQL/Articles/default.aspx

http://sqllearnings.wordpress.com/

MS SQL Server Log shipping

Prerequisites

http://sqlserverpedia.com/wiki/Prerequisites_for_Log_Shipping_(SQL_Server_2005)

1. Both source and target instances of log shipping must be running SQL Server 2005 Enterprise Edition, Workgroup Edition or Standard Edition.
   
2. Account(s) running SQL Server service on source and destination instance must have access to the network folder where you will store transaction log backup files.
   
3. Computers hosting primary and standby SQL Servers must be connected to a network.
   
4. SQL Server Agent service must be running on both source and destination servers. Log shipping is implemented as a collection of jobs. If SQL Server Agent service isn't running, none of the jobs will execute.
   
5. Log shipping must be setup at the database level. The database must use either full or bulk logged recovery model. Simple recovery model truncates transaction log during each checkpoint and does not support transaction log backups. Therefore databases that use simple recovery model cannot participate in log shipping. If you want to provide high availability for multiple databases on a single instance of SQL Server, you must setup log shipping separately for each database.
   
6. Although not a prerequisite for log shipping configuration, it is essential to realize that in case of failover the target server becomes your primary database server. If you want to provide acceptable level of service to your user community in case of the primary server's failure, then ensure that the standby (target) server has the same hardware configuration as the primary (source) server.

http://databases.about.com/od/sqlserver/ht/Transaction_Log_Shipping.htm

Log shipping allows you to keep two SQL Server databases synchronized for high availability. In a log shipping relationship, the primary server transfers copies of its transaction logs to the secondary server on a periodic basis. The secondary server receives those logs and uses them to update its copy of the database so that it is then in the same state as the database on the primary server

Time Required: One hour

Here's How:

1.    Create the target database on the secondary server by restoring a full backup of the database from the primary server. Be sure to include backups of the transaction logs by using the WITH NORECOVERY option.
2.    Create a network file share on the primary server (or another location available to both servers) that will store the transaction log backups.
3.    Connect to the primary server using SQL Server Management Studio and expand the Databases folder. Locate the database you wish to replicate, right-click on it and choose Tasks -> Ship Transaction Logs from the pop-up menu.
4.    Select the "Enable This as a Primary Database in a Log Shipping Configuration" check box.
5.    Click the Backup Settings button.
6.    In the network share textbox, enter the path to the share you created in Step Two. If the share is on the primary server, also enter the path in the second textbox.
7.    Click OK to close the Transaction Log Backup Settings window.
8.    In the Secondary Databases section of the Transaction Log Shipping properties page, click the Add button.
9.    Click the Connect button and provide connection details for the secondary server that will receive the transaction log backups from the primary server.
10.  Verify that the Secondary Database drop-down box contains the name of the database you created on the secondary server in Step One.
11.  Select the Copy Files tab.
12.  Provide the destination folder for the copied files on the secondary server. This is a temporary storage location, so you may choose any path you wish.
13.  Review the other settings and make any changes you deem appropriate, then click the OK button to continue.
14.  Repeat Steps Nine through Thirteen for any additional secondary servers if you wish to configure more than one transaction log recipient.
15.  Click OK to close the Database Properties window. SQL Server will then display a status window while it configures transaction log shipping.



How to perform Log shipping


http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx

Hope this weblink may give an idea about log shipping..

http://www.tek-tips.com/faqs.cfm?fid=5754

How to: Enable Log Shipping (SQL Server Management Studio)
http://technet.microsoft.com/en-us/library/ms190640.aspx

Folder named CON - not in Microsoft OS..

Nobody can create a FOLDER anywhere on the computer which can be named as "CON".

This is something pretty cool...and unbelievable...At Microsoft the whole Team, couldn't answer why this happened!


TRY IT NOW, IT WILL NOT CREATE " CON " FOLDER

SQL Server 2005 websites..

Click to get SQL Server 2005 details..

http://www.sql-server-performance.com/articles/dba/bol_april_2006_p1.aspx


SQL Server 2005 online Book from Microsoft Site..
http://www.microsoft.com/downloads/thankyou.aspx?familyId=be6a2c5d-00df-4220-b133-29c1e0b6585f&displayLang=en

SQL Server Info

to Know the SQL Server Information check this

http://www.sql-server-performance.com/