Month: November 2010

autocheck wip

— autocheck.sql, RbS, to automate daily checks.

— create (temp) table (during development) to store results
SET NOCOUNT ON
drop table #ac
go
create table #ac(
cdate smalldatetime default getdate(),
c_free_gb int,
f_free_gb int,
up_since smalldatetime,
last_err smalldatetime,
last_jobf int)

— start a new row with todays date
insert into #ac default values

— temp-table to store raw drive-space
drop table #fs
go
create table #fs(
drive char(1),
mb_free int)
insert #fs exec xp_fixeddrives

— save drive-spaces
update #ac set c_free_gb = mb_free/1024 from #fs where drive = ‘C’
update #ac set f_free_gb = mb_free/1024 from #fs where drive = ‘F’

— save up_since
update #ac set up_since = crdate from sys.sysdatabases where name = ‘tempdb’

— latest error in sql-error-log
drop table #er
go
create table #er(
logdate datetime,
processinfo varchar(20),
text varchar(500))

insert #er exec xp_ReadErrorLog 0, 1, ‘error’ — errors in current sql-error-log
update #ac set last_err = (select max(logdate) from #er)

— date of last job failure
update #ac set last_jobf = (select max(run_date) from msdb.dbo.sysjobhistory)

select * from #ac

Database Sizes

The script below is very useful for listing database sizes. However sometimes it comes up with the following error…

Cannot insert the value NULL into column ” owner”;

This is the result of a database not having an owner which is quite a serious condition. Happily it is easiy identified like this …

select name, suser_sname(sid) owner from master..sysdatabases

… and fixed like this …

alter authorization on database::[SomeDatabase] to sa;

Or this (which is depreceiated from SQL2012) …

exec sp_changedbowner ‘sa’;

Back on topic 🙂 … here’s the script …

— dbsize.sql
set nocount on

create table #sizes (
name varchar(50),
db_size varchar(50),
owner varchar(50),
dbid char(1),
created smalldatetime,
status varchar(500),
compatability_level char(2))

insert #sizes exec sp_helpdb

select name, db_size
from #sizes
order by db_size desc

drop table #sizes

Dataguard gap check

Thanks to E for this manual check that dataguard is updating a standby machine.

On Primary

alter system switch logfile;
select * from v$log;
Record the current Sequence#

On Standby

select process, sequence#, status from v$managed_standby;
Check RFS & MRP0 values match, and are related to the primary Sequence#.

Disk Space Used (GB)

I cobbled together this script from bits on the internet. It works fine, but needs OLE Automation to be enabled on the server. Should you wish to do this, use the Surface-Area configuration tool.

— “DiskSpaceUsed.sql” eg: C-Drive 65.3GB used
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint

SET @MB = 1048576

CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)

INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate ‘Scripting.FileSystemObject’,@fso OUT
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive

OPEN dcur
FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0
BEGIN

EXEC @hr = sp_OAMethod @fso,’GetDrive’, @odrive OUT, @drive
EXEC @hr = sp_OAGetProperty @odrive,’TotalSize’, @TotalSize OUT
UPDATE #drives

SET TotalSize=@TotalSize/@MB
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END

CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso

SELECT Drive,
cast((TotalSize – FreeSpace)/1024.0 as decimal(4,2)) as ‘Used(GB)’
FROM #drives
ORDER BY drive
DROP TABLE #drives