|
|
SQL Goodies -
Administration
|
|
Written by Datagod
|
|
Thursday, 20 August 2009 |
 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 am 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 grows the transaction log beyond all reason. What do I do? I shrink it! I am a shrinker, and I am not ashamed of it! :)
Now before you go an accuse me of being a "no-brainer" myself, hear me out. I am a DBA consultant. I have multiple large clients who have hundreds upon hundreds of databases. Most of these databases have not 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 organzation 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 are stuck with what we are given. New servers crop up faster than we can document, let alone manage. Since adding more disk space requires a reboot (It is an IBM product!) 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.
Microsoft's Books Online article for DBCC SHRINKDB (http://msdn.microsoft.com/en-us/library/ms190488.aspx) only briefly mentions fragmentation, almost as an aside.
"A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database." (emphasis mine)
Paul Randal describes the issue in greater detail on his blog: http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx
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?  |
|
Last Updated ( Thursday, 20 August 2009 )
|
|
Chef's Blog -
Tiny Stats
|
|
Written by Datagod
|
|
Friday, 13 June 2008 |
This is a realtime display of the current hits per hour that this website is getting. The chart updates every 5 seconds. The chart is rendered via javascript/flash and gets its information from a MySQL query. The data is translated into XML of course, prior to being fed to the flash file. This is all wrapped in PHP. Talk about complicated! 
|
|
Last Updated ( Friday, 13 June 2008 )
|
|
Chef's Blog -
Tiny Stats
|
|
Written by Datagod
|
|
Thursday, 12 June 2008 |
 For the past 6 months I have been fairly quiet on this site. The reason for this is that I have been focusing my attention on learning FusionCharts and FusionWidgets charting tools for my masterpiece MyTinyStats.com.
I love this product, they really did a great job on putting it all together. Considering how many charts and gadgets they provide, the documentation is incredible. My favorite part, however, is in writing PHP code to translate MySQL query results into XML.
I have spent countless hours on this project and am very pleased to say that the hard work is paying off. Not only is MyTinyStats starting to look really great, but I am also able to start selling my services to other clients. The money I make all goes back into paying for the mucho hardware/software being used to hold my little web empire together.
So, if you are looking for some help with PHP, MySQL and FusionCharts, I am the man to talk to. 
|
|
Last Updated ( Thursday, 12 June 2008 )
|
|
SQL Goodies -
Performance Tuning
|
|
Written by Datagod
|
|
Wednesday, 05 March 2008 |
 If you have deleted a large part of your table or updated many variable length rows, you should consider running the optimize command.
If you want to quickly generate the optimize commands for every table in you database, executing the following MySQL statement:
select concat("optimize table " , TABLE_NAME , ';')
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA <> 'information_schema' order by 1; |
|
SQL Goodies -
Administration
|
|
Written by Datagod
|
|
Wednesday, 03 October 2007 |
Have you ever wanted to retrieve a list of values from a table, storing the results in a string?
Here is simple yet effective method without having to rely on cursors, while loops, or other complex structures.
declare @string varchar(8000)
select @string = ''
select @string = @string + MyColumn
from MyTable
print @string
It is important to initialize your string to a non-NULL value, otherwise your string will end up as NULL, which is the default behavior of NULLs and string contcatenation.
Give it a try, it works like a charm.  |
|
The News -
Latest News
|
|
Written by Datagod
|
|
Saturday, 12 June 2004 |
 Cooking with SQL Launched Greetings! Have you ever had a craving for some SQL Pizza? What about a nice big bowl of Curried Clustered Indexes? Cooking with SQL has been in the planning stages for some time now. My goal is for this site to be a showcase for all the nifty procedures, scripts, functions, etc. that I have written over the years as part of my job as a DBA. I have a passion for cooking, and a passion for SQL, especially cooking with SQL. I love rising to the challenge of "It can't be done" or "you can't do that". I get enormous satisfaction out of using SQL to solve not just business problems but technical puzzles as well. I specialize in Microsoft SQL Server, but I am branching out to MySQL as well. I am currently engaged in a Data Warehouse project for a large Government client where I am using Microsoft SQL Server 2005, Analysis Services, Business Intelligence Development Studio (which includes SQL Server Integration Services). The concepts are old hat, but some of the tools are new. At the same time, I am quickly learning the ins-and-outs of Google Custom Search Engines and Google's Search AJAX API. Check out the SQL Hunter for a live demonstration of a work in progress. I actually find it quite useful for performing SQL related searches. Over the coming months, I will be writing many articles dealing with SQL Server 2000 and 2005 performance tuning, troubleshooting, and administrating. I will also be publishing an article titled "Log Shipping for the Masses" or something like that. Why upgrade to SQL 2000 Enterprise Edition when all you really want to do is take your production database and have a warm standby version somewhere else for disaster recovery / reporting purposes? Well, with my help, you won't have to. Stay tuned, I am cooking up some SQL goodies as you read this...
|
|
Last Updated ( Thursday, 05 April 2007 )
|
|
|
