sql-server-2008

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;

How to find all the dependencies of a table - Object dependencies

In SQL Server 2008 there are two new Dynamic Management Functions introduced to keep track of Object Dependencies: sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities.

Returning the entities that refer to a given entity:

SELECT
        referencing_schema_name, referencing_entity_name,
        referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('<TableName>', 'OBJECT')

Returning entities that are referenced by an object:

SELECT
        referenced_schema_name, referenced_entity_name, referenced_minor_name,
        referenced_class_desc, is_caller_dependent, is_ambiguous
FROM sys.dm_sql_referenced_entities ('<StoredProcedureName>', 'OBJECT');

Another option is to use a pretty useful tool called SQL Dependency Tracker from Red Gate.

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

Find out the disk size of an index

SELECT
        OBJECT_NAME(I.OBJECT_ID) AS table_name,
        I.name AS index_name,  
        I.type_desc,
        AU.used_pages,
    8 * SUM(AU.used_pages) AS 'index_size_KB',         
    CAST(8 * SUM(AU.used_pages) / 1024.0 AS DECIMAL(18,2)) AS 'Index size (MB)'
FROM
    sys.indexes I
        JOIN sys.partitions P ON P.OBJECT_ID = I.OBJECT_ID AND P.index_id = I.index_id
        JOIN sys.allocation_units AU ON AU.container_id = P.partition_id
--WHERE
--      OBJECT_NAME(I.OBJECT_ID) = '<tableName>'        
GROUP BY
    I.object_id, I.name, I.type_desc, AU.used_pages
ORDER BY
    table_name;

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

Pages

Subscribe to RSS - sql-server-2008