Big Master database afetr multiple restores. 1

[AKSQL]

All:

I have a remote/offsite server that drives my clients web site that seems to be having a problem. On a weekly basis they back up their working database, move the backup to the remote server, and restore it. This process, although still too manual, works quite well but for one problem... The master database on the remote server has grown unaccountably large (2 gig) especially as compared to the local Master db (2M). All these servers do is serve this one database so that is not the issue. Does restoring a db add to the size of Master? If not, can you think of anything that might?? If so why? and is there anything I can do about it??

 

[Corran007]

well lets start by looking at what in the master is taking up all that size.

/**************************************************************************************
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* graz@sqlteam.com
* v1.1
*
**************************************************************************************/

declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)

create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)

set nocount on

-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'

open c_tables

fetch next from c_tables
into @id

while @@fetch_status = 0
begin

/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id

select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where objid = @id


/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where objid = @id

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id

update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id

fetch next from c_tables
into @id
end


select top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')

from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc

drop table #spt_space
close c_tables
deallocate c_tables


run that script. that will post the top 25 tables in size from a database. i run quite a few restores on my test system and its not even close to that. its at 17mb. lets see whats taking up the space and go from there.

 

[AKSQL]

Awesome. Will do that this afternoon at the client site.

 

[AKSQL]

OK - here are the results:

[tt]
Table Rows Reservd Data Index Unused
Loc 825416 2033424K 2011128K 136K 22160K
Lft 215448 281872K 275968K 64K 5840K
Doc 844288 109184K 60184K 48952K 48K
Wof 7162 12496K 12288K 16K 192K
Toc 13920 2752K 1248K 1376K 128K
TD61 5947 1088K 1032K 16K 40K
PV#toc 5753 520K 496K 8K 16K
Ann 1553 480K 264K 80K 136K
Orders 830 472K 160K 296K 16K
WorkTOC 5753 384K 240K 136K 8K
Employees 9 328K 240K 48K 40K
Order Details 2155 312K 72K 200K 40K
Categories 8 184K 120K 32K 32K
spt_values 728 136K 56K 80K 0K
Suppliers 29 136K 32K 48K 56K
Queue 1967 120K 48K 72K 0K
Stamp 5 104K 56K 16K 32K
Products 77 104K 8K 96K 0K
Customers 92 104K 24K 80K 0K
TD68 3 88K 8K 80K 0K
TD69 1 88K 8K 80K 0K
Tstr 11 56K 8K 48K 0K
Acl 56 56K 8K 48K 0K
GrpList 21 48K 8K 40K 0K
TD67 377 48K 16K 32K 0K

[/tt]

I'll be cracking the book to see if I can start to understand it -- but look forward to hearing your reactions.

 

[gradley]

Nice script Corran007.

Very useful!

 

[SQLSister]

If you ran that on the master database, then you should know that none of those are table names in Master. Perhaps they are somehow loading the data tables in master?

Questions about posting. See faq183-874

 

[AKSQL]

Wow. I apologise for even posting that result set to the forum. I was trying to rush home and did not even look at the table names.

I did, in fact, run Bill's script (the most useful script I've seen in a long while -- thanks VERY much) against the master database and those tables do not belong there (as SQLSister pointed out) and that is the source of the problem.

As of this moment I am fairly baffled as to how those table got in there (I do know where the came from) but the mystery of what made the master db so big is solved(enough).

Thanks to all who contributed.

 

[dbomrrsm]

another similar method -

IF EXISTS (select * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tablestats]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[tablestats]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO
CREATE PROCEDURE tablestats AS
SET nocount ON
DECLARE @spt_space TABLE
(
	tbname nvarchar(25),
rows int null,
	reserved dec(15) null,
	data dec(15) null,
	indexp dec(15) null,
	unused dec(15) NULL
)
DECLARE @objname nvarchar(776)
DECLARE @id int			
DECLARE @type character(2)
DECLARE	@pages int		
DECLARE @dbsize dec(15,0)
DECLARE @logsize dec(15)
DECLARE @bytesperpage dec(15,0)
DECLARE @pagesperMB dec(15,0)
DECLARE @row int
DECLARE TBLIST CURSOR
READ_ONLY
FOR SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE'
INSERT INTO @spt_space (tbname) SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE'
OPEN TBLIST
FETCH NEXT FROM TBLIST INTO @objname
WHILE (@@fetch_status <> -1)


     BEGIN
     IF (@@fetch_status <> -2)


         BEGIN
         SELECT @id = NULL
         SELECT @id = id, @type = xtype FROM sysobjects WHERE id = object_id(@objname)
         SELECT @pages = sum(dpages) FROM sysindexes WHERE indid < 2 AND id = @id
         SELECT @pages = @pages + isnull(sum(used), 0) FROM sysindexes WHERE indid = 255 AND id = @id
         SELECT @row = i.rows FROM sysindexes i WHERE i.indid < 2 AND i.id = @id
         INSERT INTO @spt_space (reserved) SELECT sum(reserved) FROM sysindexes WHERE indid in (0, 1, 255) AND id = @id
         UPDATE @spt_space SET data = @pages WHERE tbname=@objname
         UPDATE @spt_space SET indexp = (select sum(used) FROM sysindexes WHERE indid in (0, 1, 255) AND id = @id) where tbname=@objname
         UPDATE @spt_space SET unused = reserved - (select sum(used) FROM sysindexes WHERE indid in (0, 1, 255) AND id = @id) where tbname=@objname
         UPDATE @spt_space SET rows = @row WHERE tbname=@objname
         UPDATE @spt_space SET reserved = (select sum(reserved) FROM sysindexes WHERE indid in (0, 1, 255) AND id = @id) where tbname=@objname
         UPDATE @spt_space SET indexp = (select sum(used) FROM sysindexes WHERE indid in (0, 1, 255) AND id = @id) - data where tbname=@objname
         UPDATE @spt_space SET unused = reserved - (select sum(used) FROM sysindexes WHERE indid in (0, 1, 255) AND id = @id) where tbname=@objname
     END
     FETCH NEXT FROM TBLIST INTO @objname
 END
CLOSE TBLIST
DEALLOCATE TBLIST
SELECT tbname,
 rows = convert(char(11), rows),
 reserved = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
 data = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
 index_size = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
 unused = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')
FROM @spt_space, master.dbo.spt_values d
WHERE d.number = 1 AND d.type = 'E' and tbname IS NOT NULL
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
GRANT EXECUTE ON [dbo].[tablestats] TO [public]
GO

[blue]DBomrrsm[/blue]