Quick tips to regain space on your SQL Server

Microsoft has released performance reports available for SQL Server 2008 and higher

Frequently DBA's are tasked with restoring databases from servers across the WAN for troubleshooting and other various reasons.  SSIS can be very useful in automating these tasks.

Max Trinidad did an fantastic job presenting Extend you TSQL skills with PowerShell

My sincere thanks to Red Gate, a premier supporter of our SQL community and their many Microsoft users. They offered a holiday competition for ideas to create the best holiday party and our input won providing the Space Coast SQL Users Group an excellent holiday party evening. We were able to provide dinner, gifts, and a free round of drinks for our members and their spouses and significant others. The party was hosted in the back room of the Broken Barrel, an excellent sports bar/family restaurant in Palm Bay, Florida.  The food was delicious and consisted of Asian potato salad, sweet potato fries, smoked wings and assorted smoked meat sliders.   Broken Barrel’s service was excellent; they even helped with suggestions on food types and combinations. The best part was the gifts weren’t technical books or left over swag like the previous years. I will never forget giving a member s wife a book on Administering Windows Server 2008.  Thankfully everyone in our group loves humor. This year I found a little store in Jensen beach that had a ton of unique gifts. We gave tickets to spouses and significant others and had each person choose a wrapped gift from the table.  I picked out several Florida Santa figurines, threw in a mounted prehistoric shark’s tooth, nice chocolates and a bottle of fine wine.   Then everyone received a ticket for our door prize, a decorated Santa on the beach. The event was an outstanding success, we cannot thank Red Gate enough for their kindness.  Everyone have a wonderful holiday and a safe New Year, I know I will.
 

 Thank you Florida Institute of Technology and Dr. Philip Bernhard for sponsoring our group for a new meeting place!

A couple weeks ago a user complained that on one of our development databases, one of the views he was linking to via Access was missing some of the columns.  There were no permissions issues and the columns all showed up properly if they looked at the view via SSMS; but when they went into Access and linked to the view… the columns weren’t there.  After scratching our heads for a bit, we realized that the user didn’t have access to the User defined Types that the columns in the underlying table were designated as.  SQL doesn’t actually care about this, you can view data in the table or view whether you have the references permission or not… but Access is a bit more picky and will ignore the column if the user doesn’t have the proper permissions.  Normally we grant references on these types to Public in all the databases so that we don’t have this issue, but on this particular development server we forgot.  This is another issue that can be very puzzling when it happens out of the blue and is very hard to Google...

Read More »

Because this was a problem that Google was zero help on and I did find a couple other people afflicted with it, I figured I’d write a post about it. 

The issue was this.  I add a table to replication (SQL Server 2005).  The option to replicate DDL changes is set to true, vertical_partition on the article is set to false.  From everything you’ll read about replication (in 2005+, 2000 didn’t work this way, you needed to use the column add/delete procs), it should now just work.  Sure, you’ll find some posts floating around out there about people with random problems that resembled this and the fixes usually just involve turning on the option to replicate schema changes or some other such basic fix.  Not the case here.  I tried everything I could think of.  Rebuilt the publications from scratch.  Dropped replication all together on the server and added it back (to be fair this might have temporarily worked, but I don’t feel like dropping it and re-creating it all again to verify). 

The issue turned...

Read More »

This is a technique I’m sure many people are already using in their environments.  Instead of using the actual name of your SQL Server as the name of the linked server that you call, use a generic name.  For instance.  If you have a couple of servers named SQLSales02 and SQLMarketing01 that you want to set up linked servers for, consider using more generic names such as SalesServer and MarketingServer.

The main benefit we garner from this in our environment is that when we move back and forth between our Test/Development/Production environments, we don’t have to worry about changing the names of linked servers in the code.  We just point the linked server on development to the development sales server and the linked server on production points to the production linked server.  No code changes.  Another benefit this confers is that if you ever need to upgrade or replace SQLSales02 (and the name of the server changes) you don’t have to hunt down all the code pointed to it, you just change the linked server...

Read More »