Category: T-SQL Script

  • T-SQL Stored Procedure to Get the Time and Backup File Location of the Last Data Restore

    [sourcecode language=”sql” wraplines=”false”] /* .Synoposis Stored procedure to list the last data restore time AND backup file location for specific online user DB’s. .Last modified 2014-03-24 by Nick Xu .SQL Version 2005+ –Usage EXEC sp__GetlastDataRestoreTime @dbname = ‘DBADB’ GO EXEC sp__GetlastDataRestoreTime GO */ USE master GO /* –For generic setting IF EXISTS (SELECT * FROM…

  • T-SQL Stored Procedure to Get the Last Job Run Time and Status

    [sourcecode language=”sql”] /* Stored procedure to run the last run time and run satus of a SQL job. SQL Version: 2005+ Last modified on 2013-07-03 by Nick Xu –Usage –returns the last successful run time exec sp__GetLastJobRunTimeStatus @jobname = N’syspolicy_purge_history’, @status = N’success’ –returns the last failed runtime exec sp__GetLastJobRunTimeStatus @jobname = N’syspolicy_purge_history’, @status =…

  • T-SQL Stored Procedure to Get the UNC Path of Last Database Backup Location

    [sourcecode language=”text” wraplines=”false”] /* Script to get the last backup file location in the format of a UNC path for specified DB’s Last modified by Nick Xu on 2013-06-17 Usage: exec sp__GetLastDatabackupLocation ‘MDP’ */ use master go if exists (select * from sysobjects where name = ‘sp__GetLastDataBackupLocation’ and type = ‘P’) begin drop proc sp__GetLastDataBackupLocation…

  • T-SQL Stored Procedure to Report the Last Uptime of an Instance

    /* Stored proc to report uptime of a sql server instance based on the initialization time of its tempdb. Version 2000+ */ use master go IF EXISTS (SELECT * FROM sysobjects WHERE name = ‘sp__uptime’ AND type = ‘P’) DROP PROC sp__uptime go create proc sp__uptime AS begin select @@SERVERNAME as ServerName, crdate as LastUpTime…

  • T-SQL Script to Get Last Backup Dates for All Active DB’s

    /* Script to list the last full backup time for all active user DB’s. Version: 2005+ */ with tmp as ( select row_number() over (partition by BS.database_name order by BS.backup_set_id desc) as rowid, BS.database_name, BS.backup_finish_date from msdb.dbo.backupset BS inner join msdb.dbo.backupmediafamily BMF on BS.media_set_id = BMF.media_set_id where BS.type = ‘D’ and BS.database_name not in (‘master’,…

  • T-SQL Script to Check the ‘Auto Update Statistics’ Option for Each DB in an Instance

    [sourcecode language=”sql”] /* Script to check if AutoUpdateStats option is turned on for all the DB’s in an instance. Note: you have to take the output from the following script and run it again to get the final results. */ –SQL2000 select ‘select ”’ + name + ”’ as DB_Name, databasepropertyex(”’ + name + ”’,…

  • T-SQL Script to Split Tempdb Data Files with Alignment of the Number of CPU Cores

    “One tempdb date file per core” is proved to be a myth by Paul Randal and quoted from his blog it is a conlusion that : “If you have > 8 cores, use 8 files and if you’re seeing in-memory contention, add 4 more files at a time.” Therefore the following script will never set…

  • T-SQL Script to Delete Older-Than-N-Days Backups in a Specific Folder

    I’ve noticed there are quite some hits on this post and the old format of the T-SQL code looked a bit messy, so I’ve done a makeover of the script on 2014-04-10. This script requires a not-too-strict security setting for sql server’s service account, because it will have to execute Windows batch commands to achieve…

  • T-SQL Script to Quickly Truncate the Log File of a Database

    Sometimes, as a DBA you are requested urgently by either users or your supervisors to quickly truncate a log file. It is not a very good practice first of all and you should have already avoided this situation if you have already standardized you DB’s backup and recovery plan. However besides the above reminder, this…

  • T-SQL Script Using sp_msforeachdb to Change All Databases’ Owner to ‘sa’ Account

    Oftentimes, some DB’s in our inventory are modified or restored or created by different user accounts, which leads to having various DB owners in an instance. And a good ‘best practice’ claimed by Microsoft is to align all DB owners to the ubiquitous ‘sa’ account and here goes the script to do so without offending…