Get SQL Server Database details using T-SQL

Here is a T-SQL script which gives you the details of all the databases in an SQL Server Instance.
This will be very useful when you are gathering SQL Server information from multiple servers

SET NOCOUNT ON
IF OBJECT_ID('tempdb..#DatabaseDetails') IS NOT NULL DROP TABLE #DatabaseDetails
CREATE TABLE #DatabaseDetails (
  DatabaseID int
, DatabaseName varchar(256)
, CreateDate datetime
, Collation varchar(256)
, ComparisonStyle int
, IsAnsiNullDefault bit
, IsAnsiNullsEnabled bit
, IsAnsiPaddingEnabled bit
, IsAnsiWarningsEnabled bit
, IsArithmeticAbortEnabled bit
, IsAutoClose bit
, IsAutoCreateStatistics bit
, IsAutoShrink bit
, IsAutoUpdateStatistics bit
, IsCloseCursorsOnCommitEnabled bit
, IsFulltextEnabled bit
, [IsInStandBy] bit
, IsLocalCursorsDefault bit
, IsMergePublished bit
, IsMergeSubscribed bit
, IsNullConcat bit
, IsNumericRoundAbortEnabled bit
, IsParameterizationForced bit
, [IsQuotedIdentifiersEnabled] bit
, IsPublished bit
, IsRecursiveTriggersEnabled bit
, IsSubscribed bit
, IsSyncWithBackup bit
, IsTornPageDetectionEnabled bit
, LCID int
, [Recovery] varchar(256)
, [SQLSortOrder] tinyint
, [Status] varchar(256)
, Updateability varchar(256)
, UserAccess varchar(256)
, [Version] int
, LastDatabaseBackup datetime
, LastIncremetalBackup datetime
, LastLogBackup datetime
, TotalLogSize bigint
, LogPercentUsed int
, [TotalDBSize_MB] bigint
, [cmptlevel] int
)

INSERT INTO #DatabaseDetails(
  DatabaseID
, [DatabaseName]
, [CreateDate]
, [Collation]
, [ComparisonStyle]
, [IsAnsiNullDefault]
, [IsAnsiNullsEnabled]
, [IsAnsiPaddingEnabled]
, [IsAnsiWarningsEnabled]
, [IsArithmeticAbortEnabled]
, [IsAutoClose]
, [IsAutoCreateStatistics]
, [IsAutoShrink]
, [IsAutoUpdateStatistics]
, [IsCloseCursorsOnCommitEnabled]
, [IsFulltextEnabled]
, [IsInStandBy]
, [IsLocalCursorsDefault]
, [IsMergePublished]
, [IsMergeSubscribed]
, [IsNullConcat]
, [IsNumericRoundAbortEnabled]
, [IsParameterizationForced]
, [IsQuotedIdentifiersEnabled]
, [IsPublished]
, [IsRecursiveTriggersEnabled]
, [IsSubscribed]
, [IsSyncWithBackup]
, [IsTornPageDetectionEnabled]
, [LCID]
, [Recovery]
, [SQLSortOrder]
, [Status]
, [Updateability]
, [UserAccess]
, [Version]
, [cmptlevel])
SELECT sd.dbid as 'DatabaseID'
 , sd.[name] as 'DatabaseName'
 , sd.crdate as 'CreateDate'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Collation') as varchar(256)) as 'Collation'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'ComparisonStyle') as varchar(256)) as 'ComparisonStyle'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiNullDefault') as bit) as 'IsAnsiNullDefault'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiNullsEnabled') as bit) as 'IsAnsiNullsEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiPaddingEnabled') as bit) as 'IsAnsiPaddingEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiWarningsEnabled') as bit) as 'IsAnsiWarningsEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsArithmeticAbortEnabled') as bit) as 'IsArithmeticAbortEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoClose') as bit) as 'IsAutoClose'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoCreateStatistics') as bit) as 'IsAutoCreateStatistics'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoShrink') as bit) as 'IsAutoShrink'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoUpdateStatistics') as bit) as 'IsAutoUpdateStatistics'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsCloseCursorsOnCommitEnabled') as bit) as 'IsCloseCursorsOnCommitEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsFulltextEnabled') as bit) as 'IsFulltextEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsInStandBy') as bit) as 'IsInStandBy'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsLocalCursorsDefault') as bit) as 'IsLocalCursorsDefault'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsMergePublished') as bit) as 'IsMergePublished'
 , CASE WHEN sd.category & 8 = 8 THEN 1 ELSE 0 end as 'IsMergeSubscribed'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsNullConcat') as bit) as 'IsNullConcat'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsNumericRoundAbortEnabled') as bit) as 'IsNumericRoundAbortEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsParameterizationForced') as bit) as 'IsParameterizationForced'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsQuotedIdentifiersEnabled') as bit) as 'IsQuotedIdentifiersEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsPublished') as bit) as 'IsPublished'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsRecursiveTriggersEnabled') as bit) as 'IsRecursiveTriggersEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsSubscribed') as bit) as 'IsSubscribed'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsSyncWithBackup') as bit) as 'IsSyncWithBackup'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsTornPageDetectionEnabled') as bit) as 'IsTornPageDetectionEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'LCID') as int) as 'LCID'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Recovery') as varchar(256)) as 'Recovery'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'SQLSortOrder') as tinyint) as 'SQLSortOrder'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Status') as varchar(256)) as 'Status'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Updateability') as varchar(256)) as 'Updateability'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'UserAccess') as varchar(256)) as 'UserAccess'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Version') as int) as 'Version'
 , cmptlevel
FROM master.dbo.sysdatabases sd 
ORDER BY sd.dbid



UPDATE dbd
SET   LastDatabaseBackup = fullbak.LastDatabaseBackup
 , LastIncremetalBackup = incbak.LastIncremetalBackup
 , LastLogBackup = logbak.LastLogBackup
FROM #DatabaseDetails dbd
left join (
 SELECT sd.dbid
   , sd.[name] as 'DatabaseName'
   , max(t1.backup_finish_date) as 'LastDatabaseBackup'
 FROM master.dbo.sysdatabases sd 
 join msdb.dbo.backupset t1  on t1.type = 'D' and t1.database_name = sd.[name]
 GROUP BY sd.dbid, sd.[name]
) fullbak ON fullbak.dbid = dbd.DatabaseID
left join (
 SELECT sd.dbid
   , sd.[name] as 'DatabaseName'
   , max(t2.backup_finish_date) as 'LastIncremetalBackup'
 FROM master.dbo.sysdatabases sd 
 join msdb.dbo.backupset t2  on t2.type = 'I' and t2.database_name = sd.[name]
 GROUP BY sd.dbid, sd.[name]
) incbak on incbak.dbid = dbd.DatabaseID
left join (
 SELECT sd.dbid
   , sd.[name] as 'DatabaseName'
   , max(t3.backup_finish_date) as 'LastLogBackup'
 FROM master.dbo.sysdatabases sd 
 join msdb.dbo.backupset t3  on t3.type = 'L' and t3.database_name = sd.[name]
 GROUP BY sd.dbid, sd.[name]
) logbak on logbak.dbid = dbd.DatabaseID


IF OBJECT_ID('tempdb..#logspace') IS NOT NULL DROP TABLE #logspace
CREATE TABLE #logspace(
   DatabaseName varchar(256)
 , TotalLogSize decimal(20,4)
 , PercentUsed decimal(20,4)
 , [Status] varchar(50)
)
INSERT INTO #logspace
EXEC('DBCC sqlperf(logspace)')

UPDATE dbd
SET   TotalLogSize = ls.TotalLogSize
 , LogPercentUsed = Convert(int, ls.PercentUsed)
FROM #DatabaseDetails dbd
join #logspace ls ON dbd.DatabaseID = db_id(ls.DatabaseName)

EXEC master.dbo.sp_MSForEachDB 'update #DatabaseDetails
set TotalDBSize_MB = (select (sum([size]) * 8.0) / 1024.0 
FROM [?].[dbo].[sysfiles] )
where DatabaseID = db_id(''?'')'

SELECT DatabaseID
, DatabaseName
, [TotalDBSize_MB]
, TotalLogSize
, LogPercentUsed
, CreateDate
, [Status]
, LastDatabaseBackup
, LastIncremetalBackup
, LastLogBackup
, [Recovery]
, [Updateability]
, [UserAccess]
, [Collation]
, [ComparisonStyle]
, [LCID]
, [SQLSortOrder]
, [Version]
, [cmptlevel]
, [IsAutoUpdateStatistics]
, [IsAutoCreateStatistics]
, [IsInStandBy]
, [IsAutoShrink]
, [IsNullConcat]
, [IsFulltextEnabled]
, [IsPublished]
, [IsSubscribed]
, [IsMergePublished]
, [IsMergeSubscribed]
, [IsAnsiNullDefault]
, [IsAnsiNullsEnabled]
, [IsAnsiPaddingEnabled]
, [IsAnsiWarningsEnabled]
, [IsArithmeticAbortEnabled]
, [IsAutoClose]
, [IsCloseCursorsOnCommitEnabled]
, [IsLocalCursorsDefault]
, [IsNumericRoundAbortEnabled]
, [IsParameterizationForced]
, [IsQuotedIdentifiersEnabled]
, [IsRecursiveTriggersEnabled]
, [IsSyncWithBackup]
, [IsTornPageDetectionEnabled]
FROM #DatabaseDetails
ORDER BY DatabaseName
SET NOCOUNT OFF

Create Database fails with the error "Could not obtain exclusive lock on database 'model'. Retry the operation later."

Sometimes when you try to create a database, the operation fails by presenting the below error message.

TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for Database 'Test'.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not obtain exclusive lock on database 'model'. Retry the operation later.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 1807)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=1807&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

This happens because, the exclusive lock on the model database is a mandatory step that database engine takes to create a new database. We all know that when SQL Server creates a new database, it uses a copy of the model database to initialize the new database and its metadata. Also, users could create, modify, drop objects in the Model database. So, it is important to exclusively lock the model database to prevent copying the data in change from the Model database. Otherwise, there is no guarantee that the content copied from the Model database are consistent and valid.

To fix this issue, find out the connection that is using model database and close it and then re-issue the create database statement.

SELECT * FROM sys.sysprocesses WHERE DB_NAME(dbid)='model'

"RESTORE DATABASE is terminating abnormally" error message when you try to restore a full backup of a database taken in SQL Server 2008 R2

Consider a scenario in which you have a

1. Created a database in SQL Server 2008 R2

   CREATE DATABASE [TestRestore]
   

2. Changed the Logical Names of the database files

   USE master
   GO
   ALTER DATABASE TestRestore 
    MODIFY FILE ( NAME = 'TestRestore', NEWNAME = 'TestRestore_Data')
   GO
   ALTER DATABASE TestRestore 
    MODIFY FILE ( NAME = 'TestRestore_log', NEWNAME = 'TestRestore_logFile')
   

3. Check the Logical File names
   

   SELECT * FROM sys.master_files WHERE DB_NAME(database_id)='TestRestore'
   

4. Perform the full back of the databases
   

   BACKUP DATABASE TestRestore TO DISK = 'D:\Backup\TestRestore_FullBackup.bak' WITH STATS = 10, INIT
   

5. Now try to restore the database using the full backup taken in Step 4
   

   RESTORE DATABASE [TestRestore_Restored] FROM  DISK = N'D:\Backup\TestRestore_FullBackup.bak' 
   WITH  FILE = 1,  
   MOVE N'TestRestore_Data' TO N'D:\Databases\TestRestore_Restored.mdf',  
   MOVE N'TestRestore_logFile' TO N'D:\Databases\TestRestore_Restored_1.ldf',  
   NOUNLOAD,  STATS = 10
   GO

   And you get the error

   Msg 3234, Level 16, State 2, Line 1  Logical file 'TestRestore_Data' is not part of database 'TestRestore_Restored'. Use RESTORE FILELISTONLY to list the logical file names.  Msg 3013, Level 16, State 1, Line 1  RESTORE DATABASE is terminating abnormally.

This is a known issue and is because, the logical name of the database, after the update is corrupted in the backup file. If you run the  RESTORE FILELISTONLY on the backup file, you will notice that the last character of the logical file name is truncated.

RESTORE FILELISTONLY FROM  DISK = N'D:\Backup\TestRestore_FullBackup.bak' 

Workaround

To work around this issue, use either one of the below method

• After the logical file name is modified, take the database offline and then back to online.
  

  ALTER DATABASE TestRestore SET OFFLINE
  GO
  ALTER DATABASE TestRestore SET ONLINE
  GO

• While modifying the logical name, append a white space at the end of the new file name, for example

  USE master
  GO
  ALTER DATABASE TestRestore 
   MODIFY FILE ( NAME = 'TestRestore', NEWNAME = 'TestRestore_Data ')
  GO
  ALTER DATABASE TestRestore 
   MODIFY FILE ( NAME = 'TestRestore_log', NEWNAME = 'TestRestore_logFile ')
  

Resolution
The fix for this issue was release in Cumulative Update 6 for SQL Server 2008 R2.
To resolve this issue permanently, apply the Cumulative Update 6 or the most recent update for SQL Server 2008 R2

T-SQL Query to find Drive space details

Here is a T-SQL Query to find Drive space details.
This query returns Total drive space, Used space on the drive, Free space on the drive along with the free space percentage.

This requires, enabling xp_cmdshell

Enable xp_cmdshell:

EXEC sp_configure 'show advanced options' , 1
GO
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell' , 1
GO
RECONFIGURE
GO

Once xp_cmdshell is enabled, executing the below script will provide the details about the drives of the server that hosts the connected SQL Server Instance

 DECLARE @MOUNTVOL TABLE
 ( MOUNTVOLResult nVARCHAR(500)
  ,ExecCommand nVARCHAR(500))
 
INSERT INTO @MOUNTVOL (MOUNTVOLResult) 
EXEC XP_CMDSHELL 'MOUNTVOL'
 
DELETE @MOUNTVOL WHERE MOUNTVOLResult LIKE '%VOLUME%'
DELETE @MOUNTVOL WHERE MOUNTVOLResult IS NULL
DELETE @MOUNTVOL WHERE MOUNTVOLResult NOT LIKE '%:%'
DELETE @MOUNTVOL WHERE MOUNTVOLResult LIKE '%MOUNTVOL%'
DELETE @MOUNTVOL WHERE MOUNTVOLResult LIKE '%RECYCLE%'
 
UPDATE @MOUNTVOL SET ExecCommand = 'EXEC XP_CMDSHELL ''FSUTIL VOLUME DISKFREE ' + LTRIM(RTRIM(MOUNTVOLResult)) +''''
 
DECLARE @DRIVESpace TABLE
 ( DriveLetter VARCHAR(10)
  ,DriveInfo VARCHAR(100))
   
WHILE (SELECT COUNT(*) FROM @MOUNTVOL) <>0
BEGIN
 DECLARE @Command nVARCHAR(500), @DriveLetter nVARCHAR(10)
 Select @Command = ExecCommand, @DriveLetter= MOUNTVOLResult from @MOUNTVOL
 INSERT INTO @DRIVESpace (DriveInfo) Exec sp_executeSQL @Command
 UPDATE @DRIVESpace SET DriveLetter=@DriveLetter WHERE DriveLetter IS NULL
 DELETE FROM @MOUNTVOL WHERE ExecCommand=@Command
END
 
DECLARE @FinalResults TABLE
 ( DriveLetter nVARCHAR(10)
  ,[TotalDriveSpace(MB)] DECIMAL(18,2)
  ,[UsedSpaceOnDrive(MB)] AS ([TotalDriveSpace(MB)] - [FreeSpaceOnDrive(MB)])
  ,[FreeSpaceOnDrive(MB)] DECIMAL(18,2)
  ,[TotalDriveSpace(GB)] AS CAST(([TotalDriveSpace(MB)]/1024) AS DECIMAL(18,2))
  ,[UsedSpaceOnDrive(GB)] AS CAST((([TotalDriveSpace(MB)] - [FreeSpaceOnDrive(MB)])/1024) AS DECIMAL(18,2))
  ,[FreeSpaceOnDrive(GB)] AS CAST(([FreeSpaceOnDrive(MB)]/1024) AS DECIMAL(18,2))
  ,[%FreeSpace] AS CAST((([FreeSpaceOnDrive(MB)]/[TotalDriveSpace(MB)])*100) AS DECIMAL(18,2)))
 
INSERT INTO @FinalResults (DriveLetter, [TotalDriveSpace(MB)],[FreeSpaceOnDrive(MB)])
SELECT RTRIM(LTRIM(DriveLetter))
    ,[TotalDriveSpace(MB)] = SUM(CASE WHEN DriveInfo LIKE 'TOTAL # OF BYTES%' THEN CAST(SUBSTRING(DriveInfo, 32, 48) AS FLOAT) ELSE CAST(0 AS FLOAT) END)/1024/1024
    ,[FreeSpaceOnDrive(MB)] = SUM(CASE WHEN DriveInfo LIKE 'TOTAL # OF FREE BYTES%' THEN CAST(SUBSTRING(DriveInfo, 32, 48) AS FLOAT) ELSE CAST(0 AS FLOAT) END)/1024/1024
FROM @DRIVESpace
WHERE DriveInfo LIKE 'TOTAL # OF %'
GROUP BY DriveLetter
ORDER BY DriveLetter
 
SELECT * FROM @FinalResults

T-SQL Query to find IP Address of SQL Server

Here is a handy T-SQL script to find the IP Address of the SQL Server you are connected to.

SELECT CONNECTIONPROPERTY('local_net_address') AS [IP Address Of SQL Server]

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [Machine Name]
   ,SERVERPROPERTY('InstanceName') AS [Instance Name]
   ,LOCAL_NET_ADDRESS AS [IP Address Of SQL Server]
   ,CLIENT_NET_ADDRESS AS [IP Address Of Client]
 FROM SYS.DM_EXEC_CONNECTIONS 
 WHERE SESSION_ID = @@SPID