Recent Articles:

GetLogSpace – table and stored procedure

We’ve talked before about how to get log space usage information in SQL Server.  After the 1,032nd time I re-wrote my custom query to also see the UNused log space, it occurs to me that this would be a nice thing to have on your server on a permanent basis.

Introducing: GetLogSpace!  It’s a simple enough thing:

  1. Use the script to create the table and stored procedure.
  2. Run the SP in ReportOnly mode (default mode, @ReportOnly = 1) to see the current log space information.
  3. Or, run it in log mode (@ReportOnly = 0) to save that data to the table. When you run the SP in log mode, it will delete data older than @retentionDays days old (default is 2 years).

The SP returns the regular SQLPERF(LOGSPACE) data, plus a simple calculation of how much log space is free. For example:

DatabaseName LogSizeMB LogSpaceUsedPct LogSpaceUNUSEDmb LogSpaceUNUSEDmb_TOTAL StatusNum Logdate
master 1082.805 1.151 12.466 61.783 0 NULL
model 0.742 42.697 0.317 61.783 0 NULL
msdb 31.68 10.072 3.191 61.783 0 NULL
tempdb 0.742 69.211 0.514 61.783 0 NULL

It’s that Log Space Unused MB column I like…this shows you at a glance how much space is free in the file.

As I note in the SP header:

You could schedule this to run every day/week/month, to track log usage over time. Or you could just run the report to see the current usage.

Note that I’m NOT advocating the shrinking of log files willy nilly.  Your logs need room to breathe, and if they regularly take up 150Gb,  they probably need 150Gb. In the cases where you’ve missed log backups  on a FULL mode database, you need to back up that log, then (MAYBE)  shrink the log file to a more reasonable size.

*** TL;DR: Don’t habitually shrink log files! ***

Remember, kids: Use and/or modify this script at your own discretion!

Download the GetLogSpace script here.

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

SQL Saturday Oklahoma City precon: Becoming an Enterprise DBA

Spork instructional module not included. Probably.

We’ll be at OKC this summer for their SQL Saturday on August 23, and we’re presenting our precon “Becoming an Enterprise DBA” August 22!.  Get your early bird ticket by July 15, and save over 15%!

Precon: Becoming an Enterprise DBA

August 22, 2014, Norman Technology Center, Moore OK

There’s a real and quantifiable difference between a regular DBA and the leader of an enterprise.  Guiding the database effort of a huge enterprise takes business acumen, experience, problem solving, street-smarts, and above all… vision.  And quite often regular DBAs are thrown into big enterprises without knowing what it takes to get the job done.  In fact, most DBAs get thrown into this situation without even a hint that there’s a difference.  And while they do the best they can, they tend to do more harm than good which is why many companies have such a hard time managing their data.  What you need is to change your tactic when dealing with large numbers of servers.

The biggest piece of missing training in databases is enterprise training.  DBAs are often taught syntax and usage of features, but they’re never taught how to actually use them to plan an enterprise, run an enterprise, or cope with changing requirements, manage difficult users or groups, manage growth, manage vendors, etc.  There are so many more requirements to running an enterprise than just knowing how the individual features work.  There are disk and backup requirements, auditing requests, end-user reporting, paranoid VPs, and much more.  And there’s no way to gain this type of business sense on your own.  You have to learn from seasoned pros who have been there many times over.

I’ve built many successful DBA teams in a few companies that were resistant to the change.  And I can show you how to do it too.  Take your daily tasks down to almost nothing while you increase your visibility in your company (in a positive way).

What you will learn:

  1. How to properly manage a large environment.
  2. Managing disk space and growth requirements across hundreds of servers.
  3. Manage end user expectations.
  4. Build a self-service reporting model to keep DBAs from having to field every day requests.
  5. Reduce security management issues by up to 90%.
  6. How to assess your biggest DBA resource hogs to make the most impact in your environment.
  7. Strategies for dealing with vendors and vendor security issues.
  8. How to hire a competent DBA.
  9. How to staff your company appropriately.  Do you know how many DBAs you need?
  10. How to get a bigger raise and make your company pay for training.

And much much more.  If you manage an enterprise or would like to then you can’t afford to miss this seminar.  No other training exists to take you from being a regular DBA to running and leading an enterprise database effort.  And don’t fool yourself; There is a real difference.  Get ready to take your skills and your career to the next level.

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

The case for SQL Server 2012 Enterprise (vs. Standard) edition

sql2012In celebration of the impending SQL Server 2014 release, and in recognition that a large percentage of my clients are on that cusp of the Standard/Enterprise licensing decision for the currently available version…let’s talk about SQL Server 2012! Specifically, why Enterprise edition might be a huge advantage – or even an imperative – for your shop.

One top 10 list of Enterprise advantages

Every shop has their individual needs and wants, and so every shop will have a different top 10 (or 8, or 17) reasons why Enterprise would benefit them over Standard. This is one list, based on a common set of needs I see at client sites, including performance, resource management, data warehousing, reporting, and change tracking.

Enterprise lets you use:

  1. More than 64GB memory per instance. This is an extremely important limit, especially for data warehouse servers, which feel about memory and CPU like marathon runners do about pancakes and syrup.
  2. Partition switching. This allows you to very quickly load or archive huge chunks of data in an online operation.
  3. AlwaysOn Availability Groups, especially for offloading read-only workloads to secondary replicas (and, you know have multiple standby instances ready to recover you from a disaster).
  4. Resource Governor. This allows you to customize limits on CPU and memory consumption. This is a HUGE deal, and shops are continuously surprised and disappointed to find that this is not a Standard feature. With Resource Governor, you can map a specific workload or set of workloads to a resource pool, to prevent it taking up too much of the system. These limits can be configured in real time, as they should be.
  5. Columnstore indexes. This new (ish) type of specialized index can massively reduce IO consumption. This is great news for data warehouses, that renowned resource hog.
  6. Online reindexing for tables with large data objects (like varchar(max)).
  7. Data warehouse improvements like star join query optimizations, and parallel query processing on partitioned tables and indices.
  8. Advanced Adapters and Advanced Transforms for SSIS, including “Dimension processing destination adapter”, “Data mining query transformation”, and “Fuzzy grouping and lookup transformations”.
  9. Change Data Capture, which lets you easily track inserts/updates/deletes on user tables. Fairly simple, efficient, auto cleanup, configurable.
  10. SSRS data driven subscription.  You can dynamically determine your recipients, parameters, etc., each time the subscription runs.

Abbreviated list of SQL Server 2012 Standard vs Enterprise features

You can get the full list of SQL Server features Supported by Edition on MSDN; here again is an abbreviated list of some of the better ones, IMHO.

Feature Standard Enterprise
Per instance maximum compute capacity Limited to lesser of 4 Sockets or 16 cores Operating system maximum
Per instance maximum memory utilized 64 Gb Operating system maximum
Database mirroring Safety full only High performance and full safety
AlwaysOn Failover Cluster Instances Node support: 2 Node support: Operating system maximum
Database snapshot No Yes
AlwaysOn Availability Groups No Yes
Online page and file restore No Yes
Online indexing No Yes
Online schema change No Yes
Fast recovery No Yes
Mirrored backups No Yes
Hot Add Memory and CPU No Yes
Table and index partitioning No Yes
Data compression No Yes
Resource Governor No Yes
Partition Table Parallelism No Yes
Multiple Filestream containers No Yes
Transparent database encryption No Yes
Parallel indexed operations No Yes
Automatic use of indexed view by query optimizer No Yes
Parallel consistency check No Yes
Integration Services – Advanced Adapters / Advanced Transforms No Yes
SSRS Data driven report subscription No Yes

Further Reading

General:

Per feature:

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

April 14: Oklahoma City SQL UG's 5th Anniversary
April 28-30: Powershell Summit in Bellevue, WA
May 10: SQL Saturday Houston
August 2: SQL Saturday Baton Rouge (planned)
August 22-23: SQL Saturday Oklahoma City Precon!

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/