database size increasing
-
hi all,
sql server 2000
i have a d/b whose size is continously increasing. i would like to find out
1. is the d/b size increasing due to data growth
2. something has happened causing the db size to increase.
3. the growth rate is like in 7 days about 30 GB
i got the size of all the heavy used table. index size but when i add all these id does not match with the d/b size . am i missing something here
please can anybody guide me?
thnaks for your help.
-
I think its the transaction log file which is increasing. Check if the log file (.ldf) file size is increasing. If this is the case, check how frequently you take transaction log backup.
Regards
Sachin
-
Sachin,
thanks for the reply.
the d/b is in simple mode. so it is not the question of the transaction log backup.
-
Oh ok,
What is the database log file size then? I mean just the Ldf file size?
Also check if there is any error in the application which is retrying to finish. This can also cause the log file to grow.
-
check:
1) Do you run a reorganize database on a daily basis? That's blowing up your datafiles with a lot of unused space
2) Keep logging your tablesizes on a daily base (or more frequently if you like). That's a good point to start to see which table is rapidly growing.
3) Are the connections to the databases increased?
4) Did you have an application update?
Wilfred
The best things in life are the simple things -
What kind of a database this is? I mean how often transactions are happening on what size in this database. This may be due to heaving data load going to the database.
Susantha
-
Check for any open transactions using DBCC OPENTRAN('yourdbname')
[font="Verdana"]- Deepak[/font]
-
Execute Below Code and post the result here
use databasename
go
sp_spaceused
select * from sysfiles
Regards,
Raj
-
Sharon ,did you find the problem?
-
executed sp_spaceused and got the follwoing result for the heavily used table
Table Name|row_count|reserved|data|index size |unused|schema name
Notification|71603|44886096 KB|44875328 KB|4080 KB|6688 KB|dbo
WorkOrder|49323|44774984 KB|44760448 KB|7392 KB|7144 KB|dbo
**********************
NOTIFICATION TABLE SCRIPT
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Notification_Recipients_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Notification_Recipients] DROP CONSTRAINT Notification_Recipients_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Notification_Announcement_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Notify_Announcement] DROP CONSTRAINT Notification_Announcement_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Notify_Attachments_FK2]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Notify_Attachments] DROP CONSTRAINT Notify_Attachments_FK2
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Notify_Contract_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Notify_Contract] DROP CONSTRAINT Notify_Contract_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Notify_PO_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Notify_PO] DROP CONSTRAINT Notify_PO_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Notification_WorkOrder_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Notify_WorkOrder] DROP CONSTRAINT Notification_WorkOrder_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[POStatusHistory_FK5]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[POStatusHistory] DROP CONSTRAINT POStatusHistory_FK5
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Notification]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Notification]
GO
CREATE TABLE [dbo].[Notification] (
[NOTIFICATIONID] [bigint] NOT NULL ,
[NOTIFICATIONDESCRIPTION] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NOTIFICATIONTITLE] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTIFICATIONDATE] [bigint] NOT NULL ,
[SENDERID] [bigint] NOT NULL ,
[RECIPIENTID] [bigint] NULL ,
[MESSAGEID] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NOTIFICATIONTYPE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Notification] WITH NOCHECK ADD
CONSTRAINT [Notification_PK] PRIMARY KEY CLUSTERED
(
[NOTIFICATIONID]
) ON [PRIMARY]
GO
CREATE INDEX [Notification_IDX0] ON [dbo].[Notification]([SENDERID]) ON [PRIMARY]
GO
CREATE INDEX [Notification_IDX1] ON [dbo].[Notification]([RECIPIENTID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Notification] ADD
CONSTRAINT [Notification_FK1] FOREIGN KEY
(
[SENDERID]
) REFERENCES [dbo].[SDUser] (
[USERID]
),
CONSTRAINT [Notification_FK2] FOREIGN KEY
(
[RECIPIENTID]
) REFERENCES [dbo].[SDUser] (
[USERID]
)
GO
***********************
WORKORDER TABLE SCRIPT
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ApprovalStageMapping_FK2]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ApprovalStageMapping] DROP CONSTRAINT ApprovalStageMapping_FK2
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Drafts_Notification_FK2]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Drafts_Notification] DROP CONSTRAINT Drafts_Notification_FK2
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Notes_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Notes] DROP CONSTRAINT Notes_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Notification_WorkOrder_FK2]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Notify_WorkOrder] DROP CONSTRAINT Notification_WorkOrder_FK2
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Parent_Child_Req_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Parent_Child_Req] DROP CONSTRAINT Parent_Child_Req_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Parent_Child_Req_FK2]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Parent_Child_Req] DROP CONSTRAINT Parent_Child_Req_FK2
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RequestCharges_FK]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[RequestCharges] DROP CONSTRAINT RequestCharges_FK
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RequestOnHold_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[RequestOnHold] DROP CONSTRAINT RequestOnHold_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RequestResolution_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[RequestResolution] DROP CONSTRAINT RequestResolution_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RequestResolver_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[RequestResolver] DROP CONSTRAINT RequestResolver_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RequestRI_FK2]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[RequestRI] DROP CONSTRAINT RequestRI_FK2
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SurveyPerRequest_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SurveyPerRequest] DROP CONSTRAINT SurveyPerRequest_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WorkOrder_Fields_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[WorkOrder_Fields] DROP CONSTRAINT WorkOrder_Fields_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WorkOrder_Queue_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[WorkOrder_Queue] DROP CONSTRAINT WorkOrder_Queue_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WorkOrder_Recipients_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[WorkOrder_Recipients] DROP CONSTRAINT WorkOrder_Recipients_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WorkOrder_Threading_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[WorkOrder_Threaded] DROP CONSTRAINT WorkOrder_Threading_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WorkOrder_Threading_FK2]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[WorkOrder_Threaded] DROP CONSTRAINT WorkOrder_Threading_FK2
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WorkOrderAttachment_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[WorkOrderAttachment] DROP CONSTRAINT WorkOrderAttachment_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WorkOrderHistory_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[WorkOrderHistory] DROP CONSTRAINT WorkOrderHistory_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WorkOrderStates_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[WorkOrderStates] DROP CONSTRAINT WorkOrderStates_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WorkOrderToTaskDetails_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[WorkOrderToTaskDetails] DROP CONSTRAINT WorkOrderToTaskDetails_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WorkOrderToTaskTable_FK1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[WorkOrderToTaskTable] DROP CONSTRAINT WorkOrderToTaskTable_FK1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WorkOrder]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WorkOrder]
GO
CREATE TABLE [dbo].[WorkOrder] (
[WORKORDERID] [bigint] NOT NULL ,
[REQUESTERID] [bigint] NOT NULL ,
[CREATEDBYID] [bigint] NULL ,
[CREATEDTIME] [bigint] NOT NULL ,
[RESPONDEDTIME] [bigint] NOT NULL ,
[DUEBYTIME] [bigint] NOT NULL ,
[COMPLETEDTIME] [bigint] NOT NULL ,
[TIMESPENTONREQ] [bigint] NOT NULL ,
[TITLE] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DESCRIPTION] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MODEID] [int] NULL ,
[SLAID] [int] NULL ,
[WORKSTATIONID] [bigint] NULL ,
[DEPTID] [int] NULL ,
[SITEID] [bigint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkOrder] WITH NOCHECK ADD
CONSTRAINT [WorkOrder_PK] PRIMARY KEY CLUSTERED
(
[WORKORDERID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkOrder] WITH NOCHECK ADD
CONSTRAINT [DF__WorkOrder__RESPO__54CC6066] DEFAULT ('0') FOR [RESPONDEDTIME],
CONSTRAINT [DF__WorkOrder__COMPL__55C0849F] DEFAULT ('0') FOR [COMPLETEDTIME],
CONSTRAINT [DF__WorkOrder__TIMES__56B4A8D8] DEFAULT ('0') FOR [TIMESPENTONREQ]
GO
CREATE INDEX [WorkOrder_IDX0] ON [dbo].[WorkOrder]([REQUESTERID]) ON [PRIMARY]
GO
CREATE INDEX [WorkOrder_IDX1] ON [dbo].[WorkOrder]([MODEID]) ON [PRIMARY]
GO
CREATE INDEX [WorkOrder_IDX2] ON [dbo].[WorkOrder]([SLAID]) ON [PRIMARY]
GO
CREATE INDEX [WorkOrder_IDX3] ON [dbo].[WorkOrder]([DEPTID]) ON [PRIMARY]
GO
CREATE INDEX [WorkOrder_IDX4] ON [dbo].[WorkOrder]([SITEID]) ON [PRIMARY]
GO
CREATE INDEX [WorkOrder_IDX5] ON [dbo].[WorkOrder]([CREATEDBYID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkOrder] ADD
CONSTRAINT [WorkOrder_FK1] FOREIGN KEY
(
[REQUESTERID]
) REFERENCES [dbo].[SDUser] (
[USERID]
),
CONSTRAINT [WorkOrder_FK2] FOREIGN KEY
(
[MODEID]
) REFERENCES [dbo].[ModeDefinition] (
[MODEID]
),
CONSTRAINT [WorkOrder_FK3] FOREIGN KEY
(
[SLAID]
) REFERENCES [dbo].[SLADefinition] (
[SLAID]
),
CONSTRAINT [WorkOrder_FK4] FOREIGN KEY
(
[DEPTID]
) REFERENCES [dbo].[DepartmentDefinition] (
[DEPTID]
),
CONSTRAINT [WorkOrder_FK5] FOREIGN KEY
(
[SITEID]
) REFERENCES [dbo].[SiteDefinition] (
[SITEID]
),
CONSTRAINT [WorkOrder_FK6] FOREIGN KEY
(
[CREATEDBYID]
) REFERENCES [dbo].[SDUser] (
[USERID]
)
GO
-
hi all,
thnaks for all the help.
the problem was found. somebody accidentally inserted a lot of records in these tables. we are now in the process of deleting all the unwanted records.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply