Rebuild all the indexes

--Description : This script reorganizes and rebuilds the index if the fragmentation level is higher the given threshold
-- INPUTS : @fillfactor - While rebuilding index what would be FILLFACTOR for new index
-- @FragmentationThresholdForReorganizeTableLowerLimit - Fragmentation Level lower threshold to check for reorganizing the table, if the fragmentation is higher than this level, it will be considered for reorganize
-- @@FragmentationThresholdForRebuildTableLowerLimit - Fragmentation Level lower threshold to check for rebuilding the table, if the fragmentation is higher than this level, it will be considered for rebuild
DECLARE @cmd NVARCHAR(1000)
DECLARE @Table VARCHAR(255)
DECLARE @SchemaName VARCHAR(255)
DECLARE @IndexName VARCHAR(255)
DECLARE @AvgFragmentationInPercent DECIMAL
DECLARE @fillfactor INT
DECLARE @FragmentationThresholdForReorganizeTableLowerLimit VARCHAR(10)
DECLARE @FragmentationThresholdForRebuildTableLowerLimit VARCHAR(10)
DECLARE @Message VARCHAR(1000)

SET NOCOUNT ON

--You can specify your customized value for reorganize and rebuild indexes, the default values
--of 10 and 30 means index will be reorgnized if the fragmentation level is more than equal to 10
--and less than 30, if the fragmentation level is more than equal to 30 then index will be rebuilt
SET @fillfactor = 90
SET @FragmentationThresholdForReorganizeTableLowerLimit = '10.0' -- Percent
SET @FragmentationThresholdForRebuildTableLowerLimit = '30.0' -- Percent

BEGIN TRY

-- ensure the temporary table does not exist
IF (SELECT OBJECT_ID('tempdb..#FramentedTableList')) IS NOT NULL
DROP TABLE #FramentedTableList;

SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Retrieving indexes with high fragmentation from ' + DB_NAME() + ' database.'
RAISERROR(@Message, 0, 1) WITH NOWAIT

SELECT OBJECT_NAME(IPS.OBJECT_ID) AS [TableName], avg_fragmentation_in_percent, SI.name [IndexName],
schema_name(ST.schema_id) AS [SchemaName], 0 AS IsProcessed INTO #FramentedTableList
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) IPS
JOIN sys.tables ST WITH (nolock) ON IPS.OBJECT_ID = ST.OBJECT_ID
JOIN sys.indexes SI WITH (nolock) ON IPS.OBJECT_ID = SI.OBJECT_ID AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 AND SI.name IS NOT NULL
AND avg_fragmentation_in_percent >= CONVERT(DECIMAL, @FragmentationThresholdForReorganizeTableLowerLimit)
ORDER BY avg_fragmentation_in_percent DESC

SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Retrieved indexes with high fragmentation from ' + DB_NAME() + ' database.'

RAISERROR(@Message, 0, 1) WITH NOWAIT
RAISERROR('', 0, 1) WITH NOWAIT

WHILE EXISTS ( SELECT 1 FROM #FramentedTableList WHERE IsProcessed = 0 )
BEGIN

SELECT TOP 1 @Table = TableName, @AvgFragmentationInPercent = avg_fragmentation_in_percent,
@SchemaName = SchemaName, @IndexName = IndexName
FROM #FramentedTableList
WHERE IsProcessed = 0

--Reorganizing the index
IF((@AvgFragmentationInPercent >= @FragmentationThresholdForReorganizeTableLowerLimit) AND (@AvgFragmentationInPercent < @FragmentationThresholdForRebuildTableLowerLimit))
BEGIN
SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Reorganizing Index for [' + @Table + '] which has avg_fragmentation_in_percent = ' + CONVERT(VARCHAR, @AvgFragmentationInPercent) + '.'
RAISERROR(@Message, 0, 1) WITH NOWAIT
SET @cmd = 'ALTER INDEX [' + @IndexName + '] ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REORGANIZE'
EXEC (@cmd)
--PRINT @cmd
SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Reorganize Index completed successfully for [' + @Table + '].'
RAISERROR(@Message, 0, 1) WITH NOWAIT
RAISERROR('', 0, 1) WITH NOWAIT
END
--Rebuilding the index
ELSE IF (@AvgFragmentationInPercent >= @FragmentationThresholdForRebuildTableLowerLimit )
BEGIN
SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Rebuilding Index for [' + @Table + '] which has avg_fragmentation_in_percent = ' + CONVERT(VARCHAR, @AvgFragmentationInPercent) + '.'
RAISERROR(@Message, 0, 1) WITH NOWAIT
SET @cmd = 'ALTER INDEX [' + @IndexName + '] ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ', STATISTICS_NORECOMPUTE = OFF)'
EXEC (@cmd)
--PRINT @cmd
SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Rebuild Index completed successfully for [' + @Table + '].'
RAISERROR(@Message, 0, 1) WITH NOWAIT
RAISERROR('', 0, 1) WITH NOWAIT
END

UPDATE #FramentedTableList
SET IsProcessed = 1
WHERE TableName = @Table
AND IndexName = @IndexName
END

DROP TABLE #FramentedTableList

END TRY

BEGIN CATCH
PRINT 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' There is some run time exception.'
PRINT 'ERROR CODE : ' + CONVERT(VARCHAR, ERROR_NUMBER())
PRINT 'ERROR MESSAGE : ' + ERROR_MESSAGE()
END CATCH

BMTC Volvo Complaints

I have a horrific experience today with BMTC volvo bus. It almost crushed me.
I decided to teach those guys a lesson, and contact BMTC call center. From there I got the email-id where one can forward his/her complaints. I have already got a response back from Chief Traffic Manager.

BMTC toll free number is 18004251663
BMTC Email-ID is ctmobmtc@gmail.com

Lets hope some action is taken against those B*******ds.

Script to know what is currently running in the system

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Create CSV file from a query using SQLCMD.

ALTER PROC [dbo].[create_csv_from_query]
(
@FullFileName VARCHAR(MAX),
@Query VARCHAR(MAX),
@Server VARCHAR(50) ='DEV',
@Database VARCHAR(50) ='Test'
)
AS
BEGIN
-- Query should have nocount on
DECLARE @cmd VARCHAR(2000)

SET @cmd = 'sqlcmd -S '+@Server+' -d '+@Database+' -E -o "'+@FullFileName +'" -Q "'+@Query+'" -W -w20000 -s","'
EXEC master..xp_cmdshell @cmd

END

Clear Transaction logs

You can clear the transaction log by using the following command

Dump tran < db_name > to < file_name >
OR
Dump tran < db_name > with truncate_only
OR
Dump tran < db_name > with no_log

Please read the sybase documents for more details.

Note : Dump tran will not work, if your database option is set
to "truncate log on checkpoint".

if still log is not cleared then, either you can kill the process
or can abort the transaction log by using lct_admin command.

Logging in SQL server

ALTER procedure CreateLog
(
@Msg varchar(500),
@Category varchar(50),
@Type varchar(50),
@FilePath varchar(50) = 'C:\MyLog.txt'
)
as
begin
declare @cmd varchar(2000)

set @cmd = 'echo Category: '+ @Category +', Type: '+ @Type +', Date:' + convert(varchar(30),getdate())+', Message: ' + @Msg + ' >> ' + @FilePath
exec master..xp_cmdshell @cmd
end

COTS packages

COTS packages (vs. a custom build) that offer capabilities around calculating/reporting on the performance of the funds:



Confluence
DST (Hi Performance)
Eagle (Pace)
Factset
Milestone Systems
Statpro
SS&C (Sylvan)
Sungard