John McCormack DBA

SQL Server Databases and Cloud

John McCormack's blogs

Index maintenance using Ola Hallengren’s scripts

By Leave a Comment

Having recently set up Index maintenance using Ola Hallengren’s scripts on a range of my company’s production servers, I wanted to write about the steps involved including some custom steps to ensure the overall health of the SQL Server database.

The aim of this blog post is to be not too technical, I don’t cover what Ola has documented on his site however I do include scripts for my custom steps. I will only cover index maintenance, although these scripts give the options for statistics maintenance also. This is because we already had a custom solution for statistics maintenance that was working great, whereas we needed to make improvements to our index maintenance solution.

[Read more…]

List all views

By Leave a Comment

This script is handy when you want to list all views on an instance or even find a specific view if you know the name but don’t know the database.

[sql]

— Written by John McCormack

— This script uses the unspported stored procedure sp_msforeachdb so there are more reliable ways of going about this task.
— It uses a table variable and inserts each row returned into the table variable
— The ? is the database – All databases will be

DECLARE @views table (DBName sysname, ViewName nvarchar(100))
INSERT INTO @views
EXEC sp_msforeachdb
‘USE ?
SELECT ”?”,name FROM sys.views
— where [name] = ”vw_CurrentDaySales”’
— Uncomment the where clause if you wanted to find a specific view

SELECT * FROM @views

[/sql]

Check percentage complete of transaction

By Leave a Comment 

SELECT percent_complete,*
FROM sys.dm_exec_requests
WHERE session_id = 63 -- Remember to change session_id = to the relevant spid

/*

This simple script shows what percentage of a transaction has completed
This works for the following types of transaction:

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE,
ROLLBACK
TDE ENCRYPTION
Change the 63 in the where clause to match the spid for your transaction.
Hint exec sp_who2 and look at command. This is the spid you want.

*/