Administration – Page 6 – LincolnBlog.net

The lifecycle of a SQL query

Posted on 01/10/201401/21/2019 by Lincoln Bovee'

1. The command parser evaluates the query and generates a hash to see if the buffer pool has an execution plan already stored.…

Log-shipping Queries

Posted on 01/10/201401/21/2019 by Lincoln Bovee'

EXEC sys.sp_check_log_shipping_monitor_alert 

SELECT secondary_database, last_restored_date
  FROM msdb.dbo.log_shipping_secondary_databases

EXEC sys.sp_check_log_shipping_monitor_alert

SELECT secondary_database, last_restored_date

FROM msdb.dbo.log_shipping_secondary_databases

First check the last file copied and restored to the secondary. Run the following queries on the secondary node. The following query…

T-SQL to check the properties of all the databases on a server

Posted on 01/10/201402/07/2014 by Lincoln Bovee'

SELECT 
     name, 
     DATABASEPROPERTYEX(name, 'Recovery') AS [recovery],
     DATABASEPROPERTYEX(name, 'IsAutoShrink') AS [auto-shrink],
     CASE WHEN primary_database IS NULL THEN 'no' ELSE 'yes' END AS [log-shipped],
     DATABASEPROPERTYEX(name, 'IsInStandBy') AS [stand-by],     
     DATABASEPROPERTYEX(name, 'Status') AS [status]
FROM master.dbo.sysdatabases sdb
     LEFT JOIN msdb.dbo.log_shipping_primary_databases lspd ON lspd.primary_database = sdb.name
ORDER BY 1

SELECT

name,

DATABASEPROPERTYEX(name, 'Recovery') AS [recovery],

DATABASEPROPERTYEX(name, 'IsAutoShrink') AS [auto-shrink],

CASE WHEN primary_database IS NULL THEN 'no' ELSE 'yes' END AS [log-shipped],

DATABASEPROPERTYEX(name, 'IsInStandBy') AS [stand-by],

DATABASEPROPERTYEX(name, 'Status') AS [status]

FROM master.dbo.sysdatabases sdb

LEFT JOIN msdb.dbo.log_shipping_primary_databases lspd ON lspd.primary_database = sdb.name

ORDER BY 1

Views – 2168

How to shrink huge log files

Posted on 01/10/201404/28/2016 by Lincoln Bovee'

Here is a short script to quickly shrink your log file if you DO NOT NEED to ever recover the database before the…

Some thoughts on auto-incrementing IDs

Posted on 01/10/201402/07/2014 by Lincoln Bovee'

The implementation & syntax feels like an afterthought, like the engineers of SQL Server made something self-consistent, and then the market research wing…

Fix broken log-shipping due to a full disk drive

Posted on 01/10/201401/21/2019 by Lincoln Bovee'

If the disk drive that receives the log-shipping transaction files (*.trn) fills up, it will cause the log shipping chain to break for…

Configure Email Alerts via T-SQL

Posted on 01/10/201402/07/2014 by Lincoln Bovee'

You can run the following SQL script to ensure email alerts are configured for the LSCopy and LSRestore jobs:

-- Get the job_id values and stick them into a cursor:
DECLARE @jobID UNIQUEIDENTIFIER
DECLARE cur CURSOR LOCAL FOR
    SELECT [job_id|job_id] 
    FROM [msdb|msdb].[dbo|dbo].[sysjobs|sysjobs] 
    WHERE [name|name] LIKE 'LSCopy_%' OR [name|name] LIKE 'LSRestore_%'

-- Loop through the cursor:
OPEN cur
    FETCH NEXT FROM cur INTO @jobID
    WHILE @ @FETCH_STATUS = 0 
    BEGIN
        -- Call sp_update_job for each ID. Set email level to 2 
        -- (alert on fail) and the operator name:
        EXEC [msdb|msdb].[dbo|dbo].sp_update_job 
            @job_id=@jobID,
            @notify_level_email=2,
            @notify_email_operator_name=N'DBA-EmailGroup'

        FETCH NEXT FROM cur INTO @jobID
    END
CLOSE cur
DEALLOCATE cur

-- Get the job_id values and stick them into a cursor:

DECLARE @jobID UNIQUEIDENTIFIER

DECLARE cur CURSOR LOCAL FOR

SELECT [job_id|job_id]

FROM [msdb|msdb].[dbo|dbo].[sysjobs|sysjobs]

WHERE [name|name] LIKE 'LSCopy_%' OR [name|name] LIKE 'LSRestore_%'

-- Loop through the cursor:

OPEN cur

FETCH NEXT FROM cur INTO @jobID

WHILE @ @FETCH_STATUS = 0

BEGIN

-- Call sp_update_job for each ID. Set email level to 2

-- (alert on fail) and the operator name:

EXEC [msdb|msdb].[dbo|dbo].sp_update_job

@job_id=@jobID,

@notify_level_email=2,

@notify_email_operator_name=N'DBA-EmailGroup'

FETCH NEXT FROM cur INTO @jobID

END

CLOSE cur

DEALLOCATE cur

Views – 2572

Using sp_WhoIsActive

Posted on 01/10/201401/10/2014 by Lincoln Bovee'

Here is an awesome extentions of the sp_who and sp_who2 (undocumented) sprocs DBA’s use to see slow running queries etc. Download link for…

Getting the current connections to a SQL Server

Posted on 01/10/201402/07/2014 by Lincoln Bovee'

SELECT * 
FROM sys.dm_exec_connections

1 2	SELECT * FROM sys.dm_exec_connections

Views – 1943

Keeping Indexes online while reindexing

Posted on 01/10/2014 by Lincoln Bovee'

This will also display the SQL Statement that is the offending blocker. Online index operations: Online index operations were added as an availability…

LincolnBlog.net

…my observations in the world of SQL Server, PowerShell, Linux and PostgreSQL etc…

Category: Administration

The lifecycle of a SQL query

Log-shipping Queries

T-SQL to check the properties of all the databases on a server

How to shrink huge log files

Some thoughts on auto-incrementing IDs

Fix broken log-shipping due to a full disk drive

Configure Email Alerts via T-SQL

Using sp_WhoIsActive

Getting the current connections to a SQL Server

Keeping Indexes online while reindexing