database-maintenance

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>';

Renaming the physical file names for Datafiles in SQL Server

  • Update data file location
    ALTER DATABASE <your-database-name>
    MODIFY FILE (NAME = logical_file_name, FILENAME = '<full-path-of-your-mdf-file' )
  • Update log file location (optionally)
    ALTER DATABASE <your-database-name>
    MODIFY FILE (NAME = logical_file_name, FILENAME = '<full-path-of-your-ldf-file' )
  • Detach the database
  • Rename the file(s) on disk
  • Re-attach the database

How to drop a list of tables, including the associated foreign key constraints

DECLARE
     @TableName AS SYSNAME
   , @RefTableName AS SYSNAME
   , @ConstraintName AS SYSNAME

-- For each table...
WHILE 1 = 1
BEGIN
   SET @TableName = NULL
   
   SELECT TOP 1 @TableName = name FROM sys.tables WHERE name LIKE '<TablePrefix>%'
   IF @TableName IS NULL
      BREAK;
       
      -- For each FK referring this table
      WHILE 1 = 1
      BEGIN
         SET @ConstraintName = NULL
                 
         SELECT
            @ConstraintName = FK.name,
            @RefTableName = OBJECT_NAME(C.parent_object_id)
         FROM
            sys.foreign_keys AS FK
            INNER JOIN sys.foreign_key_columns AS C
                        ON FK.OBJECT_ID = C.constraint_object_id
            INNER JOIN sys.tables T ON t.OBJECT_ID = C.referenced_object_id
         WHERE
            OBJECT_NAME (FK.referenced_object_id) = @TableName

         IF @ConstraintName IS NULL
            BREAK;

         PRINT 'Dropping constraint ' + @ConstraintName
         EXEC ('ALTER TABLE [' + @RefTableName + '] DROP CONSTRAINT ' + @ConstraintName)
      END        
       
      PRINT 'Dropping table ' + @TableName
      EXEC ('DROP TABLE [' + @TableName + ']') 
END

GO

How to get database backup history

This script will allow you to see the last performed backups and their type (full, differential or log):

SELECT
        database_name
        , [backup_start_date]
        , CASE [type]
                WHEN N'D' THEN N'Full Backup'
                WHEN N'I' THEN N'Diff Backup'
                WHEN N'L' THEN N'Log Backup'
                ELSE N'Unknown'
          END AS N'backup_type'
        , [position]
        , [name]
        , [description]
        , [first_lsn]
        , [last_lsn]
        , [backup_finish_date]
FROM
        msdb.dbo.[backupset] AS S
        INNER JOIN msdb.dbo.[backupmediafamily] AS M ON S.[media_set_id] = M.[media_set_id]
WHERE [database_name] = N'<database_name>'
ORDER BY [backup_start_date];
GO

How to check consistency of your backups - CHECKSUM

Backups should always use WITH CHECKSUM:

BACKUP DATABASE [DatabaseName]
TO DISK = N'D:\Backups\DatabaseName.bak'
WITH INIT, CHECKSUM;
GO

Check in backup header (column IsDamaged) , if it contains a corrupted database (i.e. the database was already corrupted when the backup was performed):

RESTORE HEADERONLY
FROM DISK = N'D:\Backups\DatabaseName.bak';
GO

You can test the backup integrity:

RESTORE VERIFYONLY
FROM DISK = N'D:\Backups\DatabaseName.bak'
WITH CHECKSUM;
GO

Checking backup consistency is not an alternative to regular database consistency checking via DBCC CHECKDB.

When statistics really change?

Stats change:

  • When a table with no rows gets a row
  • When a table has fewer than 500 rows and is increased by 500 or more rows
  • Through SQL Server's auto-updating mechanism based on database option AUTO_UPDATE_STATISTICS (which by default is enabled and it performs synchronously), statistics get automatically changed when 500 + 20% of the table rows change. E.g. for a table with 100k rows, stats will get invalidated when 20500 rows are updated. Read more about this.
  • In addition to statistics on indexes, SQL Server can build statistics on columns with no indexes. See AUTO_CREATE_STATISTICS option.
  • If trace flag 2371 is turned on, then updates are dynamic Read more about this.
  • When you rebuild an index (equivalent to FULLSCAN).
  • Or you can decide to manually update the statistics.

Pages

Subscribe to RSS - database-maintenance