Better Way - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Better Way

Author

Topic

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-08-04 : 11:07:07

Is there a way to get the log sizes from a system table or a better way that dbcc. I know there is a size in sysfiles but it's not reflecting the current log size like PERF LOG RAW.

IF OBJECT_ID('tempdb..#DBCC_PERF_LOG_RAW') IS NOT NULL
DROP TABLE #DBCC_PERF_LOG_RAW

CREATE TABLE #DBCC_PERF_LOG_RAW (ID INT IDENTITY(1,1), DBName NVARCHAR(255), LOGSIZE REAL, LOGPERCENTUSED REAL, Status INT)

INSERT INTO #DBCC_PERF_LOG_RAW
exec('DBCC SQLPERF (LOGSPACE)')

--INSERT INTO @DBCC_PERF_LOG (DBName, LOGSIZE, LOGPERCENTUSED)
SELECT DBName, LOGSIZE, LOGPERCENTUSED
FROM #DBCC_PERF_LOG_RAW
ORDER BY LOGSIZE DESC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-04 : 12:42:28

SELECT CONVERT(float, size)/128
FROM sysfiles
WHERE fileid = 2

[EDIT] Added the CONVERT in there so that you get the decimal places.

Tara

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-08-06 : 20:28:34

Thanks T,

This is part of a bigger script which I will ask for comments later on.

Here a piece

SELECT LTRIM(RTRIM(A.name)) As DBName, LTRIM(RTRIM(B.filename)) AS DBLogFilePath, B.Name AS LogicalFileName
FROM
master..sysdatabases A INNER JOIN
master..sysaltfiles B ON A.dbid = B.dbid
WHERE B.fileid = 2 AND (B.size/128.00) > 10

How can I get the current size and the actual size like EM shows.

shsmonteiro
Constraint Violating Yak Guru

290 Posts

Posted - 2003-08-06 : 20:53:56

Valter,

EM uses DBCC SQLPERF(LOGSPACE). Seems that this is the way, and there is no other to get precise info.

regards,

Sérgio Monteiro
Trust in no Oracle

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-05-02 : 19:57:56

Im reading a database
and want to go to the select * from master..sysaltfiles
return LOG GROWTH and DATA GROWTH
on one line

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources