Database Growth

Question

Post reply

Database Growth

TaoCanis

Old Hand

Points: 352
More actions
March 27, 2008 at 10:23 am

#183911

Hello,
Is there a way, by either looking in a log file or through T-SQL, to tell if a database has grown and, if so, when it happened? I can't seem to find anything pointing me in this direction, but I figured I'd throw it out here and see if anyone had any answers or advice. Thanks once again.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Cory E. SSCertifiable Points: 7076 More actions · Answer 1

I just checked the SQL Log File Viewer, and I see auto grows being logged..

Is this what you are looking for?

-- Cory

TaoCanis Old Hand Points: 352 More actions · Answer 2

I reckon that's it. Thank you Cory! Do you know if this information might also be stored in a system table somewhere?

Cory E. SSCertifiable Points: 7076 More actions · Answer 3

I am fairly sure the answer is no. the log files are located ..\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG. I think there are ways to query these log files, but, I dont know them atm.

-- Cory

TaoCanis Old Hand Points: 352 More actions · Answer 4

TaoCanis

Old Hand

Points: 352

March 27, 2008 at 11:32 am

#794776

Ok. Thanks again, Cory.

Babu_Raj Newbie Points: 6 More actions · Answer 5

Babu_Raj

Newbie

Points: 6

March 31, 2008 at 12:17 pm

#796099

EXEC sp_track_db_growth

GO

Cory E. SSCertifiable Points: 7076 More actions · Answer 6

Babu_Raj (3/31/2008)
EXEC sp_track_db_growth
GO

Usually, there needs to be an existing SP prior to running a SP:cool:. sp_track_db_growth is not a SP that exists. Perhaps you would be kind enough to post the SP you have created? 🙂

-- Cory

tosscrosby SSCoach Points: 18045 More actions · Answer 7

I personally don't care when it happened, I just want to know it happened. On a weekly basis I run the following. It stores the data for me in a table and I check my data from time to time. It's great to use the metrics to "forecast" disk space needs.

CREATE PROCEDURE usp_DB_SpacedUsed AS

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

-- Declare local variables

EXEC master..sp_MSForeachdb '

USE [?]IF DB_ID(''?'')>4

BEGIN

insert into DBMaint..DBSizeUsed

SELECT name AS [File], filename as File_Name

, CAST(size/128.0 as DECIMAL(10,2)) AS Size_in_MB

, CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 as DECIMAL(10,2)) as Space_Used

, CAST(size/128.0-(FILEPROPERTY(name, ''SpaceUsed'')/128.0) AS DECIMAL(10,2)) AS Available_Space

, getdate() as RunDate

FROM SYSFILES

--BEGIN

--PRINT ''?''

--END

END'

GO

-- You can't be late until you show up.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 8

Babu_Raj (3/31/2008)
EXEC sp_track_db_growth
GO

I agree... such a sproc is NOT provided by MS in any installation of SQL Server 2000... would you post the code for this sp_track_db_growth sproc that you use?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 9

From Vyas Kondreddi (former SQL Server MVP):

sp_track_db_growth

K. Brian Kelley
@kbriankelley

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 10

Thanks, Brian...

Just so everyone knows, sp_track_db_growth does not even come close to what the OP asked for. Rather, it tracks what size the backup files are. It does not detect when a DB growth has occured nor does it figure out the true size of the DB.

For example, try running it against TempDB and see what happens. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 11

Cory Ellingson (3/27/2008)
I just checked the SQL Log File Viewer, and I see auto grows being logged..
Is this what you are looking for?

Cory... I've not had to use the SQL Log File Viewer before... I've had to use the Error log before. Are they the same? If not, how do I get to the SQL Log File Viewer you spoke of in SQL Server 2000 and 2005?

Thanks...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Cory E. SSCertifiable Points: 7076 More actions · Answer 12

Jeff Moden (3/31/2008)
Cory... I've not had to use the SQL Log File Viewer before... I've had to use the Error log before. Are they the same? If not, how do I get to the SQL Log File Viewer you spoke of in SQL Server 2000 and 2005?
Thanks...

Hey Jeff -

thanks for asking, and pointing out that maybe some may not know what I was talking about. In both SQL 2000 and SQL 2005 SSMS, there is a SQL Server Logs stored under "Management" -> "SQL Server Logs". In these, there are events such as DB growth happening. There are ways to use some sort of viewer to read these, or you can use the SSMS tool.

Let me know if any one has trouble finding this.

PS - Thanks Jeff - I am not sure if you were prodding me to learn by teaching, or what, but thank you. 🙂

-- Cory

Cory E. SSCertifiable Points: 7076 More actions · Answer 13

I found this article from our very own Brian Knight.

http://www.sqlservercentral.com/articles/Administering/readpurgelog/279/

-- Cory

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 14

Jeff Moden (3/31/2008)
Thanks, Brian...
Just so everyone knows, sp_track_db_growth does not even come close to what the OP asked for. Rather, it tracks what size the backup files are. It does not detect when a DB growth has occured nor does it figure out the true size of the DB.
For example, try running it against TempDB and see what happens. 😉

True, but it's not unusual to track backup sizes as a reflection of database growth. Especially full backups.

K. Brian Kelley
@kbriankelley