REDGATE another useful script tool, ScriptsManager1.3

REDGATE another useful script tool ScriptsManager1.3: this tool is free of charge: this tool is a commonly used script tool, which already contains scripts, these scripts involve many aspects, including backup, diagnosis, export, index, space, templates, and tools. You can call these scripts without using them.

REDGATE another useful script tool ScriptsManager1.3: this tool is free of charge: this tool is a commonly used script tool, which already contains scripts, these scripts involve many aspects, including backup, diagnosis, export, index, space, templates, and tools. You can call these scripts without using them.

REDGATE another useful script tool, ScriptsManager1.3

The tool is free of charge.

:

This tool is a commonly used scripting tool, which already contains scripts of SQLSERVER. These scripts involve many aspects.

Including backup, diagnosis, export, index, space, template, and tools

You can call these scripts without opening your script folder to search for your notes.

Select the script and open it.

The purpose of the script is displayed.

Script code

Contributed script author information

These authors are from well-known database websites.

Click "Continue"

Select which database to run this script on

Click "run" to run

Running result

You can also export the result to a csv file.

You can also contribute your code to the community.

All script code in the tool

Script Daquan redgate ScriptsManager sd. name, bs. type bs. type bs. type bs. type bs. type bs. type bs. type, bs. backup_start_date master .. sysdatabases sdmsdb .. backupset bs (sd. name) msdb .. backupmediafamily bmf ON bs. media_set_id = bmf. media_set_idbs.backup_start_date sd. name, --, bs. type bs. type bs. type bs. type bs. type bs. type bs. type bs. type, master .. sysdatabases sdmsdb .. backupset bs (sd. name) msdb .. backupmediafam Ily bmf ON bs. media_set_id = bmf. media_set_id sd. name, bs. type, bs. database_name -- nocount # Data (FileID, TotalExtents, UsedExtents, sysname not null, (MAX) not null, (MAX) NULL) create table # Results (db sysname NULL, fileType varchar (4) not null, sysname not null, sysname not null, TotalMB numeric () not null, UsedMB numeric () not null, PctUsed numeric () NULL, filePath nvarchar (MAX) NULL, FileID in T null) create table # Log (db sysname not null, LogSize numeric (18,5) not null, LogUsed numeric (18,5) not null, Status, (MAX) NULL), TotalExtents, usedExtents,) update # Dataset # Data. fileGroup = sysfilegroups. groupnamefrom # Data, sysfilegroupswhere # Data. fileGroupId = sysfilegroups. groupid, FileType, TotalMB, UsedMB, PctUsed, FilePath, FileID) SELECT DB_NAME () db, FileType, TotalExtents. totalMB, UsedExtents UsedMB, UsedExtents * 100. /TotalExtents UsedPct, FileIDFROM # Data (), # Log (db, LogSize, LogUsed, Status), FileType, TotalMB, UsedMB, PctUsed, FilePath, FileID) select DB_NAME () db, FileType, s ., s. size/128. as LogSize,) LogUsedSpace ,(()).) usedPct, s. fileName FilePath, s. fileID FileIDfrom # Log l, master. dbo. sysaltfiles f, dbo. sysfiles swhere f. dbid = DB_ID () s. fileID = f. fileIDand l. db = DB _ NAME () SELECT r. db AS "Database", r. fileType AS "File type", r. fileGroupEND "File group", r. fileName AS "Logical file name", r. totalMB AS "Total size (MB)", r. usedMB AS "Used (MB)", r. pctUsed AS "Used (%)", r. filePath AS "File name", r. fileID AS "File ID", (decimal (18, 2), s. (maxsize/128 .) END "Max. size (MB) ", CONVERT (decimal (18, 2), s. growth/128 .) "Autogrowth increment (MB)" FROM # Results rINNER JOIN dbo. sy Sfiles sON r. fileID = s. fileID, # DataDROP TABLE # Results -- t2.cntr _ value (t2.cntr _ value) sys. dm_ OS _performance_counters t1, sys. dm_ OS _performance_counters t2WHEREt1. t1.t1. counter_namet2.counter_name -- (ST ., (CASE statement_end_offset (ST. text) else qs. statement_end_offset) + 1) AS "Statement Text", total_worker_time "Average Worker Time (MS)", execution_count AS "Execution Count", total_worker_time "Tot Al Worker Time (MS) ", total_logical_reads AS" Total Logical Reads ", total_logical_reads/execution_count AS" Average Logical Reads ", total_elapsed_time" Total Elapsed Time (MS )", total_elapsed_time "Average Elapsed Time (MS)", QP. query_plan AS "Query Plan (double click to open)" FROM sys. dm_exec_query_stats qscross apply sys. dm_exec_ SQL _text (QS. SQL _handle) STCROSS APPLY sys. dm_exec_query_plan (QS. plan_han Dle) QP -- () OBJECT_SCHEMA_NAME (ddips ., (ddips .,, I ., ddips ., ddips ., ddips ., ddips ., ddips .,),), ddips ., ddips. sys. dm_db_index_physical_stats (DB_ID (), NULL,) ddipsi I. ddips. i. ddips. ddips. ddips .,, DB_ID (), I. --, dopc_fraction ., dopc_fraction.instance_name, dopc_fraction.counter_name, (DECIMAL (38,2), CAST (alias as float)/CAST (CASE dopc_base.cntr_value) sys. dm _ OS _performance_counters AS dopc_baseJOIN sys. dm_ OS _performance_counters AS region Limit = UPPER, '') = UPPER (UPPER) (,'') ORDER BY province, UPPER, dopc_fraction.counter_name else o. nameFROM Sys. objects o (sys. indexes ii. type_desc) ----------------------------------------------------------------------------- (DTL .), DTL ., (,) DTL. (DTL. resource_associated_entity_id) (,) FROM sys. partitionsDTL .), DTL ., DTL ., DOWT ., DOWT ., DOWT ., DES_Blocked .,, (DEST_Blocked.text) else der. statement_end_offset), DOWT ., DES_Blocking ., DEST_Blocking ., DOWT. resource_description sys. dm_tran_locks DTLIN Ner join sys. dm_ OS _waiting_tasks DOWTON DTL. lock_owner_address = DOWT. resource_address DERDER. sys. dm_exec_sessions DES_BlockedDES_Blocked. sys. dm_exec_sessions DES_BlockingDES_Blocking.sys.dm_exec_connections. APPLY sys. dm_exec_ SQL _text () AS DEST_BlockingCROSS APPLY sys. dm_exec_ SQL _text (DER. SQL _handle) AS DEST_Blocked --------------------------------------------------------------------------------( 10) RANK (), execution_count, SUBSTRING (execText. text, deqs. statement_start_offset deqs. statement_start_offset deqs. statement_start_offset, deqs. statement_end_offset deqs. statement_end_offset deqs. statement_end_offset (execText. text) deqs. statement_start_offset deqs. statement_start_offset deqs. statement_start_offset), execText. sys. dm_exec_query_stats deqsCROSS APPLY sys. dm_exec_ SQL _text (deqs. plan_h Andle) AS execTextORDER BY deqs. total_worker_time DESC; values (database_id), values, num_of_reads, CAST (values/(1.0 + num_of_reads) as numeric (10, 1), io_stall_write_ms, num_of_writes, CAST (io_stall_write_ms/(1.0 + num_of_writes) as numeric (10, 1), io_stall_read_ms, num_of_reads, CAST (I O_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) sys. dm_io_virtual_file_stats (; -- FROM sys. dm_io_virtual_file_stats (DB_ID ('adventureworks'), NULL) define OBJECT_SCHEMA_NAME (ddius. (ddius .,) (DECIMAL (38,2), CAST (SUM (user_seeks + user_scans + user_lookups) as decimal)/CAST (SUM (user_updates + user_seeks + User_scans + user_lookups) as decimal),) (DECIMAL (38,2), CAST (SUM (user_updates) as decimal)/CAST (SUM (user_updates + user_seeks + user_scans + user_lookups) as decimal), sys. dm_db_index_usage_stats AS ddiusddius. index_id = I. index_id,) ddius. OBJECT_SCHEMA_NAME (ddius. (ddius. object_id) ------------------------------------------------------------------------------ list the longest resource waits at the server instance level to help you identify system bottlenecks W ITH WaitsAS (SELECT wait_type, wait_time_ms/1000. AS wait_time_sec, 100. * wait_time_ms/SUM (wait_time_ms) OVER () AS pct, ROW_NUMBER () OVER (order by wait_time_ms DESC) AS rnFROMsys. dm_ OS _wait_stats,), Waitswait_time_sec --, der. command, dest ., des. login_time, des ., des ., der. session_id, der. status, sys. dm_exec_requests derder. session_id = dec. session_idINNER JOIN sys. dm_exec _ Sessions desON des. session_id = der. session_idCROSS APPLY sys. dm_exec_ SQL _text (SQL _handle) AS dest -- (60) (1400) name from msdb. sys. sysdatabasesCREATE TABLE # parts (dbname VARCHAR (60), part int) OPEN getdb # parts VALUES (@ db, 0 ). sys. sysobjects sowhere sp. object_id = so. id and partition_number! = 1 and # parts. dbname = (@ vsql) getdb, part # partsdrop table # partsdeallocate getdb Partitioned Tables into Tables -- (2000) # TempLog (LogDateDATETIME, ProcessInfo NVARCHAR (50), (MAX )) create table # logF (ArchiveNumberINT, LogDateDATETIME, LogSizeINT) insert into # logF EXEC partition (ArchiveNumber) FROM # logF # TempLog (ArchiveNumber) FROM # logF (, Text AS DetailsFROM # TempLog (, text AS DetailsFROM # TempLog # TempLogDROP TABLE # logF --------------------------------------------------------------------- (), master. dbo. sysdatabasesmaster. dbo. sysdatabases