Category Archives: Uncategorized

SQL Script to find the list of all the jobs failed yesterday

By | January 20, 2015

One of the important task of any DBA is to find out all the jobs which are failed yesterday. Below SQL Script can be used to find out all the jobs which are failed yesterday. SELECT DISTINCT CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11)) AS ‘Failure Date’, SUBSTRING(T2.name,1,40) AS ‘Job Name’, T1.step_id AS ‘Step_id’, T1.step_name AS ‘Step… Read More »

SQL Script to search stored procedures containing a given text

By | January 20, 2015

Sometimes we need to find out how many stored procedures contains a given text. Below SQL query can be used to find out the list of all the stored procedures which contains a  particular given text as input. SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id) FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE ‘%Given text%’

T-SQL script to find the growth size of database files

By | January 9, 2015

Below query can be used to see the growth size of database files. DECLARE @filename NVARCHAR(1000); DECLARE @bc INT; DECLARE @ec INT; DECLARE @bfn VARCHAR(1000); DECLARE @efn VARCHAR(10); — Get the name of the current default trace SELECT @filename = CAST(value AS NVARCHAR(1000)) FROM ::fn_trace_getinfo(DEFAULT) WHERE traceid = 1 AND property = 2; — rip… Read More »

T-SQL script to find the highly populated tables in database

By | January 9, 2015

Sometimes we need to check which tables of our database is highly populated. Below is the T-SQL Script which we can use to find out that. SELECT      t.NAME AS TableName,     i.name as indexName,     sum(p.rows) as RowCounts,     sum(a.total_pages) as TotalPages,      sum(a.used_pages) as UsedPages,      sum(a.data_pages)… Read More »

SQL Script to find the last executed commands on the SQl Server

By | January 8, 2015

Sometimes we needs to find out the last executed commands on the database server.  Below is the SQL script share by one of my friend Ken Watson to find out the last executed queries. SELECT usecounts, cacheobjtype, objtype, text  FROM sys.dm_exec_cached_plans  CROSS APPLY sys.dm_exec_sql_text(plan_handle)  WHERE usecounts > 1  ORDER BY usecounts DESC;  About Ken He… Read More »

Script to find the Fragmentation of indexes

By | January 4, 2015

Below is the script to find the fragmentation of the indexes created on a database. SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level, avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count FROM sys.dm_db_index_physical_stats (DB_ID( N’Database name’) , NULL, NULL, NULL , ‘SAMPLED’) ORDER BY avg_fragmentation_in_percent DESC avg_fragmentation_in_percent represents  logical fragmentation. If this value is higher than 5% and less than 30%, then we should use  ALTER INDEXREORGANIZE If this value… Read More »

How to pass Microsoft Certification Exams

By | January 3, 2015

Many times I was asked how to pass Microsoft certifications and how difficult it is. In my point of view, it simple to crack Microsoft certification exams if you have worked hard on basic concepts and implemented most of the questions and scenarios mention in your study books/blogs on your system. 1) Take out the list… Read More »