This script shows you what's happening behind the scenes of TempDB system database.

It priovides the following information:

Database Name, SQL Statement, Start_OffSet, Execution Count, Total Physical Reads, Total Logical Writes, etc.

Very helpful when troubleshooting queries

There are times when DBA will need to find the information about disk drives from within SQL Server. It is easy to just open up Windows Explorer and look at my computer to get the drive space information but getting that same information from SQL Server is little challenging. This script will help you obtain drive space information (snapshot of My Computer) from SQL Server.

When executed, the output of this script will consists of:
1) Drive Letter
2) Total Size in MB
3) Used Space in MB
4) Free Space in MB
5) Total Percent of Free disk space
6) Total percent of disk being used
7) Total space occupied by SQL Server on that drive
8) Current date and time when this information was generated (Good for maintaining history)

Note: If you are using this script on SQL Server 2005 or SQL Server 2008, you must enable "OLE Automation" feature

There are times when you would want to know how your databases are configured and setup across various drives. Imagine if you are having to rebuild the servers and you are restoring a file backup of all SQL Server data/log files and if you don't know where each of the data or log files were residing then that could cause the databases to go into suspect mode or will not start the database. In situations like that, it's always a good idea to generate a report of your database configuration and store it in a safe place should the server crash and you have to rebuild SQL Server from the backup files.

SQL_DBReport script allows you to view filegroup related information for any or all databases.

It returns the following information:
1) Database Name
2) Logical names of all the files (Data & Log (mdf, ldf & ndf))
3) Size of each files in mega bytes (MB)
4) Drive letter on which each of the files are residing
5) Type of file/filegroup (Primary, Secondary, Log, etc)
6) Location (full path) of each of those files

Syntax: SQL_DBReport <@DatabaseName>
Example: To view DB report for a specific database (ex: OpsDB database)
Exec SQL_DBReport 'OpsDB'

Example: To view DB report for all the databases residing on the local SQL Server
Exec SQL_DBReport '*'

Example: To obtain help
Exec SQL_DBReport 'HELP'

This script will uncover the details of your database backups.

It provides you with the following information:

• Database Name
• Backup Type
• Uncompressed size in MB (You can also get compressed size by slightly changing this procedure)
• Last Backup Date when the backup was performed on the database
• Duration (How much time it took to perform backup on this database)
• Megs per minute (Shows you how many mega bytes did it backup per minute)
• Backup file location (Location where the backup files are residing)

There are times when you will need to find out if you should simply defrag an index or rebuild an index.This script will help you make the right decision based on the volume of data fragmentation for your tables. This script allows you to obtain fragmentation information from tables and indexes of your local SQL Server.'

It also provides you with recommendation based on the amount of fragmentation detected in tables/indexes. It also has a feature that lets you generate a script for rebuilding or re-organizing all the indexes.

This script has been used for many properties across Microsoft and has been extremely helpful. Give it a try today...

This script allows you to quickly get the facts on how much percentage of fragmentation you have in tables/indexes for a single database or for all the databases. This tool even takes away the pain of reading the cryptic values returned by DMV's to identify the amount of fragmentation you have on your SQL Server system. Not just that but it even tells you what action you can take to fix the problem.