Friday, 28 October 2011

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

Wednesday, 26 October 2011

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.

Wednesday, 19 October 2011

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).

Tuesday, 18 October 2011

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.

Friday, 14 October 2011

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)