List Table Sizes on All Databases

This post is for Julian...One of my dear trouble makers! :)))

Junlian came to DBA team to ask for a report of table size of all tables on all databases. My instant thought was to use the standard report of disk usage by table. However, no no no, he wanted more. The report only provides the table size for each database and doesn’t list all databases’ table sizes. And then I tried to combine sp_MSforeachdb and sp_MSforeachtable mentioned in the previous post to check all the tables on all the databases. Bad news! They don't work together well. OK, more work, and lets write a script from scratch then. How exciting!

Finally I came up with this solution.

IF Object_id('tempdb..#DatabaseTables') IS NOT NULL

  DROP TABLE #databasetables;

CREATE TABLE #databasetables

  (

     id        INT IDENTITY(1, 1),

     [dbname]  SYSNAME,

     tablename NVARCHAR(200)

  )

IF Object_id('tempdb..#TableSizeStaging') IS NOT NULL

  DROP TABLE #TableSizeStaging;

CREATE TABLE #TableSizeStaging

  (

     tablename  NVARCHAR(200),

     rows       CHAR(11),

     reserved   VARCHAR(18),

     data       VARCHAR(18),

     index_size VARCHAR(18),

     unused     VARCHAR(18)

  )

Script Out / Back Up All the Store Procedures

My lovely developers like to chanllenge my scripting skills. Today I was asked to restore one database on Dev server by using the backup taken from Prod. However, they wanted to keep all the store process in Dev. In order to avoid the store procs were removed during the dateabase restoring by prod backup,  I had to script them out and then run them after restoring completed. The following codes help me to do that in one go without repeating the same process.                                   

SET NOCOUNT ON

DECLARE @SP TABLE ( Id INT IDENTITY (1, 1),

                     Code VARCHAR (MAX) );

Who Dropped The Tables?

Recently, when I did daily checks on ETL processes, and then found one of ETLs failed on a few tables. Later on I realised those tables had been dropped somehow. However no one knew who did it, when did it and why did it. To investigate the crime, SQL Server default trace became the most useful tool, I found.

Firstly, you need to check if ‘default trace enabled’ through the following two ways.

1.      Run sp_configure

-- 'default trace enabled' only shows when 'show advanced options' is enabled

sp_configure 'show advanced options', 1

reconfigure

GO

-- check if 'default trace enabled'has 1 for both 'config_value' and 'run value'

sp_configure

   

2.      Or run the following script to check the setting

 SELECT* FROM sys.configurations WHERE configuration_id = 1568

Useful Undocumented sp_MSforeachdb/sp_MSforeachtable

Finding new stuff about SQL Server always excites me. Some SQL professionals may be aware of these two store procedures. However I just knew them a couple of months ago and I absolutely love them!

sp_MSforeachdb is undocumented store proc which presents in master database on SQL Server by default. It allows you to process a single command or multiple commands against every database on the incidence.

Sample 1 Check orphan users on all databases

EXECUTE sp_MSforeachdb @command1 = '?.dbo.sp_change_users_login ''Report''';

Who Fired Up The 'Disabled' SQL Job?

At my work, there are a lot SQL agent jobs that look like disabled or not scheduled. However, the reality is that they run every day and are fired up by the other SQL jobs on the same or different servers. On the other hand, they might also fire up other jobs when processing their job steps. When I first time troubleshooted job failures, it was hard to find the dependencies between those ‘disabled’ jobs without any workflow provided. The following T-SQL script helped me to identify job dependencies.

SELECT   j.name AS CallingJobName,

         s.step_name AS CallingStepName,

         s.step_id AS CallingStepID,

         s.command AS SQLCommand

FROM     msdb..sysjobsteps AS s

         INNER JOIN

         msdb..sysjobs AS j

         ON j.job_id = s.job_id

WHERE    s.command LIKE '%sp_start_job%'

ORDER BY CallingJobName

This is just a simple syntax. However you can get more job information by the combination of  msdb..sysjobsteps and msdb..sysjobs.

Identify long running queries by using T-SQL

Two ways to find out the current running query:

First Query

SELECT s.session_id,
       Db_name(r.database_id)                    AS dbname,
       s.login_name,
       r.blocking_session_id                     AS blkby,
       r.open_transaction_count,
       r.status,
       s.host_name,
       s.program_name,
       r.command,
       (SELECT Substring(TEXT, statement_start_offset / 2, (
                       CASE
                       WHEN statement_end_offset = -1 THEN Len(
                       CONVERT(NVARCHAR(MAX), TEXT)) * 2
                       ELSE statement_end_offset
                       END  statement_start_offset ) / 2 )
         FROM   sys.dm_exec_sql_text(sql_handle)) AS querytext
FROM   sys.dm_exec_sessions s
       JOIN sys.dm_exec_requests r
         ON s.session_id = r.session_id
WHERE  r.session_id = 57 -- SPID

Create User Friendly Backup Failure Alter Email

At my current work, daily database backup is one of many steps in a SQL Agent Job. Because of business requirement, the job is required to continue if one or many steps fail.  However it would cause problems…The main issue in the past was that DBA wouldn’t be informed if the backup job failed, as the job would go on the next step on the failure action. Furthermore, when we re-run the backup job after failure, it is difficult to tell which databases have been backed up and which databaes are left, and we don’t want to multi backup the same databases.

Therefore, my solution is to use ‘sp_send_dbmail’ and ‘RAISERROR’ inside the Catch block of a TRY…CATCH construct.

Use Extractor Utility with Litespeed Backups

Can I restore Litespeed backups on the server that does not have Litespeed installed? The answer is ‘Yes, you can.’  Litespeed provides an Extractor.exe utility that allows extracting Litespeed backups to native SQL Server backups, so that you can restore the converted native backups on any SQL Servers without Litespeed installation required.

There is a simple example. I used ‘Exetractor.exe’ to convert a 5GB Litespeed backup to the native backup that is about 34.5 after extracting from the local disk drive (Sample: U:\MSSQL\Backup) to the backup folder on a share server (Sample: \\lofmcdfiler01\sql_backups).

Configuring Tempdb From The Basic

What use TempDB in SQL Server?

• Temporary user objects, such as global (##temp) or local (#temp) temporary tables, temporary indexes and stored procedures, table variables, tables returned in table-valued functions or cursors.
• Internal objects are created by database engine to process a SQL server statement

o        Work tables for cursor or spool operations and temporary large object (LOB) storage, such as varchar(max), nvarchar(max), varbinary(max) text, ntext, image, xml) data type variables and parameters.

o        Work files for hash join or hash aggregate operations.

o        intermediate results for particular GROUP BY, ORDER BY, or UNION queries.

• Row versioning values for online index processes, Multiple Active Result Sets (MARS) sessions, AFTER triggers and index operations (SORT_IN_TEMPDB).
• Row versions that are generated by data modification transactions in a database that uses snapshot or read committed using row versioning isolation levels.

I Need To Be The Excutor On All The Databases

A developer at work asked me to grant her the permissions that allows her to execute store procedures and view the definitions on all the databases on the server. However, there are over 100 databases on the same server, it would take ages to grant the permissions to her one database by another one. And on some database, she was already db_datareader.

Therefore I decided to create ‘db_excutor’ role with the permission of ‘excute’ and ‘view definition’ on all the  databases, and create new users on those databases that she didn’t have access to…there is my code.

/* Create a database list that the user need get access to */

IF Object_id ('tempdb..#name') IS NOT NULL

        DROP TABLE #name

  SELECT name INTO #name

   FROM master.sys.databases

   WHERE database_id NOT IN (1,2,3,4,6,163)