QA-Taskpad

/*Title: Script to show the Database information similar to what is shown in EM TaskpadName : Database Query Analyzer Taskpad V 1.0Author: Ganesh PittalaDescription:This Script shows most of the information which is shown when you view the Taskpad by selecting a Database inEnterprise Manager. In Cases when you try to use EM and it hangs or is very slow or when TempDB is full and doesn't allow you to view from EM, you can see the information from this scriptInputs: Valid Database NameOutput: Information Similar (and More!!) to Enterprise Manager Taskpad for a particular databaseVersion Information :ReleaseDateRemarks-----------------------------------------------1.015-Aug-2005Initial Release-----------------------------------------------*/Declare @strDBName Varchar(50)Set @strDBName = 'tempdb'
--Declare other variables which are required in the scriptDeclare @strScratchPad Varchar(50)Declare @dtDBCreatedDate Varchar(24)Declare @strDBOwner Varchar(50)Declare @intTotalDBSize Varchar(12)Declare @strSQL Varchar(1000)Declare @strTotalDataSizeUsed decimal(10,3)Declare @strTotalLogSize decimal(10,3)Declare @strTotalLogSizeUsed decimal(10,3)Declare @dtLastFullBkp Varchar(22)Declare @dtLastDiffBkp Varchar(22)Declare @dtLastLogBkp Varchar(22)Declare @dtLastRestore Varchar(22)Declare @strLogiName Char(20)Declare @intTotSize decimal(10,3)Declare @intUsedSize decimal(10,3)Declare @strFileName Varchar(100)Declare @PctLogUsed IntegerDeclare @intTotalDatSize decimal(10,3)Declare @intTotalUsedSize decimal(10,3)Declare @intTotalFreeSize decimal(10,3)Declare @intDataSizeFree decimal(4,2)Set @intTotalDatSize = 0Set @intTotalUsedSize = 0Set @intTotalFreeSize = 0Set NoCount On--Check if the database name provided is a valid database nameSelect @strScratchPad=name,@dtDBCreatedDate=crdate,@strDBOwner=suser_sname(sid) from master..sysdatabases where name=@strDBNamePrint '-----------**********Database Query Analyzer Taskpad V 1.0**********-----------'If @strScratchPad is NullBeginPrint ''Print 'The Database name provided is not a valid one on this server'EndElseBegin--This is a Valid DatabasePrint ''Print '-----------Database Information-----------'Print 'Database Name : ' + @strScratchPadPrint 'Database Owner : ' + @strDBOwnerPrint 'Date Created : ' + @dtDBCreatedDate--Space Calculations to show the Space InformationCreate Table #TempValTable(strAdhocCol Varchar(100))Create Table #DataFileDetails(FileID Int,FileGr Int,TotExt Numeric,UsedExt Numeric,LogiName Varchar(100),FilName Varchar(255))Create Table #LogFileDetails(strDBName Varchar(100),LogSize Decimal(10,2),PctUsed Decimal(10,2),Sts Int)
Set @strSQL = 'Insert Into #TempValTable select CONVERT(decimal(10,2),Sum(((size*8)/64)/16)) from [' + @strDBName + '].dbo.sysfiles'Exec (@strSQL)
Select @intTotalDBSize=strAdhocCol from #TempValTablePrint 'Database Size : ' + @intTotalDBSize + ' MB (Data + Log size)'Insert Into #DataFileDetails Exec('use ' + @strDBName + ' DBCC SHOWFILESTATS')Insert Into #LogFileDetails Exec('DBCC SQLPerf(LogSpace)')
Select @strTotalDataSizeUsed=(Sum((UsedExt)*8*8/1024)) from #DataFileDetailsSelect @strTotalLogSizeUsed=((PctUsed/100.00)*LogSize),@strTotalLogSize=LogSize,@PctLogUsed = PctUsed from #LogFileDetails where strDBName=@strDBName
Print 'Space Available : ' + Convert(Varchar(15),(@intTotalDBSize - (@strTotalDataSizeUsed + @strTotalLogSizeUsed))) + 'MB (Data + Log Size)'--Show the Backup InformationPrint ''Print '--------------Maintenance-----------------'Set @dtLastFullBkp = ' None'Set @dtLastDiffBkp = ' None'Set @dtLastLogBkp = ' None'Set @dtLastRestore = ' None'
select @dtLastFullBkp=backup_finish_date from msdb..backupset where database_name=@strDBName And type='D' Order by backup_finish_date Descselect @dtLastDiffBkp=backup_finish_date from msdb..backupset where database_name=@strDBName And type='I' Order by backup_finish_date Descselect @dtLastLogBkp=backup_finish_date from msdb..backupset where database_name=@strDBName And type='L' Order by backup_finish_date DescSelect @dtLastRestore=restore_date from msdb..restorehistory where destination_database_name=@strDBName Order by restore_date DescPrint 'Last Full Database Backup : ' + @dtLastFullBkpPrint 'Last Diff Database Backup : ' + @dtLastDiffBkpPrint 'Last Log Database Backup : ' + @dtLastLogBkpPrint 'Last Database Restore : ' + @dtLastRestore--Show Space Allocated InformationPrint ''Print '--------------Space Allocated-------------'Print 'Data : 'Print '  Logical File NameSizeUsed SpaceFree SpaceFile Path'Print '  ------------------------------------------------------------------------------------------------'DECLARE a_cur cursor forSelect LogiName,((TotExt*8*8)/1024),((UsedExt*8*8)/1024),FilName from #DataFileDetailsOPEN a_curFETCH NEXT FROM a_cur INTO @strLogiName,@intTotSize,@intUsedSize,@strFileNameWHILE (@@FETCH_STATUS = 0)BEGIN--Set @curCount = @curCount + 1--rint 'EXEC ' + @DBtoCheck + '..sp_addrolemember ''' + @strRole + ''',''' + @logintoCreate + ''''Print ' ' + @strLogiName + '' + Convert(Varchar(10),@intTotSize) + 'MB' + Convert(Varchar(10),@intUsedSize) + 'MB' + Convert(Varchar(10),(@intTotSize-@intUsedSize)) + 'MB ' + @strFileNameSet @intTotalDatSize = @intTotalDatSize + @intTotSizeSet @intTotalUsedSize = @intTotalUsedSize + @intUsedSizeSet @intTotalFreeSize = @intTotalFreeSize + (@intTotSize-@intUsedSize)FETCH NEXT FROM a_cur INTO @strLogiName,@intTotSize,@intUsedSize,@strFileName--Declare @intDataSizeFree IntegerENDPrint '  ------------------------------------------------------------------------------------------------'Set @intDataSizeFree = ((@intTotalFreeSize/@intTotalDatSize)*100)Print 'Total:' + Convert(Varchar(12),@intTotalDatSize) + 'MB ' + Convert(Varchar(12),@intTotalUsedSize) + 'MB ' + Convert(Varchar(12),@intTotalFreeSize) + 'MB (' + Convert(Varchar(12),@intDataSizeFree) + '%)'Print ''Print 'Transaction Log Space : 'Print '' + Convert(Varchar(12),@strTotalLogSize) + 'MB' + Convert(Varchar(12),@strTotalLogSizeUsed) + 'MB (' + Convert(Varchar(6),@PctLogUsed) + '%)' + Convert(Varchar(12),@strTotalLogSize-@strTotalLogSizeUsed) + 'MB'--CLOSE a_curDEALLOCATE a_curDrop Table #LogFileDetailsDrop Table #TempValTableDrop Table #DataFileDetailsEndPrint ''Print '-----------------------------------------------------------------------------------'