Move files from sub-directory to root folder

Had a need to move a bunch of database backup files from sub-directories into a root folder so it was easier for my restore script to process them all. Came across this little gem and decided it really needed to go into my toolbox

for /f "tokens=5* skip=2" %i in ('dir *.bak ^| findstr "DIR"') do (move %i\*.* .)

SQL Server x32 Perfmon on Windows x64

For me to find later 

http://weblogs.asp.net/guybarrette/archive/2009/06/16/missing-sql-server-x32-performance-counters-on-x64-windows.aspx

Quick snapshot of current activity

This query provides a quick snapshot of current active and waiting sessions in SQL Server 2008.

select er.[session_id], txt.[text], pln.[query_plan]
  , sess.login_name, sess.host_name, db.name as db_name
    , sess.last_request_start_time, sess.last_request_end_time , er.[blocking_session_id], er.[last_wait_type], er.[wait_time]
  , er.[cpu_time], er.[reads], er.[writes], er.[logical_reads]
from sys.dm_exec_sessions sess
  inner join sys.dm_exec_requests er
  on sess.session_id = er.session_id
    left join sys.databases db
    on er.database_id = db.database_id
    outer apply sys.dm_exec_sql_text(er.[sql_handle]) txt
    outer apply sys.dm_exec_query_plan(er.[plan_handle]) pln
where sess.is_user_process = 1

SQL 2008 Database Mail Resend

Try this query to resend failed items in the Database Mail queue.

declare @mailItem int
declare @mailRequest nvarchar(max)
declare @mailRecipient nvarchar(max)
declare @mailSubject nvarchar(255)
declare @mailSendDate datetime
declare @statusMsg nvarchar(400)
declare @rc int

declare curFailedMail cursor fast_forward for
  SELECT [mailitem_id]
  FROM [msdb].[dbo].[sysmail_faileditems]

open curFailedMail
fetch next from curFailedMail into @mailItem

while @@fetch_status = 0 
begin -- fetch loop
    -- Create request xml
    SET @mailRequest = '<requests:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" '
        + 'xsi:schemaLocation="http://schemas.microsoft.com/databasemail/requests RequestTypes.xsd" '
        + 'xmlns:requests="http://schemas.microsoft.com/databasemail/requests"> '
        + '<MailItemId>' + convert(nvarchar(20), @mailItem) + N'</MailItemId></requests:SendMail>'

    -- put the request on the queue.
    EXEC @rc = msdb..sp_SendMailQueues @mailRequest
    IF @rc = 0
    BEGIN -- resend success
        set @statusMsg = N'Mailitem "' + convert(nvarchar(20), @mailItem) + '" '
            + N', was added to the queue for re-sending.'
        RAISERROR(@statusMsg, 10, 1) WITH NOWAIT
    END -- resend success
    ELSE
    BEGIN -- resend failure
         RAISERROR(14627, 16, 1, @rc, 'send mail') WITH LOG
    END -- resend failure
    
    fetch next from curFailedMail into @mailItem

end -- fetch loop

close curFailedMail
deallocate curFailedMail

Check last backups for all databases

 

Been way to long between posts. Have had my head down busy with work and being secretary of the cricket club.

 

Here's a handy script from my tool chest. It shows the database status, last full backup and last log backup.

select

    db.[db_name]
    , databasepropertyex(db.[db_name], 'status') as [db_status]
    , db.[last_backup] as [last_db_backup]
    , lg.[last_backup] as [last_log_backup]
    , getdate() as [current_date]
from ( 
    select
        convert(varchar (50), substring(db .name,1,50)) as [db_name]
        , max (bs.backup_finish_date) as [last_backup]
    from master..sysdatabases db (nolock)
        left join msdb..backupset bs ( nolock) 
        on db .name = bs.database_name
        and bs.type = 'd'
    group by db. name
) as db
left join (
    select
        convert (varchar(50), substring (db.name,1 ,50)) as [db_name]
        , max(bs.backup_finish_date ) as [last_backup]
    from master..sysdatabases db (nolock )
        left join msdb ..backupset bs (nolock) 
        on db.name = bs .database_name
        and bs.type = 'l'
    group by db.name
) as lg
on db.[db_name] = lg.[db_name]

 

Tired of Vista's screen spasms yet ??

For those of you that are tired of your screen going through all sort of spasms when the UAC prompt comes up (for those that still have it on that is). This little trick will turn off the shaded background that causes the problem.

Open "Local Security Policy" from the Administrative Tools

Select "Local Polices" and then "Security Options"

Scroll down to the bottom of the list where the "User Account Control" settings are.

Find the setting titled "User Account Control: Switch to the secure desktop when prompting for elevation" and disable it.

This will leave UAC active so you still get all the prompts, but the gray "secure" background doesn't appear.

Of course many more conscientious users will tell you that you're now susceptible to having a Trojans/worms automatically clicking the "OK" button for you. My take on this is that if you've got a Trojan or worm that's going to do that then your system is already compromised so it doesn't make much difference.

So if your screen has the jitterbugs, give this a go. Before turning off the secure desktop my video driver used to fail on an almost daily basis, that is now a thing of the past.

Super sp_who for SQL 2005

Uses DMVs and shows statement text.

SELECT req.session_id
       , req.blocking_session_id
       , req.cpu_time
       , req.Reads
       , req.writes
       , req.logical_reads
       , sess.login_time
       , conn.last_read
       , conn.last_write
       , sess.host_name
       , conn.client_net_address
       , sess.program_name
       , db_name(req.database_id) As databasename
       , stmt.text As command_text
       , req.status
FROM   sys.dm_exec_requests req
       INNER JOIN sys.dm_exec_connections conn
         On req.session_id = conn.session_id
       INNER JOIN sys.dm_exec_sessions sess
         ON req.session_id = sess.session_id
       CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS STMT
WHERE  req.session_id >= 51

Split function

Another script that I'm forever tracking down.

CREATE FUNCTION [dbo].[fn_Split] (
  @arr AS VARCHAR(MAX)
  , @sep AS CHAR(1)
)
RETURNS TABLE
AS
RETURN

  SELECT n - Len(REPLACE(LEFT(@arr,n),@sep,'')) + 1 AS pos
    ,CAST(Substring(@arr,n,Charindex(@sep,@arr + @sep,n) - n) AS INT) AS Element
  FROM (SELECT @arr AS arr) AS a
    JOIN dbo.nums
     ON n <= Len(@arr)
     AND Substring(@sep + @arr,n,1) = @sep

Script to generate Nums table

Forever tracking this down so I thought I'd post it here so I can get it easily.


-- create table
CREATE TABLE dbo.nums (n INT NOT NULL PRIMARY KEY)
GO
DECLARE @rows INT

SET @rows = 10000
-- prime the table
INSERT INTO dbo.nums VALUES (1)

-- loop around while rows are being inserted
WHILE @@rowcount > 0
BEGIN
  INSERT dbo.nums
  SELECT t.n + x.MaxRowNum   FROM dbo.nums t
    CROSS JOIN (SELECT MAX(n) MaxRowNum FROM dbo.nums) x
  WHERE t.n <= @rows - x.MaxRowNum
END
GO


Handy Virtual PC trick

While setting up PCs for a training course that uses nine virtual machines, I discovered a real handy trick for quickly adding the Virtual Machines to the Virtual PC Console.

Just create shortcuts to the Virtual Machine files in,
C:\Documents and Settings\< your profile name >\Application Data\Microsoft\Virtual PC\Virtual Machines

You may need to alter the folder options in Windows Explorer to view hidden folders. If you’ve got the console open, you’ll need to close and re-open it. When it opens your Virtual Machines are magically added the list.