John McCormack DBA

SQL Server Databases and Cloud

  • Personal
    • About
  • Free Training
    • SQL Server on Amazon RDS (Free Course)
    • Free practice questions to help you pass DP-900
  • Save money in Azure
    • Azure IaaS SQL Backups – Stop burning money
    • Your Azure SQL Database and Managed Instance is too big
    • Turn the cloud off at bedtime to save 70%
    • Your Azure SQL Virtual Machine might be too big
    • Save money with Azure SQL DB serverless
    • Save up to 73% with reserved instances
    • Delete unused instances to save money in Azure
  • Hire me
    • 60 minute cost optimization
    • Let me solve your SQL Server problems
    • Take a look at my Sessionize speaker’s profile

Index maintenance using Ola Hallengren’s scripts

17th July 2015 By John McCormack 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…]

Filed Under: front-page, Guides Tagged With: dba, dba scripts, index maintenance, index rebuild, index reorganize, ola hallangren, t-sql scripts

Check when log backups were last restored

17th November 2014 By John McCormack Leave a Comment

This script is useful if want to check when transaction log backups were last restored to a specific database.

With minor amendments, you can also check other restore types and check for all databases, not just a specific one.

[Read more…]

Filed Under: front-page, T-SQL Tagged With: check when log backups were last restored, log restore, log restores, log shipping, t-logs, t-sql, transaction logs

Check percentage complete of transaction

30th July 2014 By John McCormack 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.

*/

Filed Under: front-page, T-SQL Tagged With: DBCC, DBCC SHRINKFILE, DbccFilesCompact, percent complete, spid, sys.dm_exec_requests

  • « Previous Page
  • 1
  • …
  • 14
  • 15
  • 16
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

John McCormack · Copyright © 2023