Virtual Log Active and Inactive

Hi,
This is a script which will help to look up the active and inactive virtual log file count.


CREATE TABLE #eachDB(FileID INT, FileSize BIGINT, StartOffset BIGINT, FSeqNo BIGINT
, [Status] BIGINT, Parity BIGINT, CreateLSN NUMERIC(38))

CREATE TABLE #summary(Database_Name sysname, VLF INT, VLF_Count INT, Log_File_ID INT)

EXEC sp_MSforeachdb N'Use [?];

Insert Into #eachDB

Exec sp_executesql N''DBCC LogInfo(?)'';

Insert Into #summary

Select DB_Name(),Status,Count( Status), FileID

From #eachDB
group by Status,FileID

Truncate Table #eachDB;'

SELECT Database_Name,Case When VLF=2 Then 'Active' else 'InActive' End as Virtual_log,VLF_Count,Log_File_ID
FROM #summary
where Database_Name not in ('model','master','tempdb','msdb')
ORDER BY Database_Name,Log_File_ID ;

DROP TABLE #eachDB;
DROP TABLE #summary;

Tushar

Generating Script for re-building and re-organising indexes

Hi,
This script will help to prepare the script for re-building or re-oragnising index based on 30% default fragment index.

-- USE DB
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
-- EXEC (@command); -- the execute command is commented here..
PRINT N'Executed: ' + @command;
END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

reference: sqlservercentral.com

Tushar

Listing Jobs names and Schedule

Hi,
This is a script to detail the job names and schedule.

Script:
use msdb
Select Job.job_name, Job.category,

CASE
WHEN Job.enabled=0 THEN 'INACTIVE'
WHEN Job.enabled=1 THEN 'ACTIVE'
END AS STATUS

,D.schedule
from
(select j.name as job_name,c.name as category,j.enabled ,j.job_id
from sysjobs j
join syscategories c on c.category_id=j.category_id
) as Job
join sysjobschedules sc on sc.job_id = Job.job_id
join
(SELECT s.schedule_id,
s.[name],
CASE
WHEN s.freq_type = 0x1
THEN
'Once on '
+ CONVERT (
CHAR (10),
CAST (CAST (s.active_start_date AS VARCHAR) AS DATETIME),
102)
WHEN s.freq_type = 0x4 AND s.freq_interval > 0
THEN
CASE
WHEN s.freq_interval > 1
THEN
'Every ' + CAST (s.freq_interval AS VARCHAR) + ' days'
ELSE
'Every day'
END
WHEN s.freq_type = 0x8
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN
'Weekly on '
WHEN s.freq_recurrence_factor > 1
THEN
'Every '
+ CAST (s.freq_recurrence_factor AS VARCHAR)
+ ' weeks on '
END
+ LEFT (
CASE
WHEN s.freq_interval & 1 = 1 THEN 'Sunday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 2 = 2 THEN 'Monday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 16 = 16 THEN 'Thursday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 32 = 32 THEN 'Friday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 64 = 64 THEN 'Saturday, '
ELSE ''
END,
LEN (
CASE
WHEN s.freq_interval & 1 = 1 THEN 'Sunday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 2 = 2 THEN 'Monday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 16 = 16 THEN 'Thursday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 32 = 32 THEN 'Friday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 64 = 64 THEN 'Saturday, '
ELSE ''
END)
- 1)
WHEN s.freq_type = 0x10
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN
'Monthly on the '
WHEN s.freq_recurrence_factor > 1
THEN
'Every '
+ CAST (s.freq_recurrence_factor AS VARCHAR)
+ ' months on the '
END
+ CAST (s.freq_interval AS VARCHAR)
+ CASE
WHEN s.freq_interval IN (1, 21, 31) THEN 'st'
WHEN s.freq_interval IN (2, 22) THEN 'nd'
WHEN s.freq_interval IN (3, 23) THEN 'rd'
ELSE 'th'
END
WHEN s.freq_type = 0x20
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN
'Monthly on the '
WHEN s.freq_recurrence_factor > 1
THEN
'Every '
+ CAST (s.freq_recurrence_factor AS VARCHAR)
+ ' months on the '
END
+ CASE s.freq_relative_interval
WHEN 0x01 THEN 'first '
WHEN 0x02 THEN 'second '
WHEN 0x04 THEN 'third '
WHEN 0x08 THEN 'fourth '
WHEN 0x10 THEN 'last '
END
+ CASE s.freq_interval
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
WHEN 8 THEN 'day'
WHEN 9 THEN 'week day'
WHEN 10 THEN 'weekend day'
END
WHEN s.freq_type = 0x40
THEN
'Automatically starts when SQLServerAgent starts.'
WHEN s.freq_type = 0x80
THEN
'Starts whenever the CPUs become idle'
ELSE
''
END
+ CASE
WHEN s.freq_subday_type = 0x1 OR s.freq_type = 0x1
THEN
' at '
+ LEFT (s.active_start_time, 2)
+ ':'
+ substring (s.active_start_time, 3, 2)
+ ':'
+ right (s.active_start_time, 2)
WHEN s.freq_subday_type IN (0x2, 0x4, 0x8)
THEN
' every '
+ CAST (s.freq_subday_interval AS VARCHAR)
+ CASE freq_subday_type
WHEN 0x2 THEN ' second'
WHEN 0x4 THEN ' minute'
WHEN 0x8 THEN ' hour'
END
+ CASE WHEN s.freq_subday_interval > 1 THEN 's'
ELSE ' '
END
ELSE
' '
END
+ CASE
WHEN s.freq_subday_type IN (0x2, 0x4, 0x8)
THEN
' between '
+ LEFT (s.active_start_time, 2)
+ ':'
+ substring (s.active_start_time, 3, 2)
+ ':'
+ right (s.active_start_time, 2)
+ ' and '
+ LEFT (s.active_end_time, 2)
+ ':'
+ substring (s.active_end_time, 3, 2)
+ ':'
+ right (s.active_end_time, 2)
ELSE
''
END
AS schedule
FROM (
SELECT schedule_id,
[name],
freq_type,
freq_interval,
freq_subday_type,
freq_subday_interval,
freq_relative_interval,
freq_recurrence_factor,
active_start_date,
active_end_date,
REPLICATE ('0', 6 - len (cast (active_start_time AS VARCHAR)))
+ cast (active_start_time AS VARCHAR)
AS active_start_time,
REPLICATE ('0', 6 - len (cast (active_end_time AS VARCHAR)))
+ cast (active_end_time AS VARCHAR)
AS active_end_time,
date_created,
date_modified,
version_number
FROM msdb.dbo.sysschedules) AS s ) as D on D.schedule_id=sc.schedule_id


Refernence: sqlservercentral.com

Tushar

When was Server Last Restarted

There was a report that we had to put together for the servers that had re-started within the tenure and the probable reason for the restart.
Below query gives you the time the SQL Server Reatrted. This query assumes that the tempdb is created everytime the SQL Server restarts.
SELECT [Name] , CrDate as DateServerRebooted
from sys.sysdatabases
WHERE dbid = 2

Now if you want to further when the SQL Server was stopped and the probable reason for the Server shutdown you have to go to the individual error logs and identify.

Tushar

Getting User Access of all Users in a Database

Hi,
Had a requirement to refresh the database in dev from prod but the criteria was to retain the user access for that db intact. So needed to work on this as a priority. I have broken down the task in a two phases.

1st phase:
To identify the script to generate the present user access for the users in a database. Below is mentioned the script which will help me get the list.

select su.name as UserName ,sg.name as AccessGroup from sysmembers sm
join sysusers su on sm.memberuid=su.uid
join sysusers sg on sg.uid=sm.groupuid

2nd phase:
To identify the script which will help me restore the access for the users in dev enviroment after the database refresh.

select 'Exec sp_addrolemember ' +''''+ sg.name+ ''''+','+''''+ su.name+''''
from sysmembers sm
join sysusers su on sm.memberuid=su.uid
join sysusers sg on sg.uid=sm.groupuid


Tushar

Why no T-Log Backup possible in SIMPLE

When in SIMPLE recovery model a database log records in the active VLFs. These records are retained, as they may be required for a rollback operation. However, the inactive VLFs are truncated when a checkpoint occurs, meaning that the log records in these VLFs can be immediately overwritten with new log records. This is why a database operating in SIMPLE recovery is referred to as being in auto-truncate mode. In this mode, no "history" is maintained in the log and so it cannot be captured in a log backup and used as part of the restore process.

reference: sqlservercentral

Tushar

sp_lock and sp_who

Hi prepared a script to imitiate the sp_lock using DMV.

SELECT
tl.request_session_id as spid,
tl.resource_database_id as dbid,
tl.resource_associated_entity_id as objid,
tl.resource_type type,
tl.resource_description as resource,
tl.request_mode as mode,
tl.request_status as status
FROM sys.dm_tran_locks tl

Preparing a script for sp_who and sp_who2. Should be here soon.

Tushar