Say that 5 times fast… Sometimes, you have checked all the tables and views and you cannot find the information you are looking for. You know someone ran a command that messed things up, you don’t want to place blame, you just want to know WHEN it happened. Your last resort is searching the cached [...]
Do you have a server that has been a repository for stored procedures for more than 10 years… Do you even know if all the procs are used, let alone whether they still work after databases have moved, changed names/schemas, had columns refactored and renamed? Here is a handy little program for you. I called [...]
One of the things we have found when doing maintenance is to make sure no jobs kick off in the middle of our manual maintenance tasks. SELECT * INTO msdb.dbo.sysjobs_temp FROM msdb.dbo.sysjobs UPDATE msdb.dbo.sysjobs SET enabled = 0 Then, when you are finished: update sj set sj.enabled = sjt.enabled from msdb.dbo.sysjobs sj join msdb.dbo.sysjobs_temp sjt [...]
Continue reading about SQL: When doing maintenance disable all jobs…
If you are re-attaching databases after a SQL 2008 upgrade, here is a quick n dirty T-SQL command to upgrade to the the correct compatibility level, just run, copy the results and run those: select ‘ALTER DATABASE [' + [name] + ‘] SET COMPATIBILITY_LEVEL = 100′ from master.dbo.sysdatabases
We had a global change of one of our fields here at work that is used in about 5000 tables. It needed to be changed from a varchar(4) to a varchar(10). The challenge was all the data integrity issues, so I wrote up this script to allow us to change the datatypes. It gets the [...]
Continue reading about SQL: Change a datatype updating all PKs and FKs
Sometimes when I come across something in SQL, I usually don’t think of it as a blog topic because I have a mistaken belief that “everyone” already knows this. So, today, a quick tip that was needed for a situation that arose the other day. We have a 2000, 2005 and (blessedly) 2008 environment currently [...]
Continue reading about SQL Tip: Running a large script from command line
I am have been working on a project to Archive tables to a fileserver and one of the things I needed was a way to script out all the objects that I was archiving. Adding a reference to SqlServer.SMO and SqlServer.ConnectionInfo allows you to do this: Public Function ScriptTableOrView(ByVal tablename As String, ByVal servername As [...]
Ok, I need to blog more. I have information that I want to convey and I want to put this on the web for all to see… So, here is my schedule (posts should be up between 4-5pm EST) Monday – Game Review (be it a video game, board game or card game) Wednesday – [...]
I had a job failure over the weekend on a SQL Server and while it gave me the job step number, it occurred to me that this maintenance task that does a DBCC CHECKDB on each database separated into separate job steps was getting difficult to manage. The issue was that as new databases were [...]
In my series of articles to help a DBA who is managing multiple servers, I present my first in a long list of harvests that allow you to have a central repository of information about all your servers. All of these harvests use a similar model. They all cursor through the ServerList table I talked [...]
DandyID
AIM
Amazon
Digg
Ebay
Facebook
Flickr
Google Reader
Gyminee
Hulu
iTunes PodCast
LibraryThing
myOpenID
Netflix
Odeo
Plaxo
Shelfari
Stack Overflow
Stumbleupon
Twitter
YouTube