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) 

 

All About RAID

I have been asked about RAID a lot of times at the interviews previously. My mindset was quite blurred at a couple of times. But now, I am asking myself, ‘What is RAID?’, ‘What are RAID levels?’ and ‘How do you configure RAID level for SQL Server?’ In this post, I am starting with the basic concept of RAID.

What is RAID?

RAID shorts for Redundant Array of Independent (or Inexpensive) Disks. It’s the combination of multiple disk drives into large, high performance logical disks, which allows the same data to be redundantly stored in the different places crossed the physical disks. As the result, it will increase fault tolerance, data availability, system reliability and I/O performance.  

What are RAID levels?

The common RAID levels used for SQL Server are RAID 0, 1, 5 0+1 or 1+0.

Finding and Troubleshooting SQL Server Permissions

It can be very tricky to troubleshoot user permissions on SQL Server. As SQL Server DBA, we are quite often questioned by users about what permissions they’ve got.  The permissions can be granted on server, database, schema and database object level (such as table, store proc, views, functions, and etc). To avoid dig through many screens or levels in management studio, fn_my_permission, as a built-in table value function from SQL Server 2005, provides the list of all the permissions effectively granted to the principle on a securable.

Before starting the permission checks, your login needs to be an account that has sysadmin role.

To list all permissions on server:

If login isn't specified, it shows what permissions the caller has by default.

                                                                       

EXECUTE AS LOGIN = 'Domain\UserLogin'

SELECT * FROM fn_my_permissions(NULL, NULL)

REVERT

Revert is to switche the execution context back to the caller of the last EXECUTE AS statement

Or alternative use

SELECT * FROM fn_my_permissions(NULL, 'SERVER')

Scheduling Server Side Trace (Profiler) For Deprecated Features

I was trying to use profiler to trace deprecated features in a SQL Server 2005, before it’s migrated from 2005 to 2008. And also capturing the use of deprecated features in different time period during a day was required, especially for those SQL queries that run over night. However SQL Server Profiler doesn’t provide a build-in scheduling option, so I decided to use SQL command to schedule an agent job, so that the profiler trace can automatically start.

Firstly, Use SQL Profiler to create the trace definition

1. Start SQL Profiler and select File > New Trace. Specify the events, columns, filters you want in your trace.
2. Start the trace and then stop it.
3. Export the definition. Click File > Export > Script Trace Definition > For SQL Server 2005. Save the scripted trace as ‘*.sql’.
   Note: For SQL Sever 2000 and 2008 choose the appropriate output type.

Secondly, Create a store procedure to set up profiler trace.

Here, I am using tracing deprecated features as a sample.