Uneven Technology

Tuesday, January 13, 2015

MSSQL - View SQL Server Execution History

Last runs SQL scripts from execution history. This script can only work without server restart

USE AdventureWorks2012

SELECT
execquery.last_execution_time AS [Date Time]
,execsql.text AS [Script]
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC

Friday, January 9, 2015

MSSQL - Sotred Procedure Template

Use this template to create Stored Procedure:

-- EXEC USP_Test

USE DBName
GO

SET QUOTED_IDENTIFIER OFF
GO

IF OBJECT_ID('dbo.USP_Test') IS NULL EXEC('CREATE PROCEDURE dbo.USP_Test AS DUMMY:')
GO

GRANT EXECUTE ON USP_Test TO [Domain\UserName]
GO

ALTER PROCEDURE USP_Test
AS

MSSQL - Shrink All DBs

-- Don’t do this unless you have to.
EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'

MSSQL - Shrink TempDB

--DON'T DO THIS UNLESS WITH GOOD REASON

-- check db size
SELECT name, size FROM sys.master_files WHERE database_id = DB_ID(N'tempdb');

--How to shrink tempdb using DBCC
DBCC SHRINKDATABASE(tempdb, 10);
DBCC SHRINKFILE(tempdev, 5);

--How to shrink tempdb using ALTER DATABASE
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, SIZE=100Mb);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, SIZE=100Mb);
GO

MSSQL - List Running SQL Process

The script below lists all running sql and its session_id. Use session_id to kill the process.

-- List running processes
DECLARE @DatabaseName VARCHAR(50) = DB_NAME()
SELECT * FROM MASTER.dbo.SysProcesses WHERE DBId = DB_ID(@DatabaseName)

-- List SQL for running processes

SELECT

sqltext.TEXT

    ,req.session_id
    ,req.status
    ,req.command
    ,req.cpu_time
    ,req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

KILL [session_id]

MSSQL - Read and Sort Column Names

USE AdventureWorks2012

DECLARE @TBLName nvarchar(128)
SET @TBLName = N'Person'

SELECT name, object_id FROM sys.tables WHERE name = @TBLName

SELECT name, column_id, object_id
FROM sys.columns
WHERE object_id IN (SELECT object_id FROM sys.tables WHERE name = @TBLName)
ORDER BY name

MSSQL - Run SQL and Email Result as HTML

USE AdventureWorks2012

DECLARE @xml NVARCHAR(MAX), @body NVARCHAR(MAX)
SET @xml = CAST((SELECT
                     ISNULL(PersonType, '') 'td',''
                    ,ISNULL(Title, '') 'td',''
                    ,ISNULL(FirstName, '') 'td',''
                    ,ISNULL(LastName, '') 'td'
                FROM Person.Person
                FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))

IF @xml IS NOT NULL
BEGIN
    SET @body ='<html><body><H3>Header</H3>
                <table border=1>
                    <tr>
                        <th>Type</th>
                        <th>Title</th>
                        <th>FirstName</th>
                        <th>LastName</th>
                    </tr>'
    SET @body = @body + @xml + '</table><br><a href="http://yahoo.com">This is a link</a></body></html>'

    EXEC DataWarehouse.dbo.sp_SQLNotify 'FromEmail', 'ToEmail', 'Subject', @body, ''
END
　