Thursday, May 21, 2009

SQL Server 2008 Shrink Log File

When DBAs wanted to shrink a log file in SQL 2000 and 2005, they would often backup the log with TRUNCATE_ONLY. When they attempt to do the same thing in SQL Server 2008, they will be greeted with this message:

'TRUNCATE_ONLY' is not a recognized BACKUP option.

Here is a replacement script for SQL Server 2008:

USE Test
GO
ALTER DATABASE Test
SET RECOVERY SIMPLE
GO

DBCC SHRINKFILE(Test_Log, 10)

GO
ALTER DATABASE Test
SET RECOVERY FULL

This script puts the database in Simple Recovery mode. It then shrinks the log file. Finally, it puts the database back in Full Recovery mode.

Monday, May 18, 2009

New Index grayed out in SQL Server Management Studio

In SQL Server Management Studio, if you right-click on the indexes and you see new index grayed out, it is because the table is schema-locked in another window. Close the design view of the table and new index should now be black and usable.

Thursday, May 14, 2009

Putting for Analytics

I often do my best thinking on a putting green that is conveniently located about half mile from my desk.

Recently, I was all lined up, taking a practice swing, and it was perfect, very smooth. My practice swings are usually pretty good, I remember and focus on all the technique I have learned from coaches, lectures, books, experience. I swing freely and effortlessly.

Things only seem to go awry when I put a ball in front of me. I stop swinging freely, I tense up, tighten my grip, and both my swing and the ball become erratic. I have spent years attempting to recreate my practice swing with a ball present, I am sometimes successful, sometimes less so, but I’m always a scratch golfer when I golf without a ball.

Why is there a difference between my practice swing and my real swing? I think it's because when the ball is there, I become so focused on performance that I no longer think about the process that gets me the putt I am after.

I have found the same to be true about a performance focused mindset in business intelligence. Companies spend much less time examining the fundamentals of what got them to where they are in the first place. While examining the reports of a standard company, all focus is directed on dissecting "What happened?" When companies are only performance-oriented, much time is spent analyzing sales by product, by territory, by sales person, by location, or by time of the year. Retail organizations look sharply at sales during "the season", while time and billing firms examine their "top performers" with a keen interest. But how much do these things impact the future? Regardless of what the answer might have been in the past, our current recession has made it very clear that the companies that are solid are those who focused on sound fundamentals, on the process. How effective is it now to examine December 2007 sales? Will those figures have any impact or accurate prediction on what sales will be like in December 2009? What about December 2010? It is likely that if companies continue to be overly performance focused, reactions will be tense and erratic.

I see examples of this behavior in the news, all the time. The latest government stimulus packages are very reactive, with little thought put into goals, impact, future. Wall Street is too results oriented. Every time a firm posts numbers that are shorter than expectations, there is a direct impact on stock price, which affects the longevity of that organization. As companies focus on current numbers, avoid creating a vision, and the process to implement it, they too compromise the future.

So I loosen up, I remember what I have been taught and what I have learned from experience. I know how to do this. I remind myself that keeping an eye on the ball is important...but I will never make it to the hole/goal if I lose my grip on the club.

Tuesday, May 12, 2009

SSRS 2008 List All Reports, Users, Roles, and Security

Someone on the newsgroups today asked the following question:

Is there any report that can be run in Reporting Services that lists
all reports and the users that have access?


I assumed he meant that he wanted a list of all the reports he has, and a list or roles and the users that are in them. I quickly wrote the following query for him. I hope some of you find it useful:

SELECT u.UserName, c.[Path], c.[Name], r.RoleName, *
FROM Users u
JOIN PolicyUserRole pur
ON u.UserID = pur.UserID
JOIN Policies p
ON pur.policyID = p.PolicyID
JOIN Catalog c
ON p.policyID = c.policyID
JOIN Roles r
ON pur.RoleID = r.RoleID
ORDER BY u.UserName, c.[Name], r.RoleName

Tuesday, May 5, 2009

Book Review - 10 Days to Faster Reading


I loved this book, and I think all software developers and business stakeholders should read it. We all have a lot to read, and getting through some of it quickly will give us back the gift of time.

Here is what I learned:

1) In general, authors are repetitive, so once you get the point, and you don't need to see the supporting evidence used to reinforce it, just skip the rest and find the next point.

2) Read with an index card covering the line and you'll stay focused and read faster. I don't find that particularly enjoyable. I only do this when there's a book I have to read for work, like a standards specification, or something really boring.

3) If I read 150 words per minute or 400 words per minute, my comprehension stays the same, around 80%. So if my comprehension is the same, I might as well read fast.

If you want to buy this book, here's the Amazon link:

10 Days to Faster Reading