September 5, 2008 at 11:24 am
Comments posted to this topic are about the item QA-Taskpad
March 2, 2011 at 1:30 pm
I decided to reformat this script (more out of curiosity than need), but please let the code in the initial post be a lesson to all - this is how to NOT format your code for sharing out here. What a headache!
Not that mine is much better, but I have revised it so it will at least work. There are a couple of lines
commented out (because frankly, I wasn't able to completely figure out what the code was trying to do - maybe someone else can make better sense of it now), as it was causing the script not to run.
Here's a reformatting of the code:
Declare @strDBName
Varchar(50)
Set @strDBName = 'tempdb'
--Declare other variables which are required in the script.
Declare @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 Integer
Declare @intTotalDatSize decimal(10,3)
Declare @intTotalUsedSize decimal(10,3)
Declare @intTotalFreeSize decimal(10,3)
Declare @intDataSizeFree decimal(4,2)
Set @intTotalDatSize = 0
Set @intTotalUsedSize = 0
Set @intTotalFreeSize = 0
Set NoCount On--Check if the database name provided is a valid database name
Select @strScratchPad = name,@dtDBCreatedDate=crdate,@strDBOwner=suser_sname(sid)
from master..sysdatabases
where name=@strDBName
Print '-----------**********Database Query Analyzer Taskpad V 1.0**********-----------'
If @strScratchPad is Null
Begin
Print ''Print 'The Database name provided is not a valid one on this server'
End
Else
Begin--This is a Valid Database
Print ''Print '-----------Database Information-----------'
Print 'Database Name : ' + @strScratchPad
Print 'Database Owner : ' + @strDBOwner
Print 'Date Created : ' + @dtDBCreatedDate--Space Calculations to show the Space Information
Create 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 #TempValTable
Print '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 #DataFileDetails
Select @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 ''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 Desc
select @dtLastDiffBkp=backup_finish_date
from msdb..backupset
where database_name=@strDBName
And type='I'
Order by backup_finish_date Desc
select @dtLastLogBkp=backup_finish_date
from msdb..backupset
where database_name=@strDBName
And type='L'
Order by backup_finish_date Desc
Select @dtLastRestore=restore_date
from msdb..restorehistory
where destination_database_name=@strDBName
Order by restore_date Desc
Print 'Last Full Database Backup : ' + @dtLastFullBkp
Print 'Last Diff Database Backup : ' + @dtLastDiffBkp
Print 'Last Log Database Backup : ' + @dtLastLogBkp
Print 'Last Database Restore : ' + @dtLastRestore--Show Space Allocated Information
Print ''
Print '--------------Space Allocated-------------'
Print 'Data : '
Print ' Logical File NameSizeUsed SpaceFree SpaceFile Path'
Print ' ------------------------------------------------------------------------------------------------'
DECLARE a_cur cursor for
Select LogiName,((TotExt*8*8)/1024),((UsedExt*8*8)/1024),FilName
from #DataFileDetailsOPEN a_cur
FETCH NEXT FROM a_cur
INTO @strLogiName,@intTotSize,@intUsedSize,@strFileName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @curCount INT
SET @curCount = @curCount + 1
DECLARE @DBtoCheck VARCHAR(50)
DECLARE @strFileNameSet VARCHAR(50)
DECLARE @logintoCreate VARCHAR(50)
DECLARE @strRole VARCHAR(50)
Print '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 Integer
END
Print ' ------------------------------------------------------------------------------------------------'
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_cur
DEALLOCATE a_cur
Drop Table #LogFileDetails
Drop Table #TempValTable
Drop Table #DataFileDetails
End
Print ''
Print '-----------------------------------------------------------------------------------'
I'm not real big on cursors, and have been discouraged from using them any longer in favor of better methods, but for anyone with a curiosity behind this Taskpad - enjoy.
:w00t:
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply