October 9, 2014 at 9:23 am
I was asked to look at a database that was growing out of control. We release our initial DB as 1 gig. This gives it plenty of room for growth. This database is mostly static data.
First look at the DB it was 47 Gb. The 47 gb was in one table. I thought the issue was the data within the table. I exported the data into a text file to see what the size would be. It had LOB fields - Varbinary (Max), xml (Not Typed). The size was less than 500mb.
Second look 2-3 weeks later the DB was 97Gb. The table was suspect again. Even if I wanted to shrink the DB it would do nothing. The table was using all the space. The indexes were good, but I rebuilt them anyway. I wanted to see if it would continue to grow on a static dataset.
Next time the DB was over 100Gb. It was about a week later. As a last ditch effort i created a table mirroring the other. Inserted all the data into that. Then truncated the production table and re-inserted. When I inserted the data the table was at 200mb(ish). I then shrunk the DB leaving plenty of space and rebuilt all the indexes in the DB. Life is good for the moment.
What I researched to see why this could happen
1) Indexes - It was never the index. The index was fine
2) Jobs - there are no Jobs on the Server
3) LOB Fields - They seemed fine and the application was working normally
4) Replication - No replication on the server
5) Bat files or other processes - I did notice that there were some foreign tables in the DB. They said those tables were our original tables from way back when. They were empty so I do not think they are in use. They are just sitting there.
6) Admins - They all log in using one user. That user is SysAdmin (beyond my control) so i can't tell if other processes could be doing something with this table.
7) Simple Mode - This DB is in Simple mode and does get regularly backed up.
Questions -
1) Is there an easy way to Audit (Profiler) other processes? I want to monitor growth
2) Should I have looked for something else on the server as to why the DB is growing
3) The table with the issue is heavily used by the application. It is mostly static data. Could I cap the size of the table? I know I can cap the size of the DB. I would rather have a warning or something pop up when the table grows.
October 9, 2014 at 9:58 am
Is the table in question a heap? (no clustered index)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2014 at 10:15 am
The clustered index is on an int identity. There is a Unique Non-Clustered index on two unique-identifier fields
October 9, 2014 at 3:46 pm
Totally wild stab in the dark, but what's the fill factor on the index?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 9, 2014 at 4:07 pm
Quick questions
1) can you post the DDL for the table and all the indexes?
2) what is the result of select @@version
3) what kind of activity is on the table?
😎
October 9, 2014 at 4:20 pm
Version of SQL is
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1
<X64> (Build 7601: Service Pack 1)
Activity on the table is at start up of the application. It will hit the table and take all the records that belong to that user
DDL
CREATE TABLE [Common].[Foo](
[FooId] [uniqueidentifier] NOT NULL,
[FooBinaryData] [varbinary](max) NOT NULL,
[FooType] [nvarchar](255) NULL,
[CreatedDate] [smalldatetime] NOT NULL,
[RecordVersion] [timestamp] NOT NULL,
[SystemFooId] [int] IDENTITY(1,1) NOT NULL,
[ParentFooId] [int] NULL,
[Dependencies] [xml] NULL,
[FooXMLData] [xml] NULL,
[FooDataType] [tinyint] NOT NULL,
[OwnerId] [uniqueidentifier] NOT NULL,
[OwnerType] [tinyint] NOT NULL,
[DataVersion] [varchar](50) NOT NULL,
CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED
(
[SystemFooId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE NONCLUSTERED INDEX [IX_Settings] ON [Common].[Settings]
(
[FooId] ASC,
[OwnerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
October 10, 2014 at 2:29 am
Quick notes:
1) First of all this server needs patching, suggest you bring it up to SP3.
2) Read activity will not alter the data in any way, shape or form, focus on inserts, updates and deletes.
Question: what are the connection settings and the provider used for the data manipulation (everything but select)?
You can poke into the storage allocation with this query, gives a rough idea on what the space is used for
😎
SELECT OBJECT_NAME(SP.object_id),* FROM sys.partitions SP
INNER JOIN sys.allocation_units AU
ON SP.hobt_id = AU.container_id
WHERE SP.object_id = OBJECT_ID(N'Common.Foo')
October 27, 2014 at 9:38 am
I did some auditing and I can see the mdf growing because of that one table mentioned above 3 times (500mb X 3 =1.5Gb) in 12 hours. The source is not replication or anything on the server. It is coming from the .Net application we have them use. In the autogrow event I really can't see what forced the grow. I can just see the source and the login. The Login has SysAdmin rights. I am thinking the following to test this -
1) changing the permissions on the user to read/write on the DB. That could flush potential errors where maybe someone has some sort of upgrade procedure that is messing with the data.
2) Having them upgrade to SP 3. I am getting some resistance since this is the only DB that is having this issue. They also have this DB on other servers that is operating normally
Those are really the only things I can think of. When I move the data out and move it back in the data is less than 200mb and the DB shrinks without issue. Any other ideas would be great.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply