The T-SQL query below will check if “Enforce Password Policy” is On. A value of 1 under is_policy_checked column indicates that it is turned on. Otherwise, it is disabled.
select name, is_policy_checked from sys.sql_logins
No comments
SQL Server – The EXECUTE permission was denied on the object ‘xp_instance_regread’, database ‘mssqlsystemresource’,schema ‘sys’ (Error 229)
One of our users encountered the error below.
I was able to fix the problem by granting the user direct access to the procedure.
USE master GO GRANT EXECUTE ON [sys].[xp_instance_regread] TO [DOMAIN\USER]; --To veriy that the user has been granted the privilege EXECUTE AS USER = 'DOMAIN\USER';Select * from fn_my_permissions('xp_instance_regread','Object')
SQL Server – View FileNames and Paths Using TSQL
The T-SQL script below can be used to check the Filenames and paths of your data and log files.
SELECT DB_NAME ([database_id]) AS [Database Name] ,name ,physical_name ,type_desc ,state_desc ,CONVERT(bigint, size/128.0) AS [Total Size in MB] ,CONVERT(decimal(18,2), size/131072.0) AS [Total Size in GB] FROM sys.master_files WITH(NOLOCK) WHERE [database_id] > 4 AND [database_id] <> 32767 OR [database_id] = 2 ORDER BY DB_NAME ([database_id]) OPTION(RECOMPILE)
Output
SQL Server – Check Installation Date of SQL Server
SQL Server – Get VLF Counts for All Databases in an Instance
Below is a script to get the VLF count for all databases on an instance.
CREATE TABLE #VLFInfo ( RecoveryUnitID INT ,FileID INT ,FileSize BIGINT ,StartOffset BIGINT ,FSeqNo BIGINT ,Status BIGINT ,Parity BIGINT ,CreateLSN NUMERIC(38) ); CREATE TABLE #VLFCountResults ( DatabaseName sysname ,VLFCount INT ) EXEC sp_MSforeachdb N'Use [?]; INSERT INTO #VLFInfo EXEC sp_executesql N''DBCC LOGINFO([?])''; INSERT INTO #VLFCountResults SELECT DB_NAME(), COUNT(*) FROM #VLFInfo; TRUNCATE TABLE #VLFInfo;' SELECT DatabaseName ,VLFCount FROM #VLFCountResults ORDER BY VLFCount DESC DROP TABLE #VLFInfo DROP TABLE #VLFCountResults -- High VLF counts can affect write performance -- and they can make database restored and recovery take much longer
The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication
I encountered the error below during Transaction Replication setup between SQL Server 2008 R2 (Publication) and SQL Server 2012 (Subscriber). When you hit this error, what you need to do is to set up the Subscriber from SQL Server 2012 Management studio in the ‘Subscriber’ database server.
SQL Server – Check Latest Transaction Log Backup
To know what the last transaction log backup of your database was, execute the T-SQL script below.
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS ServerName,
msdb.dbo.backupset.database_name AS DatabaseName,
MAX(msdb.dbo.backupset.backup_finish_date) AS Last_TLog_Backup_Date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'L'
GROUP BY
msdb.dbo.backupset.database_name
ORDER BY
msdb.dbo.backupset.database_name
SQL Server – Check SQL Server Error Logs via T-SQL
Below is a script to check SQL Server Error logs.
declare @Time_Start datetime;
declare @Time_End datetime;
set @Time_Start=getdate()-2;
set @Time_End=getdate();
-- Create the temporary table
CREATE TABLE #ErrorLog (logdate datetime
, processinfo varchar(255)
, Message varchar(MAX))
-- Populate the temporary table
INSERT #ErrorLog (logdate, processinfo, Message)
EXEC master.dbo.xp_readerrorlog 0, 1, null, null , @Time_Start, @Time_End, N'desc';
-- Filter the temporary table
SELECT LogDate, Message FROM #ErrorLog
WHERE (Message LIKE '%error%' OR Message LIKE '%failed%') AND processinfo NOT LIKE 'logon'
ORDER BY logdate DESC
-- Drop the temporary table
DROP TABLE #ErrorLog
SQL Server – TSQL Script to Check Job Run Status
Below is a T-SQL script to check SQL job run status.
SET NOCOUNT ON
--Checking for SQL Server verion
IF CONVERT(tinyint,(SUBSTRING(CONVERT(CHAR(1),SERVERPROPERTY('productversion')),1,1))) <> 8
BEGIN
---This is for SQL 2k5 and SQL2k8 servers
SET NOCOUNT ON
SELECT Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,
j.name AS job_name,
CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status,
CASE jh.run_status WHEN 0 THEN 'Error Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress' ELSE
'Status Unknown' END AS 'last_run_status',
ja.run_requested_date as last_run_date,
CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,
ja.next_scheduled_run_date,
CONVERT(VARCHAR(500),jh.message) AS step_description
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE ja.session_id=(SELECT MAX(session_id) from msdb.dbo.sysjobactivity) ORDER BY job_name,job_status
END
ELSE
BEGIN
--This is for SQL2k servers
SET NOCOUNT ON
DECLARE @SQL VARCHAR(5000)
--Getting information from sp_help_job to a temp table
SET @SQL='SELECT job_id,name AS job_name,CASE enabled WHEN 1 THEN ''Enabled'' ELSE ''Disabled'' END AS job_status,
CASE last_run_outcome WHEN 0 THEN ''Error Failed''
WHEN 1 THEN ''Succeeded''
WHEN 2 THEN ''Retry''
WHEN 3 THEN ''Cancelled''
WHEN 4 THEN ''In Progress'' ELSE
''Status Unknown'' END AS last_run_status,
CASE RTRIM(last_run_date) WHEN 0 THEN 19000101 ELSE last_run_date END last_run_date,
CASE RTRIM(last_run_time) WHEN 0 THEN 235959 ELSE last_run_time END last_run_time,
CASE RTRIM(next_run_date) WHEN 0 THEN 19000101 ELSE next_run_date END next_run_date,
CASE RTRIM(next_run_time) WHEN 0 THEN 235959 ELSE next_run_time END next_run_time,
last_run_date AS lrd, last_run_time AS lrt
INTO ##jobdetails
FROM OPENROWSET(''sqloledb'', ''server=(local);trusted_connection=yes'', ''set fmtonly off exec msdb.dbo.sp_help_job'')'
exec (@SQL)
--Merging run date & time format, adding run duration and adding step description
select Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,jd.job_name,jd.job_status,jd.last_run_status,
CONVERT(DATETIME,RTRIM(jd.last_run_date)) +(jd.last_run_time * 9 + jd.last_run_time % 10000 * 6 + jd.last_run_time % 100 * 10) / 216e4 AS last_run_date,
CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,
CONVERT(DATETIME,RTRIM(jd.next_run_date)) +(jd.next_run_time * 9 + jd.next_run_time % 10000 * 6 + jd.next_run_time % 100 * 10) / 216e4 AS next_scheduled_run_date,
CONVERT(VARCHAR(500),jh.message) AS step_description
from (##jobdetails jd LEFT JOIN msdb.dbo.sysjobhistory jh ON jd.job_id=jh.job_id AND jd.lrd=jh.run_date AND jd.lrt=jh.run_time) where step_id=0 or step_id is null
order by jd.job_name,jd.job_status
--dropping the temp table
drop table ###jobdetails
END
SQL Server – Setup SQL Profiler to Capture Costly Queries / Stored Procedures
Click Tools > SQL Server Profiler
Connect to the instance
On this page, perform the following:
Indicate Trace name
Choose “Tuning” template
Save to file
Set 1000 maximum file size
Set trace stop time (optional. You may stop the trace anytime)
Go to Events Selection tab
Tick Show all columns and choose the following columns
Click Column Filters
Filter DatabaseName and Duration (>= 500ms)
Click Run
