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