October 14, 2013 at 11:59 am
Perry Whittle (10/14/2013)
Hidoesnt seem to support mounted volumes, will you be addressing this?
Eventually perhaps, but likely not in the near future (just need the time to do it really). 🙂
October 14, 2013 at 12:01 pm
Torsten Schüßler-406123 (10/14/2013)
Hi Sean,I like your script.
What about some more instance property information - I mean something like this:
...,instance_name =
CASE
WHEN SERVERPROPERTY (N'InstanceName') IS NULL THEN 'Default Instance'
ELSE SERVERPROPERTY (N'InstanceName')
END
, @@LANGUAGE as [Language]
, right(substring(@@version,charindex(' - ',@@version)+17,PATINDEX('%Copyright%',@@version)-27-(charindex(' - ',@@version)+17)),3) as [32|64]
,(CASE WHEN CONVERT(CHAR(30), Serverproperty('ISIntegratedSecurityOnly')) = 1
THEN 'Integrated Security '
WHEN CONVERT(CHAR(30), Serverproperty('ISIntegratedSecurityOnly')) = 0
THEN 'SQL Server Security '
ELSE 'INVALID INPUT/ERROR'
END) AS 'SECURITY',
CU
tosc
Oh, I like this. Okay with you if I officially add it as part of the next release?
October 14, 2013 at 12:36 pm
No problem. 🙂
...
(CASE WHEN CONVERT(CHAR(30), Serverproperty('ISSingleUser')) = 1
THEN 'Single User'
WHEN CONVERT(CHAR(30), Serverproperty('ISSingleUser')) = 0
THEN 'Multi User'
ELSE 'INVALID INPUT/ERROR'
END) AS 'USER MODE',
[font="Verdana"]CU
tosc[/font]
October 14, 2013 at 1:12 pm
Fantastic! Added to the next release's to do list. 🙂 Thanks for sharing!
October 14, 2013 at 1:54 pm
Great work ! thanks for script. I was able to get the report in email by adding the Global parameter @profile in the script.
Thanks again 🙂
October 14, 2013 at 2:27 pm
My pleasure. 🙂
October 21, 2013 at 9:26 am
Great update.
I added in some conditional highlighting if anyone was interested in seeing it. Currently it will turn the cell red if a differential backup is over x days. I'm also using it in a few other places.
,'',(CASE WHEN x.backup_type = 'Differential Database' THEN
CASE WHEN X.days_ago > @Diff_Threshold
THEN 'bkalert' + X.days_ago
ELSE 'right_align' + X.days_ago
END
ELSE 'right_align' + X.days_ago
END) AS 'td'
SET @Body = Replace (@Body, N'<td>bkalert', N'<td bgcolor="#E0A4A4" align="right">')
October 21, 2013 at 9:35 am
Oh, nice!
December 19, 2013 at 9:04 am
... 🙂
,(CASE
WHEN CONVERT(varchar(128), SERVERPROPERTY('ProductVersion')) LIKE '11%' Then
(CASE SERVERPROPERTY('IsHadrEnabled')
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'N/A'
END)
ELSE 'N/A'
END ) as IsHadrEnabled
,(CASE
WHEN CONVERT(varchar(128), SERVERPROPERTY('ProductVersion')) LIKE '11%' Then
(CASE SERVERPROPERTY('HadrManagerStatus')
WHEN 0 THEN 'Not started, pending'
WHEN 1 THEN 'Started'
WHEN 2 THEN 'Not started, failed'
ELSE 'N/A'
END)
ELSE 'N/A'
END ) as HadrManagerStatus
**************
,(CASE sqCFG.remote_admin_connections
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'N/A'
END) AS remote_admin_connections
,(CASE sqCFG.backup_compression_default
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'N/A'
END) AS backup_compression_default
INTO
dbo.#temp_sssr_server_settings
FROM
-- ...
,MAX (CASE
WHEN CFG.configuration_id = 1576 THEN CFG.value
END) AS remote_admin_connections
,MAX (CASE
WHEN CFG.configuration_id = 1579 THEN CFG.value
END) AS backup_compression_default
FROM
sys.configurations CFG
WHERE
CFG.configuration_id IN (518, 1543, 1544, 1562, 1568, 1576, 1579, 16386, 16390)
) sqCFG
[/code]
Wishing you and yours all the best this holiday season!
[font="Verdana"]CU
tosc[/font]
December 19, 2013 at 9:48 am
Thanks for sharing! I've actually added some of these so the next version which I will be posting soon. 🙂
December 19, 2013 at 10:20 am
Actually, here's what I've done so far (needs to be uploaded to SSC.com):
Bug Fixes
Enhanced HTML Output
"Index" Ouputs Now Include Indexed Views
Added "instance_name", "product_name", And "server_memory_mb" To "Instance Information" Section
Added "authentication", "date_format", "fill_factor_percentage", "is_compressed_backups_enabled", "is_optimize_for_ad_hoc_workloads_enabled""is_scan_for_startup_procs_enabled", "is_sql_mail_enabled", "language", And "locale_id" To "Instance Settings" Section
Added "cached_mb" To "Database Summary" Section
Added "database_owner", "drive_letter", "file_name", "file_path", "file_read_mb", "file_reads", "file_writes", "file_written_mb", "io_wait_time_reads", And "io_wait_time_writes" To "Database Details" Section
Added "physical_device_name" To "Last Backup Set Details" Section
Added "category", "class", "date_created", "date_modified", "job_owner", "type", And "version" To "SQL Server Agent Jobs (Last 24 Hours)" Section
Added "filter_definition", "include_key", "object_type", And "row_count" To "Index Fragmentation" Section
Added "object_type" And "row_count" To "Missing Indexes" Section
Added "filter_definition", "include_key", "object_type", And "row_count" To "Unused Indexes" Section
January 2, 2014 at 5:15 am
Hi Sean,
very nice script.:-)
Is it possible to insert the result of Database Summery/Stats into a separate table in your script? I would like to trend the database size.
best regards
mattias
January 2, 2014 at 6:20 am
Sure. You would just need to define a table (or tables) for the elements of the report you wish to trend and then add something like the following code right before the proc drops the temp table:
--> NEW CODE
INSERT INTO dbo.sssr_database_summary_archive
SELECT
*
FROM
dbo.#temp_sssr_database_summary
--< NEW CODE
IF OBJECT_ID (N'tempdb.dbo.#temp_sssr_database_summary', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_sssr_database_summary
END
You'll likely want do add date information and perhaps some other stuff, but you should get the general idea. 🙂
January 2, 2014 at 7:07 am
Thanks a lot Sean:)
I created a tbl like your #temp but added id and date columns, now I just have to figure out how to add the timestamp for the insert to the date column.
best regards
//Mattias
January 2, 2014 at 7:11 am
You could do something like this:
INSERT INTO dbo.sssr_database_summary_archive
SELECT
*
,GETDATE () AS report_date
FROM
dbo.#temp_sssr_database_summary
Viewing 15 posts - 136 through 150 (of 189 total)
You must be logged in to reply to this topic. Login to reply