October 28, 2008 at 4:22 am
I need to figure out a query which will give the used space and freee space on the log files ( dbcc sqlperf(logspace) wont do, since there are more than 1 logfiles, and this statement gives details only as a total). Please suggest on this.
Thanks in advance!! 🙂
October 28, 2008 at 4:51 am
try this:
select * from sysfiles
Why do you have more than 1 logfile? It won't acclerate your database ....
Wilfred
The best things in life are the simple things
October 28, 2008 at 4:56 am
I do not get the corect nos.. can u pls let me know which are the fields , and do I need to do any conversions, like conversion from extents to kb/ mb or so...
Thank u.... 🙂
October 28, 2008 at 4:57 am
Sorry, I posted the wrong answer. Try this:
print '*** Creating objecten voor DBSize'
print ''
if not exists (select 'yes' from sysobjects where name = 'dbsize')
BEGIN
CREATE TABLE DBSize(
[Servername] sysname DEFAULT @@SERVERNAME
, [Servicename] sysname DEFAULT @@SERVICENAME
, [databaseid] int NOT NULL
, [databasesize] bigint null
, [logsize] bigint null
, [rows] bigint NULL
, [reserved] BIGINT NULL
, [data] bigint NULL
, [index_size] bigint NULL
, [unused] bigint NULL
, [timestamps] SMALLDATETIME NOT NULL DEFAULT GETDATE()
)
CREATE UNIQUE INDEX IDX__DBSIZE__01 ON dbsize(servername,servicename, databaseid, timestamps) WITH IGNORE_DUP_KEY
END
GO
if exists (select 'yes' from sysobjects where name = 'vw_dbsize')
DROP VIEW vw_DBSize
GO
CREATE VIEW vw_dbsize as
SELECT *
from dbsize
where timestamps = (select max(timestamps) from DBSize)
GO
if exists (select 'yes' from sysobjects where name = 'usp_collectDBsize')
DROP PROCEDURE usp_collectDBSize
GO
CREATE PROCEDURE USP_CollectDBSize @verbose bit = 0, @debug bit = 0 AS
/*
Verzamelt statistieken over de database.
Deze procedure is een afgeleide van de sp_spaceused procedure
*/
SET NOCOUNT ON
BEGIN
DECLARE @SQLCmd nvarchar(2048)
if @verbose = 1 or @debug = 1
if LEFT(CAST(SERVERPROPERTY('productversion') AS CHAR),1) = '8'
print 'Using the MSSQL 2000 method'
else
print 'Using the MSSQL 2005 method'
if LEFT(CAST(SERVERPROPERTY('productversion') AS CHAR),1) = '8'
set @SQLCMD = N'
USE [?]
GO
DECLARE @rows bigint
DECLARE @reserved bigint
DECLARE @data bigint
DECLARE @index_size bigint
DECLARE @unused bigint
SELECT @dbsize = sum(convert(bigint, case when status & 64 = 0 then size else 0 end))
, @logsize = sum(convert(bigint, case when status & 64 <> 0 then size else 0 end))
FROM dbo.sysfiles
SELECT @rows = sum(rows) FROM sysindexes WHERE indid < 2
SELECT @reserved = sum(reserved) * 8 FROM sysindexes WHERE indid in (0, 1, 255)
SELECT @data = (SELECT sum(dpages) FROM sysindexes WHERE indid < 2 ) * 8 + (SELECT sum(used)FROM sysindexes WHERE indid = 255) * 8
SELECT @index_size = (SELECT sum(used) FROM sysindexes WHERE indid in (0, 1, 255)) * 8 - @data
SELECT @unused= @reserved - (SELECT sum(used) FROM sysindexes WHERE indid in (0, 1, 255)) * 8
INSERT INTO ' + DB_NAME() + '.dbo.DBSize([databaseid],[databasesize],[logsize],[rows],[reserved],[data],[index_size],[unused])
VALUES (DB_ID(),@dbsize, @logsize,@rows,@reserved,@data,@index_size,@unused)'
else
set @SQLCMD = N'
USE [?]
DECLARE @dbsize bigint
DECLARE @logsize bigint
DECLARE @rows bigint
DECLARE @reservedpages bigint
DECLARE @pages bigint
DECLARE @Usedpages bigint
DECLARE @index_size bigint
DECLARE @unused bigint
SELECT @dbsize = sum(convert(bigint, case when status & 64 = 0 then size else 0 end))
, @logsize = sum(convert(bigint, case when status & 64 <> 0 then size else 0 end))
FROM dbo.sysfiles
select @reservedpages = sum(a.total_pages) *8,
@usedpages = sum(a.used_pages) *8,
@pages = sum(
CASE
-- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"
When it.internal_type IN (202,204) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
) *8
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
SELECT @rows = sum(rows) FROM sysindexes WHERE indid < 2
INSERT INTO ' + DB_NAME() + '.dbo.DBSize([databaseid],[databasesize],[logsize],[rows],[reserved],[data],[index_size],[unused])
VALUES (DB_ID(),@dbsize, @logsize,@rows,@reservedpages,@pages,@usedpages-@pages,@reservedpages-@usedpages)'
if @debug = 0
EXEC sp_msforeachdb @SQLCmd
else
print @SQLCmd
END
GO
print '*** Executing usp_collectDBsize'
print ''
exec usp_collectdbsize
GO
print 'ok.'
Wilfred
The best things in life are the simple things
October 28, 2008 at 6:23 am
Hey thanks a lot... 🙂 .. that was Gr8
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply