Tag Archives: TSQL Tuesday

Log Management Made Easy

Hey guys… I wrote a nice little log management script I thought some of you might like. It doesn’t do anything to your system so it’s safe to run. Well, I’m turning xp_cmdshell on and off so if you don’t like that then this script isn’t for you.
I’m just putting more info into SQLPERF. These are the most common things I need to know when I have a runaway log. Of course feel free to modify it any way you like. Unfortunately, due to when PS came into play, you can only run this on SQL2K8 boxes and above. If you want to run it on lower boxes you’ll have to take out the PS portion of it.

One more thing before I give you the script. You’re responsible for anything you run on your box. So don’t blame me if something happens and you mess something up. Like I said, I believe this script to be safe but only if you manage the xp_cmdshell portion properly. If you run the script as it is now it’ll turn off xp_cmdshell and if you’ve got processes that rely on it then they will fail. So just be warned yet again that this is a risk. And the reason I’m turning it off again is because I don’t want to open up anything on your box that shouldn’t be. But I really like having the extra info so I don’t have to go look it up. I hope you do too.

Some important notes about the script:
1. It gives you the physical location of the log files, so if there’s more than 1 you’ll see more than 1 entry for each DB.
2. The ShrinkCmd has a variable at the top used to control its default. I like to shrink down to 1GB a lot of times, but set this to whatever you like.
3. You can expand this for yourself in ways I can’t do for you. For instance you could tie it to your log backup job to see when the last execution was and if it failed.
4. I’ve added the last log backup date for you… you’re welcome.
5. The nature of how PS works, you have to change the instance name at the top to the current instance you’re working with. There’s nothing I could do about that. I tried to make it as simple as possible.
6. The PS portion relies on xp_cmdshell. However, I turn it on and off for you in the script. If you want it left on, make sure you comment that portion out of the code or you could have stuff that breaks because you just turned on xp_cmdshell.

declare @LogSize int,
		@Instance varchar(100)
SET @LogSize = 1024
SET @Instance = 'localhost\default' -- If default instance then put Default ex: 'MyServer\default'
DBName varchar(100),
LogSizeInMB float,
LogSpaceUsedInPCT real,
Status tinyint
INSERT #LogSpace
EXEC ('dbcc sqlperf(logspace)')
DBName varchar(100),
LogBackupDate varchar(25)
exec sp_configure 'show advanced options', 1
exec sp_configure 'xp_cmdshell', 1
declare @cmd nvarchar(2000)
		SET @cmd = 'sqlps "cd sqlserver:\sql\' + @Instance + '\databases; $a = dir; foreach($DB in $a){$DBName = $DB.Name; $LogBackupDate = $DB.LastLogBackupDate; invoke-sqlcmd -query ""INSERT ##LogSpacePSTemp SELECT ''$DBName'', ''$LogBackupDate''""" -SuppressProviderContextWarning}"'
		--PRINT @cmd
		exec xp_cmdshell @cmd, no_output
, LS.LogSizeInMB
, LS.LogSpaceUsedInPCT
, D.log_reuse_wait_desc as LogReuseWait
, LT.LogBackupDate as LastLogBackup
, DATEDIFF(mm, LT.LogBackupDate, getdate()) as MinsSinceLastLogBackup
, D.recovery_model_desc as RecoveryModel
, MF.physical_name 
, 'USE [' + D.name + ']; DBCC SHRINKFILE([' + MF.name + '], ' + CAST(@LogSize as varchar(10)) + ')' as ShrinkCmd
from #LogSpace LS
INNER JOIN master.sys.databases D
ON D.Name = LS.DBName
INNER JOIN master.sys.master_files MF
ON D.database_id = MF.database_id
ON LT.DBName = LS.DBName
drop table #LogSpace
drop table ##LogSpacePSTemp
exec sp_configure 'xp_cmdshell', 0
exec sp_configure 'show advanced options', 0
reconfigure reconfigure

Do I like Speaking? — T-SQL Tuesday #41


Well, I actually have a love/hate relationship with speaking.  I’ve read that there are 2 types of speakers, those who get nervous before going on stage, and liars.  Sure, I don’t expect that you’ll have the same level of nerves after you’ve been doing it for 10yrs, but there’s still going to be a bit of butterflies going on no matter what. 

I think it’s the process I like though.  Coming up with an idea is crucial.  I can never come up with anything when I want to.  My best sessions are inspired and they pretty much write themselves once I get the right inspiration.  However, there’s still getting all the demos ready, and figuring out exactly what I want to say.  It’s a big part of the process.

Next, it’s submitting the abstract.  This can be just as hard as doing the session itself because you have no idea who’s on the other end reading it and coming up with the exact wording that’ll get you picked is a crap shoot most of the time.  I’ve seen popular, experienced speakers get turned down in lieu of “nobodies”.  So sitting there with someone, in my case Jen, and coming up with that poetry that’ll get you noticed over all the others is a fun part of the process.  It’s like going up on a rollercoaster.  You’re heading up that first hill just waiting to see if you’re going on the rest of the ride or if it’s going to break down and leave you sitting at the top having to walk back down.  It’s kinda like a little nerdy xmas.

Now you’re at the event and it’s the day of your session and time to make sure demos are working.  Well, are they?  You get them all worked out, get your environment reset and do it again.  Then reset it again.  Everything’s perfect.  Time to get to your session without a moment to lose.

You’re now on the platform watching people come in and just hoping that you have decent attendance.  Then it happens.  The thing you dread every time you speak.  You see an MCM walk in and sit down in the back.  DAMMIT!  Now I’ve gotta make sure I don’t say anything stupid.  But wait, what’s this?  Another MCM?  He’s coming up to the front and promising he’ll be listening intently.  Then 2 MVPs come in together and sit next to a couple guys from the product team.  ARE YOU KIDDING ME?!?  Ok, session’s over.  I’m putting on Blazing Saddles for you guys instead .  It’s alright.  I can do this.  These guys aren’t here to see you fail, they’re just here to tweet it if you do.  No worries.

So now you start your session and your first demo has an error.  Oh come on man… I triple checked it.  WTx is the problem?  Ok, it’s easily fixable.  No worries.  Everything’s back on track.  The next demo goes well so now it’s smooth sailing.  Now the questions start coming in.  This first one is easy.  The next 2 I haven’t really thought of.  Hope I know enough about this topic off the top of my head to be able to answer it in front of the A-Team down there.  I see they’ve got their thumbs on the twitter buttons on their phones.  Now the thumbs are going crazy.  What did I say?  Was it stupid?  Can’t stop now, time to move on.  Now a complicated topic comes up and while the demo goes well questions start popping up.  Again with the thumbs guys, WTx?  Now the next topic and there are already questions.  A burning look goes to the MCMs.  If you guys even move I’m breaking your thumbs.

Session’s over now.  More questions are coming in and whether you know them or not you’re hitting your stride.  You try as hard as you can to remember some of them so you can add them to your session next time.  The MCMs come up and say they thought it was great and they never really thought about it that way before.  SCORE!!!  You pack up your stuff and leave the room and as soon as you get out you put everything down and check twitter to see what the hell those guys were tweeting so much.  “In session with MidnightDBA.  Backup tuning is awesome.” — “Backup tuning with MidnightDBA.  I’ve never seen that trace flag before.  This is the best!” — “Our tweeting is making him nervous.  Keep it up guys!”

So do I like presenting?  Hell YEAH!

T-SQL Tuesday: A file delete exercise

This is my submission for T-SQL Tuesday on Files and filegroups.  You can find the blog tsql2sday.


One of the things I’m known for in the shops I work in is giving my DBAs some interesting exercises.  And sometimes they even come with prizes.  This is an exercise I’ve been giving off and on for a few years now.  I typically point them to a test server and send them to the MyDocs folder under one of the profiles.  In there is a list of files that I want them to delete.  Typically the email has similar verbiage to this:


I have an assignment for you guys.  I want you to go to the following location and delete this list of files.

And whoever gets to that server first and deletes those files first gets their pick of these 3 books.


They always rush to be the first there and I can hear their initial failure one at a time as they find out it’s not just an exercise in speed.  Just for fun, here’s a screenshot of the files I have them delete.


About that time I follow-up with another email.  It says:

You’ve just discovered that the files cannot be easily deleted.  The only hint I’ll give you is that I did it by highlighting a feature in the new version of SQL Server. 

Good luck.


For a few years now, the race has been whoever could find the obscure setting in filestream the fastest (because they figure that’s the best place to go).  There has to be something in filestream that’s doing it.  So they dig through everything filestream-related they can find.  They dig and they dig and they dig.  They put together some test DBs and do their best to recreate the issue.  I hear all kinds of wacky theories flying around.  But they never hit that magic bullet that makes it all come together (if you don’t mind me mixing metaphors).

It typically takes them 2-3 days before they give up for good.  I’ll tell you something… in the years I’ve been doing this I’ve never had anyone actually get it with no prompting.  So then at the end, we come together and talk about the process they went through to troubleshoot this issue and their reasoning.  They talk about handles and locked files and permissions, and all the new features in SQL Server that would cause something like that, but they’re just not sure because they couldn’t find anything on google and they don’t know the undocumented XPs, etc. 

And as it turns out, this exercise has nothing to do with the files at all.  I mean, it does, but it’s really meant to serve a much greater purpose.  I want to teach them that the customer always lies, or does his best to throw you off track by sprinkling whatever piece of misinformation he has into his request.  And you never know what they’ll do to their systems.  I want to teach them the following things:

  1. Listen to the wording.
  2. Users lie.
  3. Think for yourself, don’t get distracted by what they tell you.
  4. Ask Questions… for the love of GOD ask questions.


So what’s the resolution?  Tell ya what, I’ll give you the code to repro it and that’ll tell you what the issue is.

create database UserDB




filename=’c:\users\sean.midnight\my documents\Master20120105.bak’





filename=’ c:\users\sean.midnight\my documents\BlogPost.docx’




filename=’ c:\users\sean.midnight\my documents\Expenses.xlsx’





filename=’ c:\users\sean.midnight\my documents\LovePets.pdf’



It’s not always exactly like that, but this is one variation of it.  Now, you may wanna say that I lied, but didn’t.  What I said was that this hinged on a feature in the new version of SQL Server.  But I didn’t say it was a new feature.  It is in fact a feature in the new SQL Server, it just so happens to be a feature of all of them (well, most of them).  And that feature is the ability to create DB files of any name and any extension you like.

Happy T-SQL Tuesday, and I hope this fits in with the theme.