Database With Fun (While 1 = 1)

USE MASTER -- SELECT SESSION_ID, '[' + CAST(DATABASE_ID AS VARCHAR(10)) + '] ' + DB_NAME(DATABASE_ID) AS [DATABASE], PERCENT_COMPLETE, START_TIME, STATUS, COMMAND, DATEADD(MS, ESTIMATED_COMPLETION_TIME, GETDATE()) AS ESTIMATED_COMPLETION_TIME, CPU_TIME FROM SYS.DM_EXEC_REQUESTS --Apply this Where Clause Filter if you need to check specific events such as Backups, Restores, Index et al. WHERE COMMAND LIKE '%BACKUP%' OR COMMAND LIKE '%RESTORE%' OR COMMAND LIKE '%INDEX%' OR COMMAND LIKE '%DBCC%'

USE [master] GO IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwWho3]')) DROP VIEW [dbo].[vwWho3] GO CREATE VIEW vwWho3 AS SELECT dess.session_id AS [SPID],CASE der.status WHEN 'background' THEN 'BACKGROUND' ELSE der.status END AS 'Status' , CONVERT(varchar(32),dess.original_login_name) AS [Login], ISNULL(dess.host_name,'.') AS [HostName],CASE CONVERT(varchar(12),der.blocking_session_id) WHEN '0' THEN '.' ELSE CONVERT(varchar(12),der.blocking_session_id) END AS BlkBy, DB_NAME(der.database_id) AS DBName, der.command AS 'Command',der.cpu_time AS [CPUTime],der.logical_reads AS [DiskIO],dess.last_request_start_time AS [LastBatch],ISNULL(dess.program_name,'') AS [ProgramName],ISNULL(dest.text,'') AS 'CurrentQuery', ISNULL(deqp.query_plan,'') AS 'CurrentPlan',rgwg.name AS [ResourceWor...

These scripts will extract security for Windows Logins, Database Users, Roles, Objects and SQL Logins. -- Create SQL Logins SET NOCOUNT ON SELECT 'EXEC sp_addlogin @loginame = ''' + name + '''' ,', @defdb = ''' + default_database_name + '''' ,', @deflanguage = ''' + default_language_name + '''' ,', @encryptopt = ''skip_encryption''' ,', @passwd =' , cast(password_hash AS varbinary(256)) ,', @sid =' , sid FROM sys.sql_logins WHERE name NOT IN ('sa') ORDER BY name -- Create Windows Logins EXEC sp_MSforeachdb 'Print''-- Users for [?]'' Use [?]; Print''Use [?];'' select ''CREATE LOGIN ['' + server_login_name + ''] FROM WINDOWS WITH DEFAULT_DATABASE=[TEMPDB], DEFAULT_LANGUAGE=[us_english]'' from ( select dp.name as database_principal_name, sl.name server_logi...

All processes that are currently running on the instance- SELECT * FROM sys.dm_exec_requests a OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b WHERE session_id > 50 and session_id <> @@spid If nothing is currently running on the server. Open sql profiler, connect to the instance and trace the following events: (Be sure to select all columns in the output) RPC: Completed (Under stored procedures) SQL: BatchCompleted (Under TSQL) Profiling should help identify the bottleneck. You will need to look for rows which have a high cpu value. 1.Find disk delays for a particular database- select DB_NAME(database_id) DB_NAME, di.file_id,df.name,io_stall_read_ms ,num_of_reads ,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms' ,io_stall_write_ms,num_of_writes ,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms' ,io_stall_read_ms + io_stall_write_ms as io_stalls ,num_of_reads + ...

1. Conventional one-file backup- BACKUP DATABASE [hhhh]  TO  DISK =  N'D:\DBA-HIMALAYA-PC$SQL2008R2\AAA\hhhh.bak'  GO 2. let us see how we can split one database into two different database files. This method is very similar to taking a single-file backup. By simply adding an additional DISK option we can split the files backup files. BACKUP DATABASE [hhhh] TO DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\A.bak', DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\B.bak', DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\C.bak' GO RESTORE DATABASE [hhhh] FROM DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\A.bak', DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\B.bak', DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\C.bak' GO It's a common practice to create an exact copy of the backup and store it to several places. There is a Mirror command that makes a copy of the database backup to different locations while taking the original backup. T...

Example 1 – A query is slow because the response time is greater than 10 seconds and normally it’s 2 seconds Example 2  -  Client User requests are queing up and we notice CPU usage at 100% , whereas the normal level is 25% In Example 1, we’re using the response time  as the unit of measure for the task i.e query In Example 2 , CPU usage is the unit of measure for the client user requests Once the problem is quantified in this manner , break the quantities down into smaller parts. It may be session IDs (SPID), execution plan data etc. Each profile has it’s own data requirements. The key message is : attempt to gather performance data matching the problem Creating the profile at the correct level of detail requires relevant data. SQL Server offers different methods of collecting data e.g DMV, Traces , Custom queries.   It’s important to collect data at the right range. If analysing a problem at a user level , then collect data at the user level. ...

Your two main enemies are developers and SAN admins #youmightbeaDBA When your girlfriend has to lean around the laptop to kiss you goodnight #youmightbeadba You always plan an exit strategy, even when entering a McDonald's #youmightbeaDBA  You can't explain to your family what you really do for a living #youmightbeaDBA  You have at least one set of scripts you won't share #youmightbeaDBA  You and the Oracle DBA would happily fight off a developer together #youmightbeaDBA  You've sent a vendor suggestions on improving their database design or code (and been ignored) #youmightbeaDBA  You've sent a vendor suggestions on improving their database design or code (and been ignored) #youmightbeaDBA  You refer to clothes as "Data Abstractions" #youmightbeaDBA  Your girlfriend knows what "ETL" means #youmightbeaDBA  You check your server logs before you check your e-mail in the morning so you can reply "Yeah...