database-maintenance

Get database restore history

Returns last 10 restores performed on your server:

SELECT TOP 10
        RH.destination_database_name AS [Database],
        RH.user_name AS [Restored By],
        RH.restore_date AS [Restore Started],
        BMF.physical_device_name AS [Restored From],
        RF.destination_phys_name AS [Restored To],
        RH.*
FROM
        msdb.dbo.restorehistory RH
        INNER JOIN msdb.dbo.backupset BS ON RH.backup_set_id = BS.backup_set_id
        INNER JOIN msdb.dbo.restorefile RF ON RH.restore_history_id = RF.restore_history_id
        INNER JOIN msdb.dbo.backupmediafamily BMF ON BMF.media_set_id = BS.media_set_id
--WHERE destination_database_name = '<DatabaseName>'
ORDER BY RH.restore_history_id DESC
GO

Index design recommendation - how to choose optimal indexes

Indexes should be considered on all columns that are frequently accessed by the WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.

Clustered index recommendations
- It is highly recommended that every table should have at least a clustered index. However, there is also a very interesting topic to be taken into account on use-the-index-luke.com.
- Consider having a clustered indexes when the data retrieval needs to be sorted.
- Avoid creating a clustered index on columns that are highly updatable as row locator of all the non clustered indexes will be updated accordingly.
- Create the clustered index first. In case the clustered index is created later => all non clustered indexes need to be rebuilt.
- Keep clustered indexes narrow as this directly impacts index size on disk. This is very important as all non clustered indexes store the clustered keys as their row locator, or simpler said:
clustered index row width = non clustered index column width + clustered index column width

Nonclustered Index Recommendations
- A nonclustered index is most useful when all you want to do is retrieve a small number of rows and columns from a large table.
- Consider using INCLUDE clause in order to create covering indexes.
- To improve the performance of a query, SQL Server can use multiple indexes on a table. Therefore, instead of creating wide index keys, consider creating multiple narrow indexes.
- Foreign keys columns are good index candidates.

Disk consideration
- Place the table and index on separate disks.
- Take into account index compression i.e. fewer pages and fewer index levels are needed to store the index.

Column order matters in a composite index
Using the most selective column first will help filter the index rows more efficiently.

Avoid indexes on columns with lower selectivity
If selectivity ratio > 0.85 => a table scan will be preferred.

Take into account column uniqueness
Creating an index on columns with a very low range of possible unique values (such as gender) will not benefit performance, because the query optimizer will not be able to use the index to effectively narrow down the rows to be returned.

The data type of an index matters
Unless they are absolutely necessary, minimize the use of wide data type columns with large sizes in an index. A large index key size increases the number of index pages, thereby increasing the amount of memory and disk activities required for the index.

Indexed or materialized views
- A database view can be materialized on the disk by creating a unique clustered index on the view. After a unique clustered index is created on the view, the view’s result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing costly operations during query execution.
- Aggregations can be precomputed and persisted in the indexed view to minimize expensive computations during query execution.
- Available only in Enterprise Edition.

Other aspects to be taken into account
- Considering using the Database Engine Tuning Advisor tool provided by SQL Server that helps you determine the correct indexes in a database for a given SQL workload ( a trace file or a table or, new with SQL Server 2012, you can use the queries that exist in the plan cache).
- ColumnStore indexes. Used to index information by columns rather than by rows.
- Spatial indexes.
- XML indexes.
- Index computed columns.
- Consider using filtered indexes.
- Resolve Key or RID lookups by using a clustered index or by using a covering index or by using a index join (i.e. an index intersection between two or more indexes to fully cover a query).

How to revalidate/refresh all stored procedures and functions

After a stored procedure is created, you might make changes to the underlying objects that could break the stored procedure. The script presented will retrieve the list of stored procedures and functions and then will re-validate them, based on sp_refreshsqlmodule system stored procedure.

Here is the script that performs this.

SET NOCOUNT ON;

DECLARE
        @RowIndex AS SMALLINT = 1
        , @ObjectName AS NVARCHAR(128)
        , @Sql AS NVARCHAR(MAX)
       

-- Get list of objects (stored procedures, functions) to be refreshed
DECLARE @ObjectsTable AS Table (RowIndex SMALLINT IDENTITY(1,1), ObjectType nvarchar(60), ObjectName NVARCHAR(128), IsRefreshed BIT, Error NVARCHAR(MAX))
INSERT INTO @ObjectsTable (ObjectType, ObjectName, IsRefreshed)
SELECT O.type_desc, SCHEMA_NAME(O.schema_id) + '.' + O.name, 1
FROM
        sys.sql_modules M
        INNER JOIN sys.objects O ON O.object_id = M.object_id
WHERE
        O.is_ms_shipped = 0
        AND O.type_desc NOT IN ('VIEW')
ORDER BY O.type_desc, O.name

-- For each function/sp...
WHILE @RowIndex <= (SELECT COUNT(*) FROM @ObjectsTable)
BEGIN  
        SELECT @ObjectName = ObjectName        
        FROM @ObjectsTable
        WHERE RowIndex = @RowIndex
       
        SET @Sql = 'EXEC sp_refreshsqlmodule ''' + @ObjectName + ''''
        BEGIN TRY
                EXEC sp_executesql @Sql
        END TRY
        BEGIN CATCH
                UPDATE @ObjectsTable
                SET
                        IsRefreshed = 0,
                        Error = ERROR_MESSAGE()
                WHERE RowIndex = @RowIndex
               
                IF @@TRANCOUNT > 0
                        ROLLBACK TRAN
        END CATCH
                                                               
        -- Process next object
        SET @RowIndex = @RowIndex + 1
END


-- List all objects not refreshed
SELECT ObjectType, ObjectName, IsRefreshed, Error
FROM @ObjectsTable
WHERE IsRefreshed = 0
GO

Get list of replicated tables

T-SQL script for getting list of replicated tables and associated subscriptions / publications:

USE master;

SELECT
        P.publication,
        S.publisher_db, S.subscriber_db,
        A.source_owner, A.source_object, A.destination_owner, A.destination_object
FROM
        distribution.dbo.MSsubscriptions S
        INNER JOIN distribution.dbo.MSpublications P ON P.publication_id = S.publication_id
        INNER JOIN distribution.dbo.MSarticles A
                ON A.publication_id = P.publication_id AND A.article_id = S.article_id
ORDER BY
        P.publication, S.subscriber_db, A.source_object;

Get the space used for each database

SELECT
        DB.name,
        MF.physical_name,
        MF.type_desc AS FileType,
        MF.size * 8 / 1024 AS FileSizeMB,
        fileproperty(MF.name, 'SpaceUsed') * 8/ 1024 AS UsedSpaceMB
FROM
        sys.master_files MF
        JOIN sys.databases DB ON DB.database_id = MF.database_id
ORDER BY
        DB.name,
        FileSizeMB DESC

EXEC sp_msforeachdb @command1='use [?] exec sp_spaceused'

USE <DatabaseName>
EXEC sp_spaceused

Using DBCC SQLPERF to check log free space for a SQL Server database:

DBCC SQLPERF(logspace)

T-SQL script for copying a database

USE master;

DECLARE
        @SourceDatabaseName AS SYSNAME = '<SourceDatabaseName>',
        @TargetDatabaseName AS SYSNAME = '<NewDatabaseName>'


       
-- ============================================
-- Define path where backup will be saved
-- ============================================
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @SourceDatabaseName)
        RAISERROR ('Variable @SourceDatabaseName is not set correctly !', 20, 1) WITH LOG      
       
DECLARE @SourceBackupFilePath varchar(2000)
SELECT @SourceBackupFilePath = BMF.physical_device_name
FROM
        msdb.dbo.backupset B
        JOIN msdb.dbo.backupmediafamily BMF ON B.media_set_id = BMF.media_set_id
WHERE B.database_name = @SourceDatabaseName
ORDER BY B.backup_finish_date DESC

SET @SourceBackupFilePath = REPLACE(@SourceBackupFilePath, '.bak', '_clone.bak')



-- ============================================
-- Backup source database
-- ============================================
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = 'BACKUP DATABASE @SourceDatabaseName TO DISK = ''@SourceBackupFilePath'''
SET @Sql = REPLACE(@Sql, '@SourceDatabaseName', @SourceDatabaseName)
SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
SELECT 'Performing backup...', @Sql as ExecutedSql
EXEC (@Sql)



-- ============================================
-- Automatically compose database files (.mdf and .ldf) paths
-- ============================================
DECLARE
        @LogicalDataFileName as NVARCHAR(MAX),
        @LogicalLogFileName as NVARCHAR(MAX),
        @TargetDataFilePath as NVARCHAR(MAX),
        @TargetLogFilePath as NVARCHAR(MAX)
               
SELECT
        @LogicalDataFileName = name,
        @TargetDataFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.mdf'
FROM sys.master_files
WHERE
        database_id = DB_ID(@SourceDatabaseName)       
        AND type = 0            -- datafile file

SELECT
        @LogicalLogFileName = name,
        @TargetLogFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.ldf'
FROM sys.master_files
WHERE
        database_id = DB_ID(@SourceDatabaseName)       
        AND type = 1            -- log file    



-- ============================================
-- Restore target database
-- ============================================
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @TargetDatabaseName)
        RAISERROR ('Variable @SourceDatabaseName is not set correctly as a database with the same name already exists!', 20, 1) WITH LOG       
       
SET @Sql = 'RESTORE DATABASE @TargetDatabaseName
FROM DISK = '
'@SourceBackupFilePath''
WITH MOVE '
'@LogicalDataFileName'' TO ''@TargetDataFilePath'',
MOVE '
'@LogicalLogFileName'' TO ''@TargetLogFilePath'''
SET @Sql = REPLACE(@Sql, '@TargetDatabaseName', @TargetDatabaseName)
SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
SET @Sql = REPLACE(@Sql, '@LogicalDataFileName', @LogicalDataFileName)
SET @Sql = REPLACE(@Sql, '@TargetDataFilePath', @TargetDataFilePath)
SET @Sql = REPLACE(@Sql, '@LogicalLogFileName', @LogicalLogFileName)
SET @Sql = REPLACE(@Sql, '@TargetLogFilePath', @TargetLogFilePath)
SELECT 'Restoring...', @Sql as ExecutedSql
EXEC (@Sql)

Get database backup history

In case you need to see the last backups performed on a specific database:

SELECT
        BMF.physical_device_name as BackupFileName     
        , B.backup_finish_date as BackupDate
        , B.backup_size/1024.0/1024.0 AS BackupSizeMB
FROM
        msdb.dbo.backupset B
        JOIN msdb.dbo.backupmediafamily BMF ON B.media_set_id = BMF.media_set_id
WHERE database_name = '<YourDatabaseName>'
ORDER BY B.backup_finish_date DESC

Get data and log file sizes for all databases

SELECT
        DB.name,
        SUM(CASE WHEN type = 0 THEN MF.size * 8 / 1024 ELSE 0 END) AS DataFileSizeMB,
        SUM(CASE WHEN type = 1 THEN MF.size * 8 / 1024 ELSE 0 END) AS LogFileSizeMB
FROM
        sys.master_files MF
        JOIN sys.databases DB ON DB.database_id = MF.database_id
GROUP BY DB.name
ORDER BY DataFileSizeMB DESC

Get size of all tables in a database

SELECT
        T.NAME AS TableName,
        S.Name AS SchemaName,
        P.rows AS RowCounts,
        SUM(AU.total_pages) * 8 / 1024 AS TotalSpaceMB,
        SUM(AU.used_pages) * 8 / 1024 AS UsedSpaceMB
FROM
        syS.tables T
        JOIN sys.indexes I ON T.object_id = I.object_id
        JOIN sys.partitions P ON I.object_id = P.object_id AND I.index_id = P.index_id
        JOIN sys.allocation_units AU ON P.partition_id = AU.container_id
        JOIN sys.schemas S ON T.schema_id = S.schema_id
GROUP BY
        T.Name, S.Name, P.Rows
ORDER BY
        TotalSpaceMB DESC

How to delete job history (sp_purge_jobhistory)

Remove History for all jobs:

USE msdb ;
EXEC dbo.sp_purge_jobhistory;

By specifying the , we can remove the history of that job:

USE msdb ;
EXEC dbo.sp_purge_jobhistory @job_name = N'<job name>';
GO

To delete history for a specific job up to specific date:

USE msdb ;
EXEC dbo.sp_purge_jobhistory @job_name = N'<job name>', @oldest_date = '<date>';

Pages

Subscribe to RSS - database-maintenance