http://www.tsql.ca/Resources/Articles/Listobjectsperfilegroup/tabid/607/Default.aspx
The following script lists link between database objects and filegroups. You can also use this script to identify filegroup with no objects associated
SQL2000:
==========
IF OBJECT_ID('tempdb.dbo.#FileGroup') IS NOT NULL
DROP TABLE #FileGroup
IF OBJECT_ID('tempdb.dbo.#ObjectFileGroup') IS NOT NULL
DROP TABLE #ObjectFileGroup
CREATE TABLE #FileGroup (
FileGroup sysname
)
CREATE TABLE #ObjectFileGroup (
ObjectName sysname,
ObjectType varchar(20),
FileGroupID int,
FileGroup sysname
)
SET NOCOUNT ON
DECLARE @TableName sysname
DECLARE @id int
DECLARE cur_Tables CURSOR FAST_FORWARD FOR
SELECT TableName = [name], id FROM dbo.sysobjects WHERE type = 'U'
OPEN cur_Tables
FETCH NEXT FROM cur_Tables INTO @TableName, @id
WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE TABLE #FileGroup
INSERT #FileGroup (FileGroup)
EXEC sp_objectfilegroup @id
INSERT #ObjectFileGroup (ObjectName, ObjectType, FileGroupID, FileGroup)
SELECT @TableName, 'TABLE', FILEGROUP_ID(FileGroup), FileGroup
FROM #FileGroup
FETCH NEXT FROM cur_Tables INTO @TableName, @id
END
CLOSE cur_Tables
DEALLOCATE cur_Tables
INSERT #ObjectFileGroup (ObjectName, ObjectType, FileGroupID, FileGroup)
SELECT OBJECT_NAME(id) + ' * ' +[name], 'INDEX', groupid, FILEGROUP_NAME(groupid) FROM dbo.sysindexes
WHERE FILEGROUP_NAME(groupid) IS NOT NULL
AND OBJECT_NAME(id) NOT LIKE 'sys%'
AND [name] NOT LIKE '_WA_Sys%'
AND [name] NOT LIKE 'Statistic_%'
SELECT FileGroupName = FILEGROUP_NAME(sf.groupid),/*ofg.FileGroup, */ofg.ObjectName, ofg.ObjectType, FileName = sf.filename, FileSize = sf.[size] / 128
FROM #ObjectFileGroup ofg
RIGHT JOIN dbo.sysfiles sf
ON ofg.FileGroupID = sf.groupid
ORDER BY FileGroup, ObjectName
SQL 2005 (very simplified):
===========================
SELECT
fg.data_space_id, fg.name,
ObjectName = OBJECT_NAME(p.object_id), p.index_id
,df.name, df.physical_name, [Size] = df.size*8/1024
FROM sys.filegroups fg
LEFT JOIN sys.database_files df
ON fg.data_space_id = df.data_space_id
LEFT JOIN sys.partitions p
ON fg.data_space_id = p.partition_number
WHERE (p.object_id>4096 or p.object_id IS NULL)
maandag 29 december 2008
The Best Script for --Move a table between filegroups
http://www.tsql.ca/Resources/Articles/MoveTablesbetweenFilegroups/tabid/610/Default.aspx
Sometimes we need to move tables between filegroups. There are two options how to do it.
The first option is to rename existing table, create a new one with the same structure on the desired filegroup(s), copy the data over and then to drop the original table. It requires log space, some outage for the system, risk of having problems with security, and so on. But this is the only option if table has BLOB fields.
The second option is to recreate table’s clustered index on the desired filegroup. It will move the table itself on that filegroup. As it was mentioned the BLOBs field will have problems – they are stored separately from the table’s data pages and are not affected by any index operation.I had to reallocate all the tables in multiple databases, to put them into new filegroups.
Since option #1 was not good for some reasons I implemented such operation as a script. Script moves tables to proper filegroup if it's not aligned with desired filegroup specified in the configuration list. Script may operate with multiple databases at once, so one may specify tables from different databases.
Script structure:
A configuration list (database, table, filegroup) is created.
Existence of all filegroups is checked - quits if there is an assumed filegroup that does not exist in the database.
Every table in the database is checked against that list.
For those having different filegroup a script is built and preserved: that script re-creates existing indexes (creates with drop existing) I.e. it repeats existing indexes for the table with the new filegroup specified. Since some of the non-clustered indexes may be located in a different filegroup, they are moved to the table's filegroup. See index (re-)creation script description below.
Those scripts are executed within a transaction one-by-one.
Index (re-)creation script structure:
compose drop index commands list create index commands list after existing indexes for non-clustered indexes which are located not in the desired filegroup or if the clustered index for the table is not in the desired filegroup.
compose create index (with drop existing) commands for the clustered indexes that are to be moved. For tables without any clustered index the PK supporting index is made clustered and recreated with other.
compose create index commands list after existing indexes for non-clustered indexes which are located not in the desired filegroup or if the clustered index for the table is not in the desired filegroup. The index creation command does not include filegroup name, so it will be created in the same filegroup as the table it belongs to.
Those commands are saved in a table with following structure:
record id (identity)
table name
sequence number (the same as topics listed just above)
index name
sql command
The very structure manipulation script will go through that table in order of table name then sequence number then index name (latter is just for convinience - to analyze the log). There will be a transaction for each table - too costly to make it around everything.
The script comments verbosely its execution process at the run time.
Script is also is assumed to be a tool for operational DBA team parameterized by the table with tables/filegroups (see statements inserting data into #desired_layout table variable).
In order to get all the messages in a printable form the script output must be directed to text or a file.
/*******************************************************************************
** Name: Database tables moved to proper filegroups.sql
** Description: Script moves tables to proper filegroup if it's not aligned
with a list which is derived from the model(s).
Script structure:
- A list (database, table, filegroup) is created.
- Existence of all filegroups is checked - quits if there is an
assumed filegroup that does not exist in the database.
- Every table in the database is checked against that list.
- For those having different filegroup a script is built
and preserved: that script re-creates existing indexes
(creates with drop existing)
I.e. it repeats existing indexes
for the table with the new filegroup specified.
Since some of the non-clustered indexes may be located in
a different filegroup, they are moved to the table's filegroup.
See index (re-)creation script description below.
- Those scripts are executed within a transaction one-by-one.
Index (re-)creation script structure:
1) compose drop index commands list create index commands list
after existing indexes
for non-clustered indexes which are located not in the desired
filegroup or if the clustered index for the table is not in the
desired filegroup.
2) compose create index (with drop existing) commands for the clustered
indexes that are to be moved. For tables without any clustered
index the PK supporting index is made clustered and recreated with other.
3) compose create index commands list after existing indexes
for non-clustered indexes which are located not in the desired
filegroup or if the clustered index for the table is not in the
desired filegroup. The index creation command does not include
filegroup name, so it will be created in the same filegroup
as the table it belongs to.
Those commands are saved in a table with following structure:
- record id (identity)
- table name
- sequence number (the same as topics listed just above)
- index name
- sql command
The very structure manipulation script will go through that table
in order of table name then sequence number then index name
(latter is just for convinience - to analyze the log).
There will be a transaction for each table - too costly to make
it around everything.
Script comments verbosely its execution process at the run time.
Script is also is assumed to be a tool for operations team
parameterized by the table with tables/filegroups
(see statements inserting data into #desired_layout table variable).
In order to get all the messages in a printable form the script
output must be directed to text or a file.
**
** Input Parameters: database/table/filegroup mapping in a form
of insert statements (see configurable part below).
** Returns: a table with database/table/index names with filegroup names
for each object: original one, configured(desired) one and the final one,
status of the operation for each index is specified.
** Modification History
** Sr Date Modified By Reason
** 1 05/01/2007 Alexander Karmanov Initial version
*******************************************************************************/
use master
go
set nocount on
declare
@script_name sysname
select
@script_name = 'Database tables moved to proper filegroups.sql'
print replicate('-', 80)
print 'Script name: ' @script_name
print 'Server: ' @@servername
print 'Database: ' db_name()
print 'User: ' suser_sname()
print 'Start time: ' cast(getdate() as varchar)
print replicate('-', 80)
go
/******************************************************************************
Here is a configurable part:
mapping between tables and filegroups
*******************************************************************************/
-- declare a table with desired database tables layout
if object_id('tempdb..#desired_layout') is not null drop table #desired_layout
create table #desired_layout (database_name sysname, table_name sysname, filegroup_name sysname)
insert into #desired_layout (database_name, table_name, filegroup_name)
-- database_name_1
/* database_name_1 is not moved for a while */
-- database_name_2
select 'database_name_2', 'address_type', 'srv001_db_2_group_1' union all
<....>
select 'database_name_2', 'workflow_status', 'srv001_db_2_group_x'
/******************************************************************************
End of configurable part,
below goes the actual code
*******************************************************************************/
go
use tempdb
go
-- a table with the column list for all the indexes in the source database is prepared,
-- but is left emtpy - it's needed to be created prior to a helper UDF
if object_id('tempdb..index_columns') is not null drop table index_columns
create table index_columns (database_name sysname, table_name sysname, index_name sysname,
column_name sysname, key_no int,
is_index_clustered bit, is_index_unique bit, is_column_descending bit)
go
-- a helper function converting a table with column names into a comma-separated list
if object_id('tempdb..f_get_column_list') is not null drop function f_get_column_list
go
create function dbo.f_get_column_list (
@database_name sysname,
@table_name sysname,
@index_name sysname
)
returns varchar(8000)
as
begin
declare @col_list varchar(8000)
select @col_list = isnull(@col_list ', ', '')
column_name ' '
case is_column_descending when 1 then 'DESC' else '' end
from index_columns
where database_name = @database_name
and table_name = @table_name
and index_name = @index_name
order by key_no
return @col_list
end
go
-- after that point the only 'go' command is the very final one - many variables are reused.
declare
@sql varchar(8000),
@crlf char(2),
@rowcnt int
select
@crlf = char(13) char(10),
@sql = ''
-- check for existence of all the filegroups mentioned in the layout table, exit if there is any problem:
-- get a list of existing filegroups for all the databases:
if object_id('tempdb..#existing_filegroups') is not null drop table #existing_filegroups
create table #existing_filegroups (database_name sysname, filegroup_name sysname)
select @sql = @sql @crlf 'select ''' database_name ''' as database_name, groupname as group_name from '
database_name '.dbo.sysfilegroups union all'
from #desired_layout
group by database_name
select
@sql = replace (@sql ';', 'union all;', '')
insert into #existing_filegroups (database_name, filegroup_name)
exec(@sql)
(select @rowcnt = count(distinct d.filegroup_name)
from #desired_layout d
left join #existing_filegroups e
on d.database_name = e.database_name
and d.filegroup_name = e.filegroup_name
where e.database_name is null)
if @rowcnt > 0
begin
select 'In ' upper(d.database_name) ' database there is no filegroup ''' upper(d.filegroup_name) ''''
as 'Filegroups that does not exist in the target database:'
from #desired_layout d
left join #existing_filegroups e
on d.database_name = e.database_name
and d.filegroup_name = e.filegroup_name
where e.database_name is null
group by d.database_name, d.filegroup_name
print 'Script is aborted because there is at least one filegroup that does not exist (see above).'
return
end
print 'Check for the missing filegroups completed successfully - all the filegroups mentioned in the list do exist.'
-- get the existing database/table/index/filegroup layout
if object_id('tempdb..#existing_layout') is not null drop table #existing_layout
create table #existing_layout (database_name sysname, table_name sysname, index_name sysname, ind_id int,
filegroup_name sysname, is_pk_or_uk int, table_filegroup_name sysname)
select @sql = null
select @sql = isnull(@sql @crlf 'union all' @crlf, '') 'select ''' database_name ''' as database_name, ' @crlf
'o.name as table_name, i.name as index_name, i.indid as ind_id, g.groupname as filegroup_name, ' @crlf
'case when k.name = i.name then 1 else 0 end as is_pk_or_uk, ' @crlf
'g2.groupname as table_filegroup_name ' @crlf
' from ' database_name '.dbo.sysobjects o ' @crlf
' join ' database_name '.dbo.sysindexes i ' @crlf
' on o.id = i.id ' @crlf
' join ' database_name '.dbo.sysfilegroups g ' @crlf
' on i.groupid = g.groupid ' @crlf
' left join (select c.id, o2.name ' @crlf
' from ' database_name '.dbo.sysconstraints c ' @crlf
' join ' database_name '.dbo.sysobjects o2 ' @crlf
' on c.constid = o2.id ' @crlf
' where o2.xtype in (''PK'', ''UQ'')) k ' @crlf
' on o.id = k.id ' @crlf
' join ' database_name '.dbo.sysindexes i2 ' @crlf
' on o.id = i2.id ' @crlf
' and i2.indid < 2 ' @crlf
' join ' database_name '.dbo.sysfilegroups g2 ' @crlf
' on i2.groupid = g2.groupid ' @crlf
' where o.xtype = ''U'' ' @crlf
' and i.name not like ''\_WA%'' escape ''\'' ' @crlf
' --and indexproperty(object_id(o.name), i.name, ''isstatistics'') = 0 ' @crlf
' and i.indid < 255 ' @crlf
from #desired_layout
group by database_name
insert into #existing_layout (database_name, table_name, index_name, ind_id, filegroup_name, is_pk_or_uk, table_filegroup_name)
exec(@sql)
-- compose table with commands changing the database structure
if object_id('tempdb..#sql') is not null drop table #sql
create table #sql (rowid int identity (1,1), database_name sysname, table_name sysname,
index_name sysname, seq_number int, sql varchar(7000))
-- get all the indexes that must be dropped:
-- 1. indexes that are not clustered, are not created as PK or UNIQUE constraint.
-- 2. either:
-- 2a. index is located not in a desired filegroup
-- 2b. clustered index on the table (index belongs to) is located not in a desired filegroup
-- All those indexes got sequence code of 1 meaning it will be executed first.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct e.database_name, e.table_name, e.index_name, 1 as seq_number,
sql = 'use ' e.database_name '; drop index dbo.' e.table_name '.' e.index_name
from #existing_layout e
join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where e.is_pk_or_uk = 0
and e.ind_id > 1
and (
e.filegroup_name <> d.filegroup_name
or
e.table_filegroup_name <> d.filegroup_name
)
-- get all the indexes that must be created as clustered with drop_existing option:
-- 1. all the indexes that are clustered or PK or unique
-- (all the PK indexes on the tables that don't have clustered key get here as PK/UQ so the distinction needs to be done -
-- when the CLUSTERED word is included.)
-- All those indexes got sequence code of 2 meaning they will be executed when all the dependant indexes are dropped
-- The very command is to be added later - now just identifiers.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct e.database_name, e.table_name, e.index_name, 2 as seq_number,
sql = 'use ' e.database_name '; create %unique% %clustered% index ' e.index_name ' on dbo.' e.table_name
'(%column_list%) with fillfactor = 90, drop_existing on ' quotename(d.filegroup_name)
from #existing_layout e
join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where
e.is_pk_or_uk = 1 -- check for PK or unique key
or e.ind_id = 1 -- indid in sysindexes means clustered key on table
-- get all the indexes that are dropped in the first step - with the new destination filegroup and
-- all the parameters like fillfactor, and so on.
-- no need to analyze layouts, just take those that were marked as to be dropped and
-- compose create index statement for them.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct s.database_name, s.table_name, s.index_name, 3 as seq_number,
sql = 'use ' s.database_name '; create %unique% nonclustered index ' s.index_name ' on dbo.' s.table_name
'(%column_list%) with fillfactor = 90 on ' quotename(d.filegroup_name)
from #sql s
join #desired_layout d
on s.database_name = d.database_name
and s.table_name = d.table_name
where seq_number = 1
-- get attributes, column lists from the existing database
select @sql = null
select @sql = isnull(@sql @crlf, '')
'use ' database_name @crlf
'insert into tempdb..index_columns (database_name, table_name, index_name, column_name, ' @crlf
' key_no, is_index_clustered, is_index_unique, is_column_descending)' @crlf
'select ''' database_name ''', o.name as table_name, i.name as index_name, c.name as column_name, k.keyno, ' @crlf
' INDEXPROPERTY (o.id, i.name, ''isclustered'') as is_index_clustered,' @crlf
' INDEXPROPERTY (o.id, i.name, ''isunique'') as is_index_unique,' @crlf
' INDEXKEY_PROPERTY(o.id, i.indid, k.keyno, ''isdescending'') as is_column_descending' @crlf
' from ' database_name '.dbo.sysobjects o' @crlf
' join ' database_name '.dbo.sysindexes i' @crlf
' on o.id = i.id' @crlf
' join ' database_name '.dbo.sysindexkeys k' @crlf
' on i.id = k.id' @crlf
' and i.indid = k.indid' @crlf
' join ' database_name '.dbo.syscolumns c' @crlf
' on k.colid = c.colid' @crlf
' and k.id = c.id' @crlf
' where ' @crlf
' o.xtype = ''U''' @crlf
' and INDEXPROPERTY (i.id, i.name, ''isstatistics'') = 0' @crlf
''
from #desired_layout
group by database_name
exec(@sql)
-- replace %column_list% placeholders in the index creation statements with proper column list
update #sql
set sql = replace(sql, '%column_list%', dbo.f_get_column_list(database_name, table_name, index_name))
-- specify proper clustered option for indexes:
-- for indexes that are clustered already:
update s
set sql = replace(sql, '%clustered%', 'clustered')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
and ic.is_index_clustered = 1
-- for indexes that are PK in table without any clustered index - we make PK indexes clustered,
-- so it will be rebuilt in the new (desired) filegroup.
update s
set sql = replace(sql, '%clustered%', 'clustered')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
join #existing_layout e
on s.database_name = e.database_name
and s.table_name = e.database_name
and e.ind_id = 0 -- that means the table is a heap right now (no clustered index)
-- for the rest set it as nonclustered
update s
set sql = replace(sql, '%clustered%', 'nonclustered')
from #sql s
-- specify proper unique option for indexes:
-- for indexes that are clustered already:
update s
set sql = replace(sql, '%unique%', 'unique')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
and ic.is_index_unique = 1
-- for the rest set it as non-unique
update s
set sql = replace(sql, '%unique%', '')
from #sql s
-- at this point all the commands are prepared in #sql table
-- and all we need is just to execute them one-by-one.
-- Actions for a table are wrapped into a transaction,
-- so if there is any error no table indexes structures
print cast(getdate() as varchar) ': Starting processing the tables'
declare
@rowid int,
@database_name sysname,
@table_name sysname,
@index_name sysname,
@seq_number int
declare t cursor static for
select distinct database_name, table_name
from #sql
order by database_name, table_name
open t
fetch next from t into @database_name, @table_name
while @@fetch_status = 0
begin
print cast(getdate() as varchar) ': Processing table ' @table_name ' in ' @database_name ' database'
begin tran
declare i cursor static for
select rowid, index_name, sql, seq_number
from #sql
where database_name = @database_name
and table_name = @table_name
order by seq_number, index_name
open i
fetch next from i into @rowid, @index_name, @sql, @seq_number
while @@fetch_status = 0
begin
if @seq_number = 1 print cast(getdate() as varchar) ': Dropping index ' @table_name '.' @index_name
else if @seq_number = 2 print cast(getdate() as varchar) ': Moving index ' @table_name '.' @index_name ' to another filegroup'
else if @seq_number = 3 print cast(getdate() as varchar) ': Re-creating index ' @table_name '.' @index_name
print cast(getdate() as varchar) ': A command to be executed: ' @sql
exec(@sql)
-- if there is an error - stop processing that table, rolling back transaction for it and reporting failure for it
if @@error <> 0
begin
rollback
print cast(getdate() as varchar) ': Command failed to execute'
break
end
print cast(getdate() as varchar) ': Command successfully completed'
fetch next from i into @rowid, @index_name, @sql, @seq_number
end
close i
deallocate i
-- if we have uncommitted transaction here that means no error occured in th loop on the indexes
-- and there is no transaction rollback issued above - i.e. everything is fine for that table,
-- report success on it (and commit it of course):
if @@trancount > 0
begin
commit
print cast(getdate() as varchar) ': Successfully processed table ' @table_name ' in ' @database_name ' database'
end
fetch next from t into @database_name, @table_name
end -- of loop on t cursor
close t
deallocate t
-- get the tables layout after the changes (similar to what was done before the changes:
-- get the existing database/table/index/filegroup layout
if object_id('tempdb..#updated_layout') is not null drop table #updated_layout
create table #updated_layout (database_name sysname, table_name sysname, index_name sysname, ind_id int,
filegroup_name sysname, is_pk_or_uk int, table_filegroup_name sysname)
select @sql = null
select @sql = isnull(@sql @crlf 'union all' @crlf, '') 'select ''' database_name ''' as database_name, ' @crlf
'o.name as table_name, i.name as index_name, i.indid as ind_id, g.groupname as filegroup_name, ' @crlf
'case when k.name = i.name then 1 else 0 end as is_pk_or_uk, ' @crlf
'g2.groupname as table_filegroup_name ' @crlf
' from ' database_name '.dbo.sysobjects o ' @crlf
' join ' database_name '.dbo.sysindexes i ' @crlf
' on o.id = i.id ' @crlf
' join ' database_name '.dbo.sysfilegroups g ' @crlf
' on i.groupid = g.groupid ' @crlf
' left join (select c.id, o2.name ' @crlf
' from ' database_name '.dbo.sysconstraints c ' @crlf
' join ' database_name '.dbo.sysobjects o2 ' @crlf
' on c.constid = o2.id ' @crlf
' where o2.xtype in (''PK'', ''UQ'')) k ' @crlf
' on o.id = k.id ' @crlf
' join ' database_name '.dbo.sysindexes i2 ' @crlf
' on o.id = i2.id ' @crlf
' and i2.indid < 2 ' @crlf
' join ' database_name '.dbo.sysfilegroups g2 ' @crlf
' on i2.groupid = g2.groupid ' @crlf
' where o.xtype = ''U'' ' @crlf
' and i.name not like ''\_WA%'' escape ''\'' ' @crlf
' --and indexproperty(object_id(o.name), i.name, ''isstatistics'') = 0 ' @crlf
' and i.indid < 255 ' @crlf
from #desired_layout
group by database_name
insert into #updated_layout (database_name, table_name, index_name, ind_id, filegroup_name, is_pk_or_uk, table_filegroup_name)
exec(@sql)
-- report difference between the previously existing layout, what was the intent and what we've got after the update:
print 'Script execution status report (see the grid if it is not displayed below):'
select
e.database_name '.' e.table_name '.' e.index_name as object,
e.filegroup_name as original_fileroup,
isnull(d.filegroup_name, 'NOT CONFIGURED') as desired_new_filegroup,
isnull(u.filegroup_name, 'OBJECT DISAPPEARED!!!') as actual_new_filegroup,
case
when d.filegroup_name is null then 'NOT CONFIGURED'
when u.filegroup_name is null then 'OBJECT WAS LOST'
when d.filegroup_name <> u.filegroup_name then 'NO UPDATE HAPPENED'
else 'ok'
end as update_status
from #existing_layout e
left join #updated_layout u
on e.database_name = u.database_name
and e.table_name = u.table_name
and e.index_name = u.index_name
left join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where e.ind_id > 0 -- no need to show items for the heap (which item is not actually an index anyway).
-- If such heap table has a PK it is shown in a separate line (with ind_id > 1)
order by e.database_name, e.table_name, e.index_name
go
-- clean-up
use tempdb
if object_id('tempdb..f_get_column_list') is not null drop function f_get_column_list
if object_id('tempdb..index_columns') is not null drop table index_columns
go
-- to release any database we switched to
use master
go
-- the end of the script
/*
select *
from #desired_layout
select *
from #updated_layout
order by table_name
select *
from #existing_layout
order by table_name
select *
from #sql
order by table_name
*/
Sometimes we need to move tables between filegroups. There are two options how to do it.
The first option is to rename existing table, create a new one with the same structure on the desired filegroup(s), copy the data over and then to drop the original table. It requires log space, some outage for the system, risk of having problems with security, and so on. But this is the only option if table has BLOB fields.
The second option is to recreate table’s clustered index on the desired filegroup. It will move the table itself on that filegroup. As it was mentioned the BLOBs field will have problems – they are stored separately from the table’s data pages and are not affected by any index operation.I had to reallocate all the tables in multiple databases, to put them into new filegroups.
Since option #1 was not good for some reasons I implemented such operation as a script. Script moves tables to proper filegroup if it's not aligned with desired filegroup specified in the configuration list. Script may operate with multiple databases at once, so one may specify tables from different databases.
Script structure:
A configuration list (database, table, filegroup) is created.
Existence of all filegroups is checked - quits if there is an assumed filegroup that does not exist in the database.
Every table in the database is checked against that list.
For those having different filegroup a script is built and preserved: that script re-creates existing indexes (creates with drop existing) I.e. it repeats existing indexes for the table with the new filegroup specified. Since some of the non-clustered indexes may be located in a different filegroup, they are moved to the table's filegroup. See index (re-)creation script description below.
Those scripts are executed within a transaction one-by-one.
Index (re-)creation script structure:
compose drop index commands list create index commands list after existing indexes for non-clustered indexes which are located not in the desired filegroup or if the clustered index for the table is not in the desired filegroup.
compose create index (with drop existing) commands for the clustered indexes that are to be moved. For tables without any clustered index the PK supporting index is made clustered and recreated with other.
compose create index commands list after existing indexes for non-clustered indexes which are located not in the desired filegroup or if the clustered index for the table is not in the desired filegroup. The index creation command does not include filegroup name, so it will be created in the same filegroup as the table it belongs to.
Those commands are saved in a table with following structure:
record id (identity)
table name
sequence number (the same as topics listed just above)
index name
sql command
The very structure manipulation script will go through that table in order of table name then sequence number then index name (latter is just for convinience - to analyze the log). There will be a transaction for each table - too costly to make it around everything.
The script comments verbosely its execution process at the run time.
Script is also is assumed to be a tool for operational DBA team parameterized by the table with tables/filegroups (see statements inserting data into #desired_layout table variable).
In order to get all the messages in a printable form the script output must be directed to text or a file.
/*******************************************************************************
** Name: Database tables moved to proper filegroups.sql
** Description: Script moves tables to proper filegroup if it's not aligned
with a list which is derived from the model(s).
Script structure:
- A list (database, table, filegroup) is created.
- Existence of all filegroups is checked - quits if there is an
assumed filegroup that does not exist in the database.
- Every table in the database is checked against that list.
- For those having different filegroup a script is built
and preserved: that script re-creates existing indexes
(creates with drop existing)
I.e. it repeats existing indexes
for the table with the new filegroup specified.
Since some of the non-clustered indexes may be located in
a different filegroup, they are moved to the table's filegroup.
See index (re-)creation script description below.
- Those scripts are executed within a transaction one-by-one.
Index (re-)creation script structure:
1) compose drop index commands list create index commands list
after existing indexes
for non-clustered indexes which are located not in the desired
filegroup or if the clustered index for the table is not in the
desired filegroup.
2) compose create index (with drop existing) commands for the clustered
indexes that are to be moved. For tables without any clustered
index the PK supporting index is made clustered and recreated with other.
3) compose create index commands list after existing indexes
for non-clustered indexes which are located not in the desired
filegroup or if the clustered index for the table is not in the
desired filegroup. The index creation command does not include
filegroup name, so it will be created in the same filegroup
as the table it belongs to.
Those commands are saved in a table with following structure:
- record id (identity)
- table name
- sequence number (the same as topics listed just above)
- index name
- sql command
The very structure manipulation script will go through that table
in order of table name then sequence number then index name
(latter is just for convinience - to analyze the log).
There will be a transaction for each table - too costly to make
it around everything.
Script comments verbosely its execution process at the run time.
Script is also is assumed to be a tool for operations team
parameterized by the table with tables/filegroups
(see statements inserting data into #desired_layout table variable).
In order to get all the messages in a printable form the script
output must be directed to text or a file.
**
** Input Parameters: database/table/filegroup mapping in a form
of insert statements (see configurable part below).
** Returns: a table with database/table/index names with filegroup names
for each object: original one, configured(desired) one and the final one,
status of the operation for each index is specified.
** Modification History
** Sr Date Modified By Reason
** 1 05/01/2007 Alexander Karmanov Initial version
*******************************************************************************/
use master
go
set nocount on
declare
@script_name sysname
select
@script_name = 'Database tables moved to proper filegroups.sql'
print replicate('-', 80)
print 'Script name: ' @script_name
print 'Server: ' @@servername
print 'Database: ' db_name()
print 'User: ' suser_sname()
print 'Start time: ' cast(getdate() as varchar)
print replicate('-', 80)
go
/******************************************************************************
Here is a configurable part:
mapping between tables and filegroups
*******************************************************************************/
-- declare a table with desired database tables layout
if object_id('tempdb..#desired_layout') is not null drop table #desired_layout
create table #desired_layout (database_name sysname, table_name sysname, filegroup_name sysname)
insert into #desired_layout (database_name, table_name, filegroup_name)
-- database_name_1
/* database_name_1 is not moved for a while */
-- database_name_2
select 'database_name_2', 'address_type', 'srv001_db_2_group_1' union all
<....>
select 'database_name_2', 'workflow_status', 'srv001_db_2_group_x'
/******************************************************************************
End of configurable part,
below goes the actual code
*******************************************************************************/
go
use tempdb
go
-- a table with the column list for all the indexes in the source database is prepared,
-- but is left emtpy - it's needed to be created prior to a helper UDF
if object_id('tempdb..index_columns') is not null drop table index_columns
create table index_columns (database_name sysname, table_name sysname, index_name sysname,
column_name sysname, key_no int,
is_index_clustered bit, is_index_unique bit, is_column_descending bit)
go
-- a helper function converting a table with column names into a comma-separated list
if object_id('tempdb..f_get_column_list') is not null drop function f_get_column_list
go
create function dbo.f_get_column_list (
@database_name sysname,
@table_name sysname,
@index_name sysname
)
returns varchar(8000)
as
begin
declare @col_list varchar(8000)
select @col_list = isnull(@col_list ', ', '')
column_name ' '
case is_column_descending when 1 then 'DESC' else '' end
from index_columns
where database_name = @database_name
and table_name = @table_name
and index_name = @index_name
order by key_no
return @col_list
end
go
-- after that point the only 'go' command is the very final one - many variables are reused.
declare
@sql varchar(8000),
@crlf char(2),
@rowcnt int
select
@crlf = char(13) char(10),
@sql = ''
-- check for existence of all the filegroups mentioned in the layout table, exit if there is any problem:
-- get a list of existing filegroups for all the databases:
if object_id('tempdb..#existing_filegroups') is not null drop table #existing_filegroups
create table #existing_filegroups (database_name sysname, filegroup_name sysname)
select @sql = @sql @crlf 'select ''' database_name ''' as database_name, groupname as group_name from '
database_name '.dbo.sysfilegroups union all'
from #desired_layout
group by database_name
select
@sql = replace (@sql ';', 'union all;', '')
insert into #existing_filegroups (database_name, filegroup_name)
exec(@sql)
(select @rowcnt = count(distinct d.filegroup_name)
from #desired_layout d
left join #existing_filegroups e
on d.database_name = e.database_name
and d.filegroup_name = e.filegroup_name
where e.database_name is null)
if @rowcnt > 0
begin
select 'In ' upper(d.database_name) ' database there is no filegroup ''' upper(d.filegroup_name) ''''
as 'Filegroups that does not exist in the target database:'
from #desired_layout d
left join #existing_filegroups e
on d.database_name = e.database_name
and d.filegroup_name = e.filegroup_name
where e.database_name is null
group by d.database_name, d.filegroup_name
print 'Script is aborted because there is at least one filegroup that does not exist (see above).'
return
end
print 'Check for the missing filegroups completed successfully - all the filegroups mentioned in the list do exist.'
-- get the existing database/table/index/filegroup layout
if object_id('tempdb..#existing_layout') is not null drop table #existing_layout
create table #existing_layout (database_name sysname, table_name sysname, index_name sysname, ind_id int,
filegroup_name sysname, is_pk_or_uk int, table_filegroup_name sysname)
select @sql = null
select @sql = isnull(@sql @crlf 'union all' @crlf, '') 'select ''' database_name ''' as database_name, ' @crlf
'o.name as table_name, i.name as index_name, i.indid as ind_id, g.groupname as filegroup_name, ' @crlf
'case when k.name = i.name then 1 else 0 end as is_pk_or_uk, ' @crlf
'g2.groupname as table_filegroup_name ' @crlf
' from ' database_name '.dbo.sysobjects o ' @crlf
' join ' database_name '.dbo.sysindexes i ' @crlf
' on o.id = i.id ' @crlf
' join ' database_name '.dbo.sysfilegroups g ' @crlf
' on i.groupid = g.groupid ' @crlf
' left join (select c.id, o2.name ' @crlf
' from ' database_name '.dbo.sysconstraints c ' @crlf
' join ' database_name '.dbo.sysobjects o2 ' @crlf
' on c.constid = o2.id ' @crlf
' where o2.xtype in (''PK'', ''UQ'')) k ' @crlf
' on o.id = k.id ' @crlf
' join ' database_name '.dbo.sysindexes i2 ' @crlf
' on o.id = i2.id ' @crlf
' and i2.indid < 2 ' @crlf
' join ' database_name '.dbo.sysfilegroups g2 ' @crlf
' on i2.groupid = g2.groupid ' @crlf
' where o.xtype = ''U'' ' @crlf
' and i.name not like ''\_WA%'' escape ''\'' ' @crlf
' --and indexproperty(object_id(o.name), i.name, ''isstatistics'') = 0 ' @crlf
' and i.indid < 255 ' @crlf
from #desired_layout
group by database_name
insert into #existing_layout (database_name, table_name, index_name, ind_id, filegroup_name, is_pk_or_uk, table_filegroup_name)
exec(@sql)
-- compose table with commands changing the database structure
if object_id('tempdb..#sql') is not null drop table #sql
create table #sql (rowid int identity (1,1), database_name sysname, table_name sysname,
index_name sysname, seq_number int, sql varchar(7000))
-- get all the indexes that must be dropped:
-- 1. indexes that are not clustered, are not created as PK or UNIQUE constraint.
-- 2. either:
-- 2a. index is located not in a desired filegroup
-- 2b. clustered index on the table (index belongs to) is located not in a desired filegroup
-- All those indexes got sequence code of 1 meaning it will be executed first.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct e.database_name, e.table_name, e.index_name, 1 as seq_number,
sql = 'use ' e.database_name '; drop index dbo.' e.table_name '.' e.index_name
from #existing_layout e
join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where e.is_pk_or_uk = 0
and e.ind_id > 1
and (
e.filegroup_name <> d.filegroup_name
or
e.table_filegroup_name <> d.filegroup_name
)
-- get all the indexes that must be created as clustered with drop_existing option:
-- 1. all the indexes that are clustered or PK or unique
-- (all the PK indexes on the tables that don't have clustered key get here as PK/UQ so the distinction needs to be done -
-- when the CLUSTERED word is included.)
-- All those indexes got sequence code of 2 meaning they will be executed when all the dependant indexes are dropped
-- The very command is to be added later - now just identifiers.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct e.database_name, e.table_name, e.index_name, 2 as seq_number,
sql = 'use ' e.database_name '; create %unique% %clustered% index ' e.index_name ' on dbo.' e.table_name
'(%column_list%) with fillfactor = 90, drop_existing on ' quotename(d.filegroup_name)
from #existing_layout e
join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where
e.is_pk_or_uk = 1 -- check for PK or unique key
or e.ind_id = 1 -- indid in sysindexes means clustered key on table
-- get all the indexes that are dropped in the first step - with the new destination filegroup and
-- all the parameters like fillfactor, and so on.
-- no need to analyze layouts, just take those that were marked as to be dropped and
-- compose create index statement for them.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct s.database_name, s.table_name, s.index_name, 3 as seq_number,
sql = 'use ' s.database_name '; create %unique% nonclustered index ' s.index_name ' on dbo.' s.table_name
'(%column_list%) with fillfactor = 90 on ' quotename(d.filegroup_name)
from #sql s
join #desired_layout d
on s.database_name = d.database_name
and s.table_name = d.table_name
where seq_number = 1
-- get attributes, column lists from the existing database
select @sql = null
select @sql = isnull(@sql @crlf, '')
'use ' database_name @crlf
'insert into tempdb..index_columns (database_name, table_name, index_name, column_name, ' @crlf
' key_no, is_index_clustered, is_index_unique, is_column_descending)' @crlf
'select ''' database_name ''', o.name as table_name, i.name as index_name, c.name as column_name, k.keyno, ' @crlf
' INDEXPROPERTY (o.id, i.name, ''isclustered'') as is_index_clustered,' @crlf
' INDEXPROPERTY (o.id, i.name, ''isunique'') as is_index_unique,' @crlf
' INDEXKEY_PROPERTY(o.id, i.indid, k.keyno, ''isdescending'') as is_column_descending' @crlf
' from ' database_name '.dbo.sysobjects o' @crlf
' join ' database_name '.dbo.sysindexes i' @crlf
' on o.id = i.id' @crlf
' join ' database_name '.dbo.sysindexkeys k' @crlf
' on i.id = k.id' @crlf
' and i.indid = k.indid' @crlf
' join ' database_name '.dbo.syscolumns c' @crlf
' on k.colid = c.colid' @crlf
' and k.id = c.id' @crlf
' where ' @crlf
' o.xtype = ''U''' @crlf
' and INDEXPROPERTY (i.id, i.name, ''isstatistics'') = 0' @crlf
''
from #desired_layout
group by database_name
exec(@sql)
-- replace %column_list% placeholders in the index creation statements with proper column list
update #sql
set sql = replace(sql, '%column_list%', dbo.f_get_column_list(database_name, table_name, index_name))
-- specify proper clustered option for indexes:
-- for indexes that are clustered already:
update s
set sql = replace(sql, '%clustered%', 'clustered')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
and ic.is_index_clustered = 1
-- for indexes that are PK in table without any clustered index - we make PK indexes clustered,
-- so it will be rebuilt in the new (desired) filegroup.
update s
set sql = replace(sql, '%clustered%', 'clustered')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
join #existing_layout e
on s.database_name = e.database_name
and s.table_name = e.database_name
and e.ind_id = 0 -- that means the table is a heap right now (no clustered index)
-- for the rest set it as nonclustered
update s
set sql = replace(sql, '%clustered%', 'nonclustered')
from #sql s
-- specify proper unique option for indexes:
-- for indexes that are clustered already:
update s
set sql = replace(sql, '%unique%', 'unique')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
and ic.is_index_unique = 1
-- for the rest set it as non-unique
update s
set sql = replace(sql, '%unique%', '')
from #sql s
-- at this point all the commands are prepared in #sql table
-- and all we need is just to execute them one-by-one.
-- Actions for a table are wrapped into a transaction,
-- so if there is any error no table indexes structures
print cast(getdate() as varchar) ': Starting processing the tables'
declare
@rowid int,
@database_name sysname,
@table_name sysname,
@index_name sysname,
@seq_number int
declare t cursor static for
select distinct database_name, table_name
from #sql
order by database_name, table_name
open t
fetch next from t into @database_name, @table_name
while @@fetch_status = 0
begin
print cast(getdate() as varchar) ': Processing table ' @table_name ' in ' @database_name ' database'
begin tran
declare i cursor static for
select rowid, index_name, sql, seq_number
from #sql
where database_name = @database_name
and table_name = @table_name
order by seq_number, index_name
open i
fetch next from i into @rowid, @index_name, @sql, @seq_number
while @@fetch_status = 0
begin
if @seq_number = 1 print cast(getdate() as varchar) ': Dropping index ' @table_name '.' @index_name
else if @seq_number = 2 print cast(getdate() as varchar) ': Moving index ' @table_name '.' @index_name ' to another filegroup'
else if @seq_number = 3 print cast(getdate() as varchar) ': Re-creating index ' @table_name '.' @index_name
print cast(getdate() as varchar) ': A command to be executed: ' @sql
exec(@sql)
-- if there is an error - stop processing that table, rolling back transaction for it and reporting failure for it
if @@error <> 0
begin
rollback
print cast(getdate() as varchar) ': Command failed to execute'
break
end
print cast(getdate() as varchar) ': Command successfully completed'
fetch next from i into @rowid, @index_name, @sql, @seq_number
end
close i
deallocate i
-- if we have uncommitted transaction here that means no error occured in th loop on the indexes
-- and there is no transaction rollback issued above - i.e. everything is fine for that table,
-- report success on it (and commit it of course):
if @@trancount > 0
begin
commit
print cast(getdate() as varchar) ': Successfully processed table ' @table_name ' in ' @database_name ' database'
end
fetch next from t into @database_name, @table_name
end -- of loop on t cursor
close t
deallocate t
-- get the tables layout after the changes (similar to what was done before the changes:
-- get the existing database/table/index/filegroup layout
if object_id('tempdb..#updated_layout') is not null drop table #updated_layout
create table #updated_layout (database_name sysname, table_name sysname, index_name sysname, ind_id int,
filegroup_name sysname, is_pk_or_uk int, table_filegroup_name sysname)
select @sql = null
select @sql = isnull(@sql @crlf 'union all' @crlf, '') 'select ''' database_name ''' as database_name, ' @crlf
'o.name as table_name, i.name as index_name, i.indid as ind_id, g.groupname as filegroup_name, ' @crlf
'case when k.name = i.name then 1 else 0 end as is_pk_or_uk, ' @crlf
'g2.groupname as table_filegroup_name ' @crlf
' from ' database_name '.dbo.sysobjects o ' @crlf
' join ' database_name '.dbo.sysindexes i ' @crlf
' on o.id = i.id ' @crlf
' join ' database_name '.dbo.sysfilegroups g ' @crlf
' on i.groupid = g.groupid ' @crlf
' left join (select c.id, o2.name ' @crlf
' from ' database_name '.dbo.sysconstraints c ' @crlf
' join ' database_name '.dbo.sysobjects o2 ' @crlf
' on c.constid = o2.id ' @crlf
' where o2.xtype in (''PK'', ''UQ'')) k ' @crlf
' on o.id = k.id ' @crlf
' join ' database_name '.dbo.sysindexes i2 ' @crlf
' on o.id = i2.id ' @crlf
' and i2.indid < 2 ' @crlf
' join ' database_name '.dbo.sysfilegroups g2 ' @crlf
' on i2.groupid = g2.groupid ' @crlf
' where o.xtype = ''U'' ' @crlf
' and i.name not like ''\_WA%'' escape ''\'' ' @crlf
' --and indexproperty(object_id(o.name), i.name, ''isstatistics'') = 0 ' @crlf
' and i.indid < 255 ' @crlf
from #desired_layout
group by database_name
insert into #updated_layout (database_name, table_name, index_name, ind_id, filegroup_name, is_pk_or_uk, table_filegroup_name)
exec(@sql)
-- report difference between the previously existing layout, what was the intent and what we've got after the update:
print 'Script execution status report (see the grid if it is not displayed below):'
select
e.database_name '.' e.table_name '.' e.index_name as object,
e.filegroup_name as original_fileroup,
isnull(d.filegroup_name, 'NOT CONFIGURED') as desired_new_filegroup,
isnull(u.filegroup_name, 'OBJECT DISAPPEARED!!!') as actual_new_filegroup,
case
when d.filegroup_name is null then 'NOT CONFIGURED'
when u.filegroup_name is null then 'OBJECT WAS LOST'
when d.filegroup_name <> u.filegroup_name then 'NO UPDATE HAPPENED'
else 'ok'
end as update_status
from #existing_layout e
left join #updated_layout u
on e.database_name = u.database_name
and e.table_name = u.table_name
and e.index_name = u.index_name
left join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where e.ind_id > 0 -- no need to show items for the heap (which item is not actually an index anyway).
-- If such heap table has a PK it is shown in a separate line (with ind_id > 1)
order by e.database_name, e.table_name, e.index_name
go
-- clean-up
use tempdb
if object_id('tempdb..f_get_column_list') is not null drop function f_get_column_list
if object_id('tempdb..index_columns') is not null drop table index_columns
go
-- to release any database we switched to
use master
go
-- the end of the script
/*
select *
from #desired_layout
select *
from #updated_layout
order by table_name
select *
from #existing_layout
order by table_name
select *
from #sql
order by table_name
*/
The Best Script ever on -- SHRINK LOG FILE
http://education.sqlfarms.com/education/ShowPost.aspx?PostID=117
-----------------------------------------------------------------
SQL Farms Admin :
================
Now, to help you resolve the shrink problem:
What you describe happens often if the database was created with small disk space allocation and was set to autogrow, and several autogrows indeed took place. The result of 2, instead of 0, in the DBCC LOGINFO command prevents the server from shrinking the file appropriately. This needs to be overcome by backing up portions of the transaction log, (until transaction log fully backed up) and then truncating the transaction log.
To help you in this task, I am attaching the following script, which should get you the desired result. (I had the script around for a while, and it worked for me, but although I tuned it a little, I am not sure who the original developer is, to give him creadit :) ).
Please let me know whether this helped your problem.
/**************************************************************
Input Params:
-------------
@target_percent tinyint. default = 0. Target percentage of remaining shrinkable
space. Defaults to max possible.
@target_size_MB int. default = 10. Target size of final log in MB.
@max_iterations int. default = 1000. Number of loops (max) to run proc through.
@backup_log_opt nvarchar(1000). default = 'with truncate_only'. Backup options.
*************************************************************/
-- DB parameters
DECLARE @target_percent tinyint
DECLARE @target_size_MB int
DECLARE @max_iterations int
DECLARE @backup_log_opt nvarchar(1000)
SET @target_percent = 0
SET @target_size_MB = 0
SET @max_iterations = 1000
SET @backup_log_opt = 'WITH TRUNCATE_ONLY'
DECLARE @db SYSNAME
DECLARE @last_row INT
DECLARE @log_size DECIMAL(15,2)
DECLARE @unused1 DECIMAL(15,2)
DECLARE @unused DECIMAL(15,2)
DECLARE @shrinkable DECIMAL(15,2)
DECLARE @iteration INT
DECLARE @file_max INT
DECLARE @file INT
DECLARE @fileid VARCHAR(5)
DECLARE @prev_max_iterations INT
DECLARE @command VARCHAR(500)
SET NOCOUNT ON
SET @db = db_name()
SET @iteration = 0
SET @prev_max_iterations = 2^31-1
IF OBJECT_ID('tempdb..#loginfo', 'U') IS NOT NULL
DROP TABLE #loginfo
-- This table stores the results of DBCC LOGINFO
CREATE TABLE #loginfo(
id INT identity,
FileId INT,
FileSize NUMERIC(22,0),
StartOffset NUMERIC(22,0),
FSeqNo INT,
Status INT,
Parity SMALLINT,
TimeorLSN VARCHAR(25))
CREATE UNIQUE CLUSTERED INDEX loginfo_FSeqNo ON #loginfo ( FSeqNo, StartOffset )
IF OBJECT_ID('tempdb..#logfiles', 'U') IS NOT NULL
DROP TABLE #logfiles
CREATE TABLE #logfiles (
id INT IDENTITY(1,1),
fileid VARCHAR(5) NOT NULL)
INSERT INTO #logfiles ( fileid )
SELECT CONVERT(VARCHAR, fileid )
FROM sysfiles
WHERE status & 0x40 = 0x40
SET @file_max = @@ROWCOUNT
INSERT INTO #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, TimeorLSN )
EXEC ( 'dbcc loginfo' )
SET @last_row = @@rowcount
PRINT 'Max iterations = ' + rtrim(cast(@max_iterations as varchar(20)))
SELECT @log_size = sum( FileSize ) / 1048576.00,
@unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
FROM #loginfo
SET @unused1 = @unused -- save for later
-- changed this so that it will print with rest of output SBP
PRINT '
iteration ........... = ' + cast(@iteration as varchar(10)) + '
log size, MB ........ = ' + cast(@log_size as varchar(10)) + '
unused log, MB ...... = ' + cast(@unused as varchar(10)) + '
shrinkable log, MB .. = ' + cast(@shrinkable as varchar(10)) + '
shrinkable % ........ = ' + cast(convert( DECIMAL(6,2), @shrinkable * 100 / @log_size ) as varchar(10))
-- If @target_size_MB is entered, it will take precedence
-- over @target_percent, by calculating a new @target_percent.
IF @target_size_MB > 0
SET @target_percent = (@target_size_MB / @log_size) * 100
ELSE
SET @target_size_MB = 10
-- Changed @target_percent to + 1, because many times the end result is
-- slightly larger than the target.
WHILE @shrinkable * 100 / @log_size > (@target_percent + 1)
AND @iteration < @max_iterations
BEGIN
SET @iteration = @iteration + 1 -- this is just a precaution
SET @file = 0
WHILE @file < @file_max
BEGIN
SET @file = @file + 1
SELECT @fileid = fileid
FROM #logfiles
WHERE [id] = @file
SET @command = 'DBCC SHRINKFILE( ' + @fileid + ',' + RTRIM(CAST(@target_size_MB as varchar(10))) + ')'
PRINT @command
EXEC (@command)
END
EXEC( 'BACKUP LOG [' + @db + '] ' + @backup_log_opt )
TRUNCATE TABLE #loginfo
INSERT INTO #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, TimeorLSN )
EXEC ( 'DBCC LOGINFO' )
SET @last_row = @@ROWCOUNT
-- The most iterations we really need to do is the number of logical log files,
-- and this should change if the log is shrinking. Therefore, reset
-- @max_iterations within the loop.
SELECT @max_iterations = COUNT(*)
FROM #loginfo
-- If the number of logical log files did not change from last iteration, get out.
IF @max_iterations = @prev_max_iterations
SET @max_iterations = 0
ELSE
SET @prev_max_iterations = @max_iterations
PRINT 'Max iterations = ' + RTRIM(CAST(@max_iterations as varchar(20)))
SELECT @log_size = SUM( FileSize ) / 1048576.00,
@unused = SUM( CASE WHEN Status = 0 THEN FileSize ELSE 0 END ) / 1048576.00,
@shrinkable = SUM( CASE WHEN id < @last_row - 1 and Status = 0 THEN FileSize ELSE 0 END ) / 1048576.00
FROM #loginfo
PRINT '
iteration ........... = ' + CAST(@iteration AS VARCHAR(10)) + '
log size, MB ........ = ' + CAST(@log_size AS VARCHAR(10)) + '
unused log, MB ...... = ' + CAST(@unused AS VARCHAR(10)) + '
shrinkable log, MB .. = ' + CAST(@shrinkable AS VARCHAR(10)) + '
shrinkable % ........ = ' + CAST(CONVERT( DECIMAL(6,2), @shrinkable * 100 / @log_size ) AS VARCHAR(10))
END
IF @unused1 < @unused
SELECT 'After ' + CONVERT( VARCHAR, @iteration ) +
' iterations the unused portion of the log has grown from ' +
CONVERT( VARCHAR, @unused1 ) + ' MB to ' +
CONVERT( VARCHAR, @unused ) + ' MB.'
UNION ALL
SELECT 'Since the remaining unused portion is larger than 10 MB,' WHERE @unused > 10
UNION ALL
SELECT 'you may try running this procedure again with a higher number of iterations.' WHERE @unused > 10
UNION ALL
SELECT 'Sometimes the log would not shrink to a size smaller than several Megabytes.' WHERE @unused <= 10
else
select 'It took ' + convert( VARCHAR, @iteration ) +
' iterations to shrink the unused portion of the log from ' +
convert( VARCHAR, @unused1 ) + ' MB to ' +
convert( VARCHAR, @unused ) + ' MB'
-- cleanup
IF OBJECT_ID('tempdb..#loginfo', 'U') IS NOT NULL
DROP TABLE #loginfo
IF OBJECT_ID('tempdb..#logfiles', 'U') IS NOT NULL
DROP TABLE #logfiles
GO
-----------------------------------------------------------------
SQL Farms Admin :
================
Now, to help you resolve the shrink problem:
What you describe happens often if the database was created with small disk space allocation and was set to autogrow, and several autogrows indeed took place. The result of 2, instead of 0, in the DBCC LOGINFO command prevents the server from shrinking the file appropriately. This needs to be overcome by backing up portions of the transaction log, (until transaction log fully backed up) and then truncating the transaction log.
To help you in this task, I am attaching the following script, which should get you the desired result. (I had the script around for a while, and it worked for me, but although I tuned it a little, I am not sure who the original developer is, to give him creadit :) ).
Please let me know whether this helped your problem.
/**************************************************************
Input Params:
-------------
@target_percent tinyint. default = 0. Target percentage of remaining shrinkable
space. Defaults to max possible.
@target_size_MB int. default = 10. Target size of final log in MB.
@max_iterations int. default = 1000. Number of loops (max) to run proc through.
@backup_log_opt nvarchar(1000). default = 'with truncate_only'. Backup options.
*************************************************************/
-- DB parameters
DECLARE @target_percent tinyint
DECLARE @target_size_MB int
DECLARE @max_iterations int
DECLARE @backup_log_opt nvarchar(1000)
SET @target_percent = 0
SET @target_size_MB = 0
SET @max_iterations = 1000
SET @backup_log_opt = 'WITH TRUNCATE_ONLY'
DECLARE @db SYSNAME
DECLARE @last_row INT
DECLARE @log_size DECIMAL(15,2)
DECLARE @unused1 DECIMAL(15,2)
DECLARE @unused DECIMAL(15,2)
DECLARE @shrinkable DECIMAL(15,2)
DECLARE @iteration INT
DECLARE @file_max INT
DECLARE @file INT
DECLARE @fileid VARCHAR(5)
DECLARE @prev_max_iterations INT
DECLARE @command VARCHAR(500)
SET NOCOUNT ON
SET @db = db_name()
SET @iteration = 0
SET @prev_max_iterations = 2^31-1
IF OBJECT_ID('tempdb..#loginfo', 'U') IS NOT NULL
DROP TABLE #loginfo
-- This table stores the results of DBCC LOGINFO
CREATE TABLE #loginfo(
id INT identity,
FileId INT,
FileSize NUMERIC(22,0),
StartOffset NUMERIC(22,0),
FSeqNo INT,
Status INT,
Parity SMALLINT,
TimeorLSN VARCHAR(25))
CREATE UNIQUE CLUSTERED INDEX loginfo_FSeqNo ON #loginfo ( FSeqNo, StartOffset )
IF OBJECT_ID('tempdb..#logfiles', 'U') IS NOT NULL
DROP TABLE #logfiles
CREATE TABLE #logfiles (
id INT IDENTITY(1,1),
fileid VARCHAR(5) NOT NULL)
INSERT INTO #logfiles ( fileid )
SELECT CONVERT(VARCHAR, fileid )
FROM sysfiles
WHERE status & 0x40 = 0x40
SET @file_max = @@ROWCOUNT
INSERT INTO #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, TimeorLSN )
EXEC ( 'dbcc loginfo' )
SET @last_row = @@rowcount
PRINT 'Max iterations = ' + rtrim(cast(@max_iterations as varchar(20)))
SELECT @log_size = sum( FileSize ) / 1048576.00,
@unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
FROM #loginfo
SET @unused1 = @unused -- save for later
-- changed this so that it will print with rest of output SBP
PRINT '
iteration ........... = ' + cast(@iteration as varchar(10)) + '
log size, MB ........ = ' + cast(@log_size as varchar(10)) + '
unused log, MB ...... = ' + cast(@unused as varchar(10)) + '
shrinkable log, MB .. = ' + cast(@shrinkable as varchar(10)) + '
shrinkable % ........ = ' + cast(convert( DECIMAL(6,2), @shrinkable * 100 / @log_size ) as varchar(10))
-- If @target_size_MB is entered, it will take precedence
-- over @target_percent, by calculating a new @target_percent.
IF @target_size_MB > 0
SET @target_percent = (@target_size_MB / @log_size) * 100
ELSE
SET @target_size_MB = 10
-- Changed @target_percent to + 1, because many times the end result is
-- slightly larger than the target.
WHILE @shrinkable * 100 / @log_size > (@target_percent + 1)
AND @iteration < @max_iterations
BEGIN
SET @iteration = @iteration + 1 -- this is just a precaution
SET @file = 0
WHILE @file < @file_max
BEGIN
SET @file = @file + 1
SELECT @fileid = fileid
FROM #logfiles
WHERE [id] = @file
SET @command = 'DBCC SHRINKFILE( ' + @fileid + ',' + RTRIM(CAST(@target_size_MB as varchar(10))) + ')'
PRINT @command
EXEC (@command)
END
EXEC( 'BACKUP LOG [' + @db + '] ' + @backup_log_opt )
TRUNCATE TABLE #loginfo
INSERT INTO #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, TimeorLSN )
EXEC ( 'DBCC LOGINFO' )
SET @last_row = @@ROWCOUNT
-- The most iterations we really need to do is the number of logical log files,
-- and this should change if the log is shrinking. Therefore, reset
-- @max_iterations within the loop.
SELECT @max_iterations = COUNT(*)
FROM #loginfo
-- If the number of logical log files did not change from last iteration, get out.
IF @max_iterations = @prev_max_iterations
SET @max_iterations = 0
ELSE
SET @prev_max_iterations = @max_iterations
PRINT 'Max iterations = ' + RTRIM(CAST(@max_iterations as varchar(20)))
SELECT @log_size = SUM( FileSize ) / 1048576.00,
@unused = SUM( CASE WHEN Status = 0 THEN FileSize ELSE 0 END ) / 1048576.00,
@shrinkable = SUM( CASE WHEN id < @last_row - 1 and Status = 0 THEN FileSize ELSE 0 END ) / 1048576.00
FROM #loginfo
PRINT '
iteration ........... = ' + CAST(@iteration AS VARCHAR(10)) + '
log size, MB ........ = ' + CAST(@log_size AS VARCHAR(10)) + '
unused log, MB ...... = ' + CAST(@unused AS VARCHAR(10)) + '
shrinkable log, MB .. = ' + CAST(@shrinkable AS VARCHAR(10)) + '
shrinkable % ........ = ' + CAST(CONVERT( DECIMAL(6,2), @shrinkable * 100 / @log_size ) AS VARCHAR(10))
END
IF @unused1 < @unused
SELECT 'After ' + CONVERT( VARCHAR, @iteration ) +
' iterations the unused portion of the log has grown from ' +
CONVERT( VARCHAR, @unused1 ) + ' MB to ' +
CONVERT( VARCHAR, @unused ) + ' MB.'
UNION ALL
SELECT 'Since the remaining unused portion is larger than 10 MB,' WHERE @unused > 10
UNION ALL
SELECT 'you may try running this procedure again with a higher number of iterations.' WHERE @unused > 10
UNION ALL
SELECT 'Sometimes the log would not shrink to a size smaller than several Megabytes.' WHERE @unused <= 10
else
select 'It took ' + convert( VARCHAR, @iteration ) +
' iterations to shrink the unused portion of the log from ' +
convert( VARCHAR, @unused1 ) + ' MB to ' +
convert( VARCHAR, @unused ) + ' MB'
-- cleanup
IF OBJECT_ID('tempdb..#loginfo', 'U') IS NOT NULL
DROP TABLE #loginfo
IF OBJECT_ID('tempdb..#logfiles', 'U') IS NOT NULL
DROP TABLE #logfiles
GO
dinsdag 23 december 2008
Capacity Planning for SQL Server 2000 Database Storage
http://www.mssqltips.com/tip.asp?tip=1267
==========================================
Problem
----------:
I am running very low on storage to support my SQL Server 2000 user defined databases on a number of my production SQL Server instances. We have made the decision to invest in some sort of NAS\SAN or direct attached storage solution. Independent of which technology, vendor and RAID sets we select, I need to plan for my database storage for the next 18 to 24 months and beyond. I have seen your script for capacity planning at a table level (Easing the Capacity Planning Burden), but how can I roll this up at a database level? I need to determine the storage needs at a database and SQL Server instance.
Solution
---------:
To me the best way to determine future storage needs is to analyze the past with an eye out for potential business changes that cannot be reflected from historical calculations. As such, below is script that calculates the needed storage at a per SQL Server instance and database level. This script will review the database backup sizes at a per database level to calculate the storage needs for the future. Check out this SQL Server database capacity planning script:
Table - SQL Server 2000 Database Capacity Planning
CREATE TABLE [dbo].[CapacityPlanning] (
[CPID] [int] IDENTITY (1, 1) NOT NULL ,
[ServerName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DatabaseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExecutionDateTime] [datetime] NULL ,
[NewDatabaseSize] [decimal](18, 0) NULL ,
[OldDatabaseSize] [decimal](18, 0) NULL ,
[NewCreationDate] [datetime] NULL ,
[OldCreationDate] [datetime] NULL ,
[VarDiff] [decimal](18, 0) NULL ,
[PercentGrowth] [decimal](18, 0) NULL ,
[AvgGrowth] [decimal](18, 0) NULL ,
[DateDiff] [decimal](18, 0) NULL ,
[Yr1DBProjections] [decimal](18, 0) NULL ,
[Yr1LogProjections] [decimal](18, 0) NULL ,
[Yr1DBProjections15Percent] [decimal](18, 0) NULL ,
[Yr1LogProjections15Percent] [decimal](18, 0) NULL ,
[Total1YrProj] [decimal](18, 0) NULL ,
[Total1YrProj15Percent] [decimal](18, 0) NULL ,
[Yr2DBProjections] [decimal](18, 0) NULL ,
[Yr2LogProjections] [decimal](18, 0) NULL ,
[Yr2DBProjections15Percent] [decimal](18, 0) NULL ,
[Yr2LogProjections15Percent] [decimal](18, 0) NULL ,
[Total2YrProj] [decimal](18, 0) NULL ,
[Total2YrProj15Percent] [decimal](18, 0) NULL ,
[Yr3DBProjections] [decimal](18, 0) NULL ,
[Yr3LogProjections] [decimal](18, 0) NULL ,
[Yr3DBProjections15Percent] [decimal](18, 0) NULL ,
[Yr3LogProjections15Percent] [decimal](18, 0) NULL ,
[Total3YrProj] [decimal](18, 0) NULL ,
[Total3YrProj15Percent] [decimal](18, 0) NULL ,
[TotalNumberofDatabases] [int] NULL
) ON [PRIMARY]
GO
Script - SQL Server 2000 Database Capacity Planning
CREATE PROCEDURE spCapacityPlanning AS
/*
----------------------------------------------------------------------------
-- Object Name: spCapacityPlanning
-- Project: Capacity Planning
-- Business Process: Capacity Planning
-- Purpose: Calculate the capacity planning for 1, 2 and 3 years for the database and transaction log
-- Detailed Description: Capture static information and write infromation to the
-- dbo.CapacityPlanning table for the database and transaction log calculations
-- Database: TBD
-- Dependent Objects:
-- - Master.dbo.sysdatabases
- MSDB.dbo.backupset
- TBD.dbo.CapacityPlanning
-- Called By: TBD
-- Upstream Systems: N\A
-- Downstream Systems: N\A
--
--------------------------------------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
--------------------------------------------------------------------------------------
-- 001 | N\A | 06.15.2007 | Edgewood | Original code
--
*/
SET NOCOUNT ON
-- Step 1 - Preliminary Information
SELECT @@SERVERNAME AS 'Server Name'
SELECT GETDATE() AS 'Execution Timestamp'
PRINT '--------------------------------------------------------'
PRINT '********************************************************'
PRINT ''
SELECT 'Disk Space Availablity'
PRINT ''
PRINT '********************************************************'
PRINT '--------------------------------------------------------'
PRINT ''
PRINT ''
EXEC Master.dbo.xp_fixeddrives
-- Step 2 - Declare the cursor variables
--Prepatory Variables
DECLARE @DatabaseName VARCHAR(50)
DECLARE @ExecutionDateTime DateTime
DECLARE @NewDatabaseSize Decimal
DECLARE @OldDatabaseSize Decimal
DECLARE @NewCreationDate DateTime
DECLARE @OldCreationDate DateTime
DECLARE @VarDiff Decimal
DECLARE @PercentGrowth Decimal
DECLARE @AvgGrowth Decimal
DECLARE @DateDiff Decimal
-- 1 Year Variables
DECLARE @Yr1DBProjections Decimal
DECLARE @Yr1LogProjections Decimal
DECLARE @Yr1DBProjections15Percent Decimal
DECLARE @Yr1LogProjections15Percent Decimal
-- 2 Year Variables
DECLARE @Yr2DBProjections Decimal
DECLARE @Yr2LogProjections Decimal
DECLARE @Yr2DBProjections15Percent Decimal
DECLARE @Yr2LogProjections15Percent Decimal
-- 3 Year Variables
DECLARE @Yr3DBProjections Decimal
DECLARE @Yr3LogProjections Decimal
DECLARE @Yr3DBProjections15Percent Decimal
DECLARE @Yr3LogProjections15Percent Decimal
-- Total Historical Variables
DECLARE @TotalRecentDatabaseSize Decimal
DECLARE @TotalOldDatabaseSize Decimal
DECLARE @TotalDiffDatabaseSize Decimal
DECLARE @TotalPercentageGrowth Decimal
DECLARE @AvgPercentageGrowth Decimal
DECLARE @AvgDateDiff Decimal
DECLARE @TotalNumberofDatabases Decimal
DECLARE @TotalDateDiff Decimal
-- Total Projection Variables
DECLARE @Total1YrDBProj Decimal
DECLARE @Total1YrLogProj Decimal
DECLARE @Total1YrDBProj15Percent Decimal
DECLARE @Total1YrLogProj15Percent Decimal
DECLARE @Total1YrProj Decimal -- Database and Log
DECLARE @Total1YrProj15Percent Decimal -- Database and Log
DECLARE @Total2YrDBProj Decimal
DECLARE @Total2YrLogProj Decimal
DECLARE @Total2YrDBProj15Percent Decimal
DECLARE @Total2YrLogProj15Percent Decimal
DECLARE @Total2YrProj Decimal -- Database and Log
DECLARE @Total2YrProj15Percent Decimal -- Database and Log
DECLARE @Total3YrDBProj Decimal
DECLARE @Total3YrLogProj Decimal
DECLARE @Total3YrDBProj15Percent Decimal
DECLARE @Total3YrLogProj15Percent Decimal
DECLARE @Total3YrProj Decimal -- Database and Log
DECLARE @Total3YrProj15Percent Decimal -- Database and Log
-- Initialize Historical Variables
SELECT @ExecutionDateTime = GETDATE()
SELECT @TotalNumberofDatabases = 0
SELECT @TotalRecentDatabaseSize = 0
SELECT @TotalOldDatabaseSize = 0
SELECT @TotalDiffDatabaseSize = 0
SELECT @TotalPercentageGrowth = 0
SELECT @AvgPercentageGrowth = 0
SELECT @AvgDateDiff = 0
SELECT @TotalDateDiff = 0
SELECT @Total1YrDBProj = 0
SELECT @Total1YrLogProj = 0
SELECT @Total1YrDBProj15Percent = 0
SELECT @Total1YrLogProj15Percent = 0
SELECT @Total1YrProj = 0 -- Database and Log
SELECT @Total1YrProj15Percent = 0-- Database and Log
SELECT @Total2YrDBProj = 0
SELECT @Total2YrLogProj = 0
SELECT @Total2YrDBProj15Percent = 0
SELECT @Total2YrLogProj15Percent = 0
SELECT @Total2YrProj = 0 -- Database and Log
SELECT @Total2YrProj15Percent = 0 -- Database and Log
SELECT @Total3YrDBProj = 0
SELECT @Total3YrLogProj = 0
SELECT @Total3YrDBProj15Percent = 0
SELECT @Total3YrLogProj15Percent = 0
SELECT @Total3YrProj = 0 -- Database and Log
SELECT @Total3YrProj15Percent = 0 -- Database and Log
-- Step 3 - Begin Cursor Processing
DECLARE CapPlanCursor CURSOR FOR
SELECT Name
FROM master.dbo.sysdatabases
ORDER BY Name
OPEN CapPlanCursor
FETCH NEXT FROM CapPlanCursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Prepatory Calculations
SELECT @NewDatabaseSize = ((backup_size)/1024/1024), @NewCreationDate = (backup_start_date)
FROM MSDB.dbo.backupset
WHERE database_name = @DatabaseName
AND TYPE = 'D'
ORDER BY backup_set_id
SELECT @OldDatabaseSize = ((backup_size)/1024/1024), @OldCreationDate = (backup_start_date)
FROM MSDB.dbo.backupset
WHERE database_name = @DatabaseName
AND TYPE = 'D'
ORDER BY backup_set_id DESC
SELECT @VarDiff = (@NewDatabaseSize - @OldDatabaseSize)
SELECT @PercentGrowth = (((@NewDatabaseSize/@OldDatabaseSize)-1)* 100)
SELECT @DateDiff = DATEDIFF(dd, @OldCreationDate, @NewCreationDate)
SELECT @AvgGrowth = (@VarDiff/@DateDiff)
-- Year 1 Figures
SELECT @Yr1DBProjections = ((@AvgGrowth * 365) + @NewDatabaseSize)
SELECT @Yr1DBProjections15Percent = ((@Yr1DBProjections * .15) + @Yr1DBProjections)
SELECT @Yr1LogProjections = (@Yr1DBProjections/4)
SELECT @Yr1LogProjections15Percent = ((@Yr1LogProjections * .15) + @Yr1LogProjections)
-- Year 2 Figures
SELECT @Yr2DBProjections = ((@AvgGrowth * 730) + @NewDatabaseSize)
SELECT @Yr2DBProjections15Percent = ((@Yr2DBProjections * .15) + @Yr2DBProjections)
SELECT @Yr2LogProjections = (@Yr2DBProjections/4)
SELECT @Yr2LogProjections15Percent = ((@Yr2LogProjections * .15) + @Yr2LogProjections)
-- Year 3 Figures
SELECT @Yr3DBProjections = ((@AvgGrowth * 1095) + @NewDatabaseSize)
SELECT @Yr3DBProjections15Percent = ((@Yr3DBProjections * .15) + @Yr3DBProjections)
SELECT @Yr3LogProjections = (@Yr3DBProjections/4)
SELECT @Yr3LogProjections15Percent = ((@Yr3LogProjections * .15) + @Yr3LogProjections)
-- Calculation Totals
SELECT @TotalRecentDatabaseSize = @TotalRecentDatabaseSize + @NewDatabaseSize
SELECT @TotalOldDatabaseSize = @TotalOldDatabaseSize + @OldDatabaseSize
SELECT @TotalDiffDatabaseSize = @TotalDiffDatabaseSize + @VarDiff
SELECT @TotalNumberofDatabases = @TotalNumberofDatabases + 1
SELECT @TotalPercentageGrowth = @TotalPercentageGrowth + @AvgGrowth
SELECT @TotalDateDiff = @TotalDateDiff + @DateDiff
-- Year 1 Projection Totals
SELECT @Total1YrDBProj = @Yr1DBProjections + @Total1YrDBProj
SELECT @Total1YrLogProj = @Yr1LogProjections + @Total1YrLogProj
SELECT @Total1YrDBProj15Percent = @Yr1DBProjections15Percent + @Total1YrDBProj15Percent
SELECT @Total1YrLogProj15Percent = @Yr1LogProjections15Percent + @Total1YrLogProj15Percent
-- Year 2 Projection Totals
SELECT @Total2YrDBProj = @Yr2DBProjections + @Total2YrDBProj
SELECT @Total2YrLogProj = @Yr2LogProjections + @Total2YrLogProj
SELECT @Total2YrDBProj15Percent = @Yr2DBProjections15Percent + @Total2YrDBProj15Percent
SELECT @Total2YrLogProj15Percent = @Yr2LogProjections15Percent + @Total2YrLogProj15Percent
-- Year 3 Projection Totals
SELECT @Total3YrDBProj = @Yr3DBProjections + @Total3YrDBProj
SELECT @Total3YrLogProj = @Yr3LogProjections + @Total3YrLogProj
SELECT @Total3YrDBProj15Percent = @Yr3DBProjections15Percent + @Total3YrDBProj15Percent
SELECT @Total3YrLogProj15Percent = @Yr3LogProjections15Percent + @Total3YrLogProj15Percent
-- Insert values into the dbo.CapacityPlanning table
INSERT INTO dbo.CapacityPlanning
(
ServerName
,DatabaseName
,ExecutionDateTime
,NewDatabaseSize
,OldDatabaseSize
,NewCreationDate
,OldCreationDate
,VarDiff
,PercentGrowth
,AvgGrowth
,DateDiff
,Yr1DBProjections
,Yr1LogProjections
,Yr1DBProjections15Percent
,Yr1LogProjections15Percent
,Yr2DBProjections
,Yr2LogProjections
,Yr2DBProjections15Percent
,Yr2LogProjections15Percent
,Yr3DBProjections
,Yr3LogProjections
,Yr3DBProjections15Percent
,Yr3LogProjections15Percent
)
VALUES
(
@@ServerName
,@DatabaseName
,@ExecutionDateTime
,@NewDatabaseSize
,@OldDatabaseSize
,@NewCreationDate
,@OldCreationDate
,@VarDiff
,@PercentGrowth
,@AvgGrowth
,@DateDiff
,@Yr1DBProjections
,@Yr1LogProjections
,@Yr1DBProjections15Percent
,@Yr1LogProjections15Percent
,@Yr2DBProjections
,@Yr2LogProjections
,@Yr2DBProjections15Percent
,@Yr2LogProjections15Percent
,@Yr3DBProjections
,@Yr3LogProjections
,@Yr3DBProjections15Percent
,@Yr3LogProjections15Percent
)
FETCH NEXT FROM CapPlanCursor INTO @DatabaseName
END
-- Step 4 - Calculate Aggregates
-- Historical Totals
SELECT @AvgPercentageGrowth = (@TotalPercentageGrowth/@TotalNumberofDatabases)
SELECT @AvgDateDiff = (@TotalDateDiff/@TotalNumberofDatabases)
-- Year 1 Totals
SELECT @Total1YrProj = @Total1YrDBProj + @Total1YrLogProj -- Database and Log
SELECT @Total1YrProj15Percent = @Total1YrDBProj15Percent + @Total1YrLogProj15Percent -- Database and Log
-- Year 2 Totals
SELECT @Total2YrProj = @Total2YrDBProj + @Total2YrLogProj -- Database and Log
SELECT @Total2YrProj15Percent = @Total2YrDBProj15Percent + @Total2YrLogProj15Percent -- Database and Log
-- Year 3 Totals
SELECT @Total3YrProj = @Total3YrDBProj + @Total3YrLogProj -- Database and Log
SELECT @Total3YrProj15Percent = @Total3YrDBProj15Percent + @Total3YrLogProj15Percent -- Database and Log
-- Step 5 - Insert Into Capacity Planning Table
INSERT INTO CapacityPlanning
(ServerName
,DatabaseName
,ExecutionDateTime
,NewDatabaseSize
,OldDatabaseSize
,NewCreationDate
,OldCreationDate
,VarDiff
,PercentGrowth
,AvgGrowth
,DateDiff
,Yr1DBProjections
,Yr1LogProjections
,Yr1DBProjections15Percent
,Yr1LogProjections15Percent
,Total1YrProj
,Total1YrProj15Percent
,Yr2DBProjections
,Yr2LogProjections
,Yr2DBProjections15Percent
,Yr2LogProjections15Percent
,Total2YrProj
,Total2YrProj15Percent
,Yr3DBProjections
,Yr3LogProjections
,Yr3DBProjections15Percent
,Yr3LogProjections15Percent
,Total3YrProj
,Total3YrProj15Percent
,TotalNumberofDatabases
)
VALUES
(
@@ServerName
,'Total Calculations'
,@ExecutionDateTime
,@TotalRecentDatabaseSize
,@TotalOldDatabaseSize
,NULL
,NULL
,@TotalDiffDatabaseSize
,NULL -- @AvgPercentageGrowth
,@TotalPercentageGrowth
,@AvgDateDiff
,@Total1YrDBProj
,@Total1YrLogProj
,@Total1YrDBProj15Percent
,@Total1YrLogProj15Percent
,@Total1YrProj
,@Total1YrProj15Percent
,@Total2YrDBProj
,@Total2YrLogProj
,@Total2YrDBProj15Percent
,@Total2YrLogProj15Percent
,@Total2YrProj
,@Total2YrProj15Percent
,@Total3YrDBProj
,@Total3YrLogProj
,@Total3YrDBProj15Percent
,@Total3YrLogProj15Percent
,@Total3YrProj
,@Total3YrProj15Percent
,@TotalNumberofDatabases
)
-- Step 6 - Generate Report
SELECT *
FROM CapacityPlanning
WHERE ExecutionDateTime = @ExecutionDateTime
CLOSE CapPlanCursor
DEALLOCATE CapPlanCursor
SET NOCOUNT OFF
GO
Special Notes:
-------------
To the best of my knowledge, this script will work for SQL Server databases that are using third party backup products (SQL Server 2005 Backup Product Options). This is because the values in backup system tables in the MSDB database are capturing values from the SQL Server Virtual Device Interface (VDI) as opposed to the compressed values from the third party product. This means that the backups can give you a historical view to use as a reasonable planning tool.
All businesses change and therefore those applications change as well. Some applications are retired while others are brought into the production environment. As such, this script will not be able to help estimate the future growth without having a representative amount of backups for calculations.
On a related, if you know you are bringing on a new customer that is going to double the revenue for the organization, odds are that you are going to have to plan for this storage as well independent of the script listed in this tip. You may be able to anecdotally plan based on the script, but the script is making a prediction on the storage needs based on the historical trends.
Next Steps
----------:
Keep an eye on the available storage across your SQL Server 2000 instances. When you are faced with the daunting task of determining how much storage you will need to support your databases at a SQL Server instance level, consider this script.
As a proactive DBA, it would be a good idea to monitor the storage on your SQL Server instances to determine how long the current storage will support the current databases at the current growth rate.
Once you have calculated the amount of storage that is needed to support your databases at a SQL Server instance level, make sure you will have sufficient storage based on the RAID set, hot spares, etc. that are needed. Work with your vendor to figure out the needed storage based on the hardware capabilities and\or functionality.
==========================================
Problem
----------:
I am running very low on storage to support my SQL Server 2000 user defined databases on a number of my production SQL Server instances. We have made the decision to invest in some sort of NAS\SAN or direct attached storage solution. Independent of which technology, vendor and RAID sets we select, I need to plan for my database storage for the next 18 to 24 months and beyond. I have seen your script for capacity planning at a table level (Easing the Capacity Planning Burden), but how can I roll this up at a database level? I need to determine the storage needs at a database and SQL Server instance.
Solution
---------:
To me the best way to determine future storage needs is to analyze the past with an eye out for potential business changes that cannot be reflected from historical calculations. As such, below is script that calculates the needed storage at a per SQL Server instance and database level. This script will review the database backup sizes at a per database level to calculate the storage needs for the future. Check out this SQL Server database capacity planning script:
Table - SQL Server 2000 Database Capacity Planning
CREATE TABLE [dbo].[CapacityPlanning] (
[CPID] [int] IDENTITY (1, 1) NOT NULL ,
[ServerName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DatabaseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExecutionDateTime] [datetime] NULL ,
[NewDatabaseSize] [decimal](18, 0) NULL ,
[OldDatabaseSize] [decimal](18, 0) NULL ,
[NewCreationDate] [datetime] NULL ,
[OldCreationDate] [datetime] NULL ,
[VarDiff] [decimal](18, 0) NULL ,
[PercentGrowth] [decimal](18, 0) NULL ,
[AvgGrowth] [decimal](18, 0) NULL ,
[DateDiff] [decimal](18, 0) NULL ,
[Yr1DBProjections] [decimal](18, 0) NULL ,
[Yr1LogProjections] [decimal](18, 0) NULL ,
[Yr1DBProjections15Percent] [decimal](18, 0) NULL ,
[Yr1LogProjections15Percent] [decimal](18, 0) NULL ,
[Total1YrProj] [decimal](18, 0) NULL ,
[Total1YrProj15Percent] [decimal](18, 0) NULL ,
[Yr2DBProjections] [decimal](18, 0) NULL ,
[Yr2LogProjections] [decimal](18, 0) NULL ,
[Yr2DBProjections15Percent] [decimal](18, 0) NULL ,
[Yr2LogProjections15Percent] [decimal](18, 0) NULL ,
[Total2YrProj] [decimal](18, 0) NULL ,
[Total2YrProj15Percent] [decimal](18, 0) NULL ,
[Yr3DBProjections] [decimal](18, 0) NULL ,
[Yr3LogProjections] [decimal](18, 0) NULL ,
[Yr3DBProjections15Percent] [decimal](18, 0) NULL ,
[Yr3LogProjections15Percent] [decimal](18, 0) NULL ,
[Total3YrProj] [decimal](18, 0) NULL ,
[Total3YrProj15Percent] [decimal](18, 0) NULL ,
[TotalNumberofDatabases] [int] NULL
) ON [PRIMARY]
GO
Script - SQL Server 2000 Database Capacity Planning
CREATE PROCEDURE spCapacityPlanning AS
/*
----------------------------------------------------------------------------
-- Object Name: spCapacityPlanning
-- Project: Capacity Planning
-- Business Process: Capacity Planning
-- Purpose: Calculate the capacity planning for 1, 2 and 3 years for the database and transaction log
-- Detailed Description: Capture static information and write infromation to the
-- dbo.CapacityPlanning table for the database and transaction log calculations
-- Database: TBD
-- Dependent Objects:
-- - Master.dbo.sysdatabases
- MSDB.dbo.backupset
- TBD.dbo.CapacityPlanning
-- Called By: TBD
-- Upstream Systems: N\A
-- Downstream Systems: N\A
--
--------------------------------------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
--------------------------------------------------------------------------------------
-- 001 | N\A | 06.15.2007 | Edgewood | Original code
--
*/
SET NOCOUNT ON
-- Step 1 - Preliminary Information
SELECT @@SERVERNAME AS 'Server Name'
SELECT GETDATE() AS 'Execution Timestamp'
PRINT '--------------------------------------------------------'
PRINT '********************************************************'
PRINT ''
SELECT 'Disk Space Availablity'
PRINT ''
PRINT '********************************************************'
PRINT '--------------------------------------------------------'
PRINT ''
PRINT ''
EXEC Master.dbo.xp_fixeddrives
-- Step 2 - Declare the cursor variables
--Prepatory Variables
DECLARE @DatabaseName VARCHAR(50)
DECLARE @ExecutionDateTime DateTime
DECLARE @NewDatabaseSize Decimal
DECLARE @OldDatabaseSize Decimal
DECLARE @NewCreationDate DateTime
DECLARE @OldCreationDate DateTime
DECLARE @VarDiff Decimal
DECLARE @PercentGrowth Decimal
DECLARE @AvgGrowth Decimal
DECLARE @DateDiff Decimal
-- 1 Year Variables
DECLARE @Yr1DBProjections Decimal
DECLARE @Yr1LogProjections Decimal
DECLARE @Yr1DBProjections15Percent Decimal
DECLARE @Yr1LogProjections15Percent Decimal
-- 2 Year Variables
DECLARE @Yr2DBProjections Decimal
DECLARE @Yr2LogProjections Decimal
DECLARE @Yr2DBProjections15Percent Decimal
DECLARE @Yr2LogProjections15Percent Decimal
-- 3 Year Variables
DECLARE @Yr3DBProjections Decimal
DECLARE @Yr3LogProjections Decimal
DECLARE @Yr3DBProjections15Percent Decimal
DECLARE @Yr3LogProjections15Percent Decimal
-- Total Historical Variables
DECLARE @TotalRecentDatabaseSize Decimal
DECLARE @TotalOldDatabaseSize Decimal
DECLARE @TotalDiffDatabaseSize Decimal
DECLARE @TotalPercentageGrowth Decimal
DECLARE @AvgPercentageGrowth Decimal
DECLARE @AvgDateDiff Decimal
DECLARE @TotalNumberofDatabases Decimal
DECLARE @TotalDateDiff Decimal
-- Total Projection Variables
DECLARE @Total1YrDBProj Decimal
DECLARE @Total1YrLogProj Decimal
DECLARE @Total1YrDBProj15Percent Decimal
DECLARE @Total1YrLogProj15Percent Decimal
DECLARE @Total1YrProj Decimal -- Database and Log
DECLARE @Total1YrProj15Percent Decimal -- Database and Log
DECLARE @Total2YrDBProj Decimal
DECLARE @Total2YrLogProj Decimal
DECLARE @Total2YrDBProj15Percent Decimal
DECLARE @Total2YrLogProj15Percent Decimal
DECLARE @Total2YrProj Decimal -- Database and Log
DECLARE @Total2YrProj15Percent Decimal -- Database and Log
DECLARE @Total3YrDBProj Decimal
DECLARE @Total3YrLogProj Decimal
DECLARE @Total3YrDBProj15Percent Decimal
DECLARE @Total3YrLogProj15Percent Decimal
DECLARE @Total3YrProj Decimal -- Database and Log
DECLARE @Total3YrProj15Percent Decimal -- Database and Log
-- Initialize Historical Variables
SELECT @ExecutionDateTime = GETDATE()
SELECT @TotalNumberofDatabases = 0
SELECT @TotalRecentDatabaseSize = 0
SELECT @TotalOldDatabaseSize = 0
SELECT @TotalDiffDatabaseSize = 0
SELECT @TotalPercentageGrowth = 0
SELECT @AvgPercentageGrowth = 0
SELECT @AvgDateDiff = 0
SELECT @TotalDateDiff = 0
SELECT @Total1YrDBProj = 0
SELECT @Total1YrLogProj = 0
SELECT @Total1YrDBProj15Percent = 0
SELECT @Total1YrLogProj15Percent = 0
SELECT @Total1YrProj = 0 -- Database and Log
SELECT @Total1YrProj15Percent = 0-- Database and Log
SELECT @Total2YrDBProj = 0
SELECT @Total2YrLogProj = 0
SELECT @Total2YrDBProj15Percent = 0
SELECT @Total2YrLogProj15Percent = 0
SELECT @Total2YrProj = 0 -- Database and Log
SELECT @Total2YrProj15Percent = 0 -- Database and Log
SELECT @Total3YrDBProj = 0
SELECT @Total3YrLogProj = 0
SELECT @Total3YrDBProj15Percent = 0
SELECT @Total3YrLogProj15Percent = 0
SELECT @Total3YrProj = 0 -- Database and Log
SELECT @Total3YrProj15Percent = 0 -- Database and Log
-- Step 3 - Begin Cursor Processing
DECLARE CapPlanCursor CURSOR FOR
SELECT Name
FROM master.dbo.sysdatabases
ORDER BY Name
OPEN CapPlanCursor
FETCH NEXT FROM CapPlanCursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Prepatory Calculations
SELECT @NewDatabaseSize = ((backup_size)/1024/1024), @NewCreationDate = (backup_start_date)
FROM MSDB.dbo.backupset
WHERE database_name = @DatabaseName
AND TYPE = 'D'
ORDER BY backup_set_id
SELECT @OldDatabaseSize = ((backup_size)/1024/1024), @OldCreationDate = (backup_start_date)
FROM MSDB.dbo.backupset
WHERE database_name = @DatabaseName
AND TYPE = 'D'
ORDER BY backup_set_id DESC
SELECT @VarDiff = (@NewDatabaseSize - @OldDatabaseSize)
SELECT @PercentGrowth = (((@NewDatabaseSize/@OldDatabaseSize)-1)* 100)
SELECT @DateDiff = DATEDIFF(dd, @OldCreationDate, @NewCreationDate)
SELECT @AvgGrowth = (@VarDiff/@DateDiff)
-- Year 1 Figures
SELECT @Yr1DBProjections = ((@AvgGrowth * 365) + @NewDatabaseSize)
SELECT @Yr1DBProjections15Percent = ((@Yr1DBProjections * .15) + @Yr1DBProjections)
SELECT @Yr1LogProjections = (@Yr1DBProjections/4)
SELECT @Yr1LogProjections15Percent = ((@Yr1LogProjections * .15) + @Yr1LogProjections)
-- Year 2 Figures
SELECT @Yr2DBProjections = ((@AvgGrowth * 730) + @NewDatabaseSize)
SELECT @Yr2DBProjections15Percent = ((@Yr2DBProjections * .15) + @Yr2DBProjections)
SELECT @Yr2LogProjections = (@Yr2DBProjections/4)
SELECT @Yr2LogProjections15Percent = ((@Yr2LogProjections * .15) + @Yr2LogProjections)
-- Year 3 Figures
SELECT @Yr3DBProjections = ((@AvgGrowth * 1095) + @NewDatabaseSize)
SELECT @Yr3DBProjections15Percent = ((@Yr3DBProjections * .15) + @Yr3DBProjections)
SELECT @Yr3LogProjections = (@Yr3DBProjections/4)
SELECT @Yr3LogProjections15Percent = ((@Yr3LogProjections * .15) + @Yr3LogProjections)
-- Calculation Totals
SELECT @TotalRecentDatabaseSize = @TotalRecentDatabaseSize + @NewDatabaseSize
SELECT @TotalOldDatabaseSize = @TotalOldDatabaseSize + @OldDatabaseSize
SELECT @TotalDiffDatabaseSize = @TotalDiffDatabaseSize + @VarDiff
SELECT @TotalNumberofDatabases = @TotalNumberofDatabases + 1
SELECT @TotalPercentageGrowth = @TotalPercentageGrowth + @AvgGrowth
SELECT @TotalDateDiff = @TotalDateDiff + @DateDiff
-- Year 1 Projection Totals
SELECT @Total1YrDBProj = @Yr1DBProjections + @Total1YrDBProj
SELECT @Total1YrLogProj = @Yr1LogProjections + @Total1YrLogProj
SELECT @Total1YrDBProj15Percent = @Yr1DBProjections15Percent + @Total1YrDBProj15Percent
SELECT @Total1YrLogProj15Percent = @Yr1LogProjections15Percent + @Total1YrLogProj15Percent
-- Year 2 Projection Totals
SELECT @Total2YrDBProj = @Yr2DBProjections + @Total2YrDBProj
SELECT @Total2YrLogProj = @Yr2LogProjections + @Total2YrLogProj
SELECT @Total2YrDBProj15Percent = @Yr2DBProjections15Percent + @Total2YrDBProj15Percent
SELECT @Total2YrLogProj15Percent = @Yr2LogProjections15Percent + @Total2YrLogProj15Percent
-- Year 3 Projection Totals
SELECT @Total3YrDBProj = @Yr3DBProjections + @Total3YrDBProj
SELECT @Total3YrLogProj = @Yr3LogProjections + @Total3YrLogProj
SELECT @Total3YrDBProj15Percent = @Yr3DBProjections15Percent + @Total3YrDBProj15Percent
SELECT @Total3YrLogProj15Percent = @Yr3LogProjections15Percent + @Total3YrLogProj15Percent
-- Insert values into the dbo.CapacityPlanning table
INSERT INTO dbo.CapacityPlanning
(
ServerName
,DatabaseName
,ExecutionDateTime
,NewDatabaseSize
,OldDatabaseSize
,NewCreationDate
,OldCreationDate
,VarDiff
,PercentGrowth
,AvgGrowth
,DateDiff
,Yr1DBProjections
,Yr1LogProjections
,Yr1DBProjections15Percent
,Yr1LogProjections15Percent
,Yr2DBProjections
,Yr2LogProjections
,Yr2DBProjections15Percent
,Yr2LogProjections15Percent
,Yr3DBProjections
,Yr3LogProjections
,Yr3DBProjections15Percent
,Yr3LogProjections15Percent
)
VALUES
(
@@ServerName
,@DatabaseName
,@ExecutionDateTime
,@NewDatabaseSize
,@OldDatabaseSize
,@NewCreationDate
,@OldCreationDate
,@VarDiff
,@PercentGrowth
,@AvgGrowth
,@DateDiff
,@Yr1DBProjections
,@Yr1LogProjections
,@Yr1DBProjections15Percent
,@Yr1LogProjections15Percent
,@Yr2DBProjections
,@Yr2LogProjections
,@Yr2DBProjections15Percent
,@Yr2LogProjections15Percent
,@Yr3DBProjections
,@Yr3LogProjections
,@Yr3DBProjections15Percent
,@Yr3LogProjections15Percent
)
FETCH NEXT FROM CapPlanCursor INTO @DatabaseName
END
-- Step 4 - Calculate Aggregates
-- Historical Totals
SELECT @AvgPercentageGrowth = (@TotalPercentageGrowth/@TotalNumberofDatabases)
SELECT @AvgDateDiff = (@TotalDateDiff/@TotalNumberofDatabases)
-- Year 1 Totals
SELECT @Total1YrProj = @Total1YrDBProj + @Total1YrLogProj -- Database and Log
SELECT @Total1YrProj15Percent = @Total1YrDBProj15Percent + @Total1YrLogProj15Percent -- Database and Log
-- Year 2 Totals
SELECT @Total2YrProj = @Total2YrDBProj + @Total2YrLogProj -- Database and Log
SELECT @Total2YrProj15Percent = @Total2YrDBProj15Percent + @Total2YrLogProj15Percent -- Database and Log
-- Year 3 Totals
SELECT @Total3YrProj = @Total3YrDBProj + @Total3YrLogProj -- Database and Log
SELECT @Total3YrProj15Percent = @Total3YrDBProj15Percent + @Total3YrLogProj15Percent -- Database and Log
-- Step 5 - Insert Into Capacity Planning Table
INSERT INTO CapacityPlanning
(ServerName
,DatabaseName
,ExecutionDateTime
,NewDatabaseSize
,OldDatabaseSize
,NewCreationDate
,OldCreationDate
,VarDiff
,PercentGrowth
,AvgGrowth
,DateDiff
,Yr1DBProjections
,Yr1LogProjections
,Yr1DBProjections15Percent
,Yr1LogProjections15Percent
,Total1YrProj
,Total1YrProj15Percent
,Yr2DBProjections
,Yr2LogProjections
,Yr2DBProjections15Percent
,Yr2LogProjections15Percent
,Total2YrProj
,Total2YrProj15Percent
,Yr3DBProjections
,Yr3LogProjections
,Yr3DBProjections15Percent
,Yr3LogProjections15Percent
,Total3YrProj
,Total3YrProj15Percent
,TotalNumberofDatabases
)
VALUES
(
@@ServerName
,'Total Calculations'
,@ExecutionDateTime
,@TotalRecentDatabaseSize
,@TotalOldDatabaseSize
,NULL
,NULL
,@TotalDiffDatabaseSize
,NULL -- @AvgPercentageGrowth
,@TotalPercentageGrowth
,@AvgDateDiff
,@Total1YrDBProj
,@Total1YrLogProj
,@Total1YrDBProj15Percent
,@Total1YrLogProj15Percent
,@Total1YrProj
,@Total1YrProj15Percent
,@Total2YrDBProj
,@Total2YrLogProj
,@Total2YrDBProj15Percent
,@Total2YrLogProj15Percent
,@Total2YrProj
,@Total2YrProj15Percent
,@Total3YrDBProj
,@Total3YrLogProj
,@Total3YrDBProj15Percent
,@Total3YrLogProj15Percent
,@Total3YrProj
,@Total3YrProj15Percent
,@TotalNumberofDatabases
)
-- Step 6 - Generate Report
SELECT *
FROM CapacityPlanning
WHERE ExecutionDateTime = @ExecutionDateTime
CLOSE CapPlanCursor
DEALLOCATE CapPlanCursor
SET NOCOUNT OFF
GO
Special Notes:
-------------
To the best of my knowledge, this script will work for SQL Server databases that are using third party backup products (SQL Server 2005 Backup Product Options). This is because the values in backup system tables in the MSDB database are capturing values from the SQL Server Virtual Device Interface (VDI) as opposed to the compressed values from the third party product. This means that the backups can give you a historical view to use as a reasonable planning tool.
All businesses change and therefore those applications change as well. Some applications are retired while others are brought into the production environment. As such, this script will not be able to help estimate the future growth without having a representative amount of backups for calculations.
On a related, if you know you are bringing on a new customer that is going to double the revenue for the organization, odds are that you are going to have to plan for this storage as well independent of the script listed in this tip. You may be able to anecdotally plan based on the script, but the script is making a prediction on the storage needs based on the historical trends.
Next Steps
----------:
Keep an eye on the available storage across your SQL Server 2000 instances. When you are faced with the daunting task of determining how much storage you will need to support your databases at a SQL Server instance level, consider this script.
As a proactive DBA, it would be a good idea to monitor the storage on your SQL Server instances to determine how long the current storage will support the current databases at the current growth rate.
Once you have calculated the amount of storage that is needed to support your databases at a SQL Server instance level, make sure you will have sufficient storage based on the RAID set, hot spares, etc. that are needed. Work with your vendor to figure out the needed storage based on the hardware capabilities and\or functionality.
How do I list all the indexes in a database?
http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-list-all-the-indexes-in-a-database.html
SQL Server 2000
===============:
Yes, you can use the following for a specific table:
EXEC sp_helpindex 'tablename'
This returns index_name, index_description, and index_keys. The index_description column tells whether or not the index is clustered, and which filegroup it resides on. The index_keys column tells you the column names that participate in the index, and from what I can tell, these are always in the order they are created (a negative symbol (-) denotes that the column is in DESC order).
This is great, but does not provide all of the information I'm often looking for.
In order to return everything I wanted to know about the indexes in my database, I needed to create a couple of extra helper functions. (Unfortunately, indexes are not covered in the INFORMATION_SCHEMA views, so we need to rely on system tables like sysindexes and sysfilegroups, and system functions like INDEXPROPERTY() and INDEX_COL().) The first function is not required, but makes the second function quite tidier, IMHO:
-- Returns whether the column is ASC or DESC
CREATE FUNCTION dbo.GetIndexColumnOrder
(
@object_id INT,
@index_id TINYINT,
@column_id TINYINT
)
RETURNS NVARCHAR(5)
AS
BEGIN
DECLARE @r NVARCHAR(5)
SELECT @r = CASE INDEXKEY_PROPERTY
(
@object_id,
@index_id,
@column_id,
'IsDescending'
)
WHEN 1 THEN N' DESC'
ELSE N''
END
RETURN @r
END
GO
-- Returns the list of columns in the index
CREATE FUNCTION dbo.GetIndexColumns
(
@table_name SYSNAME,
@object_id INT,
@index_id TINYINT
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE
@colnames NVARCHAR(4000),
@thisColID INT,
@thisColName SYSNAME
SET @colnames = INDEX_COL(@table_name, @index_id, 1)
+ dbo.GetIndexColumnOrder(@object_id, @index_id, 1)
SET @thisColID = 2
SET @thisColName = INDEX_COL(@table_name, @index_id, @thisColID)
+ dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID)
WHILE (@thisColName IS NOT NULL)
BEGIN
SET @thisColID = @thisColID + 1
SET @colnames = @colnames + ', ' + @thisColName
SET @thisColName = INDEX_COL(@table_name, @index_id, @thisColID)
+ dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID)
END
RETURN @colNames
END
GO
These functions are based largely on sp_helpindex, and while they avoid cursors, they are still not likely to be very efficient as the functions will need to be called multiple times.
Now that we have these functions, we can create this view:
CREATE VIEW dbo.vAllIndexes
AS
SELECT
TABLE_NAME = OBJECT_NAME(i.id),
INDEX_NAME = i.name,
COLUMN_LIST = dbo.GetIndexColumns(OBJECT_NAME(i.id), i.id, i.indid),
IS_CLUSTERED = INDEXPROPERTY(i.id, i.name, 'IsClustered'),
IS_UNIQUE = INDEXPROPERTY(i.id, i.name, 'IsUnique'),
FILE_GROUP = g.GroupName
FROM
sysindexes i
INNER JOIN
sysfilegroups g
ON
i.groupid = g.groupid
WHERE
(i.indid BETWEEN 1 AND 254)
-- leave out AUTO_STATISTICS:
AND (i.Status & 64)=0
-- leave out system tables:
AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0
GO
This will give you a handy resultset, but does not specify whether the index is a PRIMARY KEY CONSTRAINT. You can do that by joining against INFORMATION_SCHEMA.TABLE_CONSTRAINTS:
SELECT
v.*,
[PrimaryKey?] = CASE
WHEN T.TABLE_NAME IS NOT NULL THEN 1
ELSE 0
END
FROM
dbo.vAllIndexes v
LEFT OUTER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
ON
T.CONSTRAINT_NAME = v.INDEX_NAME
AND T.TABLE_NAME = v.TABLE_NAME
AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'
This doesn't take into account same-named tables owned by different users, but if you look at the alternative (see the source code for sp_pkeys), it is probably a valid solution for most of us, where dbo is the de facto owner of all objects.
With that limitation in mind, we can take it one step further by generating the CREATE INDEX / ADD CONSTRAINT statements:
SELECT
CASE WHEN T.TABLE_NAME IS NULL THEN
'CREATE '
+ CASE IS_UNIQUE WHEN 1 THEN ' UNIQUE' ELSE '' END
+ CASE IS_CLUSTERED WHEN 1 THEN ' CLUSTERED' ELSE '' END
+ ' INDEX [' + INDEX_NAME + '] ON [' + v.TABLE_NAME + ']'
+ ' (' + COLUMN_LIST + ') ON ' + FILE_GROUP
ELSE
'ALTER TABLE ['+T.TABLE_NAME+']'
+' ADD CONSTRAINT ['+INDEX_NAME+']'
+' PRIMARY KEY '
+ CASE IS_CLUSTERED WHEN 1 THEN ' CLUSTERED' ELSE '' END
+ ' (' + COLUMN_LIST + ')'
END
FROM
dbo.vAllIndexes v
LEFT OUTER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
ON
T.CONSTRAINT_NAME = v.INDEX_NAME
AND T.TABLE_NAME = v.TABLE_NAME
AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY
v.TABLE_NAME,
IS_CLUSTERED DESC
This is what I have to offer for now, and I realize it is pretty quick and dirty. I'll be working on a similar script using the SQL Server 2005 catalog views, but I'll save that for another day.
SQL Server 2000
===============:
Yes, you can use the following for a specific table:
EXEC sp_helpindex 'tablename'
This returns index_name, index_description, and index_keys. The index_description column tells whether or not the index is clustered, and which filegroup it resides on. The index_keys column tells you the column names that participate in the index, and from what I can tell, these are always in the order they are created (a negative symbol (-) denotes that the column is in DESC order).
This is great, but does not provide all of the information I'm often looking for.
In order to return everything I wanted to know about the indexes in my database, I needed to create a couple of extra helper functions. (Unfortunately, indexes are not covered in the INFORMATION_SCHEMA views, so we need to rely on system tables like sysindexes and sysfilegroups, and system functions like INDEXPROPERTY() and INDEX_COL().) The first function is not required, but makes the second function quite tidier, IMHO:
-- Returns whether the column is ASC or DESC
CREATE FUNCTION dbo.GetIndexColumnOrder
(
@object_id INT,
@index_id TINYINT,
@column_id TINYINT
)
RETURNS NVARCHAR(5)
AS
BEGIN
DECLARE @r NVARCHAR(5)
SELECT @r = CASE INDEXKEY_PROPERTY
(
@object_id,
@index_id,
@column_id,
'IsDescending'
)
WHEN 1 THEN N' DESC'
ELSE N''
END
RETURN @r
END
GO
-- Returns the list of columns in the index
CREATE FUNCTION dbo.GetIndexColumns
(
@table_name SYSNAME,
@object_id INT,
@index_id TINYINT
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE
@colnames NVARCHAR(4000),
@thisColID INT,
@thisColName SYSNAME
SET @colnames = INDEX_COL(@table_name, @index_id, 1)
+ dbo.GetIndexColumnOrder(@object_id, @index_id, 1)
SET @thisColID = 2
SET @thisColName = INDEX_COL(@table_name, @index_id, @thisColID)
+ dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID)
WHILE (@thisColName IS NOT NULL)
BEGIN
SET @thisColID = @thisColID + 1
SET @colnames = @colnames + ', ' + @thisColName
SET @thisColName = INDEX_COL(@table_name, @index_id, @thisColID)
+ dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID)
END
RETURN @colNames
END
GO
These functions are based largely on sp_helpindex, and while they avoid cursors, they are still not likely to be very efficient as the functions will need to be called multiple times.
Now that we have these functions, we can create this view:
CREATE VIEW dbo.vAllIndexes
AS
SELECT
TABLE_NAME = OBJECT_NAME(i.id),
INDEX_NAME = i.name,
COLUMN_LIST = dbo.GetIndexColumns(OBJECT_NAME(i.id), i.id, i.indid),
IS_CLUSTERED = INDEXPROPERTY(i.id, i.name, 'IsClustered'),
IS_UNIQUE = INDEXPROPERTY(i.id, i.name, 'IsUnique'),
FILE_GROUP = g.GroupName
FROM
sysindexes i
INNER JOIN
sysfilegroups g
ON
i.groupid = g.groupid
WHERE
(i.indid BETWEEN 1 AND 254)
-- leave out AUTO_STATISTICS:
AND (i.Status & 64)=0
-- leave out system tables:
AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0
GO
This will give you a handy resultset, but does not specify whether the index is a PRIMARY KEY CONSTRAINT. You can do that by joining against INFORMATION_SCHEMA.TABLE_CONSTRAINTS:
SELECT
v.*,
[PrimaryKey?] = CASE
WHEN T.TABLE_NAME IS NOT NULL THEN 1
ELSE 0
END
FROM
dbo.vAllIndexes v
LEFT OUTER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
ON
T.CONSTRAINT_NAME = v.INDEX_NAME
AND T.TABLE_NAME = v.TABLE_NAME
AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'
This doesn't take into account same-named tables owned by different users, but if you look at the alternative (see the source code for sp_pkeys), it is probably a valid solution for most of us, where dbo is the de facto owner of all objects.
With that limitation in mind, we can take it one step further by generating the CREATE INDEX / ADD CONSTRAINT statements:
SELECT
CASE WHEN T.TABLE_NAME IS NULL THEN
'CREATE '
+ CASE IS_UNIQUE WHEN 1 THEN ' UNIQUE' ELSE '' END
+ CASE IS_CLUSTERED WHEN 1 THEN ' CLUSTERED' ELSE '' END
+ ' INDEX [' + INDEX_NAME + '] ON [' + v.TABLE_NAME + ']'
+ ' (' + COLUMN_LIST + ') ON ' + FILE_GROUP
ELSE
'ALTER TABLE ['+T.TABLE_NAME+']'
+' ADD CONSTRAINT ['+INDEX_NAME+']'
+' PRIMARY KEY '
+ CASE IS_CLUSTERED WHEN 1 THEN ' CLUSTERED' ELSE '' END
+ ' (' + COLUMN_LIST + ')'
END
FROM
dbo.vAllIndexes v
LEFT OUTER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
ON
T.CONSTRAINT_NAME = v.INDEX_NAME
AND T.TABLE_NAME = v.TABLE_NAME
AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY
v.TABLE_NAME,
IS_CLUSTERED DESC
This is what I have to offer for now, and I realize it is pretty quick and dirty. I'll be working on a similar script using the SQL Server 2005 catalog views, but I'll save that for another day.
Abonneren op:
Posts (Atom)