View Log file information in SQL Server

Scenario:

Usually, we will face log space issue in our SQL Server. Most of the time, we dont know the reason for our logfile space. I would suggest to get a third party tool to check it. By reading through some of the forums. I found there is some undocumented DBCC command which is used to dig the log file and get some information to us.

This command won't give a very great information at least we can get some high level information. Let's see the command,

DBCC Log(Databasename, Option number)

Default option number is 0

For instance,

dbcc log ('master',0) - 0 indicates a very minimal information.

The above command will provide the below details,

Reindexing the entire database in sql server

Re-indexing the entire database:

Usually, if the database is too slow. DBA's were advised to re-index the tables. For some cases, we need to rebuild the indexes availables in the entire database.

For this scenario, we can rebuild the index by taking each table and re-index all the indexes associated with each table.

Fill factor:

Amount or compactness of data in the leaf level is defined by the term fill factor. Based on the operations on the database DBA's will decide the fill factor. If the insert/update/delete are very high in that case we will have very less fill factor (Around 60 to 70). If there is very less insert/update/delete, in that case we will have very high fill factor (Around 90). On an average, we will give 80 -90 %.



Below is the script to achieve this,

----------------------------------------------------------------------------
DECLARE @DatabaseTable VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT name AS DatabaseTable
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @DatabaseTable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'DBCC DBREINDEX('+ @DatabaseTable + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @DatabaseTable
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

-------------------------------------------------------------

How abt re-organsing the indexes (Rebuild index vs reorganise index):

1. Re-built can't be done on the production run time (Due to its high impact on rearranging the data and recreate of index) where as re-organise index can be done.

2. Indexes were recreated in case of re-building the index.

3. Re-built index is very effective when compared to the other.

To find the free space available in the log files:

Below is the command used to identify the amount the space available or percentage used.

DBCC SQLPERF (LOGSPACE)

Reindexing the entire database in sql server

Re-indexing the entire database:

Usually, if the database is too slow. DBA's were advised to re-index the tables. For some cases, we need to rebuild the indexes availables in the entire database.

For this scenario, we can rebuild the index by taking each table and re-index all the indexes associated with each table.

Fill factor:

Amount or compactness of data in the leaf level is defined by the term fill factor. Based on the operations on the database DBA's will decide the fill factor. If the insert/update/delete are very high in that case we will have very less fill factor (Around 60 to 70). If there is very less insert/update/delete, in that case we will have very high fill factor (Around 90). On an average, we will give 80 -90 %.



Below is the script to achieve this,

----------------------------------------------------------------------------
DECLARE @DatabaseTable VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT name AS DatabaseTable
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @DatabaseTable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'DBCC DBREINDEX('+ @DatabaseTable + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @DatabaseTable
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

-------------------------------------------------------------

How abt re-organsing the indexes (Rebuild index vs reorganise index):

1. Re-built can't be done on the production run time (Due to its high impact on rearranging the data and recreate of index) where as re-organise index can be done.

2. Indexes were recreated in case of re-building the index.

3. Re-built index is very effective when compared to the other.

To find the free space available in the log files:

Below is the command used to identify the amount the space available or percentage used.

DBCC SQLPERF (LOGSPACE)

Performance monitoring command in SQL Server

DBCC SQLPERF(umsstats) - This command will provide all the OS related information for your threads in the SQL Server instance. It provides the below values,

Node Id
Avg Sched
LoadSched
SwitchesSched Pass
IO Comp Passes
Scheduler ID (The below data will be populated for each scheduler ID)
online - Whether it's online or not
num tasks - Number of tasks
num runnable - Runnable tasks
num workers - Parallel workers involved in the scheduler
active workers - Active parallel workers involved in the scheduler
work queued - Works queued up in this schedule
cntxt switches - How many context switching happening in this schedule
cntxt switches(idle) - Idle threads after context switches.
preemptive switches - Prirority switches happened in this schedule.

Get details on the table contigencies

DBCC Showcontig will give a detailed insight on the data pages(OS level) for the tables.

This command will scan the table and provide the below details,

- Pages Scanned......................... - Number of pages scanned (Denotes the number of pages occupied by this table )
- Extents Scanned..............................: Number of Extents scanned (Denotes the number of Extents occupied by this table )
- Extent Switches..............................: Mixed extents
- Avg. Pages per Extent........................
- Scan Density [Best Count:Actual Count].......: How the data is compacted in the page.
- Extent Scan Fragmentation ...................: Fragmentation from Extent point of view
- Avg. Bytes Free per Page.....................
- Avg. Page Density (full) ...................... (High page density denotes the data is intact and your search will be faster. If the value is less, it indicates your data is scattered.

DBCC SHOWCONTIG --- This command will provide the details for the whole database tables.

DBCC SHOWCONTIG ('Murli_table') -- This command will provide the necessary information for that particular table.

Get Databasename from Database ID in SQL Server

DB_Name(DB_ID) will provide you the database name. The hierarchy is like system tables will have the initial values followed by user databases.

select DB_NAME(1) --- returns Master database
select DB_NAME(2)--- returns Tempdb database
select DB_NAME(3)--- returns Model database
select DB_NAME(4)--- returns MSDB database
select DB_NAME(5)--- returns User database1 etc..,

To find recently executed queries in SQL Server

To find recently executed queries,

Select dmStats.last_execution_time as 'Last Executed Time',dmText.text as 'Executed Query' from sys.dm_exec_query_stats as dmStats Cross apply sys.dm_exec_sql_text(dmStats.sql_handle) as dmText Order By
dmStats.last_execution_time desc