:::: MENU ::::
Browsing posts in: SQL Server

Find long running queries

One of the first scripts I go to when looking at what’s happening in an instance is this:

SELECT     db_name(r.database_id), r.session_id, r.blocking_session_id, r.cpu_time,
SUBSTRING(qt.text,(r.statement_start_offset/2) +1,
(CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
ELSE
r.statement_end_offset
END -r.statement_start_offset)/2) AS stmt_executing,
s.login_name,     s.HOST_NAME,     s.PROGRAM_NAME,    s.host_process_id,
r.status,     r.wait_time,    wait_type,     r.wait_resource,
r.total_elapsed_time,r.reads,r.writes, r.logical_reads, r.plan_handle
, (select query_plan from sys.dm_exec_query_plan(r.plan_handle)) as xml_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt,
sys.dm_exec_sessions s
WHERE r.session_id > 50
and r.session_id=s.session_id
ORDER BY r.cpu_time desc

It shows you among other things blocked processes, a link to the sql plan, reads, writes, the executing statement and a lot of other handy stuff.

A simple good script to have around.

//R


SQL Server 2008R2 CU11 fixed xp_delete_file memory leak

So a backup maintenance job, Ola Hallengrens Maintenance Solution, see ola.hallengren.com, was having problems removing old backups, the removal was done with Microsofts own xp_delete_file, the thing is that the instance held over 2000 databases, and after a week or two the backup times went from 10 minutes to 10 hours. Found that the part taking time was deleting old files, so I contacted MS which were to no help really, usually they are, then Ola Hallengren stepped up to the plate, got their attention and finally they released a fix! :-)

See http://support.microsoft.com/kb/2926028 for more info, it seems there was some kind of memory leak they fixed, now my backups are running fine again.

Happy customer = happy DBA consultant!

//Richard


Windows 8/2012 shortcuts

Thought it was totally awkward to use the ‘put mouse in upper right of screen to get to menu’, so I decided to search for shortcuts, of which there were a LOT!

Nicked these from http://blogs.msdn.com/b/hyperyash/archive/2012/08/28/windows-8-shortcuts.aspx  had to save them where I knew I’d always find them. Props go to Yash Tolia at Microsoft for printing these, had never found them all by myself.

Windows 8/2012 shortcuts:

Windows key: Switch between Modern Desktop Start screen and the last accessed application

Windows key + C: Access the charms bar

Windows key + Tab: Access the Modern Desktop Taskbar

Windows key + I: Access the Settings charm

Windows key + H: Access the Share charm

Windows key + K: Access the Devices charm

Windows key + Q: Access the Apps Search screen

Windows key + F: Access the Files Search screen

Windows key + W: Access the Settings Search screen

Windows key + P: Access the Second Screen bar

Windows key + Z: Brings up the App Bar when you have a Modern Desktop App running

Windows key + X: Access the Windows Tools Menu

Windows key + O: Lock screen orientation

Windows key + . : Move the screen split to the right

Windows key + Shift + . : Move the screen split to the left

Windows key + V: View all active Toasts/Notifications

Windows key + Shift + V: View all active Toasts/Notifications in reverse order

Windows key + PrtScn: Takes a screenshot of the screen and automatically saves it in the Pictures folder as Screenshot

Windows key + Enter: Launch Narrator

Windows key + E: Open Computer

Windows key + R: Open the Run dialog box

Windows key + U: Open Ease of Access Center

Windows key + Ctrl + F: Open Find Computers dialog box

Windows key + Pause/Break: Open the System page

Windows key + 1..10: Launch a program pinned on the Taskbar in the position indicated by the number

Windows key + Shift + 1..10: Launch a new instance of a program pinned on the Taskbar in the position indicated by the number

Windows key + Ctrl + 1..10: Access the last active instance of a program pinned on the Taskbar in the position indicated by the number

Windows key + Alt + 1..10: Access the Jump List of a program pinned on the Taskbar in the position indicated by the number

Windows key + B: Select the first item in the Notification Area and then use the arrow keys to cycle through the items Press Enter to open the selected item

Windows key + Ctrl + B: Access the program that is displaying a message in the Notification Area

Windows key + T: Cycle through the items on the Taskbar

Windows key + M: Minimize all windows

Windows key + Shift + M: Restore all minimized windows

Windows key + D: Show/Hide Desktop (minimize/restore all windows)

Windows key + L: Lock computer

Windows key + Up Arrow: Maximize current window

Windows key + Down Arrow: Minimize/restore current window

Windows key + Home: Minimize all but the current window

Windows key + Left Arrow: Tile window on the left side of the screen

Windows key + Right Arrow: Tile window on the right side of the screen

Windows key + Shift + Up Arrow: Extend current window from the top to the bottom of the screen

Windows key + Shift + Left/Right Arrow: Move the current window from one monitor to the next

Windows key + F1: Launch Windows Help and Support

 

PageUp: Scroll forward on the Modern Desktop Start screen

PageDown: Scroll backward on the Modern Desktop Start screen

Esc: Close  a charm

Ctrl + Esc: Switch between Modern Desktop Start screen and the last accessed application

Ctrl + Mouse scroll wheel: Activate the Semantic Zoom on the Modern Desktop screen

 

Alt: Display a hidden Menu Bar

Alt + D: Select the Address Bar

Alt + P: Display the Preview Pane in Windows Explorer

Alt + Tab: Cycle forward through open windows

Alt + Shift + Tab: Cycle backward through open windows

Alt + F: Close the current window Open the Shut Down Windows dialog box from the Desktop

Alt + Spacebar: Access the Shortcut menu for current window

Alt + Esc: Cycle between open programs in the order that they were opened

Alt + F4: Close Application

 

Alt + Enter: Open the Properties dialog box of the selected item

Alt + PrtScn: Take a screen shot of the active Window and place it in the clipboard

Alt + Up Arrow: Move up one folder level in Windows Explorer (Like the Up Arrow in XP)

Alt + Left Arrow: Display the previous folder

Alt + Right Arrow: Display the next folder

Shift + Insert: CD/DVD Load CD/DVD without triggering Autoplay or Autorun

Shift + Delete: Permanently delete the item (rather than sending it to the Recycle Bin)

Shift + F6: Cycle backward through elements in a window or dialog box

Shift + F10: Access the context menu for the selected item

Shift + Tab: Cycle backward through elements in a window or dialog box

Shift + Click: Select a consecutive group of items

Shift + Click on a Taskbar button: Launch a new instance of a program

Shift + Right-click on a Taskbar button: Access the context menu for the selected item

Ctrl + A: Select all items

Ctrl + C: Copy the selected item

Ctrl + X: Cut the selected item

Ctrl + V: Paste the selected item

Ctrl + D: Delete selected item

Ctrl + Z: Undo an action

Ctrl + Y: Redo an action

Ctrl + N: Open a new window in Windows Explorer

Ctrl + W: Close current window in Windows Explorer

Ctrl + E: Select the Search box in the upper right corner of a window

Ctrl + Shift + N: Create new folder

Ctrl + Shift + Esc: Open the Windows Task Manager

Ctrl + Alt + Tab: Use arrow keys to cycle through open windows

Ctrl + Alt + Delete: Access the Windows Security screen

Ctrl + Click: Select multiple individual items

Ctrl + Click and drag an item: Copies that item in the same folder

Ctrl + Shift + Click and drag an item: Creates a shortcut for that item in the same folder

Ctrl + Tab:  Move forward through tabs

Ctrl + Shift + Tab: Move backward through tabs

Ctrl + Shift + Click on a Taskbar button: Launch a new instance of a program as an Administrator

Ctrl + Click on a grouped Taskbar button: Cycle through the instances of a program in the group

F1: Display Help

F2: Rename a file

F3: Open Search

F4: Display the Address Bar list

F5: Refresh display

F6: Cycle forward through elements in a window or dialog box

F7: Display command history in a Command Prompt

F10: Display hidden Menu Bar

F11: Toggle full screen display

Tab: Cycle forward through elements in a window or dialog box

PrtScn: Take a screen shot of the entire screen and place it in the clipboard

Home: Move to the top of the active window

End: Move to the bottom of the active window

Delete: Delete the selected item

Backspace: Display the previous folder in Windows Explorer  Move up one folder level in Open or Save dialog box

Esc: Close a dialog box

Num Lock Enabled + Plus (+): Display the contents of the selected folder

Num Lock Enabled + Minus (-): Collapse the selected folder

Num Lock Enabled + Asterisk (*): Expand all subfolders under the selected folder

 

Press Shift 5 times Turn StickyKeys on or off

Hold down right Shift for 8 seconds Turn FilterKeys on or off

Hold down Num Lock for 5 seconds Turn ToggleKeys on or off


SQL Server Scripts

I have a large file with a lot of different sql scripts covering performance and tuning, index, logins, mirroring, DMV’s, DBCC commands and so on, I’m in the process of sorting them and creating shorter posts for a couple of scripts in each category, they will come up here shortly.

Hopefully they can come in handy, I refer to them quite often in my work as a DBA, I have been working on compiling these scripts for several years now, so it’s quite a large collection.


Are SQL Agent jobs mirror aware?

When this first was brought up I thought ‘hmm… no’ and left it at that, until one of the production systems with mirrored databases got some corrupted data.

The DB developer, which also had created the agent job, asked me again, very politely I must say, if maybe there was a chance an agent job starting on the instance holding the mirror database could login to the principal instance and run it’s code there. Again, naively I said no, no It can’t.

sigh…

 

Anyway, it seems that under certain circumstances a job that is started in the mirror instance will actually login to the principal and run the code there. I had never seen that before and would still be denying it hadn’t I seen evidence that Spotlight picked up, I could see in the process list logins from the mirror host in the principal host.

So, what happened and why?? It seems that if you create a job with a jobstep where you specify a database that is mirrored, and the job starts in the mirror instance, then it will/could outsmart itself and login to the principal and run the code there. I checked the sp_add_jobstep documentation and there was no mention of it. Have created a ticket with MS, will be interresting to see what they answer.

Will update this when I know more.

 

//Richard


Restoring many databases

Hi!

 

Had a release the other day where we had to move a couple of thousand databases from one machine to another, we could not shutdown the source instance so no detach/attach, had to go with backup restore.

So, I created a simple script that I ran on the source instance, it reads the backup information in msdb and creates restore commands which I then ran on the target instance to restore all these thousands of databases. I only did full restores, but with a bit of tweaking you could do a full restore with no recovery and then eg. diff och log with recovery.

When you run it in SSMS, set results to text, not rows, and be wary that you don’t exceed the “Maximum numbers of characters displayed in each column”, found under Options -> Query Results -> SQL Server -> Results to Text

Make sure the path pointing to the backups on the target machine are the same as on the source, goes without saying I know, but I just thought I’d mention it.

The script below will create restore commands for all full backups that were taken in the last 17 hours and for all databases with DATABASE_NAME in it’s name, change it to match your case.

SELECT  'RESTORE DATABASE [' + bs.database_name + ']
FROM DISK = N''' + bmf.physical_device_name + '''' +
' WITH  FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10'
+ CHAR(10) + 'GO' + CHAR(10)
FROM    msdb.dbo.backupmediafamily bmf with(nolock),
msdb.dbo.backupset bs with(nolock)
WHERE   bs.media_set_id = bmf.media_set_id
AND    (CONVERT(datetime, bs.backup_start_date, 102) >= dateadd(hour, -17, getdate()))
AND    bs.database_name like '%DATABASE_NAME%'
AND     bs.type = 'D'

 

If you want to check only diff backups, set bs.type to ‘I’ and for logs ‘L’

If you want to restore with NORECOVERY just add NORECOVERY like below:

SELECT  'RESTORE DATABASE [' + bs.database_name + ']
FROM DISK = N''' + bmf.physical_device_name + '''' +
' WITH  FILE = 1,  ,  NORECOVERY,
NOUNLOAD,
REPLACE,
STATS = 10'
+ CHAR(10) + 'GO' + CHAR(10)
FROM    msdb.dbo.backupmediafamily bmf with(nolock),
msdb.dbo.backupset bs with(nolock)
WHERE   bs.media_set_id = bmf.media_set_id
AND    (CONVERT(datetime, bs.backup_start_date, 102) >= dateadd(hour, -17, getdate()))
AND    bs.database_name like '%DATABASE_NAME%'
AND     bs.type = 'D'

 

STATS=10 gives progress output every 10%.

 

And as always, try in test before running it in prod!

 

//R

 

 


Search for string in text files

So, in Windows we are missing the excellent *nix commands grep, awk and sed, what to do if you want to search for strings in a lot of textfiles, as logs.

I was interrested in how many times the string ‘UPDATE STATISTICS’ was written into log files, there were some 20 odd files, all of them more than 20MB in size. The logs were from a script running to update statistics in a lot of databases in one instance, Ola Hallengrens script, the files had the form:

MP_UPDATESTATISTICS_USERDB_DATE_TIME.TXT

 

So, it was actually quite easy, I opened cmd, cd to the log directory and ran:

for %f in (MP_UpdateStatistics_UserDB*) do find /c "UPDATE STATISTICS" %f

 

The string count is shown to the far right:

find /c "UPDATE STATISTICS" MP_UpdateStatistics_UserDB_20130808_230000.txt
---------- MP_UPDATESTATISTICS_USERDB_20130808_230000.TXT: 48303

 

//Richard


Availability group failover when no agent jobs are running

Needed a job that anyone could run to perform a manual failover, the job shold wait for all other jobs to finish before triggering the failover. Came up with the below solution, very simple but it works.

If you want to use it, then alter your code to do the failover and also how long you want to wait to between checks.

 

USE [msdb]
GO

/****** Object:  Job [Failover databases]    Script Date: 2013-06-07 15:28:45 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 2013-06-07 15:28:45 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Failover databases', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'Only failover if no jobs are running', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Failover databases]    Script Date: 2013-06-07 15:28:45 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Failover databases', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'--Only failover if @failover = 1
declare @failover integer
set @failover = 0

WHILE (@failover = 0)
IF NOT EXISTS(     
        select 1 
        from msdb.dbo.sysjobs_view job  
        inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id 
        where  activity.run_Requested_date is not null  
        and activity.stop_execution_date is null
        and job.name <> ''Failover databases''

        ) 
    BEGIN
        set @failover = 1
        RAISERROR(''Failover started'', 10, 1) WITH log 
        --ALTER AVAILABILITY GROUP AGGROUP FAILOVER
    END 
ELSE 
    BEGIN 
        RAISERROR(''Jobs running, will wait for finish until failover'', 10, 1) WITH log
        WAITFOR DELAY ''00:00:03''

    END', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

You can setup a long running job with the code below to test, alter the time you want it to run,
then start it, when it runs start the failover job and check the SQL Server errorlog, it should say that it’s waiting for the long running job to finish and then start the failover.

USE [msdb]
GO

/****** Object:  Job [long running job]    Script Date: 2013-06-07 15:28:50 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 2013-06-07 15:28:50 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'long running job', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'a simple long running job', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [long running job]    Script Date: 2013-06-07 15:28:50 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'long running job', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'--Only failover if @failover = 1
declare @wait char(8)
set @wait = ''00:00:20''

RAISERROR(''Job running 20 seconds, failover should wait'', 10, 1) WITH log

WAITFOR DELAY @wait

RAISERROR(''Job finished'', 10, 1) WITH log
', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Set SPN on service account to use Kerberos to SQL Server

I always forget the syntax for setting SPN on the SQL Server service account.

So, I thought I’d write it down….

In the examples below, the account is called SvcSQLAccount in the DOMAIN domain, we are using port 1433 and the FQN is SERVER1.DOMAIN.COM

setspn -A MSSQLSvc/SERVER1.DOMAIN.COM:1433 DOMAIN\SvcSQLAccount

setspn -A MSSQLSvc/SERVER1.DOMAIN.com DOMAIN\SvcSQLAccount

setspn -A MSSQLSvc/SERVER1:1433 DOMAIN\SvcSQLAccount

setspn -A MSSQLSvc/SERVER1 DOMAIN\SvcSQLAccount

 

Check if it is ok by running:

Setspn -L DOMAIN\SvcSQLAccount

 

Kerberos authentication offers the following advantages over NTLM authentication:

  • Mutual authentication. When a client uses the Kerberos v5 protocol for authentication with a particular service on a particular server, Kerberos provides the client with an assurance that the service is not being impersonated by malicious code on the network.
  • Delegation support. Servers that use Kerberos authentication to authenticate clients can impersonate those clients and use the client’s security context to access network resources.
  • Performance. Kerberos authentication offers improved performance over NTLM authentication.
  • Simplified trust management. Networks with multiple domains no longer require a complex set of explicit, point-to-point trust relationships.
  • Interoperability. Microsoft’s implementation of the Kerberos protocol is based on standards-track specifications recommended to the Internet Engineering Task Force (IETF). As a result, the implementation of the protocol in Windows 2000 lays a foundation for interoperability with other networks where Kerberos version 5 is used for authentication.

 


Drop ALL foreign keys in all tables in a database

In SSMS, open a new Query windows, choose the right database, VERY IMPORTANT!!!!

Paste in this script and run it, it will immediately find all FK and drop them.

 

-- begin script
DECLARE @cmd nvarchar(1000)
DECLARE @fk_table_name nvarchar(1000)
DECLARE @fk_name nvarchar(1000)

DECLARE cursor_fkeys CURSOR FOR
   SELECT  OBJECT_NAME(fk.parent_object_id) AS fk_table_name,
            fk.name as fk_name
   FROM    sys.foreign_keys fk  JOIN  
           sys.tables tbl ON tbl.OBJECT_ID = fk.referenced_object_id 

OPEN cursor_fkeys
FETCH NEXT FROM cursor_fkeys 
    INTO @fk_table_name, @fk_name

WHILE @@FETCH_STATUS=0
BEGIN
    -- build alter table statement
    SET @cmd = 'ALTER TABLE [' + @fk_table_name + '] DROP CONSTRAINT [' + @fk_name + ']' 
    -- execute it
    exec dbo.sp_executesql @cmd

    FETCH NEXT FROM cursor_fkeys 
    INTO @fk_table_name, @fk_name
END 
CLOSE cursor_fkeys
DEALLOCATE cursor_fkeys

//R