Category Archives: DBA Scripts

How to: Verifying CPU Pressure via Signal Wait Time

Use the following query to verify the CPU pressure via signal wait time through MS SQL Server Management Studio.

USE master
GO
SELECT SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,
( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))
/ SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 )
AS PercentageSignalWaitsOfTotalTime
FROM sys.dm_os_wait_stats

References

How to: Find a Table on a SQL Server across all Databases

Some times it is difficult to find a table name from a SQL instance that hosted tremendous database.  “Which database I suppose to search for?” is the question that often pop-up in DBA’s mind and they hope the user can give them the database name as well.  Sadly speaking in most cases user do not know which database their application connect to.

To find a table in the SQL across all databases you can use undocumented stored procedure sp_MSForEachDB.

sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''%your_table_name%'''

Script to Check TempDB Speed

The following script adopted from Brent Ozar’s blog on his TempDB Performance and Configuration topic. I strongly encourage you to pay a visit to his site.

This query hits the dynamic management function (DMF) sys.dm_io_virtual_file_stats for all of the TempDB data files and lists out how fast they’re responding to write (and read) requests:

SELECT files.physical_name, files.name,
    stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,
    stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files
    ON stats.database_id = files.database_id
    AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'

Brent Ozar suggested to look for two things from the result generate from the script:

  • Are writes being evenly distributed between data files?
  • Are writes finishing in 20ms or less?

If either of those question is no, then we have some performance tuning work need to kick in.

How to: Check If xp_cmdshell is Enabled

“xp_cmdshell” allow us to run windows commands by using SQL Server but first we need to enable it. We can check whether the “xp_cmdshell” configuration is enabled from the system table sys.configurations which contains server-wide configuration option value in the system.

Execute the following T-SQL will allow you to check if “xp_cmdshell” is enabled on a server. You may need to manually enable the “xp_cmdshell” function if the return result is not “1”.

SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
FROM  sys.configurations
WHERE  name = N'xp_cmdshell' ;

Enable “xp_cmdshell”

Execute the following T-SQL to enable the “xp_cmdshell” configuration.

--Enable Change in Advanced Options
EXEC SP_CONFIGURE N'show advanced options', 1
GO
RECONFIGURE
GO
--Enable XP_cmdshell
EXEC SP_CONFIGURE N'xp_cmdshell', 1
GO
RECONFIGURE
GO
--Disable Changes in Advanced Options
EXEC SP_CONFIGURE N'show advanced options', 0
GO
RECONFIGURE
GO

How to: Find Who Deleted the Database or Table

Found a very useful script from SQLServerCentral Forums that it will help you to easily identify who is the person that had deleted your database or table.

With cteObjectTypes AS
    (
    SELECT
        TSV.trace_event_id,
        TSV.subclass_name,
        TSV.subclass_value
    FROM
        sys.trace_subclass_values AS TSV JOIN
        sys.trace_columns AS TC ON
            TSV.trace_column_id = TC.trace_column_id 
    WHERE
        TC.[name] = 'ObjectType'
    ),
    cteEventSubClasses AS
    (
    SELECT
        TSV.trace_event_id,
        TSV.subclass_name,
        TSV.subclass_value
    FROM
        sys.trace_subclass_values AS TSV JOIN
        sys.trace_columns AS TC ON
            TSV.trace_column_id = TC.trace_column_id 
    WHERE
        TC.[name] = 'EventSubClass'
    )
SELECT
    TE.[name],
    I.ApplicationName,
    I.BigintData1,
    I.ClientProcessID,
    I.ColumnPermissions,
    I.DatabaseID,
    I.DatabaseName,
    I.DBUserName,
    I.Duration,
    I.EndTime,
    I.Error,
    I.EventSequence,
    Convert(nvarchar(10), I.EventSubClass) + N'-' + ESC.subclass_name as EventSubClass,
    I.FileName,
    I.HostName,
    I.IndexID,
    I.IntegerData,
    I.IsSystem,
    I.LineNumber,
    I.LoginName,
    I.LoginSid,
    I.NestLevel,
    I.NTDomainName,
    I.NTUserName,
    I.ObjectID,
    I.ObjectID2,
    I.ObjectName,
    Convert(nvarchar(10), I.ObjectType) + N'-' + OT.subclass_name as ObjectType,
    I.OwnerName,
    I.ParentName,
    I.Permissions,
    I.RequestID,
    I.RoleName,
    I.ServerName,
    I.SessionLoginName,
    I.Severity,
    I.SPID,
    I.StartTime,
    I.State,
    I.Success,
    I.TargetLoginName,
    I.TargetLoginSid,
    I.TargetUserName,
    I.TextData,
    I.TransactionID,
    I.Type,
    I.XactSequence
FROM
    sys.traces T CROSS Apply 
    sys.fn_trace_gettable((SELECT CONVERT(varchar(100), value)
             FROM sys.fn_trace_getinfo(default)
             WHERE property=2), default) I JOIN
    sys.trace_events AS TE ON 
        I.EventClass = TE.trace_event_id LEFT JOIN
    cteEventSubClasses AS ESC ON
        TE.trace_event_id = ESC.trace_event_id And
        I.EventSubClass = ESC.subclass_value LEFT JOIN
    cteObjectTypes AS OT ON 
        TE.trace_event_id = OT.trace_event_id AND
        I.ObjectType = OT.subclass_value
WHERE
    T.is_default = 1 AND
    TE.NAME = 'Object:Deleted'

Source from SQLServerCentral – Identify who deleted your database or table

How to: Get the Default Backup Directory

The following query provides you the default backup directory of your SQL Server instance.

-- get the default backup directory
DECLARE    @BackupDirectory varchar(1000)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',@BackupDirectory OUTPUT;

SELECT  @BackupDirectory;

How to: Hide SQL Server User Databases in SSMS

Hiding all user databases for all logins

Suppose you want to hide all databases for all logins. Generally we hide our databases for security purposes. We can run the below statements to hide all databases for all logins. The databases will then only be visible to sysadmin logins or owners of the database.

USE MASTER
GO
DENY VIEW ANY DATABASE TO PUBLIC
GO

Once you run the above statement, you will not be able to see any databases in SQL Server Management Studio unless you are a sysadmin or your login is the owner of a database(s).

References

How to: Kill User Session

Let’s say you want to kill all running process by a particular user or account. The following query will help you to find all related SPID for all processes initiated by a particular user or account.

You may need to replace “USER_ACCOUNT” in the query with your target account name.

SELECT spid from master..sysprocesses
WHERE loginame = N'USER_ACCOUNT'

After you obtain the list of SPID, it’s time for you to reload your weapon, you can simply execute KILL command against those processes.

How to: Start SQL Server with Minimal Configuration

When you encounter configuration problem that prevent SQL Server from starting, you may need to start an instance using the minimal configuration option. This is the start up option -f. Starting an instance of SQL Server with minimal configuration automatically puts the server in single-user mode.

Kindly not what you can do when you start an instance of SQL Server in minimal configuration mode, as below:

  • Only a single user can connect, and the CHECKPOINT process is not executed.
  • Remote access and read-ahead are disabled.
  • Startup stored procedures do not run.

After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.

sqlservr.exe -c -f -s <Instance_Name>

You may want to know:
Starting SQL Server in Minimal Configuration
How to start SQL Server if you lost TEMPDB Data files?