Everything a job fail, I used to look at the job history via the GUI. Today, I finally got sick of the stupid ass GUI because when the job history history contains a lot of entry, the damn thing times out or takes FOREVER and you got people breathing down your back asking why the hell the job failed.

Seriously. 3 months archive of a job history sounds decent but when the job runs every freaking minute, it still takes a while to load. So the following query will help you ID the error message faster, via T-SQL. No overhead and no headaches, except for troubleshooting the error of course. For additional information, look to BOL.

exec msdb..sp_help_jobhistory
@job_name = ‘Name of your job’
, @run_status = 0
, @MODE = N’FULL’

I needed to add a schedule to 16 jobs on 16 servers. Again, didn’t feel like clicking thru the gui for all 16 jobs. I ended up using the procedure sp_add_jobschedule. Here’s an example:

At work, I have a table that has a gazillion rows. Last night, I had to update a column with a new value and sometime in the night it failed! FAILED. because the log file got fulled. Fine fine, I added another log file, set it to unlimited growth, what do I care, I just want the thing to finish! 5 hours later, it failed. ARRRG!

I started thinking, how can I update this gazillion row table in batches of 500k rows using the UPDATE statement. The UPDATE statement itself doesn’t allow any sort of batch processing, they should of included it. Stupid developers. Anyways, another way of doing it by batch would be to capture the row number, have three variables @maxRowCount, @minRow and @maxRow, and have a while loop loop thru it or something.

The simplest way would be to use ’set rowcount’. The code I used to updated in batches of 500k look as follow:

I remember the pre and post incrementing function in C++ and Java but yesterday I got a bit of a refresher. So I’ve decided to put it on my blog in case other noobs out there is also looking for the answer. The screenshot pretty much explain it all.

When you are attempting to drop an index on a table, you are given the following error:

An explicit DROP INDEX is not allowed on index ‘db.tbl_name.index_name’. It is being used for UNIQUE KEY constraint enforcement.

This normally means that the index on the table you are trying to modified is being referenced by another table somewhere somehow, which means, you have to find that other table, drop the FK there, and then you can go back to modifying the index. The following script will help you ID which table is use for the index that you are attempting to modify.

Before you drop the FK, you should script it out and then add it again after you made your changes.

The 16 new servers I am working on is getting a lot of these errors:

[Microsoft][ODBC SQL Server Driver][DBNETLIB] General Network error. Check your network documentation

ERROR [08S01] [Microsoft][SQL Native Client]Communication link failure

System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

Mostly the 2nd and 3rd error. I had this same problem on another set of 16 servers and after updating the NIC drivers and whatever else the network guys did, the error/issue still presist. The thing that fixed it was to run the following command on all servers:

Thru out the day, I was just hammering out WHILE loops left and right. Everything was going smoothly when looping thru using 1 condition. Cake walk. Then later in the day, I tried to throw in 2 conditions and disaster struck. The damn script never made it into the WHILE loop. It took me about 30 minutes, plus another 10 minutes discussing WHILE loop w/ a C# dev, and another 30 hammering away with my lead. We couldn’t figure it out. The syntax look like this:

Finally, after all that time, I spoke to another C# dev, who was a bit more interested in trying to help me out. I was told that the WHILE loop conditions has to be both TRUE or both FALSE! My WHILE loop will always have 1 condition equal to TRUE (except for once a day) while the other is equal to FALSE. True enough, when both conditions are either TRUE or FALSE, the WHILE loop work flawlessly.

I wonder if this is different in C/C++/C#. I’ll have to find out.

So, I went to back to the drawing board and came up with the following alternative:

Yesterday, I was deploying some scripts to production. One of the script was to create a database and set its intial size to 50GB for data and log. It created the database alright on the local server but when it attempts to create it on remote servers, it failed with the following error message:

Msg 121, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

A search on the web suggest that it could be a hard disk failure or some shared memory and/or namepipe issue. I called BS on it and refused to go down that route. I thought it might have something to do with the linked server but it wasn’t, based on how the dev wrote the db creation process. A UDL test from serverA to serverB was good, port 1433 was open, ping was responding, and able to access serverB using its virtual name.

After some time, I got annoyed and told the dev that we are going to script out the db and create it manually on each servers and that his script will have to go into the db and add the necessary objects. On all 15 servers, when attempting to create the db manually, it failed with the same error. Amazing. Now I have a feeling it might be the db initialization size that’s causing the problem. So I changed the size from 50GB to 1GB and everything went smoothly.

The 16 servers are running SQL 2005 w/ sp2 and sp2 hotfix.

Remember back in 2000 and 2005 or even older versions, when you were trying to insert multiple rows into a single table? Your code would look something like this

insert into [table_name] (column_1, column_2)
values (’1′,’2′)
insert into
(column_1, column_2)
values (’3′,’4′)
insert into
(column_1, column_2)
values (’5′,’6′)

or

insert into [table_name] (column_1, column_2)
select ‘1′,’2′
union all
select ‘3′,’4′
union all
select ‘5′,’6′

Well, now with SQL 2008, you got this bad ass statement:

insert into [table_name] (column_1, column_2)
values (’1′,’2′), (’3′,’4′), (’5′,’6′)

Don’t you hate it when you are the last one to know about any type of replication error(s)? I mean, you are the bloody DBA for crying out loud. Shouldn’t you be on top of the game?

The attached script will alert you when there are any errors in replication in the last 5 minutes. You can change this value to every one minute if you so desire. It takes one parameter, @servername. From there, it makes a call to the pass-thru server via linked server, and will then do its thing.

This script is NOT suppose to be applied directly to the replication server(s). You have to apply it elsewhere. The reason for this is that you don’t want any type of additional bottleneck on the server.

If there is/are any error(s) within the last 5 minutes, an email will be sent to you.