March 2, 2011 at 7:15 am
There's no data in the tables, but the database keeps growing! It's up to 29 GB. Logfile is only 600 MB.
The database in question has only five tables. It supports a third-party application. I have six instances of this application, on other servers, which all behave as expected (DB sizes range from 5 to 10 GB). Application is configured to purge itself, and the rows are indeed purged on all of the instances, including this problem instance.
Yet the MDF shows "data", not "unallocated", and of course this ridiculous space can not be shrunk or otherwise recovered. (And yes, I know why we don't shrink databases.)
I will probably script the database and create a new one with new objects, then migrate the data with the import/export wizard. But it's not "fixed" until I know what either I or the third party app could have done to cause this.
Any ideas, suggestions, criticism, whatever?
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
March 2, 2011 at 7:24 am
Does this database act as a staging platform for a large ETL process, or anything similar to that? If so, it will grow to accommodate it's maximum data load, even if all of that data is purged after loading.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 2, 2011 at 7:32 am
GSquared (3/2/2011)
Does this database act as a staging platform for a large ETL process, or anything similar to that? If so, it will grow to accommodate it's maximum data load, even if all of that data is purged after loading.
Agreed, and have seen that thousands of times. But the data size in those cases is "available" when rows have been purged. A 29 GB database which only contains a few rows of actual data does not continue growing - it reuses available space.
I hesitate to turn off auto-grow, because this is a production app which is essential to the teller application for a regional bank, and I don't DARE "break" it for an experiment.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
March 2, 2011 at 7:34 am
Check the logs on the purge job. Make sure it's doing what it's supposed to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 2, 2011 at 7:46 am
The tables being purged contain ZERO rows. (Refer to the attached JPGs.) The data is in fact purged, but the disk space is not behaving like it's available.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
March 2, 2011 at 7:56 am
Can we see the table definitions plz?
March 2, 2011 at 8:05 am
Are there incomplete/uncommitted transactions showing?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 2, 2011 at 8:07 am
As you wish.
-- --
[font="Courier New"]CREATE TABLE [dbo].[ActivityLog](
[ActivityLogId] [bigint] IDENTITY(1,1) NOT NULL,
[InstitutionCode] [nvarchar](10) NOT NULL,
[EnvironmentName] [nvarchar](50) NOT NULL,
[ServiceRqUID] [uniqueidentifier] NOT NULL,
[MessageRqUID] [uniqueidentifier] NOT NULL,
[RqDate] [datetime] NOT NULL,
[RsDate] [datetime] NULL,
[Signon_SPName] [nvarchar](50) NULL,
[Signon_CustLoginId] [nvarchar](50) NULL,
[Signon_Role] [nvarchar](50) NULL,
[ServiceName] [nvarchar](50) NULL,
[SPName] [nvarchar](50) NULL,
[TransactionName] [nvarchar](50) NULL,
[AcctType] [nvarchar](50) NULL,
[AcctId] [nvarchar](100) NULL,
[ClientAppName] [nvarchar](50) NULL,
[CaseId] [nvarchar](10) NULL,
[Customer_TIN] [nvarchar](50) NULL,
[RefRqUID] [uniqueidentifier] NULL,
[ComputerId] [nvarchar](50) NULL,
[ClientAppKey] [nvarchar](50) NULL,
[RqXml] [ntext] NULL,
[RsXml] [ntext] NULL,
[RqExtXml] [ntext] NULL,
[RsExtXml] [ntext] NULL,
CONSTRAINT [PK_ActivityLog] PRIMARY KEY CLUSTERED
([ActivityLogId] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-- --
CREATE TABLE [dbo].[ActivityLogCust_Config](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EnvironmentName] [nvarchar](50) NOT NULL,
[SnapInName] [nvarchar](50) NOT NULL,
[SvcName] [nvarchar](50) NOT NULL,
[MsgName] [nvarchar](50) NOT NULL,
[DeletedFlag] [bit] NULL,
CONSTRAINT [PK_ActivityLogCust_Config] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ActivityLogCust_Config] ADD CONSTRAINT [DF_ActivityLogCust_Config_DeletedFlag] DEFAULT (0) FOR [DeletedFlag]
GO
-- --
CREATE TABLE [dbo].[ActivityLogExtended_Config](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EnvironmentName] [nvarchar](50) NOT NULL,
[SnapInName] [nvarchar](50) NOT NULL,
[SvcName] [nvarchar](50) NOT NULL,
[MsgName] [nvarchar](50) NOT NULL,
[Type] [nvarchar](10) NOT NULL,
[XPath] [nvarchar](1000) NOT NULL,
CONSTRAINT [PK_ActivityLogExtended_Config] PRIMARY KEY CLUSTERED
([ID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ActivityLogExtended_Config] WITH CHECK ADD CONSTRAINT [VV_ActivityLogExtended_Config_Type] CHECK (([Type] = 'Request' or [Type] = 'Response'))
GO
ALTER TABLE [dbo].[ActivityLogExtended_Config] CHECK CONSTRAINT [VV_ActivityLogExtended_Config_Type]
GO
-- --
CREATE TABLE [dbo].[Installation](
[Instance] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
-- --
CREATE TABLE [dbo].[Version](
[VersionId] [int] IDENTITY(1,1) NOT NULL,
[Major] [tinyint] NOT NULL,
[Minor] [tinyint] NOT NULL,
[ScriptName] [nvarchar](256) NOT NULL,
[ScriptType] [nvarchar](9) NOT NULL,
[AppliedDT] [datetime] NOT NULL,
[SystemUser] [nvarchar](256) NOT NULL,
[CabDT] [datetime] NULL,
[CabUID] [uniqueidentifier] NULL,
[CabVersion] [nvarchar](30) NULL,
CONSTRAINT [PK_Version] PRIMARY KEY CLUSTERED
(
[VersionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Version] WITH CHECK ADD CONSTRAINT [CK_Version_ScriptType] CHECK (([ScriptType]='structure' OR [ScriptType]='code' OR [ScriptType]='data'))
GO
ALTER TABLE [dbo].[Version] CHECK CONSTRAINT [CK_Version_ScriptType]
GO
ALTER TABLE [dbo].[Version] ADD CONSTRAINT [DF_Version_DateApplied] DEFAULT (getutcdate()) FOR [AppliedDT]
GO
ALTER TABLE [dbo].[Version] ADD CONSTRAINT [DF_Version_SystemUser] DEFAULT (suser_sname()) FOR [SystemUser]
GO[/font]
-- --
As can be seen in the "Disk Usage by Top Tables" atttachment, only the Version and Installation tables actually contain data, ten rows and one row respectively.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
March 2, 2011 at 8:11 am
I was first thinking unreleased spacefrom ntext or image datatypes. But it is being released so that can't be it (dbcc cleantable would have solved that).
The only real question is why do you need 29 GB? The only to answer that is log the activity and tables sizes during the ETLs.
Looks like GSquared's first instincts where dead on.
March 2, 2011 at 8:11 am
GSquared (3/2/2011)
Are there incomplete/uncommitted transactions showing?
dbcc opentran() shows "No active open transactions."
The database has a full backup Sunday night, differential Mon-Sat, and logs are backed up every 15 minutes.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
March 2, 2011 at 8:26 am
How big are the diffs and log (avg daily).
March 2, 2011 at 8:43 am
Ninja's_RGR'us (3/2/2011)
How big are the diffs and log (avg daily).
Mon diff = 200 MB
Tue diff = 4.6 GB
Today's log backups - idling 58k, between 10 and 14 MB during business hours
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
March 2, 2011 at 1:22 pm
This is a log shot but try running this to see if any system objects are taking up the space. system objects do not show up on the Disk Usage by Top Tables report
SELECT object_name(a.object_id),avg(a.rows),SUM(used_pages) as used, SUM(total_pages) as total
FROM sys.partitions a
inner join sys.allocation_units b
on a.partition_id = b.container_id
group by object_name(a.object_id)
order by SUM(used_pages)desc,avg(a.rows) desc
March 2, 2011 at 1:29 pm
Ninja's_RGR'us (3/2/2011)
I was first thinking unreleased spacefrom ntext or image datatypes. But it is being released so that can't be it (dbcc cleantable would have solved that).The only real question is why do you need 29 GB? The only to answer that is log the activity and tables sizes during the ETLs.
Looks like GSquared's first instincts where dead on.
I missed where he ran that, but I'm with you on first instinct, especially with these:
[RqExtXml] [ntext] NULL,
[RsExtXml] [ntext] NULL,
but I'd certainly agree that a trace/log is necessary here.
Another option would be to see what it looks like after a backup/restore. If the problem still exists, you'll at least have a test bed to explore whatever crazy options we come up with as a cure, without risking the real db. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 2, 2011 at 1:34 pm
Robert klimes (3/2/2011)
This is a log shot but try running this to see if any system objects are taking up the space. system objects do not show up on the Disk Usage by Top Tables report
SELECT object_name(a.object_id),avg(a.rows),SUM(used_pages) as used, SUM(total_pages) as totalFROM sys.partitions a
inner join sys.allocation_units b
on a.partition_id = b.container_id
group by object_name(a.object_id)
order by SUM(used_pages)desc,avg(a.rows) desc
You definitely found something.
First five rows as follows:
(No column name)(No column name)usedtotal
queue_messages_846626059339836631990103199419
sysdercv6796973484825484888
sysdesend67969734883648856
sysconvgroup67969733140131408
sysobjvalues2596186
What are they, and what can I safely do with them?
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply