Sunday, March 18, 2012
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
Wednesday, February 15, 2012
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
Friday, February 10, 2012
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
Tuesday, January 17, 2012
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
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
Monday, January 9, 2012
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
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
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
Thursday, August 18, 2011
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
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
Subscribe to:
Posts (Atom)
