select max(isnull(datediff(dd,b.backup_start_date,getdate()),0)) as 'Days since backup', b.backup_size, d.name as database_name from master..sysdatabases d with (nolock) left join msdb..backupset b with (nolock) on d.name = b.database_name and b.backup_start_date = (select max(backup_start_date) from msdb..backupset b2 where b.database_name = b2.database_name and b2.type = 'D') where d.name != 'tempdb' group by d.name, b.type, b.backup_sizeThis produces the following:-
Friday, 4 March 2011
Would you like to know when any of your SQL backups were last backed up? Or did you want to verify that a back up actually ran? Why now you can!
Thursday, 3 March 2011
Ever wanted to know in one swoop how big your tables are for a databse in SQL Server?
Create Table #temp ( table_name sysname , row_count int, reserved_size varchar(50), data_size varchar(50), index_size varchar(50), unused_size varchar(50) ) SET NOCOUNT ON insert #temp exec sp_msforeachtable 'sp_spaceused ''?''' select a.table_name, a.row_count, count(*) as col_count, a.data_size from #temp a inner join information_schema.columns b on a.table_name = b.table_name group by a.table_name, a.row_count, a.data_size Order by CAST(Replace(a.data_size, ' KB', '') as integer) desc drop table #tempThis produces the following:-
Wednesday, 2 March 2011
Get a list of all database sizes in KB for the whole SQL server.
Create Table #temp ( table_name varchar(200) , database_size int, remarks varchar(50) ) SET NOCOUNT ON insert #temp exec sp_databases select table_name, cast(database_size / 1024 as nvarchar)+ ' KB' from #temp order by database_size desc drop table #tempAn example of what gets shown can bee seen below:-