SQL - T-SQL for last Full and Log Backup

I wanted a script to be able to list when the last backup occured on the SQL Databases that we had to check first thing as part of morning checks.

I found a number of scripts but this one met my needs:

SELECT a.name AS [Database Name],a.recovery_model_Desc AS [Recovery Model],
(select MAX(b.backup_finish_date) from msdb..backupset b where b.type = 'D' and a.name=b.database_name) AS [Full Backup],
(select MAX(b.backup_finish_date) from msdb..backupset b where b.type = 'L' and a.name=b.database_name) AS [Log Backup]
FROM master.sys.databases a

As you can see from the screen shot below of a brand spanking new SQL Express install the output also gives the Recovery Model and well as Full and Log backups.

SQL - Size of Database and Logs

One morning I noticed the usage on the drives of a SQL server increased by a lot from the previous day. I noticed because part of the morning checks was to, errrrrr, check the diskspace on said server.

Previous posts will show which script I used on a daily basis to check this.

What I wanted to do next was to find which DB and Logs were taking the space up (this particular server had Logs with DBs on the same drive).

The first one script I came across was:

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files

--For specific Databases
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks'
GO

From the Blog sqlauthority.

It gave me the information I needed, or thought I needed.

When checking the tempdb it turns out that this was the problem - it had grown quite large and the only safe way to clear down a tempdb is go into single-user mode or when all tempdb activity is stopped. See TechNet article.Alternatively restart the instance.......

I found some other scripts to use (sorry can't remember where I found them to pass on their names and links!). Script1 I found at a number of sites.

Script1
SELECT d.name,
ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.name
ORDER BY d.name

Script2
--SQL Server 2000
select
fileID
,name
,filename
,(size*8)/1024 SizeMB
,((size*8)/1024)/1024 SizeGB
,db_name(dbid) as DBName
from sysaltfiles
where db_name(dbid) = 'master'

--SQL Server 2000
select
fileID
,name
,filename
,(size*8)/1024 SizeMB
,((size*8)/1024)/1024 SizeGB
,db_name(dbid) as DBName
from sysaltfiles
where filename like '%.ldf%'

--SQL Server 2005/2008
SELECT DB_NAME(database_id) AS DBName,
Name AS LogicalName,
Physical_Name, (size*8)/1024 SizeMB
Physical_Name, ((size*8)/1024)/1024 SizeGB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'Master'
GO

Powershell - SID to User account

Usual issue of finding that the disk on a server is filling up.

One of the culprits can be the Recycle Bin which just gives you the SID of the offending user.

I found this useful link on the Microsoft site which describes how to find the information in the Registry.

It is a very short article which essentially states:
Use this tool at your own risk.

1. Open Registry Editor and navigate to:
   
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion \ProfileList
2. Under the ProfileList key, you will see the SIDs. By selecting each one individually, you can look at the value entry and see what user name is associated with that particular SID.

Worked great for me - I am also looking for a simple Powershell script to give me the information. Once I do I'll post it here.

To Powershell the query I found this useful script (just add the appropriate SID):

$objSID = New-Object System.Security.Principal.SecurityIdentifier `
 ("S-1-5-21-xxxxxxxxxx-xxxxxxxxxx-xxxxxxxxxx-27810")
 $objUser = $objSID.Translate( [System.Security.Principal.NTAccount])
$objUser.Value

Powershell - Disk usage

I found a very useful script online a few weeks ago (but unfortunately I can't remember where from otherwise I would credit them!)

The script pulls machine names from a text file and then hunts down each disk and capacity used/free then saves the information to  htm file.

#requires -version 2.0
#use parameter  drive report to html.ps1 computer1,computer2 or a computer list file
#change file path and name on line 7 below to reflect name and  path of computer list file using.
#script will open web browser with current report when completed.

Param (
$computers = (Get-Content  "C:\Temp\Computers.txt")
)

$Title="Hard Drive Report to HTML"

#embed a stylesheet in the html header
$head = @"
<mce:style><!--
mce:0
--></mce:style><style _mce_bogus="1"><!--
mce:0
--></style>
<Title>$Title</Title>
<br>
"@ 

#define an array for html fragments
$fragments=@()

#get the drive data
$data=Get-WmiObject -Class Win32_logicaldisk -filter "drivetype=3" -computer $computers

#group data by computername
$groups=$Data | Group-Object -Property SystemName

#this is the graph character
[string]$g=[char]9608 

#create html fragments for each computer
#iterate through each group object
        
ForEach ($computer in $groups) {
    
    $fragments+="<H2>$($computer.Name)</H2>"
    
    #define a collection of drives from the group object
    $Drives=$computer.group
    
    #create an html fragment
    $html=$drives | Select @{Name="Drive";Expression={$_.DeviceID}},
    @{Name="SizeGB";Expression={$_.Size/1GB  -as [int]}},
    @{Name="UsedGB";Expression={"{0:N2}" -f (($_.Size - $_.Freespace)/1GB) }},
    @{Name="FreeGB";Expression={"{0:N2}" -f ($_.FreeSpace/1GB) }},
    @{Name="Usage";Expression={
      $UsedPer= (($_.Size - $_.Freespace)/$_.Size)*100
      $UsedGraph=$g * ($UsedPer/2)
      $FreeGraph=$g* ((100-$UsedPer)/2)
      #I'm using place holders for the < and > characters
      "xopenFont color=Redxclose{0}xopen/FontxclosexopenFont Color=Greenxclose{1}xopen/fontxclose" -f $usedGraph,$FreeGraph
    }} | ConvertTo-Html -Fragment 
    
    #replace the tag place holders. It is a hack but it works.
    $html=$html -replace "xopen","<"
    $html=$html -replace "xclose",">"
    
    #add to fragments
    $Fragments+=$html
    
    #insert a return between each computer
    $fragments+="<br>"
    
} #foreach computer

#add a footer
$footer=("<br><I>Report run {0} by {1}\{2}<I>" -f (Get-Date -displayhint date),$env:userdomain,$env:username)
$fragments+=$footer

#write the result to a file
ConvertTo-Html -head $head -body $fragments  | Out-File "C:\SQL Reports\drivereport_$((Get-Date).ToString('yyyy-MM-dd-hhmmss')).htm"

The only real amendment I made was adding the date time stamp to the output file.

Once armed with the information about the disk usage I can then investigate further by seeing what is taking the space up. I found a free tool which does the job - WinDirStat.

I will endeavour to find out who wrote the script........
And I just have!! - Gandalf 50