Get all the queries that executed in the last 10 seconds...

There are sometimes when you wanted to capture the most recent queries that has hit your database but you don't want to run a heavy duty tool like a profiler or whatever.

DMVs can help you here.

As long as the query is in the cache, you can recover the SQL text for that query.

This query will help you for that

select st.text, qs.last_execution_time
 from msdb.sys.dm_exec_query_Stats qs cross apply msdb.sys.dm_exec_sql_text (qs.sql_handle) st
where qs.last_execution_time > dateadd(ss,-10,getdate())
order by qs.last_execution_time

Note:
dateadd(ss,-10,getdate())  -- This means , All of the queries executed in the last 10 seconds. If you want to get everything in the last one minute, change the condition to this dateadd(mi,-1,getdate())

Query to get the size of All databases in an instance

The following query can list the databases (over 1 GB) in an instance along with their size.

select DatabaseName,
       DBSize
From ( Select db_name(mf.database_id) As DatabaseName
      , CONVERT(varchar(30),convert(int, sum(mf.size) * 8 / 1000)/1000) + ' GB' As DBSize
      , cast(getdate() As date) As SizeDate
   From sys.master_files                mf
Where mf.state = 0
Group By
mf.database_id) a
Where DBSize <> '0 GB'

What an opportunity...!!

Thanks Paul for everything you do to the SQL Server community...!!!

Wow.. Here comes an opportunity to be mentored by THE master of SQL Server...!! I sure am not going to miss this one and will try my best to make use of this great opportunity..!!

About me and How got into IT:
I am from a remote village in India where television and electricity was luxury (in 1996)...!! I studied Mechanical Engineering and was not sure what to do after my graduation as I did not have any job offer in hand when I graduated (in 2000). A software consulting company in India gave me a job offer as a trainee in 2001. I accepted it as it was the best opportunity that life gave me until then, even though I did not have any basic idea about computers or software.

Started working as a tester in mainframes where my responsibility was only to execute the commands given in the documentation and record the results. There were some people in the company who had some computer knowledge. They got opportunity to code in Java or .Net. I used to envy them and felt bad that I am not skilled enough to be considered for those kind of projects. But, I always believed that with proper guidance I can achieve greater heights and be a king of what I am doing. I did not know what I should do to learn more and equip myself to be competent. One good thing that happened to me was that I got an opportunity to come to the US (2003) and work in the client site.

How I got into SQL Server
A few years of doing mainframes testing, I got another opportunity through a friend of mine as he wanted someone to replace his role in his company in (2008) as he was moving on to the next role.  Fortunately it was a Sybase DBA position.

I approached their management and told them I do not have any Sybase or DBA skills. but if they give me an opportunity to learn, I would show them my potential in 2 months. As a trade off I was offered less than what I was making in the previous company. I accepted it as I felt at last I found some light and I can be a DBA soon. They recognized my hardwork after few months by giving me a hike and trusted me to handle their production databases.

Two years later (in 2010), the company was making transition from Sybase to SQL Server and I was the one to implement the migration.  So finally I arrived at the technology that I would learn to make my living...!!!

In SQL Server so far
After working in SQL Server for few months, I felt this product is very user friendly and I should learn more about it. When I searched online on how to improve my SQL Server skills, the first thing that I found was to join the local SQL Server User group and start talking to people about what you do and vice versa, I joined the SQL Server user group and started attending SQL Saturdays and so on... Started writing Blogs (in 2012) as a repository of what I learn..!!

What I have done so far to improve my SQL Server knowledge
1. Attend SQL Server user group meetings.
2. Attend SQL Saturdays
3. Attended Dev connections (in 2012 March)
4. Follow SQL Server experts like you, Kimberly, Brent etc.
5. Follow discussions in SQLServerCentral.com

My Pain points / Obstacles in my learning

1. I am overwhelmed by the volume and variety of things available to learn in front of me.  I am not sure what to take and what to omit.

2. How to retain my knowledge. What I learned today is not available to recollect after a month or so.

3. What are my next steps in my career? Where do I go next..?


My request to  Paul

Before everything,  Thanks so much for your time to read my blog until now...!

Thanks again for your great service to the SQL Server community...!!!

I look up to you as a great person who has mastered the technology that helps me bring food to my family. I would be honored if I get an opportunity to hold your hand and walk in the SQL Server land for few steps.

Thanks again...!!

Getting the Size of the Full Backups in an Instance

If you are planning for a restore of all the database in an instance to another instance, you might be interested in knowing the size of all the backups that you might have to restore.

The following query will help you get the size of all the full backups (if available) in the instance.

select Database_Name, 

       backup_start_date BackupStartDate , 

       convert(numeric(10,2),(compressed_backup_size/1000000)) 'BackupSizeIn MB',

       bmf.physical_device_name

from   msdb..backupset bs inner join 

       msdb..backupmediafamily bmf on bs.media_set_id = bmf.media_set_id

where  type = 'D'

and    backup_start_date = (select  max(backup_start_date) 

                            from    msdb..backupset bs1 

                            where   bs1.type = 'D' 

                            and     bs1.database_name = bs.database_name)

order by convert(numeric(10,2),(compressed_backup_size/1000000)) desc

Estimated Completion Time of SQL Queries (BACKUP/RESTORE)

When you restore or backup a huge database, you might be anxious to know the estimated completion time of that command.

There is a "STATS" parameter that you can use with the command, but it is not dynamic.

The following query can help you in such scenario.

select dr.session_id,      

       dr.command,

       percent_complete,

       dr.start_time,

       dateadd(mi,estimated_completion_time/60000,getdate()) EstimatedCompletionTime,

       ds.text

from  sys.dm_exec_requests dr cross apply sys.dm_exec_sql_text(dr.sql_handle) as ds

where estimated_completion_time > 0

Note that, only following SQL Commands have the percentage value populated. 

·         ALTER INDEX REORGANIZE

·         SHRINK

·         BACKUP DATABASE

·         DBCC COMMANDS

·         RECOVERY

·         RESTORE DATABASE,

·         ROLLBACK

·         TDE ENCRYPTION

Report of All Databases, File Size and Space available.

If you want to get a list of all the databases and their file sizes and available free space, you can use the following script. 

You can also put a wrapper and make it a stored procedure. 

List of all backups from the last full backup

What if one of your database is corrupted and you need to restore all the backups one by one from your last full.

It will be a time consuming and confusing task to get all the backups in order to be restored so as to not break the log chain.

The following script will help you in getting the list of all backups since the last full backup for all the databases in your instance.

It assumes there was atleast one full backup in the last 30 days...if not, you have a bigger problem.

/*

  Author: Siva Ramasamy

 

  Date  : 09/18/2014

  Description: This procedure will return the list of backup files in order from the last fullbackup for the DBs for all the databases.

  Assumption:   This procedure assumes that there was atleast one full backup in the last 30 days. If not :-) you have a bigger problem.

*/

begin

declare @db_name varchar(100)

declare @backup_start_date datetime

declare @most_recent_diffbackup_date datetime

declare @sqlcmd varchar(1000)

--drop table #temp

select bps.database_name     DatabaseName,

    'Full'                BackupType,

    CAST(CAST(bps.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Size,

    bps.backup_start_date BackupStartDate,

    bps.backup_finish_date BackupFinishDate,

    CAST (DATEDIFF(MM,BPS.backup_start_date,bps.backup_finish_date) AS VARCHAR(10)) + ' Mins' DurationOfBackup,

       bmf.physical_device_name BackupFileName,

       ROW_NUMBER() OVER(PARTITION BY bps.database_name ORDER BY bps.Backup_Start_Date Desc) BackupOrder

into   #temp

from msdb..backupset bps inner join

  msdb..backupmediafamily bmf on bps.media_set_id = bmf.media_set_id

where bps.type = 'D' --  use the Full Backups as the starting point

and   bps.Is_Copy_Only = '0'   -- no copy only backups since we need to load logs as well.

and bps.backup_start_date > DATEADD (DD,-30,getdate()) -- assuming there was atleast 1 good full backup in the last 30 days

delete from #temp where BackupType = 'Full' and  BackupOrder > 1

--select * from #temp

-- Add and Update the Flag for Verification

Alter table #temp add Processed char(1)

Update #temp set Processed = 'N'

While (select count(1) from #temp where backuptype = 'Full' and  Processed = 'N') > 0

begin

  select top (1) @db_name = databasename, @backup_start_date = BackupStartDate from #temp where processed = 'N'

  --print @db_name

 --print @backup_start_date

 insert into #temp (DatabaseName, BackupType, Size, BackupStartDate, BackupFinishDate, DurationOfBackup, BackupFileName,BackupOrder,Processed)

 select  bps.database_name     DatabaseName,

       'Diff'                BackupType,

      CAST(CAST(bps.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Size,

      bps.backup_start_date BackupStartDate,

      bps.backup_finish_date BackupFinishDate,

      CAST (DATEDIFF(MM,BPS.backup_start_date,bps.backup_finish_date) AS VARCHAR(10)) + ' Mins' DurationOfBackup,

      bmf.physical_device_name BackupFileName,

   ROW_NUMBER() OVER(PARTITION BY bps.database_name ORDER BY bps.Backup_Start_Date Desc) BackupOrder,

   'P'

    from    msdb..backupset bps inner join

         msdb..backupmediafamily bmf on bps.media_set_id = bmf.media_set_id

    where   bps.type = 'I' --  Pull all the differential backups after the full backup

    and     bps.Is_Copy_Only = '0'   -- no copy only backups since we need to load logs as well.

    and     bps.backup_start_date > @backup_start_date

 and     bps.database_name  = @db_name

  delete from #temp where BackupType = 'Diff' and  BackupOrder > 1 and DatabaseName = @db_name

    --get all the log backups after most recent full or differential backup

    insert into #temp (DatabaseName, BackupType, Size, BackupStartDate, BackupFinishDate, DurationOfBackup, BackupFileName, BackupOrder,Processed)

 select  bps.database_name     DatabaseName,

       'Log'                BackupType,

   CAST(CAST(bps.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Size,

   bps.backup_start_date BackupStartDate,

   bps.backup_finish_date BackupFinishDate,

      CAST (DATEDIFF(MM,BPS.backup_start_date,bps.backup_finish_date) AS VARCHAR(10)) + ' Mins' DurationOfBackup,

      bmf.physical_device_name BackupFileName,

   ROW_NUMBER() OVER(PARTITION BY bps.database_name ORDER BY bps.Backup_Start_Date) BackupOrder,

   'P'

 from    msdb..backupset bps inner join

   msdb..backupmediafamily bmf on bps.media_set_id = bmf.media_set_id

 where   bps.type = 'L' --  Pull all the Log Backups after the most recent Differential Backup

 and     bps.Is_Copy_Only = '0'   -- no copy only backups since we need to load logs as well.

 and     bps.backup_start_date > (select max(BackupStartDate) from #temp where DatabaseName = @db_name)

 and     bps.database_name  = @db_name

 order by backup_start_date

  Update #temp set Processed = 'P' where databasename = @db_name

end

select * from #temp order by databasename, backupstartdate

end