Tag Archives: T-SQL Script

Script to Monitor the Running Job Status

Think of environment where multiple jobs are running at the same time and you want to monitor the current status of each job. One option is to use the job activity monitor.

 

And another way is you can use the below system stored procedures by running it against the MSDB database.

 

exec msdb..sp_get_composite_job_info
@execution_status=1

exec msdb.dbo.sp_help_job
@execution_status = 1

 

You can check the job execution status parameter values as below.

0 = Not idle or suspended

1 = Executing

2 = Waiting For Thread

3 = Between Retries

4 = Idle

5 = Suspended

6 = Waiting For Step To Finish

7 = Performing Completion Actions

 

Different ways to monitor Log Shipping for SQL Server databases

Problem
Log shipping has been an option for creating a fail over server for SQL Server for quite some time. In this tip, we look at different ways that you can monitor the status of your log shipped databases.

Solution
http://www.mssqltips.com/sqlservertip/2553/different-ways-to-monitor-log-shipping-for-sql-server-databases/

Steps to Attach a SQL Server database without transaction log file

Problem: There could be situation where you missed the database transaction log file(.LDF) and you have only data file (.MDF). You can attach the database using below solution.

Solution: In the below script I have created the database,dropped its log file and created the database with the .mdf file.

--created database with .mdf and .ldf file
CREATE DATABASE [singleFileDemo] ON  PRIMARY 
( NAME = N'singleFileDemo', FILENAME = N'L:\singleFileDemo.mdf' , SIZE = 2048KB , FILEGROWTH = 10240KB )
 LOG ON 
( NAME = N'singleFileDemo_log', FILENAME = N'F:\singleFileDemo_log.ldf' , SIZE = 1024KB , FILEGROWTH = 5120KB )
GO

--inserting data into database
use singleFileDemo
create table tb1 (name varchar(10))

--inserting records
insert into tb1 values('Jugal')
go 10;

--deleting the log file
--detaching the database file
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'singleFileDemo'
GO

-- now next step is delete the file manually or you can do it from command prompt
EXEC xp_cmdshell 'del F:\singleFileDemo_log.ldf'

-- script to attach the database 
USE [master]
GO
CREATE DATABASE [singleFileDemo] ON 
( FILENAME = N'L:\singleFileDemo.mdf' )
FOR ATTACH
GO 

When you will execute the CREATE DATABASE FOR Attach script you will get the below warning message.

File activation failure. The physical file name "F:\singleFileDemo_log.ldf" may be incorrect.
New log file 'F:\singleFileDemo_log.LDF' was created.

Once the database is ready execute the DBCC CHECKDB for any error.

Script to get the last update statistics date

Below script will execute against the sys.Objects,sys.indexes and sys.stats. Script will return last statistics update date and meta data of  statistics. Script will return the result for table and view level statistics.

In this script I have used the function STATS_DATE which will return last updated statistics date.

Syntax
STATS_DATE ( table_id , index_id )

SELECT sysobj.name AS objectname,
sysindex.name AS indexname,
Stats_date(sysindex.[object_id], sysindex.index_id) AS
[Statistics Update Date],
CASE sysstats.auto_created
WHEN 0 THEN ‘No’
WHEN 1 THEN ‘Yes’
END AS
isstatscreatedbyqueryprocessor,
CASE sysstats.user_created
WHEN 0 THEN ‘No’
WHEN 1 THEN ‘Yes’
END AS
isstatscreatedbyuser,
CASE sysstats.no_recompute
WHEN 0 THEN ‘No’
WHEN 1 THEN ‘Yes’
END AS
isstatscreatedwithnorecomputeoption
FROM sys.objects AS sysobj WITH (nolock)
INNER JOIN sys.indexes AS sysindex WITH (nolock)
ON sysobj.[object_id] = sysindex.[object_id]
INNER JOIN sys.stats AS sysstats WITH (nolock)
ON sysindex.[object_id] = sysstats.[object_id]
AND sysindex.index_id = sysstats.stats_id
WHERE sysobj.[type] IN ( ‘U’, ‘V’ )
ORDER BY Stats_date(sysindex.[object_id], sysindex.index_id);