May, 2016 | SQL Conjuror

SQL Server – Check Recent Backup of All Your SQL Server Databases

By Enrique 29/05/2016 03/05/2020 SQL SERVER, Database Administration, Uncategorized

Do you have a recent backup of all your SQL Server databases? Use the script below to check if you have.

SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),

MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,

ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate

FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A

ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name ORDER BY B.name

Share on linkedin

Share on facebook

Share on google

Share on twitter

SQL Server – Get Last Modified Function

By Enrique 27/05/2016 03/05/2020 SQL SERVER, Database Administration

To get the last modified function date, execute the script below.

SELECT name,

type_desc

create_date,

modify_date

FROM sys.objects

WHERE RIGHT(type_desc, 8) = 'FUNCTION'

ORDER BY modify_date DESC

Share on linkedin

Share on facebook

Share on google

Share on twitter

SQL Server – Get Physical Memory of Database Server

By Enrique 26/05/2016 03/05/2020 SQL SERVER, Database Administration

Below is a script that you can use to retrieve your DB Server’s Physical Memory

CREATE TABLE #SVer(ID int, Name sysname, Internal_Value int, Value nvarchar(512))

INSERT #SVer exec master.dbo.xp_msver

SELECT *

FROM #SVer

WHERE Name = 'PhysicalMemory'

GO

DROP TABLE #SVer

Share on linkedin

Share on facebook

Share on google

Share on twitter

SQL Server – Get Transaction Log Size and Log Space Used

By Enrique 24/05/2016 03/05/2020 SQL SERVER, Database Administration

Use the following T-SQL script to get the transaction log size and log space used of your database.

SELECT DB_NAME(database_id) AS DatabaseName ,

CAST(( total_log_size_in_bytes / 1048576.0 ) AS DECIMAL(10, 1))

AS TotalLogSizeMB ,

CAST(( used_log_space_in_bytes / 1048576.0 ) AS DECIMAL(10, 1))

AS LogSpaceUsedMB ,

CAST(used_log_space_in_percent AS DECIMAL(10, 1)) AS LogSpaceUsedPercent

FROM sys.dm_db_log_space_usage;

Share on linkedin

Share on facebook

Share on google

Share on twitter

SQL Server – Pending Disk IO Count

By Enrique 23/05/2016 06/05/2020 SQL SERVER, Performance Optimization

Below is a script to see the number of pending disk IO. The ideal value is 0. This value indicates the number of pending I/Os that are waiting to be completed. Each scheduler has a list of pending I/Os that are checked to determine whether they have been completed every time there is a context switch. The count is incremented when the request is inserted. This count is decremented when the request is completed.

SELECT SUM(pending_disk_io_count) AS [Number of pending I/Os]

FROM sys.dm_os_schedulers

Share on linkedin

Share on facebook

Share on google

Share on twitter

SQL Server – Check Constraint Status

By Enrique 16/05/2016 03/05/2020 SQL SERVER, Database Administration

Below is a script that you can use to check the constraint status of all your tables in the database.

SELECT (CASE

WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'

ELSE 'DISABLED'

END) AS STATUS,

OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,

OBJECT_NAME(FKEYID) AS TABLE_NAME,

COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,

OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,

COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME

FROM SYSFOREIGNKEYS

ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO

Output:

Share on linkedin

Share on facebook

Share on google

Share on twitter