http://www.sqlmag.com/authors/author/author/5738030/rsscomment/5738030en-USFri, 25 May 2012 08:24:17 GMTFri, 25 May 2012 08:24:17 GMThttp://www.sqlmag.com/article/database-administration/intelligent-backups-revisited#commentsAnchorMon, 10 Jan 2011 10:18:47 GMT
The correct .zip file has now been uploaded to this article. Very sorry for the delay and confusion.

Brian Reinholz,
SQL Server Magazine]]>Brian ReinholzMon, 10 Jan 2011 10:18:47 GMThttp://www.sqlmag.com/article/database-administration/intelligent-backups-revisited#commentsAnchorhttp://www.sqlmag.com/article/database-administration/intelligent-backups-revisited#commentsAnchorMon, 13 Dec 2010 12:55:19 GMT
Please provide the correct URL for code download.]]>patelMon, 13 Dec 2010 12:55:19 GMThttp://www.sqlmag.com/article/database-administration/intelligent-backups-revisited#commentsAnchorhttp://www.sqlmag.com/article/database-administration/intelligent-backups-revisited#commentsAnchorSat, 11 Dec 2010 19:27:16 GMT
Download the Code Here points to the first article 125991.zip]]>ROTCHFORDSat, 11 Dec 2010 19:27:16 GMThttp://www.sqlmag.com/article/database-administration/intelligent-backups-revisited#commentsAnchorhttp://www.sqlmag.com/article/tsql3/dbcc-checkdb-for-very-large-databases#commentsAnchorFri, 29 Jan 2010 15:01:50 GMT
Hi Robbert, I passed your question on to David Paul Giroux; his response is below: "You are correct, running DBCC CHECKALLOC, CHECKTABLE, and/or CHECKCATALOG will not update the Value for the Field “dbi_dbccLastKnownGood” from DBCC INFO. In our environment we have DBCC scheduled every night in some capacity so we do not refer to the output from DBCC INFO. However, there are a couple of things you can do. If you really want to use that field then I would add “DBCC CHECKDB() WITH ESTIMATEONLY, NO_INFOMSGS” as the last step in the job. The command will only run for about a second on even very large databases. It took 1 second on a 1.5TB database that I tested it on. Even though the command is only producing some estimate values – it actually updates the value for dbi_dbccLastKnownGood. That would be the simplest and you would not need to make further changes to your current system. Alternatively, you could create a table and update it every time you perform a non DBCC CHECKDB command. You could also check the default trace using a query similar as follows: SELECT TextData, StartTime, DatabaseName FROM sys.fn_trace_gettable((select [path] from sys.traces where is_default = 1) , default) WHERE TextData LIKE ’DBCC%’ ORDER BY StartTime desc GO Of course, that data will eventually “roll over.” Hope this helps." Please feel free to contact me directly at mkeller@sqlmag.com if you have any more questions. Thanks! Megan Keller Associate Editor, SQL Server Magazine]]>MeganFri, 29 Jan 2010 15:01:50 GMThttp://www.sqlmag.com/article/tsql3/dbcc-checkdb-for-very-large-databases#commentsAnchorhttp://www.sqlmag.com/article/tsql3/dbcc-checkdb-for-very-large-databases#commentsAnchorThu, 17 Dec 2009 03:24:24 GMT
Hi David, I want to implement this procedure in our environment, but I ran against this issue. We check all the databases on the "Last Known Good Checkdb" available through DBCC DBINFO. Your procedure does not update this field. How do you check regulary that a checkdb has occurred? thanks, Robbert]]>RobbertThu, 17 Dec 2009 03:24:24 GMThttp://www.sqlmag.com/article/tsql3/dbcc-checkdb-for-very-large-databases#commentsAnchorhttp://www.sqlmag.com/article/tsql3/utilities-assess-data-file-usage-and-provide-commands-so-you-can-do-something-about-it#commentsAnchorSat, 22 Aug 2009 11:41:08 GMT
Of all the things to get excited about in the MSSQL world, I never thought shrinking databases would be one of them. To me, it has always been a no-brainer. I’m running out of disk space, and I find a database with a ton of free space. What do I do? I shrink it! Somebody issues a massive update statement that grows the transaction log beyond all reason. What do I do? I shrink it! I’m a shrinker, and I’m not ashamed of it! Now before you go an accuse me of being a "no-brainer" myself, hear me out. I’m a DBA consultant. I have multiple large clients who have hundreds upon hundreds of databases. Most of these databases haven’t been given the love and attention they deserve. As a result, I often inherit poorly managed systems and end up spending most of my time putting out fires. The single most occurring fire is low disk space. Not every organization is blessed with endless terabytes of disk storage. One of my clients has a large virtual server farm, and each virtual server gets just about enough disk space to hold the main database and a few backup files. Yes, I know, I have more space on my MP3 player, but this is the "do more with less" world I work in. Unless the DBA team cries for more space, we’re stuck with what we’re given. New servers crop up faster than we can document, let alone manage. Since adding more disk space requires a reboot, we usually have to shrink the data files and transaction log in order to keep the server up and running. That being said, a DBA should still exercise caution prior to shrinking/truncating a live production database. Outages need to be scheduled, backups need to be confirmed, etc. As the author stated in his response, sometimes you have to do what you have to do. And his script helps me do just that. What it comes down to is that if you shrink a database, you really need to defragment the indexes and data pages. But you are already doing that regularly anyway, right? Posted on behalf of Bill McEvoy]]>KarenSat, 22 Aug 2009 11:41:08 GMThttp://www.sqlmag.com/article/tsql3/utilities-assess-data-file-usage-and-provide-commands-so-you-can-do-something-about-it#commentsAnchorhttp://www.sqlmag.com/article/tsql3/utilities-assess-data-file-usage-and-provide-commands-so-you-can-do-something-about-it#commentsAnchorThu, 20 Aug 2009 16:31:55 GMT
David - thanks for responding. When you say "The article is narrow in scope and gives NO ADVICE," it seems to ignore one of the early lines in the article: "In order to grow the data file for DB without depleting all available disk space, you must first free up some disk space by shrinking other data files." You’re telling the DBA that their only option - not one of, but their ONLY option - is to shrink other data files. That’s bad advice, and that’s what we’re trying to avoid. One other option might be to find out why the other data files are large, and get the right amount of disk space to handle the problem. Otherwise, with your solution, you’re recommending that they keep growing and shrinking different files, which ends up being a fragmentation nightmare. If you could have included the other options in the article, just like you explained them in the comments, then the article would have stood great on its own. Otherwise, it comes off as an instruction manual on how to build a handgun. While some publications have no problem explaining how to build a handgun, my hope is that we strive for a higher level of education at SQL Server Magazine. If we start publishing articles on how to truncate tables or how to format drives - without explaining the risks and drawbacks - then we’re doing a bad job of educating the community.]]>BrentThu, 20 Aug 2009 16:31:55 GMThttp://www.sqlmag.com/article/tsql3/utilities-assess-data-file-usage-and-provide-commands-so-you-can-do-something-about-it#commentsAnchorhttp://www.sqlmag.com/article/tsql3/utilities-assess-data-file-usage-and-provide-commands-so-you-can-do-something-about-it#commentsAnchorThu, 20 Aug 2009 15:53:59 GMT
We asked the David to respond to these comments. He noted that, "The script is a tool. The script does not make any changes. The article is narrow in scope and gives NO ADVICE. The article is not in regards to file management and does not attempt to be all encompassing. There are countless articles regarding file management, I don’t desire to write another one. No DBA should attempt to read one single article on one single issue and expect his/her training to be complete. Learn file management elsewhere. As Paul Randal states in his post ’Why you should not shrink your data files’: ’Bottom line - TRY TO AVOID running data file shrink at all costs!’ (I added the emphasis on ’try to avoid’.) If the competent DBA decides a file needs to be shrunk OR GROWN, this script can assist the DBA." "I created this script because of one particular server that had 24 databases and no free disk space. One of the databases ran out of database free space. I could not unilaterally truncate tables, or move databases to a different server or add physical drives. The only thing I could do instantly was rob Peter to pay Paul. Yes. I shrunk the file of Database A so I could increase the file for Database B. That gave me time to THEN come up with a long term solution." Karen Bemowski, senior editor, SQL Server Magazine, Windows IT Pro]]>KarenThu, 20 Aug 2009 15:53:59 GMThttp://www.sqlmag.com/article/tsql3/utilities-assess-data-file-usage-and-provide-commands-so-you-can-do-something-about-it#commentsAnchorhttp://www.sqlmag.com/article/tsql3/utilities-assess-data-file-usage-and-provide-commands-so-you-can-do-something-about-it#commentsAnchorWed, 19 Aug 2009 15:49:01 GMT
This is really, really, REALLY dangerous advice that can cause performance problems. I responded to it in full here: http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/]]>BrentWed, 19 Aug 2009 15:49:01 GMThttp://www.sqlmag.com/article/tsql3/utilities-assess-data-file-usage-and-provide-commands-so-you-can-do-something-about-it#commentsAnchorhttp://www.sqlmag.com/article/tsql3/utilities-assess-data-file-usage-and-provide-commands-so-you-can-do-something-about-it#commentsAnchorWed, 19 Aug 2009 15:45:27 GMT
I’m sorry but you really shouldn’t be shrinking databases in the first place. No offense... http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/]]>JorgeWed, 19 Aug 2009 15:45:27 GMThttp://www.sqlmag.com/article/tsql3/utilities-assess-data-file-usage-and-provide-commands-so-you-can-do-something-about-it#commentsAnchorhttp://www.sqlmag.com/article/tsql3/utilities-assess-data-file-usage-and-provide-commands-so-you-can-do-something-about-it#commentsAnchorSat, 08 Aug 2009 20:21:17 GMT
.... disregard previous comment - works fine in SQL 2005. Need some modifications to work on sql 2000.]]>GarySat, 08 Aug 2009 20:21:17 GMThttp://www.sqlmag.com/article/tsql3/utilities-assess-data-file-usage-and-provide-commands-so-you-can-do-something-about-it#commentsAnchorhttp://www.sqlmag.com/article/tsql3/utilities-assess-data-file-usage-and-provide-commands-so-you-can-do-something-about-it#commentsAnchorSat, 08 Aug 2009 20:20:27 GMT
This sounded like a great script, however there seems to be a problem with it. When you try to run the ’CandidateFileSizeMgmtCommands.sql’ against a SQL 2000 or SQL 2005 database you get the error ’EXECUTE cannot be used as a source when inserting into a table variable.’ at this line of code ’INSERT INTO @DiskInfo EXEC master..xp_fixeddrives’. Not sure how anyone has actually been able to run the script as posted.]]>GarySat, 08 Aug 2009 20:20:27 GMThttp://www.sqlmag.com/article/tsql3/utilities-assess-data-file-usage-and-provide-commands-so-you-can-do-something-about-it#commentsAnchor