SQL Server – Page 10 – LincolnBlog.net

Use temp table variables instead of temp tables for certain applications

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

Temp tables are your friend, but there are other options. One of those is using a local TABLE variable table in your query.…

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 for identifying slow-running queries on a database

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

SELECT sqltext.TEXT,
    req.session_id AS 'SPID',
    req.status AS 'Status',
    req.command AS 'Command',
    req.cpu_time AS 'CPU Time',
    req.total_elapsed_time/1000 AS 'Total Elapsed Time in Seconds'
FROM sys.dm_exec_requests req
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
ORDER BY [CPU Time] DESC

SELECT sqltext.TEXT,

req.session_id AS 'SPID',

req.status AS 'Status',

req.command AS 'Command',

req.cpu_time AS 'CPU Time',

req.total_elapsed_time/1000 AS 'Total Elapsed Time in Seconds'

FROM sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

ORDER BY [CPU Time] DESC

Views – 2238

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 – 2198

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 – 2608

Using the LIKE keyword more efficiently

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

from Pinal Dave’s article here: http://blog.sqlauthority.com/

SELECT *
FROM Employee
WHERE FirstName LIKE 'A%'
OR FirstName LIKE 'B%'
OR FirstName LIKE 'C%'
OR FirstName LIKE 'D%'
OR FirstName LIKE 'E%'
OR FirstName LIKE 'F%'
OR FirstName LIKE 'G%'
OR FirstName LIKE 'H%'
OR FirstName LIKE 'I%'
OR FirstName LIKE 'J%'
OR FirstName LIKE 'K%'

SELECT *

FROM Employee

WHERE FirstName LIKE 'A%'

OR FirstName LIKE 'B%'

OR FirstName LIKE 'C%'

OR FirstName LIKE 'D%'

OR FirstName LIKE 'E%'

OR FirstName LIKE 'F%'

OR FirstName LIKE 'G%'

OR FirstName LIKE 'H%'

OR FirstName LIKE 'I%'

OR FirstName LIKE 'J%'

OR FirstName LIKE 'K%'

The previous query does find FirstName values beginning with the letters ‘A’ thru ‘K’. However, when…

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…

LincolnBlog.net

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

Category: SQL Server

Use temp table variables instead of temp tables for certain applications

Log-shipping Queries

T-SQL for identifying slow-running queries on a database

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 the LIKE keyword more efficiently

Using sp_WhoIsActive