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%'''

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: 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?

What is: the Event and Category Queries in Default Trace

The default trace is a valuable tool for the modern DBA’s tool belt. It offers a wealth of information, while minimally impacting the system. The default trace is not a widely publicized feature of SQL Server 2005, but is slowly gaining fame. The default trace gives administrators the ability to get detailed information about auditing events, database events, error events, full text events, object creation, object deletion and object alteration events. With this much information at their fingertips, administrators are more productive and can more easily identify problems in a production environment.

The following scripts will shows you the events and the information that you need to know in with regards to your instance. Default trace should not only be used re-actively but proactively. A proactive mentality will reveal small problems before they escalate to bigger problems.

List of Events

SELECT *
FROM sys.trace_events

List of Categories

SELECT *
FROM sys.trace_categories

List of everything default trace can capture

SELECT DISTINCT cat.name AS CategoryName, e.name AS EventName
FROM fn_trace_geteventinfo(1) AS rt
INNER JOIN sys.trace_events AS e ON rt.eventid = e.trace_event_id
INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id
ORDER BY CategoryName, EventName

List of Subclass Values

SELECT *
FROM sys.trace_subclass_values

Get Trace Event Columns

SELECT 
     t.EventID,
     t.ColumnID,
     e.name AS Event_Descr,
     c.name AS Column_Descr
FROM ::fn_trace_geteventinfo(1) t
     INNER JOIN sys.trace_events e 
          ON t.eventID = e.trace_event_id
     INNER JOIN sys.trace_columns c 
          ON t.columnid = c.trace_column_id

References:

How to: Get All Databases Index Fragmentation Details

This SQL script will provide you the index fragmentation details for all databases. It would be helpful while scheduling or determining maintenance task of re-indexing.

This SQL script will provides you all the databases names, object names, index id, index name, average fragmentation percent, index type & action required.

The “Action required column” on the basis of fragmentation value using sys.dm_db_index_physical_stats DMV.

If average fragmentation value is >5 & 30 index should rebuild, those value is less than 5 then no result is require.

IF EXISTS (	SELECT *
FROM [tempdb].[dbo].[sysobjects]
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[tmp_indexfragmentation_details]'))
DROP TABLE [tempdb].[dbo].[tmp_indexfragmentation_details]
GO

CREATE TABLE [tempdb].[dbo].[tmp_indexfragmentation_details](
[DatabaseName] 					[nvarchar] (100) NULL,
[ObjectName]					[nvarchar] (100) NULL,
[Index_id] 						INT,
[indexName] 					[nvarchar] (100) NULL,
[avg_fragmentation_percent]		float NULL,
[IndexType] 					[nvarchar] (100) NULL,
[Action_Required] 				[nvarchar] (100) default 'NA'
) ON [PRIMARY]

DECLARE @dbname varchar(1000)
DECLARE @sqlQuery nvarchar(4000)

DECLARE dbcursor CURSOR for
SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')

OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlQuery = '
USE [' + @dbname + '];

IF EXISTS
(
SELECT compatibility_level
FROM sys.databases
WHERE
name = N'''+ @dbname +'''
AND compatibility_level >= 90
)
BEGIN
INSERT INTO [tempdb].[dbo].[tmp_indexfragmentation_details]
(
DatabaseName
, ObjectName
, Index_id
, indexName
, avg_fragmentation_percent
, IndexType
)
SELECT
db_name() as DatabaseName
, OBJECT_NAME (a.object_id) as ObjectName
, a.index_id, b.name as IndexName
, avg_fragmentation_in_percent
, index_type_desc
FROM
sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON
a.object_id = b.object_id
AND a.index_id = b.index_id
WHERE
b.index_id <> 0
AND avg_fragmentation_in_percent <> 0
END;'

EXEC sp_executesql @sqlQuery

FETCH NEXT FROM dbcursor
INTO @dbname
END

CLOSE dbcursor
Deallocate dbcursor

-- Update the action require for item with average fragmentation value >30 to "Rebuild"
UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details]
SET Action_Required = 'Rebuild'
WHERE avg_fragmentation_percent >30
GO

-- Update the action require for item with average fragmentation value >5 & <30 to "Reindex"
UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details]
SET Action_Required = 'Reorganize'
WHERE avg_fragmentation_percent <30 and avg_fragmentation_percent >5
GO

-- Show the index fragmentation result
SELECT * FROM [tempdb].[dbo].[tmp_indexfragmentation_details]
ORDER BY databasename

NOTE: This SQL script will work on both SQL Server 2005, 2008 & 2008R2. It won’t work for SQL 2000 as DMV’s are involved in this script.

Original scripts adopted from : http://gallery.technet.microsoft.com/scriptcenter/All-Databases-Index-a68ed3c5

How to: Performance Tunining on Long Wait Transaction

Most of the time DBA will fire the “sp_who2 active” command to find out the active session on the SQL Server instance and quickly kill the dead lock as soon as he/she find it blocking one another. While, there’s always other way to find out more detail about it before you pull your trigger to kill the running transaction. Thus, you can avoid possible lost of data after you kill the running transaction.

Although you can look at wait statistics to understand specifically what is causing the server to slow down. It’s a great metrics for understanding the system as a whole. You could run a query against sys.dm_os_wait_stats and other by the number of currently waiting task, the cumulative wait time or the max wait time to see what, in general is causing the server to run slow.

To see what’s currently running on the server, you could run a query against the Dynamic Management Object (DMO) sys.dm_exec_requests.

SELECT 
  der.session_id , --internal identifier for the running session
  der.status , --determines if the query is active or waiting
  der.start_time , --gives you an idea when the query started
  der.command , --the type of command involved
  der.database_id , --which database you're connected to
  der.user_id , --which login is running the command
  der.blocking_session_id , --session id of blocking session
  der.wait_type , -- what is the waiting session it waiting on
  der.wait_time , --how long has it been waiting
  der.last_wait_type , --what caused it to last wait
  der.cpu_time , --how much of the CPU has been used
  der.total_elapsed_time , --how long has the command been running
  der.reads , --has the command hit the disk for information
  der.writes , --how much information was written to the disk
  der.logical_reads --how many reads came out of memory
FROM sys.dm_exec_requests AS der;

After you capture the long running transaction that probably use plenty of the resources. Now you need to figure out what is the query this session was running and its execution plan. The query below will help you to pull all this information together in a hurry.

SELECT SUBSTRING(dest.text, ( der.statement_start_offset / 2 ) + 1,
(der.statement_end_offset - der.statement_start_offset )
/ 2 + 1)
FROM sys.dm_exec_requests AS der
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
WHERE der.session_id = 442;

Reference :
Red-Gate.com – The Top 5 Hard-earned Lessons of a DBA

How to: Find the Last Modified Date of The Database

This one will list all online databases on the server and the last modified date

DECLARE @sqlString NVARCHAR(max)
DECLARE @union NVARCHAR(max)
SET @sqlString = ''
SET @union = ''
DECLARE @name nvarchar(50);

DECLARE crs CURSOR FOR 
SELECT Name FROM sys.databases WHERE  state = 0
OPEN crs
FETCH NEXT FROM crs INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @sqlString = @sqlString + @union 
   SET @sqlString = @sqlString + '
    SELECT 
    TOP 1
  ''' + @name + ''' as DBName, modify_date
  FROM
   [' + @name + '].sys.tables'

 SET @union = ' UNION '

    FETCH NEXT FROM crs INTO @name
END 

SET @sqlString = @sqlString + ' ORDER BY DBName ASC'

CLOSE crs;
DEALLOCATE crs;

EXEC(@sqlString)

How to: Check Databases That Have Not Had a Full Backup in x Days

Below is a nice quick sanity check showing all the dbs that have NOT been backed up for X days or have never been backed up at all.

DECLARE @num_of_days INT
SET @num_of_days = 1 

SELECT
	A.name
	, MAX(B.backup_finish_date) AS 'LastBackupDateTime'
FROM
	master.dbo.sysdatabases A WITH(NOLOCK) LEFT OUTER JOIN
	msdb.dbo.backupset B WITH(NOLOCK) ON A.name = B.database_name
WHERE (B.TYPE = 'D' OR B.TYPE IS NULL)
GROUP BY A.name
HAVING (MAX(B.backup_finish_date) < GETDATE() - @num_of_days OR MAX(B.backup_finish_date) IS NULL) 
ORDER BY A.name

Reference :
SQL DBs That Have Not Had A Full Backup in X Days