24 Hours of PASS: Summit 2012 Preview

It's that time of the year again, the PASS Summit is approaching and to help kick things off they are giving you a preview of the type of sessions you can expect at the Summit! That's right another 24 Hours of PASS (24HOP) event with back-to-back sessions starting at 12:00 GMT (UTC) on September 20, 2012.

For those of you who don't know about the '24 Hours of PASS' event, it is 24 live webcasts that provide free SQL Server training from some of the top SQL Server and BI experts from around the world. This Summit Preview edition will show case 24 sessions, out of the 180 + sessions that are going to be available at the Summit. Go to the 'Sessions by Track' page to see who are the 24 international presenters as well as the sessions that they will be delivering.

Want to know if the PASS Summit is for you, want justification for attending or want to have an idea of what sessions to attend while at the Summit then attend this 24HOP event!

Registration is open and if you have any questions then contact PASS at 24hrs@sqlpass.org

 

Continue Reading →

T-SQL Tuesday #33 - Monitoring Log File Size Without Logging On To The Server

This month’s T-SQL Tuesday is hosted by Mike Fal (Blog | Twitter). The topic is TrickShots and the purpose of this topic is to highlight a cool trick or process and explained what you learned from this trick.

I encountered my first log file issue in my early days as a DBA using SQL Server 2005. While I was on vacation, I received a phone call from the company's system administrator because he noticed the reduced free space on one of the drives of the database server. When I logged on to the server I immediately noticed the large log files that were not present a few days ago. After fixing the issue one of the main questions that was on my mind was "How can I know the log file size without logging on to the server to check the logs folder?".

I did some research and came across the T-SQL Statement:

DBCC SQLPERF (LOGSPACE)

This provides transaction logs usage statistics for all the databases on the instance (for more information see MSDN: DBCC SQLPERF)

I found the information very interesting and thought it would be a good idea to store the data everyday and look for some trend so that I can predict the size of the log files.  The following code was used to store it in a table:

INSERT INTO Information(DatabaseName,LogSize,LogSpaceUsed,Status)

EXEC ('DBCC SQLPERF (LOGSPACE)')

Around the time I was working on this issue, I was also now getting familiar with SQL Server 2005 Reporting Services and anything that was being stored in a table was suitable test information for a report. This resulted in a report with the data from the 'Information' table but I had to run the script and the report manually.

Using the little knowledge that I had of SQL Server 2005 as well as the excitement to try out new things, I decided to use a stored procedure as well as the subscription capability of Reporting Services to automate the process. The stored procedure was created to execute the insert script and also returned the inserted data every time the report was executed. The subscription capability allowed me to schedule when the report would be executed, how it would be delivered and the specific format of the report. The end result of this process enabled me to get my log size information in an Excel file via email. A great advantage of this was the fact that I could receive the email on my phone and check the file even if I was out of office or away from my machine.

Subset of the Log File Information in the Excel File

While this process could have been done more easily back then and much more easily now, it was a great way for me to learn and test the new features of SQL Server 2005. Another benefit of this process was the fact that the information that I received from the DBCC statement encouraged me to learn and understand more about log files.

Continue Reading →

TRUNCATE TABLE: A possible alternative for the DELETE statement

While reviewing an archiving script at my company I realised that most of the time spent waiting for the script to complete was due to the deletion process. While trying to find a better way to perform the deletion of data from the database I came across the DDL statement: TRUNCATE TABLE.

The TRUNCATE TABLE and DELETE statements can be used to remove records from a table, the difference is that DELETE can be used to remove some or all of the data in the table while TRUNCATE TABLE can only be used to remove all the data from a table (no WHERE clause). However TRUNCATE TABLE is faster than the DELETE statement and requires fewer system and transaction logs resources because of the difference in how they execute and operate. TRUNCATE TABLE removes data by deallocating data pages instead of removing rows one at a time.

I decided to do a comparison of the two statements using a test table with 3.4 million rows.

The results for each script/statement had four grids:

• The first result grid shows the size of the MDF and LDF before the process.
• The second result grid shows the number of records in the table before the DELETE/TRUNCATE TABLE statement is executed.
• The third result grid shows the number of records in the table after the DELETE/TRUNCATE TABLE statement is executed.
• The fourth result grid shows the size of the MDF and LDF after the process.

DELETE Statement

Script

Results

Notice the increase sized of LDF after the process (from 5.5625 MB to 2321.375 MB). The script was executed three times and each time the LDF was the increased to the same size.

The execution time results:

 SQL Server Execution Times:

   CPU time = 6177 ms,  elapsed time = 114451 ms.

 SQL Server Execution Times:

   CPU time = 6349 ms,  elapsed time = 105675 ms.

 SQL Server Execution Times:

   CPU time = 6193 ms,  elapsed time = 105816 ms.

TRUNCATE TABLE Statement

Script

Results

Notice no change to the size of the MDF or LDF. The script was also executed three times.

The execution time results:

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 140 ms.

 SQL Server Execution Times:

   CPU time = 15 ms,  elapsed time = 96 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 191 ms.

Conclusion:

Based on the execution times results it is very clear that the TRUNCATE TABLE statement is faster than the DELETE statement.  It is also clear that the DELETE statement process of removing rows one at a time requires more transaction log space when compared to the TRUNCATE TABLE process.

See the following MSDN links to understand for more information about TRUNCATE TABLE and DELETE.

Continue Reading →

SQL Server 2008 R2 SP2

Microsoft just released Service Pack 2 for SQL Server 2008 R2 which includes product improvements based on requests from the SQL Server community and hotfix solutions provided in SQL Server 2008 R2 SP1 Cumulative Updates 1 to 5.

Both the Service Pack and Feature Pack updates are available for download:

• SQL Server 2008 R2 SP2
• SQL Server 2008 R2 SP2 Express
• SQL Server 2008 R2 SP2 Feature Pack

Continue Reading →

TTSSUG Event: SQL Server 2012 Presentations

Last week, members of the TTSSUG committee presented at two events to highlight the new and improved features of SQL Server 2012.

The presentations as well as scripts and videos can be downloaded here:

What's New in SQLServer 2012 for Database Administrators - Nigel Sammy

What's New in SQLServer 2012 for Developers - Mitra Sinanan

SQL Server 2012 Analysis Services - Jason Deyalsingh

PowerPivot - Hasani Holder

Useful links:
SQL Server 2012 Trial
SQL Server 2012 Upgrade Technical Guide

Links to the TTSSUG Facebook site, Twitter account and LinkedIn group are available on the second-to-last page of each presentation.

Continue Reading →

New SQL Server 2012 Reporting Services Book

If you are interested in understanding SQL Server 2012 Reporting Services' architecture and BI fundamentals then the 'Professional SQL Server 2012 Reporting Services' book is the resource for you. This book covers the capabilities of the new reporting platform as well as the new BI capabilities introduced in SQL Server 2012 including semantic model design, enhancements to Report Builder, PowerPivot for Excel 2012 and SharePoint 2012, and Power View.

This book was written by esteemed authors: Paul Turley, Robert Bruckner, Grant Paisley, Thiago Silva and Ken Withee. It is also the first book that I worked on as a technical editor along with Joe Salvatore and Chris Albrektson. This as well as the SQL Server 2012 Upgrade Technical Guide is what kept me extremely busy for the last few months hence the lack of blog posts. Thanks to Paul Turley, my colleague at SolidQ, and Wrox Press for this great opportunity.

It is currently available from Wrox.com, Amazon & other resellers in paperback and electronic editions.

Continue Reading →

TTSSUG Event: SQL Server 2012 In-Depth Sessions

The Trinidad and Tobago SQL Server User Group (TTSSUG) will be hosting two (2) SQL Server 2012 In-depth Technical Sessions on 13th and 14th June at Microsoft Office in Chamber of Commerce Building, Westmoorings.

Session 1: What's new in 2012 for DBAs and Developers

Date: 13th June 2012

Time: 9am -11am

Presenters: Nigel Sammy and Mitra Sinanan

Session 2: Business Intelligence Solutions 2012

Date: 14th June 2012

Time: 9am -11am

Presenters: Hasani Holder and Jason Deyalsingh

Seats are limited so book your space early! 

To register for each event, please contact:

Anand Singh: events@ttssug.org – 868-356-4090

Please specify if you will be attending one or both sessions.

Continue Reading →