InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 01:34:40 GMT20http://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxI couldn't get past a runtime error using the VBA script but found a more direct and easier way to get data from a SQL Server into Excel 2003. I used the wizard that opens from Data --> Import External Data --> Import Data and added a new data source pointing to our SQL Server (2005).Thu, 20 Aug 2009 08:26:15 GMTgrubbsdwhttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxI 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 2003Thu, 20 Aug 2009 08:24:24 GMTgrubbsdwhttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxI 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.Wed, 08 Jul 2009 04:47:38 GMTpanoslondon1http://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxMike.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 informationCheersThu, 04 Jun 2009 07:51:55 GMTlokeshgm7http://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxThanks. It's working now...Thu, 04 Jun 2009 06:10:01 GMTMatthieuQhttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxDoes 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.Wed, 03 Jun 2009 10:46:56 GMTes_caseyhttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspx[quote][b]mike.wade (6/2/2009)[/b][hr]This is a function and view I wrote which uses the default trace to report on the recent auto growths and shrinks.[/quote]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.Wed, 03 Jun 2009 03:39:41 GMTLempsterhttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxCouple 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 HelpsBillTue, 02 Jun 2009 23:38:07 GMTwsaharemhttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxGo 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 onwardsCheers,NityaTue, 02 Jun 2009 17:51:17 GMTNityahttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxInteresting 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[udf_DBA_DefaultTraceFilename]() RETURNS varchar(255) ASBEGINDECLARE @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=1RETURN @FilenameENDGOCREATE VIEW [dbo].[vw_DBA_FileAutoGrowths] AS/*Purpose: Returns a list of auto-growths and auto shrinks that have occurred across all the databasesAuthor: Michael I WadeCreated: 24 December 2008Dependencies: 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)GOCalling SELECT * FROM vw_DBA_FileAutoGrowthswill 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 :)Tue, 02 Jun 2009 12:29:36 GMTmike.wadehttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxI 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]Tue, 02 Jun 2009 11:44:55 GMTrandall.c.newcombhttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxGood 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 ?Tue, 02 Jun 2009 11:42:13 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxThe table name have been corrected at all ends and Hopefully we shouldnt have users complaining about it anymore but I guess thats just wishful thinkingTue, 02 Jun 2009 10:27:35 GMTlokeshgm7http://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxIts 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 articleTue, 02 Jun 2009 09:07:38 GMTlokeshgm7http://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxI never could get this to workTue, 02 Jun 2009 09:03:54 GMTlcarrethershttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspx[quote][b]Chris Howarth (6/2/2009)[/b][hr]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.[/quote]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.Tue, 02 Jun 2009 08:36:48 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxLokesh, the updated article is approved, so the corrections should be visible.Good job on the article.Tue, 02 Jun 2009 08:30:43 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxHey ....Thanks for the posts and feed back guys. I have corrected the mistake with the table name at the alter table statement. I have also created a user database called TRACKDBGROWTH and create the table within that database so as to free the sytem databases as per Microsoft's practice. I know this is not a perfect solution but I just created it as a simple report for my manager to query and see the information in excel and I know for sure this cannot be deployed at an enterprise level. Since the number of SQL installations is pretty low, this was a STOP and GO solution rather than full fledged monitoring deployment. I am sure this is possible with SQL Dashboard and SSIS too and possibly much easier. We also will go for a dedicated third party tool (I am currently evaluating SCOM and Quest Spotlight) eventually. But this article needs to be treated like a convenient store at the corner for basic commodities and not a Walmart.About using a central repository, the Site security doesnt provide me the option to run a distributed query to dump the data into one place from multiple machines. So yes That thought had crossed my mind.Tue, 02 Jun 2009 08:08:24 GMTlokeshgm7http://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxUsing the ADODB.* early binding requires you set a reference to ActiveX Objects in Excel. Within the VBA editor Tools>References and then look through the list for the most recent. Otherwise you will get an error.Tue, 02 Jun 2009 07:39:32 GMTbruce.nixhttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxFor an exercise on how to query database size and growth this is a fun project, but I would agree with other posts that this solution may not be enterprise ready yet.I would take the table you are using for tracking growth out of MSDB and put it in a User database instead. It's not best practice to use system db for custom user applications and you may find the table vanishes if you apply a service pack or hotfix at some point. It probably wouldn't, but better safe than sorry.The create DDL has an error in it - as someone else pointed out. The table created is called DBInformation and the ALTER statement is looking for DBInfo. I'm guessing you have both of those tables in MSDB and that's why you didn't get an error when testing the script before posting it here. You may want to drop that table in MSDB so you don't get confused later.Connecting Excel to multiple servers to get the information is not ideal - especially if you want to give the spreadsheet to others for review. There could be security issues involved that would require troubleshooting down the road. You may want to consider consolidating the information from many servers into 1 db on 1 reporting server. SSIS will make this very easy for you using variables, configurations, and expressions.You may also want to fire up SSRS for reporting as well to make things easier. That way you can review the results any time you wish or have it emailed to you at the an interval that is appropriate.Like I said, this is a good beginning to your monitoring project - and a great way to get familiar with some inner workings of SQL Server. Long term however, you will more than likely want a third party application to do this for you. The last thing you want is to be the person that has to maintain the custom solution and troubleshoot issues when they arise. Especially since you are using undocumented stored procedures in your code - this solution could break with any service pack or upgrade. Same goes for sp_MSForEachTable - it's undocumented and may be removed in newer releases. Good job! Happy hunting.Tue, 02 Jun 2009 07:33:13 GMTSimon Worthhttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspx[quote][b]Chris Howarth (6/2/2009)[/b][hr]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.[/quote]I couldn't agree more Chris. We use exactly the approach you mention, plus we set a configurable default threshold for each data and log file so that we get an email alert if a database is running short on space. That enables us to tweak the autogrowth settings (or take whatever action we deem appropriate) well before users notice anything untoward. This is also useful for identifying databases that have been incorrectly sized when created.LempsterTue, 02 Jun 2009 07:19:39 GMTLempsterhttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxThe first script is incorrect therefore should be corrected.The name of the table in the CREATE id dbInformation and in the ALTER is dbInfo.Please correct it.Lucas BenevidesDBA CabulosoTue, 02 Jun 2009 07:00:28 GMTDBA Cabulosohttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxMsForEachDB can be easily replaced by using variables in SSIS.But the idea is great!I have a complete set of scripts that works for sql2000 and higher edditions and it gives me detailed information on every instance, when I need it. The difference is,that I put that data in a separate DB.Tue, 02 Jun 2009 06:40:47 GMTgpitkishttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxThe solution in the article is much more detailed and I'm certainly going to try it, but if you regularly take backups and want a quick result, you can query db growth history like this (its the total dbsize, incl log and accurate as long as there's no compression involved):select database_name, BackupDate = convert(varchar(10),backup_start_date, 111) ,SizeInGigs=( backup_size/1024000000) from msdb..backupset where type = 'd'order by database_name, backup_start_date descTue, 02 Jun 2009 05:15:33 GMTive.henderickxhttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxThe solution seems to be okay for beginner levels or if doing for KB purposes. But I do not see it as an Enterprise level solutions but I rather go with SQL Server performance Dashboard if i am running SQL 2005 or higher.Also, when I tried this I got error message "User-Defined type not defined" on line "Dim cnPubs As ADODB.Connection":hehe:However, I appreciate the efforts for knowledge sharing.Tue, 02 Jun 2009 04:51:17 GMTVishal Singhhttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxWhile I think the article has merit, the fact that the table is created in msdb breaks another rule . . . . . . . I'm fairly sure that Microsoft strongly recoment that you dont use the system DB's for your own objectsCreate an 'Admin' database on each server and create all of your objects within that . . . you can still access the stored procedures and system tables in the system db's using the full syntax (database.schema.objectname).The use of undocumented stored procedures is always questionable . . . I have been caught out when they have been dropped . . . (last one was when they dropped xp_getfiledetails) . . . but there are alternatives, and certainly one of the prime considerations when you are upgrading to another version of SQL Server should be . . . what will stop working when we do thisTue, 02 Jun 2009 04:47:56 GMTBlackpool Council DBA Teamhttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxsp_MSForEachDB has been around since at least SQL Server 6.5, - it is [b]UNDOCUMENTED[/b] and, therefore, should not be used in Production code.ChrisTue, 02 Jun 2009 04:00:31 GMTChris Howarth-536003http://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxFor me, the best thing about this post was the mention of the stored procedure [b]sp_Msforeachdb[/b], which I've not heard of before - it's a very useful tool and I can see I will be using it a lot!Also worth mentioning that the code works in both SQL 2000 and SQL 2008 - always useful when you have a mixture of versions to support.Tue, 02 Jun 2009 03:50:05 GMTjts_2003http://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxThe idea of the central reporting of database statistics is great, but surely using Excel to connect to each SQL Server instance and generate reports like this can't be considered to be an Enterprise-level solution?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.ChrisTue, 02 Jun 2009 02:07:18 GMTChris Howarth-536003http://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxMy workplace was similar to your's - they'd never had a DBA before (and I'd never been one before either). I got tasked with a similar thing - but I used SQLH2 - its a free microsoft tool that does something very similar to what you are doing here.Mon, 01 Jun 2009 22:42:37 GMTLouise Moorehttp://www.sqlservercentral.com/Forums/Topic727128-1559-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Monitoring/66257/">Tracking and Reporting Database Growth</A>[/B]Mon, 01 Jun 2009 22:39:30 GMTlokeshgm7