T-SQL to get Get Database Files Usage information

Here is an handy T-SQL script to get Database files usage Information

DECLARE @FreeSpaceThreshold int
SET @FreeSpaceThreshold = 20 -- Change this Threshold Value. Any number between 0 to 100

CREATE TABLE #GetDetails
( 
 ServerName nvarchar(100),
 DatabaseName nvarchar(100),
 LogicalFileName nvarchar(100),
 FileType nvarchar(10),
 FileLocation nvarchar(max),
 [FileSize(MB)] decimal (15,2),
 [UsedSpace(MB)] decimal (15,2),
 [FreeSpace(MB)] decimal (15,2),
 [UsedSpace(%)] decimal (15,2),
 [FreeSpace(%)] decimal (15,2)
 )

EXEC sp_MSforeachdb 'Use [?];
INSERT INTO #GetDetails
SELECT @@SERVERNAME AS ServerName,
    DB_NAME() AS DatabaseName,
    name AS LogicalFileName, 
    type_desc AS FileType, 
    physical_name AS FileLocation,
    CAST(size/128.0 AS decimal(15,2)) AS [FileSize(MB)],
    CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS decimal(15,2)) AS [UsedSpace(MB)],
    CAST(size/128.0 AS decimal(15,2)) - CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS decimal(15,2)) AS [FreeSpace(MB)],
    CAST(((FILEPROPERTY(name, ''SpaceUsed'')/128.0 ) /  (size/128.0))*100 AS decimal(15,2)) AS [UsedSpace(%)],
    CAST((((size/128.0) - FILEPROPERTY(name, ''SpaceUsed'')/128.0 ) /  (size/128.0))*100 AS decimal(15,2)) AS [FreeSpace(%)]
FROM sys.database_files'

SELECT * FROM #GetDetails Where [FreeSpace(%)] < @FreeSpaceThreshold
DROP TABLE #GetDetails

Download this script from https://gallery.technet.microsoft.com/T-SQL-to-Get-Database-f16f60c7?redir=0

Power BI Architecture for Enterprises

T-SQL script to get Backup Details of a Database and the Sequence it was taken

Here is an handy script which give the details of the list of backup(s) taken of a database and the sequence it was taken.

DECLARE @db_name VARCHAR(100)
SELECT @db_name = '<DB Name>'

SELECT 
 BS.server_name AS [Server Name]
,BS.database_name AS [Database Name]
,BS.recovery_model AS [Recovery Model]
,BMF.physical_device_name [Location]
,(CAST(BS.backup_size / 1000000 AS INT)) AS [Size of Backup (MB)]
,CASE BS.[type] WHEN 'D' THEN 'Full'
  WHEN 'I' THEN 'Differential'
  WHEN 'L' THEN 'Transaction Log'
  END AS [Type of Backup]
,BS.backup_start_date AS [Backup Date]
,BS.first_lsn AS [First LSN]
,BS.last_lsn AS [Last LSN]
FROM msdb.dbo.backupset BS
INNER JOIN msdb.dbo.backupmediafamily BMF ON BS.media_set_id = BMF.media_set_id
WHERE BS.database_name = @db_name
ORDER BY backup_start_date DESC , backup_finish_date

Download the script from https://gallery.technet.microsoft.com/T-SQL-script-to-get-Backup-5a9b029b?redir=0

Kernel for SQL Database - Product Review - A guest Post by Andre Williams

Most of the organizations that handle large amount of data is very much dependent on MS SQL Server for their operations. However, an MS SQL database corruption, can cause a lot of troubles to the organization owing to the criticality of the data contained in the database. Fortunately, many SQL Server recovery tools are available in the market. We will try to understand the features, working, and benefits & drawbacks of a popular, SQL recovery tool—the Kernel for SQL Database.

Product information

• Name - Kernel for SQL Database
• Description – MDF file recovery software (recovers SQL data from corrupt databases)
• Developers – Lepide Software

Company information 

Lepide Software, the developers of Kernel range of products, is known for sophisticated IT security, management, and recovery solutions. Its Kernel range, designed and developed for small and mid-sized organizations offers cost-effective solutions for most of the day-to-day issues faced by IT organizations. These products help organizations in email migration, email recovery, data recovery, file repair, and more. The company has a loyal customer-base spanning across the world owing to its quality products and dedicated customer support.

Notable features of the software

• Automatically detects the SQL version of the database
• Fixes almost all types of SQL corruption issues
• Recovers all type of SQL data—tables, triggers, views, stored procedures, constraints, indexes, rules, and user defined functions/data types
• Works even with large SQL databases
• Recovers data even when the SQL Server is live
• Supports MS SQL 2000, 2005 and 2008, 2008R2 and 2012 versions

Kernel for SQL Database - versions available

• Demo version
  The demo version, meant educate users on SQL recovery process, is completely free. But it is not equipped with the saving facility.
• Licensed version
  Licensed version is the fully featured version of the software and is available on purchase only. It can save all the recovered data.

How to use Kernel for SQL Database to repair corrupt MDF files?

The hallmark of Kernel for SQL Database is the simplicity of its operation. The steps are simple, the interface is intuitive, and recovery is quick. The major steps of the SQL database recovery process are outlined here:

1. Selecting MDF file for recovery
   The Open button (in the main page of the software) opens the Select SQL Database dialogue box. In this dialogue box, one can browse for the corrupt database. If users are sure about the SQL server version, the can select the version. Otherwise, they can use the auto detect option. Finally, the recovery process can be initiated by clicking the Recover button.
   
2. Previewing the recovered data
   Once the software displays the recovered objects, the users can click an object on the left tree to preview its data.
   
3. Saving the recovered data
   Two saving options are available—saving to SQL Server and saving to batch file. The first option allows to save directly to an SQL Server either through server authentication or through Windows authentication. In the Batch File option, the data is stored in script files in the desired location (which can be copied to a new database).
   

Benefits and drawbacks of Kernel for SQL Database

Benefits:

The important benefits of Kernel for SQL Database are:

• Friendly interface
  The software interface itself provides information on how to execute the recovery process. So even non-technical users can perform recovery without any help. 
• Preview facility
  Everything recovered can be viewed on the software’s preview pane before saving. Previewing helps users to know about the recoverability of the data.
• Automatic detection of SQL Version
  If the users know the version of the SQL, they can select it. But if they do not know, the software can automatically detect the SQL version.
• Multiple saving options
  The software saves the recovered MDF file files to SQL Server directly. In addition, it can save files in batch file, which users can copy to a new database when required.

Drawbacks:

The major drawback of Kernel for SQL Database is:

• No saving option in the trial version
  The trial version of Kernel for SQL Database cannot save the data. With this version, users can only preview the data. 

Final verdict

Kernel for SQL Database is a great tool for SQL recovery. As we understand, users are happy with the product. Still the product can be improved in some areas. Also, it is good if Lepide can add some limited saving facility to the trial version. https://www.nucleustechnologies.com/sql-recovery.html 

Performance rating

Overall rating for the software is 4/5

About Andre Williams

Andre Williams, with more than three year's experience in SQL related technologies, contributes articles, blogs, and how-to tips regularly.

Social Media Profiles

Facebook: https://www.facebook.com/andre.williams1986

LinkedIn: https://www.linkedin.com/in/andrewilliams594

Using DBCC CLONEDATABASE to generate a schema and statistics only copy of a user database in SQL Server 2014 SP2

DBCC CLONEDATABASE is a new DBCC command introduced in SQL Server 2014 SP 2 which is used for creating the clone of a specified user database which helps in troubleshooting the performance issues related to the query optimizer.

When a clone of the database is created using DBCC CLONEDATABASE, it will create a schema and statistics only copy of the specified database and does not contain any copy of the data.

Creating the clone is as simple as passing the source database name and clone database name to the DBCC command.

DBCC CLONEDATABASE ('SansSQL', 'SansSQL_Clone') 

Output of the DBCC Command

Once the cloning is completed, the cloned database will be in Read-Only mode.

SELECT name, database_id, is_read_only  
FROM sys.databases 
WHERE name in ('SansSQL', 'SansSQL_Clone') 

So what actually happens when we issue the DBCC CLONEDATABASE command on a database?
It will start with few validations before the clone is created, The following validations are performed by DBCC CLONEDATABASE. The command fails if any of the validations fail.

• The source database must be a user database. Cloning of system databases (master, model, msdb, tempdb, distribution database etc.) isn't allowed.
• The source database must be online or readable.
• A database that uses the same name as the clone database must not already exist.
• The command isn't in a user transaction.

If all the validations succeed, DBCC CLONEDATABASE will do the following operations:

• Creating primary data file and log file
• Adding secondary dataspaces
• Adding secondary files

The destination database files will inherit the size and growth settings from the model database and the file names of the destination database will follow the source_file_name _underscore_random_number convention. 

SELECT database_id, file_id, type_desc, name, physical_name 
FROM sys.master_files 
WHERE DB_NAME(database_id) in ('SansSQL', 'SansSQL_Clone') 

Then the DBCC CLONEDATABASE will do a Internal Database Snapshot with the following steps

• Validate the source database
• Get S lock for the source database
• Create snapshot of the source database
• Create a clone database (this is an empty database which inherits from model)
• Get X lock for the clone database
• Copy the metadata to the clone database
• Release all DB locks

Using the below command, we can check if a database is a clone or a normal database.

SELECT DATABASEPROPERTYEX('SansSQL','isClone') AS SansSQL_DB_CloneStatus 
      ,DATABASEPROPERTYEX('SansSQL_Clone','isClone') AS SansSQL_CloneDB_CloneStatus

Reference: https://support.microsoft.com/en-in/kb/3177838