Data types of columns with Clustered Indexes !

So..... there is a new application development and you get bunch of scripts from bunch of developers to be executed on UAT and subsequently PROD , right? You review the scripts look for any blunders/mistakes made but darn some scripts are just painfully long and are mostly generated from some third party software like SQL Compare or whatever.

 One thing I often want to make sure is they (developers) did not create clustered indexes (PK automatically creates one - I am sure you already know that) on columns with datatypes let's say GUID ;) or varchar(max).. !! Exaggeration ?? Well be that as it may.. I have seen some blunders and I want to list all indexes with their --- type, columns they are on and the data types of those columns.

I tried to write the query myself but my t-sql scripting skills are ___ well they are okay but I don't want to re-invent the wheel. So I just asked Google about it. My rule is : If Google won't find it, I won't write it. :)

Okay here you go -- (Thanks to Muhammad Imran from "raresql.com")

/* 
All Indexes With Column Names (DataTypes) And Index Types
----------------------------------------------------------
sys.indexes === system view is used to get type of indexes 
type
tinyint
Type of index:
0 = Heap
1 = Clustered
2 = Nonclustered
3 = XML
4 = Spatial
5 = Clustered xVelocity memory optimized columnstore index (Reserved for future use.)
6 = Nonclustered columnstore index
Taken from http://raresql.com/2013/03/29/sql-server-list-of-all-indexes-with-column-name-and-datatype-including-columnstore-index/
and modified so you don't have to create stored proc on production server
*/
USE Your_DB_Name --enter your database name here
GO
declare @Type int
set @type = 1 -- for clustered (Change it according to header for other index types)
BEGIN
;WITH CTE AS ( SELECT TBL.object_id AS [Object ID]
, schema_name(schema_id) AS [Schema Name]
, tbl.name AS [Table Name]
, i.name AS [Index Name]
, i.type AS [Index type]
, i.type_desc AS [Index Desc]
, clmns.name AS [Column Name]
, styps.name AS [Type Name]
FROM  sys.tables AS tbl  
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0)
AND (i.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic
ON (ic.column_id > 0 and (ic.key_ordinal > 0
OR ic.partition_ordinal = 0
OR ic.is_included_column != 0))
AND (ic.index_id=CAST(i.index_id AS int)
AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns
ON clmns.object_id = ic.object_id
AND clmns.column_id = ic.column_id
INNER JOIN sys.systypes AS styps
ON clmns.system_type_id=styps.type
WHERE ((1=(CASE WHEN @Type is Null THEN 1 ELSE 0 END)
OR i.type = @Type)))
--To convert all datatypes into one row
SELECT [Object ID]
, [Schema Name]
, [Table Name]
, [Index Name]
, [Index Desc]
, STUFF((SELECT ', ' + [Column Name] + '(' + [Type Name] + ')'
FROM CTE AS CTE1
WHERE CTE1.[Object ID]= CTE2. [Object ID]
FOR XML PATH('')),1,1,'') as [Column Name with datatype]
FROM CTE AS CTE2
GROUP BY
 [Object ID]
,[Schema Name]
,[Table Name]
,[Index Name]
,[Index Desc]
ORDER BY [Object ID],[Schema Name],[Table Name]
END
GO

DBCC SQLPerf (logspace) - Sorted by size !

So, you find out one of the database server had log drive almost full. Basically one of the database log file has grown big. But you have multiple databases on the server and you don't know which one!

So you just go to the log file folder and sort the files with size. Sure ! That would work, but I will have to Remote Desktop to the server. And I don't like to do that. Besides it doesn't really tell me if that's really all the log files for the database and so forth.  Point is I want to write this blog and tell you a nice way to get the results of DBCC SQLPerf(logspace) sorted by size. There you're !!!


CREATE TABLE #LogSpace (cap_date Datetime default getdate(),
        db sysname,
log_size float,
space_used float,
status bit)

Insert #LogSpace (db, log_size, space_used, status) exec ('dbcc sqlperf (logspace)')

SELECT * FROM #LogSpace order by log_size desc


And that's the end of that story.

SQL Server - what's using my log !

So you want to possibly shrink the log file  (not the database) because - oh well you do! It has grown for some reason and you don't really know what in God's name happened. So you go to SSMS right-click the database and shrink the file or run the DBCC ShrinkFile, right? But the log file won't shrink. Now before you jump on LOG IS ACTIVE - I need to take a backup. Let's look at what's currently using the log..ehh!

Here is the query:

select name, log_reuse_wait_desc  from sys.databases

Example result set:

So now you know what's going on with your log file. You may even find that 'REPLICATION'  is using log file even though there is no Replication set up for the database.

May be someone tried to set up replication and didn't remove it properly! So, you'll have to remove it properly and then shrink the log file, simple stuff right?

Daily DBA Health Check - SQL Agent Jobs failed

One of the things we DBAs look for any failed jobs. Although each failed job will send notification, at times depending on where you are working, we may or may not get failed job alerts. To double check, all jobs finished fine, run the script below to see if there are any jobs with last run status as 'failed'.

I haven't worked out a PowerShell script for this yet which will send an HTML email alert of all the jobs that failed every morning. The second part of the script is to check the jobs which are running currently. You may notice a few jobs which are not supposed to be running (for example they are nightly jobs and should have been finished when you run this script in the morning).

Run this on a center management server or as multi-server query on your registered servers.

--Check jobs with last run status as 'failed'
use msdb
go
select h.server as [Server],
j.[name] as [Name],
h.message as [Message],
h.run_date as LastRunDate, 
h.run_time as LastRunTime
from sysjobhistory h
inner join sysjobs j on h.job_id = j.job_id
where j.enabled = 1 
and h.instance_id in
(select max(h.instance_id)
from sysjobhistory h group by (h.job_id))
and h.run_status = 0

go

--Check jobs with status as 'executing'
exec msdb..sp_help_job @execution_status = 1

go