ITTechINFO

MS SQL downloads

2012-02-15T11:55:00.000-08:00

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

2012-02-10T16:13:00.000-08:00

select job_name, run_datetime, run_durationfrom( 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) torder by job_name, run_datetime

DISK I/O Latency

2012-01-17T13:04:00.000-08:00

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

2012-01-09T16:48:00.000-08:00

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

2012-01-09T15:35:00.000-08:00

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.

2011-08-18T11:03:00.000-07:00

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

2009-11-18T04:00:00.000-08:00

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

SQL Server Cluster

2009-03-08T04:54:00.000-07:00

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

2008-11-13T03:35:00.000-08:00

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

2008-08-03T21:40:00.000-07:00

Prerequisites

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

Both source and target instances of log shipping must be running SQL Server 2005 Enterprise Edition, Workgroup Edition or Standard Edition.
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.
Computers hosting primary and standby SQL Servers must be connected to a network.
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.
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.
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 SQLServer databases synchronized for high availability. In a log shippingrelationship, the primary server transfers copies of its transaction logs tothe secondary server on a periodic basis. The secondary server receives thoselogs and uses them to update its copy of the database so that it is then in thesame state as the database on the primary server

Time Required: One hour

Here'sHow:

1.    Createthe target database on the secondary server by restoring a full backup of thedatabase from the primary server. Be sure to include backups of the transactionlogs by using the WITH NORECOVERY option.
2.    Createa network file share on the primary server (or another location available toboth servers) that will store the transaction log backups.
3.    Connectto the primary server using SQL Server Management Studio and expand theDatabases folder. Locate the database you wish to replicate, right-click on itand choose Tasks -> Ship Transaction Logs from the pop-up menu.
4.    Selectthe "Enable This as a Primary Database in a Log ShippingConfiguration" check box.
5.    Clickthe Backup Settings button.
6.    In thenetwork share textbox, enter the path to the share you created in Step Two. Ifthe share is on the primary server, also enter the path in the second textbox.
7.    ClickOK to close the Transaction Log Backup Settings window.
8.    In theSecondary Databases section of the Transaction Log Shipping properties page,click the Add button.
9.    Clickthe Connect button and provide connection details for the secondary server thatwill receive the transaction log backups from the primary server.
10. Verifythat the Secondary Database drop-down box contains the name of the database youcreated on the secondary server in Step One.
11. Selectthe Copy Files tab.
12. Providethe destination folder for the copied files on the secondary server. This is atemporary storage location, so you may choose any path you wish.
13. Reviewthe other settings and make any changes you deem appropriate, then click the OKbutton to continue.
14. RepeatSteps Nine through Thirteen for any additional secondary servers if you wish toconfigure more than one transaction log recipient.
15. Click OK to close the Database Properties window. SQL Serverwill 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..

2007-08-27T08:46:00.000-07:00

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..

2007-08-02T10:40:00.000-07:00

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

2007-06-20T11:08:00.000-07:00

to Know the SQL Server Information check this

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