| |
 |
|
Script to display SQL Server files for
autogrowth
SQL Server Tips by Donald Burleson |
This is one of the many SQL
Server Windows scripts to display storage filegroups from the book "High
Performance SQL Server DBA".From
filegroups , the DBA can drill down to the individual file level with the
up_bn_storage_files
procedure in SQL Server 2000 and
above. This script will reveal whether any files are not enabled for autogrowth
and if any file has a maximum file limit imposed.
See
code depot for full script
IF
OBJECT_ID('dbo.up_bn_storage_files') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.up_bn_storage_files
IF OBJECT_ID('dbo.up_bn_storage_files') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.up_bn_storage_files
>>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.up_bn_storage_files >>>'
END
go
CREATE PROCEDURE up_bn_storage_files
AS
set nocount on
declare @db_name sysname,
@testamt float
create table #file_info
(dbname sysname NULL,
groupid int NULL,
groupname sysname NULL,
logicname nchar(128) NULL,
filename nchar(260) NULL,
filestatus int NULL, /* to get data or log */
file_size float NULL,
file_max_size float NULL,
file_growth_size float NULL)
declare
db_cursor cursor for
select
name
from
master..sysdatabases
where
databasepropertyex(name,'status') not in
('SUSPECT', 'OFFLINE', 'RESTORING', 'RECOVERING') and
has_dbaccess(name) = 1
open db_cursor
fetch
db_cursor
into
@db_name
while @@fetch_status = 0
begin
insert
into #file_info
(dbname,
groupid,
groupname,
logicname,
filename,
filestatus,
file_size,
file_max_size,
file_growth_size)
exec
('use [' + @db_name + ']
select
db_name = db_name(),
b.groupid,
a.groupname,
b.name,
b.filename,
b.status,
b.size,
b.maxsize,
b.growth
from
See
code depot for full script
select
dbname,
logicname,
file_group = case groupname
when NULL then 'LOG'
else groupname
end,
filename,
size_in_mb = convert(decimal(17,2),((file_size * 8) / 1024)),
can_grow = case file_growth_size
when 0 then 'NO'
else 'YES'
end,
growth_amount = case (filestatus&0x100000)
when 0x100000 then convert(varchar(18),file_growth_size) +
'%'
else convert(varchar(18),(file_growth_size * 8) / 1024) + '
MB'
end,
max_file_size_mb = case(file_max_size)
when -1 then 'UNLIMITED'
else convert(varchar(18),((file_max_size * 8) / 1024))
end
from
#file_info
See
code depot for full script
go
IF OBJECT_ID('up_bn_storage_files') IS NOT NULL
PRINT '<<< CREATED PROCEDURE up_bn_storage_files >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE up_bn_storage_files >>>'
go
Figure 5.3 is a representation of the demographic
information for the database and log files.
This is
one of the many SQL Server Windows scripts to display storage filegroups from
the book "High
Performance SQL Server DBA".
|
|
|
and include the URL for the page.
