Troubleshooting « SQL Feather and Quill

A script to create missing default bindings

Today five people showed up in my cube at 5:15 saying that the new development databases are missing defaults. I did some checking and the default objects were there, the columns with default constraints were there, but the columns with bindings to the default objects were missing the bindings. I created a script to run on the source database. The output of this script will be a script to run on the destination database to get things in sync.

Hopefully you won’t need it, with new tools like VS2010 and Redgate’s SQL Compare, but here it is if you do:

Select
     'EXECUTE sp_bindefault ''dbo.' + SO.Name + ''', N''dbo.' + OBJECT_NAME(SC.object_id) + '.' + SC.name + ''''
from sys.columns SC
Join sys.objects SO on SO.object_id = SC.default_object_id and type = 'D' and parent_object_id = 0
Order by SO.Name

T-SQL Tuesday #21 – 9 Tips to avoid the HACK title

This month’s T-SQL Tuesday Wednesday topic is “reveal your crap to the world” and is hosted by the T-SQL Tuesday originator, or the Godfather as I like to call him – Adam Machanic (Blog/Twitter).

Almost a year ago, we had a major database migration issue, actually it was one issue that caused another issue that cause performance issues – nevermind the specifics, it was complicated. The bottom line is that I knew I could fix our SQL server issues, so for a couple of weeks, I was deep, deep, deep into not only SQL issues, but presenting the issues to management, other developers – technical and non-technical, my boss’s boss’s boss was hanging on every word I’d say. In fact, the days were so full of talking, I ended up doing most of the technical work in the evenings after the kids were in bed (it’s been a re-occurring theme/frustration for me).

After the issues were solved, which I merely assisted in solving, but I ran a lot of the meetings – there were a lot of others who stepped up and really impressed me with there determination – I wrote some notes on what I’d learned from those weeks. Here’s the list I came up with:

Five Technical tips that should be kept in the back of your mind when troubleshooting MAJOR issues to avoid the HACK title:

Temp tables vs table variables – be consistent, I didn’t think the code would last, so we had scripts with Temp tables and table variables – HACK!.
Insert statement – use full insert statements when using Excel or a SQL Script to create a script. There is a 1000 row limit to using the insert …. values (…), (…), etc.
Instantiate variables – especially if you are using them in loops
Update stats as a performance boost
Check missing indexes

Four Non-Technical Issues to be kept in the back of your mind when troubleshooting MAJOR issues to avoid the Hack title:

Stay focused on the issues.
Project your screen so people can see your logic – you can be the show and they will learn from watching you – at least they will learn that you are smart!
Know the data don’t do extra work just to feel good – don’t waste time checking everything, at times there needs to be the leap of faith that things will work – if there are issues, baby steps is a good method for troubleshooting.
Sometimes quick hacks turn into permanent/repeatable fixes, keep TSQL clean/consistent/use best practices even in your quick fixes – you never know.

That last one was really in my notes – unedited! I said I was going to post a blog about it, but haven’t until now.

So, keep in mind, your quick hacks may turn into permanent fixes, therefore consistently follow best practices and you will avoid the HACK title!

Random Error Generator

I believe I first heard this idea at PASS in Rob Farley’s (blog/twitter) session, “The Incredible Shrinking Execution Plan”. If you want to play a cruel trick on someone, insert this somewhere in a script. I’ve actually used this in testing my deploys to see at what point things blow up. Here’s the script to produce a random error.

DECLARE @ErrorMessage NVARCHAR(4000),
     @ErrorSeverity INT,
     @ErrorState INT;

Set @ErrorSeverity = ROUND((RAND() * (18)),0) –severity 0 –18
Set @ErrorState = ROUND((RAND() * (255)),0)

Raiserror('Operation not allowed',
     @ErrorSeverity,
     @ErrorState) with log

How to: Find the backup history of a DB – TSQL Style

There’s been many times I’ve tried to find the backup history of a database. Questions like when was the last backup taken? Where was it backed up to? etc. I googled a script for this and I used MSSQLTips as a baseline – found here – then altered it to this:

Declare @DBName varchar(255)

Set @DBName = '<<Database Name Here>>'

SELECT  top 10
@@ServerName AS Server,
BUS.database_name,
BUS.backup_finish_date,
CASE BUS.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
BUS.backup_size,
BUMF.physical_device_name
FROM   msdb.dbo.backupmediafamily  BUMF
INNER JOIN msdb.dbo.backupset BUS ON BUMF.media_set_id = BUS.media_set_id
WHERE BUS.database_name = @DBName
And BUS.type = 'D'
ORDER BY BUS.backup_finish_date desc

How to: Find the restore history of a DB – TSQL Style

There’s been many times I’ve tried to find the restore history of a database. Questions like when was the backup taken? Can we re-restore and re-do this? Can we create a copy of the original database? etc. Most of the times I use jobs to restore, but sometimes I’ve got to use LiteSpeed, etc. I had to find the source of a backup this morning – here’s what I came up with for a script:

Declare @DBName varchar(255)
Set @DBName = '<<Database Name Here>>'
Select top 10
     destination_database_name,
     RH.restore_date,
     BUS.server_name as 'SourceServer',
     BUS.database_name as 'SourceDB',
     BUS.backup_start_date,
     BUS.type,
     BMF.physical_device_name
from msdb.dbo.restorehistory RH
Join msdb.dbo.backupset BUS on BUS.backup_set_id = RH.backup_set_id
Join msdb.dbo.backupmediafamily BMF on BMF.media_set_id  = BUS.media_set_id
where RH.destination_database_name = @DBName
Order by RH.restore_date desc

Note – Here’s Technet’s documents on the Backup and Restore Tables in MSDB.

How to: find the name of a server from an IP address

About 8 years ago I worked with a guy who taught me a lot of non SQL IT stuff. He always explained things very well – if I asked. He taught me how to find a server’s name by using the ping command and a switch. I couldn’t remember how we did it and every time I’ve looked up how to do it, I couldn’t figure it out – until today. Open a command prompt and:

Ping -a ###.##.##.##

Note the –a resolves addresses to hostnames. Also, the –a needs to be in the front of the IP! I think that’s the part I couldn’t get figured out. I’ll now have it on my blog if I ever forget again.

Blogged - SQLQuill – Link Round Up – May 2012 Edition http://t.co/XwIom1A1 . 6 hours ago
The SQLQuill Daily is out! http://t.co/dP0SXZRy ▸ Top stories today via @johnkriesel @friendsbwcaw @sqlaj . 9 hours ago
The SQLQuill Daily is out! http://t.co/dP0SXZRy ▸ Top stories today via @sirsql @bwcabloglady @grbeaumont . 2012/05/17
The SQLQuill Daily is out! http://t.co/dP0SXZRy ▸ Top stories today via @cityminneapolis @mjswart @grandmaraismn @sqlpass @des_davila . 2012/05/16
The SQLQuill Daily is out! http://t.co/dP0SXZRy ▸ Top stories today via @sqlinsaneo @gfritchey @sqlchicken @ryanjadams . 2012/05/15
The SQLQuill Daily is out! http://t.co/dP0SXZRy ▸ Top stories today via @gopherhole @markvsql @tjaybelt @gopherathletics @fsnorth . 2012/05/14

SQL Feather and Quill

Twitter

Subscribe

Search Site

Categories

My Other Sites

Archives