June 2, 2009 at 8:36 am
Chris Howarth (6/2/2009)
It continues to amaze me that people don't use Integration Services (to pull data into a central repository) and Reporting Services to assist with tasks of this nature - particularly as they're both included 'in the box' and are relatively easy to use.
I think part of the reason SSIS isn't used is that it's a programming environment, and it's not familiar for many DBAs. Lots of people have sysadmin backgrounds and the idea of programming, even in the visual SSIS way, is foreign, and intimidating. However, they could use Linked Servers if they really needed to do this.
Also, most companies aren't "enterprises". Most companies are smaller, relatively few servers, and this would work.
I did work in an enterprise, and we had each server gather all its information, then rolling that up with SQLCMD instead of DTS (at the time) because it worked well. We stored it in a central server, and we would have a missing row(s) if one server didn't report information. I think if I had to do it, I would use SSIS, make simple transforms that pulled data from each server to a central location.
In that company, however, we did have to mark an Excel sheet with our initials that signified we had checked on the servers for ISO/SOX compliance. It's one thing to make a report, but another to be sure someone checked it (or at least signified they did). This would work for that, get an Excel sheet mailed to each person, let someone initial it and save it off as a verified report.
June 2, 2009 at 9:03 am
I never could get this to work
June 2, 2009 at 9:07 am
Its currnetly being edited. I used Bold characters to type the database name and it inturn has added HTML tags to it which the SQL Engine would not understand. I will waiting for the editor to repost the updated article
June 2, 2009 at 10:27 am
The table name have been corrected at all ends and Hopefully we shouldnt have users complaining about it anymore but I guess thats just wishful thinking
June 2, 2009 at 11:42 am
Good article. I am running something similar to extract report on daily basis. But, had not used Excel. Don't know if using Excel across different servers would be a good idea or not? This could be accomplished easily by SSRS, if you had got data in a table.
Any one here ?
SQL DBA.
June 2, 2009 at 11:44 am
I use a similar technique on a server I work with. The primary benefit of this information is to be able to talk to business owners in a way they understand. You can get user buy-in for a data retention policy when they understand what it would cost to keep large amounts of old data online.
[begin example]
ME: Mary, we have been tracking database growth. According to this chart we project that we will run out of space sometime in mid-November. Our choices are to either add disk space or cut back on the amount of data we store.
MARY: How much will the extra disk storage cost?
ME: For $XXXX we can get enough extra storage to keep us running for another 18 months. Then we will have to add additional storage.
MARY: How about cutting back on the data stored? How much data do we store now?
ME: We have transactional and summary data back to 2006. If we only kept 6 months of transactional data it would allow use to run indefinitely with the current storage. We could archive the older data in case we need to pull it up again for some reason.
[end example]
- Randall Newcomb
June 2, 2009 at 12:29 pm
Interesting Discussion so far.
My opinion is that autogrowth should be considered a contingency feature. Every time there is a growth there will likely be disk fragmentation as a result, so autogrowth should be kept to a minimum and databases sized with sufficient growth for a decent period of time. Fewer growths = less disk fragmentation.
So for me whilst i find it useful for capacity planning how big the database is over time, i tend to focus more on avoiding autogrowths and detecting them. This is a function and view I wrote which uses the default trace to report on the recent auto growths and shrinks.
USE [master]
GO
/****** Object: View [dbo].[vw_DBA_FileAutoGrowths] Script Date: 06/02/2009 19:09:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_DBA_DefaultTraceFilename]() RETURNS varchar(255) AS
BEGIN
DECLARE @Filename varchar(255)
SELECT @Filename=LEFT(t.Path,LEN(t.Path)-CHARINDEX('\',REVERSE(t.Path)))+'\log.trc'
FROM sys.traces t
CROSS JOIN sys.configurations c
WHERE c.name='default trace enabled' AND c.value=1
RETURN @Filename
END
GO
CREATE VIEW [dbo].[vw_DBA_FileAutoGrowths] AS
/*
Purpose: Returns a list of auto-growths and auto shrinks that have occurred across all the databases
Author: Michael I Wade
Created: 24 December 2008
Dependencies: dbo.udf_DBA_DefaultTraceFilename
*/
SELECT t.DatabaseName,
t.StartTime,
t.EndTime,
CASE WHEN t.EventClass IN (92,94) THEN 'Data File' ELSE 'Log File' END FileType,
CASE WHEN t.EventClass IN (92,93) THEN 'Auto-Grow' ELSE 'Auto-Shrink' END ChangeType,
t.[Filename],
t.IntegerData*8.0/1024 ChangeInKB,
t.ApplicationName,
t.LoginName
FROM ::fn_trace_gettable(dbo.udf_DBA_DefaultTraceFilename(),default) t
WHERE t.EventClass IN (92,93,94,95)
GO
Calling SELECT * FROM vw_DBA_FileAutoGrowths
will tell you all the recent growths and what application / login caused it (This bit is handy for those annoying users who do stupid things like carteasean join inserts!)
Feel free to use my code, but just give me credit if you republish it 🙂
June 2, 2009 at 5:51 pm
Go to Tools->References->
Check the latest version of Microsoft ActiveX Data Objects and say OK.
Now Hit Debug->Compile VBA Project. It shouldn't give you any error.
Now upon running the function it will populate the Cells from A2 onwards
Cheers,
Nitya
June 2, 2009 at 11:38 pm
Couple of points I noticed that I think would improve your solution.
1. The database table is not very normalized and included information not related to space, but related to state or mode of the DB. It might be better to create a Server table and reference this in your DBINFORMATION table. (or DBINFORMATION could be changed to DBSizes)
2. A simple query to master.sys.databases would get you a list of all the databases. (eliminating sp_msforeachdb) Since you don't have to be "in" each DB, you can run your query and only change the from statement using either a cursor or while loop.
3. The way your query is written, you are running this on a specific machine and storing it in the database on the target machine. Look into Powershell or C# or SSIS to reference a "server" table and collect information from multiple servers.
4. You might add the file type to your query.
case when FileProperty(Name,''IsLogFile'') = 1 then ''Log'' else ''Data'' end as FileType
You could then query based on the filetype versus having to parse the name if you were looking for Log versus Data files.
Hope that Helps
Bill
June 3, 2009 at 3:39 am
mike.wade (6/2/2009)
This is a function and view I wrote which uses the default trace to report on the recent auto growths and shrinks.
Mike,
Your function is useful because it returns extra information. If anyone just wants basic information about autogrowth for a particular database, it is available via the Standard Reports that can be executed from SSMS.
June 3, 2009 at 10:46 am
Does anyone have code to track space used/remaining under mount points? For example, xp_fixeddrives only reports what is visible at the root, not under the mount points themselves.
esc
June 4, 2009 at 6:10 am
Thanks. It's working now...
June 4, 2009 at 7:51 am
Mike.wade,
Like randall.c.newcomb mentioned, the format of the report was primarily meant to update the non-technical staff with information they would understand in their language. You code provides a mode drilled down view of db growth info...I am fine with it if you wish to tweak code to accomodate your stuff and republish it as a new article..It will be another way for extracting db information
Cheers
July 8, 2009 at 4:47 am
I think this article is a copy from http://www.mssqltips.com/tip.asp?tip=1426, So lets give some credit to that.
It works Ok, but do agree with the others that Excel is not the best way to use it. Reporting services and a SQL Job will be the best way to use it, instead of relying on Excel to register values.
August 20, 2009 at 8:24 am
I don't see a response to your question. For this error, I added a reference (Tools --> References) to Microsoft ActiveX Data Objects 2.5 Library since I'm using Excel 2003
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply