Deep . . . I was already lost at the first word . . .
I wrote this PowerShell code, which may do what I need. Right now I only have a few select DBs we are backing up with SMSQL although we plan to backup many more shortly. Again, this solution will only work in a particular scenario (Tx Logs only, and only if Full Recovery Model is used).
# Select all the backups which were taken since yesterday
$Date = [DateTime]::Now.AddDays(-1)
$Path = "SnapInfo Directory where the log backups ar stored"
# $_.Mode -ne "d----" - only show files, but no directories
$Files = Get-ChildItem –Recurse -Force $Path | Where {$_.lastwritetime -gt $Date -and $_.Mode -ne "d----"} | Sort Length -Desc
# Write header output
""
"{0,-50} {1,8}" -f "File Name","Size (MB)"
"{0,-50} {1,8}" -f "---------","---------"
# Write the file name and size in MB
foreach ($File in $Files) {
"{0,-50} {1,8}" -f $File.Name, ("{0:N2}" -f ($File.Length / 1MB))
}
To sum up what we have learned so far - a snapshot can be large because:
- Database file (MDF, NDF) growing
- I can catch this by monitoring the physical file size
- Transaction Log Growing
- Full Recovery Mode:
- Check the size of the Tx log stored in SnapInfo, or
- Check the physical size of the Tx Log file
- Simple Mode:
- Check the physical size of the Tx Log file
- If this is after a Full Backup and your Tx Log is now overwriting existing data without changing the file size, you're flat outa luck!
- Rebuild Indexes or Defragmentation
- You just need to know when these tasks run and expect the worst
- DeDup
- This wasn't suggested, but in the past we have found that all DeDup really does is create a BIG FAT snapshot (since it modifies all the blocks)
Anyone have any other ideas?
Thanks!
