Espacio utilizado de la BD por Transac

Hola.

Exec sp_helpdb [DatabaseName]

qwalgrande

"Diego Vega" wrote:

como obtengo el tamaño usado de la BD por transac?

Hola.

Usa esto.

use master
go

if exists (select * from dbo.sysobjects where id object_id(N'[dbo].[#sp_dbspaceall]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_dbspaceall]
GO


CREATE procedure #sp_dbspaceall(@all char(1)='N')
as

set nocount on

declare @cmd varchar(500)
declare @db varchar(128)

create table #results(FileType varchar(4) NOT NULL,
[Name] sysname NOT NULL,
Total numeric(9,2) NOT NULL,
Used numeric(9,2) NOT NULL,
[Free] numeric(9,2) NOT NULL,
dbname sysname NULL)

create table #data(Fileid int NOT NULL,
[FileGroup] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[Name] sysname NOT NULL,
[FileName] varchar(300) NOT NULL)

create table #log(dbname sysname NOT NULL,
LogSize numeric(15,7) NOT NULL,
LogUsed numeric(9,5) NOT NULL,
Status int NOT NULL)

If @all='N' /* just the current database */
begin
/* Get data file(s) size */
insert #data
exec('DBCC showfilestats')

/* Get log file(s) size */
insert #log
exec('dbcc sqlperf(logspace)')

insert #results(FileType,[Name],Total,Used,[Free])
select 'Data',
left(right([FileName],charindex('\',reverse([FileName]))-1),
charindex('.',right([FileName],
charindex('\',reverse([FileName]))-1))-1),
CAST(((TotalExtents*64)/1024.00) as numeric(9,2)),
CAST(((UsedExtents*64)/1024.00) as numeric(9,2)),
(CAST(((TotalExtents*64)/1024.00) as numeric(9,2))
-CAST(((UsedExtents*64)/1024.00) as numeric(9,2)))
from #data

union all

select 'Log',dbname+'_log',LogSize,
((LogUsed/100)*LogSize),LogSize-((LogUsed/100)*LogSize)
from #log
where dbname = db_name()

select FileType,[Name],Total,Used,[Free]
from #results order by FileType

drop table #data
drop table #log
drop table #results

return
end
else
begin

/* Get data file(s) size */
declare dcur cursor local fast_forward
for
select CATALOG_NAME
from INFORMATION_SCHEMA.SCHEMATA
where has_dbaccess(CATALOG_NAME) = 1

open dcur

fetch next from dcur into @db

while @@fetch_status=0
begin

set @cmd = 'use ' + @db + ' DBCC showfilestats'
insert #data
exec(@cmd)

insert #results(FileType,[Name],Total,Used,[Free],dbname)
select 'Data',

left(right([FileName],charindex('\',reverse([FileName]))-1),
charindex('.',right([FileName],
charindex('\',reverse([FileName]))-1))-1),
CAST(((TotalExtents*64)/1024.00) as numeric(9,2)),
CAST(((UsedExtents*64)/1024.00) as numeric(9,2)),
(CAST(((TotalExtents*64)/1024.00) as numeric(9,2))
-CAST(((UsedExtents*64)/1024.00) as numeric(9,2))),
@db
from #data

delete #data

fetch next from dcur into @db

end
close dcur
deallocate dcur

/* Get log file(s) size */
insert #log
exec('dbcc sqlperf(logspace)')

insert #results(FileType,[Name],Total,Used,[Free],dbname)
select 'Log',dbname+'_log',LogSize,
((LogUsed/100)*LogSize),LogSize-((LogUsed/100)*LogSize),
dbname
from #log


select dbname,FileType,[Name],Total,Used,[Free]
from #results order by dbname,FileType

drop table #data
drop table #log
drop table #results

return

end

GO

exec #sp_dbspaceall 'Y'
GO

drop procedure #sp_dbspaceall

GO

"Diego Vega" wrote:

Perdón, no aclare q el SP_HELPDB muestra el tamaño y no
es espacio utilizado y el libre que es lo que necesito
gracias..

>Hola.
>
>Exec sp_helpdb [DatabaseName]
>
>qwalgrande
>
>"Diego Vega" wrote:
>
>> como obtengo el tamaà±o usado de la BD por transac?
>>
>.
>

Hola Diego,

Puedes utilizar...

* sp_helpdb testDB , para ver el tamaño de la BD y sus archivos.

* sp_spaceused, para ver el espacio usado y libre de una BD.
Use testdb
exec sp_spaceused 'TestDB_Data'

* DBCC SQLPERF, para ver el tamaño y espacio libre del LOG de transacciones.
DBCC SQLPERF(LOGSPACE)
GO

Espero que esto te sirva.

Hermilson Tinoco
Colombia

"Diego Vega" wrote:

como obtengo el tamaño usado de la BD por transac?

Hola:

Te paso un procedimiento almacenado muy interesante que te muestra la
información que necesitas y otra adicional, para todas las bases de datos de
tu servidor
http://www.sqlservercentral.com/scr...s/1396.asp
(Si no puedes acceder directamente al link, deberás darte de alta)

Un saludo
Salvador Ramos
Murcia - España
[Microsoft MVP SQL Server]
www.helpdna.net (información sobre SQL server, Windows DNA y .NET)

"Diego Vega" escribió en el mensaje
news:0a3701c4fef2$4409a500$
Perdón, no aclare q el SP_HELPDB muestra el tamaño y no
es espacio utilizado y el libre que es lo que necesito
gracias..

Hola.

Exec sp_helpdb [DatabaseName]

qwalgrande

"Diego Vega" wrote:

como obtengo el tamaà±o usado de la BD por transac?

.

ahora si
gracias!

Hola.

Usa esto.

use master
go

if exists (select * from dbo.sysobjects where id >object_id(N'[dbo].[#sp_dbspaceall]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_dbspaceall]
GO


CREATE procedure #sp_dbspaceall(@all char(1)='N')
as

set nocount on

declare @cmd varchar(500)
declare @db varchar(128)

create table #results(FileType varchar(4) NOT NULL,
[Name] sysname NOT NULL,
Total numeric(9,2) NOT NULL,
Used numeric(9,2) NOT NULL,
[Free] numeric(9,2) NOT NULL,
dbname sysname NULL)

create table #data(Fileid int NOT NULL,
[FileGroup] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[Name] sysname NOT NULL,
[FileName] varchar(300) NOT NULL)

create table #log(dbname sysname NOT NULL,
LogSize numeric(15,7) NOT NULL,
LogUsed numeric(9,5) NOT NULL,
Status int NOT NULL)

If @all='N' /* just the current database */
begin
/* Get data file(s) size */
insert #data
exec('DBCC showfilestats')

/* Get log file(s) size */
insert #log
exec('dbcc sqlperf(logspace)')

insert #results(FileType,[Name],Total,Used,[Free])
select 'Data',
left(right([FileName],charindex('\',reverse

([FileName]))-1),

charindex('.',right([FileName],
charindex('\',reverse([FileName]))-1))-1),
CAST(((TotalExtents*64)/1024.00) as numeric

(9,2)),

CAST(((UsedExtents*64)/1024.00) as numeric

(9,2)),

(CAST(((TotalExtents*64)/1024.00) as numeric

(9,2))

-CAST(((UsedExtents*64)/1024.00) as numeric

(9,2)))

from #data

union all

select 'Log',dbname+'_log',LogSize,
((LogUsed/100)*LogSize),LogSize-((LogUsed/100)

*LogSize)

from #log
where dbname = db_name()

select FileType,[Name],Total,Used,[Free]
from #results order by FileType

drop table #data
drop table #log
drop table #results

return
end
else
begin

/* Get data file(s) size */
declare dcur cursor local fast_forward
for
select CATALOG_NAME
from INFORMATION_SCHEMA.SCHEMATA
where has_dbaccess(CATALOG_NAME) = 1

open dcur

fetch next from dcur into @db

while @@fetch_status=0
begin

set @cmd = 'use ' + @db + ' DBCC

showfilestats'

insert #data
exec(@cmd)

insert #results(FileType,[Name],Total,Used,

[Free],dbname)

select 'Data',

left(right([FileName],charindex('\',reverse([FileName]))-

1),

charindex('.',right([FileName],
charindex('\',reverse

([FileName]))-1))-1),

CAST(((TotalExtents*64)/1024.00) as

numeric(9,2)),

CAST(((UsedExtents*64)/1024.00) as

numeric(9,2)),

(CAST(((TotalExtents*64)/1024.00) as

numeric(9,2))

-CAST(((UsedExtents*64)/1024.00) as

numeric(9,2))),

@db
from #data

delete #data

fetch next from dcur into @db

end
close dcur
deallocate dcur

/* Get log file(s) size */
insert #log
exec('dbcc sqlperf(logspace)')

insert #results(FileType,[Name],Total,Used,

[Free],dbname)

select 'Log',dbname+'_log',LogSize,
((LogUsed/100)*LogSize),LogSize-((LogUsed/100)

*LogSize),

dbname
from #log


select dbname,FileType,[Name],Total,Used,[Free]
from #results order by dbname,FileType

drop table #data
drop table #log
drop table #results

return

end

GO

exec #sp_dbspaceall 'Y'
GO

drop procedure #sp_dbspaceall

GO

"Diego Vega" wrote:

Perdón, no aclare q el SP_HELPDB muestra el tamaà±o y

no

es espacio utilizado y el libre que es lo que necesito
gracias..

>Hola.
>
>Exec sp_helpdb [DatabaseName]
>
>qwalgrande
>
>"Diego Vega" wrote:
>
>> como obtengo el tamaàf±o usado de la BD por

transac?

>>
>.
>

.