Kevin3NF

Query your SQL Server ERRORLOGS

April 2, 2017 by Kevin3NF Leave a Comment

Quick post in response to a number of Twitter and Forum questions I’ve seen lately.

Downloadable from the Microsoft code Gallery (with ranking options!)

“How can I search my SQL ERRORLOGS with native tools?”

Create Table #Errorlog 
	(Logdate datetime, 
	 ProcessInfo varchar(50), 
	 LogText varchar(5000))

--Dump all the things into the table
insert into #Errorlog
EXEC sys.xp_readerrorlog 
	0			-- Current ERRORLOG
	,1			-- SQL ERRORLOG (not Agent)

--Query just like you would anything else:
Select * 
from #Errorlog 
Where 1=1
	--and LogText like '(c) Microsoft Corporation%'
	and (LogText like '%Error%'	or LogText like '%Fail%'--or LogText like '%SPN%'
	)
	And Logdate > getdate() -1
	And LogText Not Like '%CheckDB%'
	And LogText not like '%35262%'
	And LogText not like '%35250%'

--Clean up your mess, you weren't raised in a barn!
Drop Table #Errorlog

I run this every morning against my Prod Local Server Group in SSMS Registered Servers

The Apprentice: Locks and Blocks and Deadlocks….oh my!

March 30, 2017 by Kevin3NF Leave a Comment

I re-posted SQL 101 Blocking vs. Deadlocking – in English for the Non-DBA the other day for two reasons:

Its good info for new DBAs struggling to understand the interaction and differences in these terms
It was next on the list to walk the Apprentice through and he reads my Tweets 🙂

We had about an hour to spend working through this so we briefly covered the article, using Starbucks and Chick-Fil-A interchangeably as something you almost always have to wait in line for.

Things we managed to cover, test, or define in that one hour:

Lock: When a customer “Sally” (query1) walks up to the cashier “Ken” (Resource1), she has locked him into taking her order.
- Ken is a CPU here, or a Row/Page/Table…don’t overthink my analogies 😀
Block: The dude “Broseph” (query2) behind Sally (query1) has to wait…he’s blocked.
If the manager (Query Optimizer) sees that Sally is ordering 20 drinks for the office, he may open a second register and have Joe (Resource2) starting ringing up some of Sally’s orders. Brospeh is still in line, waiting. Sally has gone parallel.
If the manager decides Ken needs to work all of Sally’s order himself (MAXDOP1), he may open a second register (Joe/resource2) and move Broseph to that line.
If Max (query3) walks in, sees what is going on and decides he doesn’t really want coffee…he just rolls on back out the door and leaves a 1-star review on Yelp (failed transaction, retry?)

Other stuff we covered, in no particular order:

Deadlocking and the Mom process (see above link)
INSERT statements to create sample blocking
IMPLICIT and EXPLICIT transactions, so the tests actually work
4 parts of an object name [instance].[database].[schema].[object]
Why a sysadmin cannot directly query Instance2 from Instance1, regardless of his level of sysadmin-ness unless…
…Linked Server
sp_lock
master..sysprocesses (old skool)

Good times were had, jokes were made, stuff was learned. Oh…and every time we meet I ask him random stuff from previous meetings to gauge retention. So far so good.

Thanks for reading!

Kevin3NF

xp_CmdShell is not a security risk…people are!

March 22, 2017 by Kevin3NF 4 Comments

Quickie today….

Someone please tell me why this is considered insecure:

A stored procedure that, out of the box, is disabled and has no explicit rights granted (or denied) is locked down to everyone but those in the sysadmin server role.

If someone exploits your SQL Server via xp_cmdshell, its because you LET them, either by granting permissions or by putting someone in sysadmin that clearly should not have been there.

Told you it was a quickie 🙂

Thanks for reading!

Kevin3NF

Prediction: SQL server DBA role

March 20, 2017 by Kevin3NF 3 Comments

There have been a lot of questions, posts, answers, guesses and such floating around the SQL blogs lately…most of which seem to suggest that the DBA is going away.

Hogwash.

The DBA position is not going away. Ever. Or at least not before I retire to Utah to spend my days mountain biking 😉

But…it is changing and will do so even more over the next 3-5 years. Long gone are the days where a DBA stays busy backing up databases and creating indexes. Those are still necessary, or even critical, but thanks to some brilliant minds and thousands of willing testers, the basic tasks of the DBA have largely been scripted away.

Automation. Powershell. Cloud technologies. SQL Server on Linux.

Some of these have been around awhile, some are newer and some are still in CTP. But they are ABSOLUTELY going to be required knowledge for anyone in or around the database world…admins, developers and managers.

Automation – this is a basic need at this point. Whether running Ola‘s backup/maintenance scripts, or using Powershell to shut down Virtual SQL Server in Azure, if you are not automating repetitive tasks you are wasting your employer or client’s money. Simple as that.

Powershell – 10+ years old now, and is really starting to pick up steam in the SQL realm…thanks largely but not solely to the dbatools team. Companies increasing their use of PS are going to make obsolete the “I can do everything I need in T-SQL” argument. My decision to switch.

Cloud tech – AWS, Azure, Google, Cisco, internal, public, private…lions and tigers and bears, oh my! As a DBA you are going to have to help guide your firm in the right path when the marketing/sales guys try to influence them. You cannot do that unless you know the pros and cons…from Data Lakes to simple blob storage for backup. Learn the lingo so you are not caught unaware.

SQL on Linux – Something I have just started messing around with. I guarantee you this is going to be huge. Microsoft made a brilliant decision here and has done an amazing job porting a massive number of SQL features to the Linux based “vNext”. My prediction – in 5 years if you aren’t competent enough to navigate Linux in order to troubleshoot a SQL issue, your job prospects will be limited. Just start learning now…free Linux training is all over the place.

This is just touching the surface of how the DBA job will be changing…just like it has been changing for the 18 years I’ve been in it. Don’t be afraid, just begin adapting now so you are ahead of the game!

I sincerely hope this has been an encouragement to you. Your job as a DBA is not going away…its actually becoming more necessary. Are you up to the task of adapting?

Thanks for reading!

Kevin3NF

TSQL Tuesday: The daily WTW?

March 14, 2017 by Kevin3NF Leave a Comment

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Kennie N Ponotoppidan (b|t), and covers the topic “The Daily Database WTW” (yes, I know, I changed it to “What the What”. Personal preference…)

Kennie is asking us to post about the most insane thing we’ve seen in SQL Server. Mine was so bad I’ve already blogged about it here, so I’m just going to copy and paste…because I’m lazy 🙂

I walked into a new client in 2015 and found this, all one one server:

o SQL 2008 R2, RTM

o Incorrect Memory configuration

o Full recovery model on data that only changes once a week at most

o ZERO documentation of server settings

o New data is imported to a new table and a UNION ALL query is modified to add that table

o ZERO documentation of code/databases

o Stored Procedures have no comments, poor formatting and developer names in the name of the proc

o Autogrow is 1MB, data imports are hundreds of MB each

o Everyone is sysadmin, probably including you…

o Change control process is intentionally shortcut and ignored on this internal production system

o Ownership changed to me in December, then was yanked back 3 weeks later with developers overwriting my fixes in prod.

I still feel the stench of that assignment…but its slowly wearing off 🙂

Thanks for reading!

Kevin3NF

Why is my SQL Log File Huge?

March 8, 2017 by Kevin3NF 11 Comments

Pluralsight courses for new SQL Server DBAs

Do you need our help? Or our DBA retainer service for emergencies?

HUGE Log files and how to troubleshoot:

The single most common question I have encountered in 18+ years of working with SQL Server:

Why is the .LDF file filling up my 500GB drive? I only have 100MB of data!!?!?!? Why am I getting error 9002?

For new or non-DBAs, this is a very frustrating situation without a logical reason (or so it seems). It is also very common for it to be accompanied by applications that won’t work, alerts firing for drive space issues, etc.

If you like video, I recorded my response to this question and discuss the two most common remedies. If you don’t like video, scroll down for text:

There are a number of reasons a log file can fill to extreme sizes. The most common one by far is that the database is in full recovery model, and Transaction Log backups are not happening fast enough, or not happening at all. Next to that, it could be that you had a massive transaction happen such as a huge data import, rebuild all indexes, etc. These are logged and stay there until the .ldf file is backed up (or checkpointed if you are in Simple Recovery).

Step 1: Verify recovery model

Right-click the database, go to properties, click the Options tab. You will see Full, Bulk-Logged or Simple. If you are in Full, you have the option of backing up the log…which is the best possible situation.

SQL Server Database Options

Step 2: Verify if the log is full or “empty”

Verify if the log file is actually full or not. If you are backing up and the file still grew to ridiculous size…it may have just been a one time thing and you can deal with that easily. Right-click the database, go to reports, standard reports, disk usage. This will give you 2 pie charts. Left is the data file, right is the log. If the log shows almost or completely full AND the huge size, you need to backup. If the log file is huge and mostly empty, you simply need to shrink to an acceptable size.

SQL Server Disk Usage

Step 3: Shrink the file (if empty)

Right-click the database>>Tasks>>Shrink>>Files

Choose ‘Log ‘ from the File Type drop down. Hopefully there is only one log file. If not, pick the big one. Under Shrink Action, choose an appropriate size and ‘Reorganize pages before releasing space” option, even though log file shrinks don’t actually do that. Pick a size in MB and click ok. 0 is not a good choice here.

SQL Server Shrink File

Step 4: Backup

I’m not going to go into a ton of detail here….Right-click the database>>Tasks>>Backup Change the backup type to Transaction Log and work through the rest of the steps.

If the Log Backup works, but the space is not freed (refresh the usage report), you have a different issue that these steps will not help with. Check out the “Wrapping Up” section at the bottom of this post.

If you don’t have enough room on any local, attached or network drive to create a log backup, even with compression, keep reading:

Step 5: Flip the Recovery Model (if log backup is not possible)

Warning: Doing this WILL cause you to lose point-in-time recoverability, but if you cannot backup the log, you are pretty much already there anyway.

Right-click the database>>Properties>>Options

Change the recovery model to Simple and click OK

SQL Server Recovery Model

Wait a few seconds and then go refresh the Disk Usage report. The log file will be the same size, but should be almost empty:

SQL Server Disk Usage

Step 6: Shrink the Log file

See step 3 above…

Step 7: Flip the recovery back to Full

See step 1…

Step 8: Set up recurring log backups

If you don’t know how to do this, go to Management, Maintenance Plans, right-click Maintenance Plan>>Maintenance Plan Wizard and go from there. This is well documented elsewhere.

Wrapping Up:

Hopefully, this resolved your issue but there are definitely other reasons for this issue to happen aside from a simple failure to back up. Most notably, a very large transaction in a database that is participating in SQL Replication as a publisher.

If the above methods do not work, run these two statements and go post the results in the MSDN SQL Server forums, along with a description of the issue and what you have already tried (hopefully all of the above):

Select [name],recovery_model_desc, log_reuse_wait_desc 
from sys.databases
Where [name] = 'MyDatabase' --change this

DBCC OPENTRAN --results will be in the messages section

I love comments on my post, but if you need quick help go to the forums first, or maybe even a call to Microsoft Support if the “quick hits” don’t get you the resolution you need. If this helped, please comment and share the link…

Thanks for reading!

Kevin3NF

My Pluralsight course for new SQL Server DBAs

Follow @Dallas_DBAs