Bookmark and Share
DBA tips: check size, log full, etc
(Publish Date: 2014-5-21 5:08pm, Total Visits: 315, Today: 1, This Week: 1, This Month: 3)

==check all databases' log file sizes

DBCC SQLPERF(LOGSPACE);
GO

==check a table size

USE mbprod2;
GO
EXEC sp_spaceused N'dbo.htf43t_onstand_status';
GO

==check a database size

USE dbprod;
GO
EXEC sp_spaceused @updateusage = N'TRUE';
GO

Or using another query

USE dbprod;

GO

SELECT name AS Name,
    size * 8 / 1024. as SizeinMB,
    FILEPROPERTY(name, 'SpaceUsed') * 8 /1024. as SpaceUsedInMB,
    CAST(FILEPROPERTY(name, 'SpaceUsed') as decimal(10,4))
      / CAST(size as decimal(10, 4)) * 100 as PercentSpaceUsed
FROM sys.database_files;
GO

==check laST LSN number for a database

dbcc log(database_name)
go

==SQL SERVER Transaction log full

BACKUP LOG WITH TRUNCATE_ONLY 
--or save to log to other drives on disk if required
GO

CHECKPOINT
GO

==replace 2 with your actual log file number.

DBCC SHRINKFILE (2, 100) 

DBCC SHRINKFILE (N'LogFileName', 1) -- shrink the log file to 1 MB

==check failed jobs

SELECT sj.name, sjh.run_date, sjh.run_time, sjh.message,
    sjh.run_status, sjh.run_duration
FROM msdb.dbo.sysjobhistory AS sjh
INNER JOIN msdb.dbo.sysjobs AS sj
on sjh.job_id = sj.job_id
WHERE sjh.step_id > 0
AND sjh.run_status <> 1
ORDER BY sjh.run_date DESC;
GO