SSWUG TSQL Workshop: Coming this Friday, 11/12 "TSQL for the Rest of Us" – Tomorrow
Time is getting short. Don’t forget to look at the opportunity you have to attend the workshop tomorrow. Stephen Wynkoop will be presenting this workshop providing a foundation for those working with SQL Server databases. His focus is the key concepts of TSQL used to design, create, manage and retrieve data from a SQL Server database. Click here to visit the workshop page. You can read or get more info from the online video, and sign up to attend. Take the time to review the contents to see if you need to attend.
SQL Server 2011 CTP Released
That buzzing in your ear may be the noise of people getting their first look at the next version of SQL Server 2011 in the Community Technical Preview Microsoft released yesterday.
SQL-PASS 2010 Keynote Addresses Streamed on the Web
For those of us not able to attend this year, you can View The Tuesday Keynote Presentation over the web. The others will be streamed as well.
Featured White Paper(s)
Free Poster – SQL Server Perfmon Counters of Interest
When you’re looking for a reliable tool to diagnose SQL Server issues, Perfmon is often the answer. No more wasting time trac… (read more)
Featured Script
Show index information for all tables in a database
This stored procedure can be added to any database and executed to display a printable list of tables and indexes. (Uses prin… (read more)
Shrinking Your Database Files
One of the things I like to do for my frequently changing databases is to defragment on a nightly basis. I have one 18 GB database that almost 100% of the data is updated at least once per day. As you can imagine, the database becomes fragmented very quickly.
In order to keep things working at top speed, I found it important to do index reorganization every half hour. The index reorganization simply reorganizes the indexes at the leaf level. None of the higher level nodes that would require moving data to different pages are updated with a reorganization.
Still, the tables and indexes need to be defragmented completely which I accomplish by re-indexing the entire table and all non-clustered indexes at the slowest time in our production cycle. This is where the enterprise edition of SQL Server comes in handy because you can do the re-indexing with less impact on production use.
What does this have to do with shrinking the database files? Well, unless your tempdb is bigger than the largest table you have to re-index, SQL server will use the files of the database itself for sorting. After re-indexing I end up with 30-40 GB of un-used space. So, I shrink the database down to recover some of the disk in case it is needed by another database.
Ouch…yesterday I’m reading a blog and find that shrinking your database files may result in fragmenting your tables. That’s what I just finished fixing was table and index fragmentation. When you shrink the file, there is no guarantee where a page will be moved in order to free up space at the end of the file so that the database size can be reduced. Seems self defeating.
This seemed like a big enough issue to put in my editorial and see if you have any direction or experience with this problem. Drop me a note with your experience, questions or suggestions.
Cheers,
Ben