In SQL 2008, there’s a built in SQL job named: Syspolicy_purge_history. This sucker’s job is to purge your job history every day so that it doesn’t pile up on your server. In the past, it would take forever to get the SQL job log because it has to read all those past job run. By [...]
Archive for the “sql” Category
Finding Execution Plan
Use the following to execution plans that’s cached on the SQL server.
Return a status or value from sp_start_job
In my procedure, I’m calling a job on a remote server and if this call fails, I would like the code to handle it accordingly. So if I get a return value or status of 0, the job started successfully. If 1, then it failed to start. Now I can’t monitor the entire duration of [...]
Creating Windows Clustered Resources
Over the past 3 weeks, I’ve built over 40 pairs of active/active cluster on Windows 2003. I’ve looked around the web, to see if someone has any type of script that will help me, but alas, everyone wants to show me how to PnC (point & click) my way thru the bullshit GUI. Don’t get [...]
sql 2005 connection error
Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. I have a server named: virtualA\virtualSQLA. This server was working fine a few days ago [...]
finding physical servername
For some reason, I have a problem remembering server name but don’t have any problem remembering SQL name instances. Maybe because I connect to the name instance more often then not. But when someone ask me what’s the physical servername of so and so SQL name instances, i’m lost. a co-worker showed me a SQL [...]
order by asc for two (2) columns
a co-worker today asked if there was a way to insert data from one table into another but sort the columns by ASC for BOTH columns. For example, tableA has the following value: rowid: 1,2,3,4 columnA: 3,7,1,6 columnB: 6,7,2,4 In his new table, he wants it to look like so (tableB): rowid: 1,2,3,4 columnA: 1,3,6,7 [...]
Finding filesizes
Yesterday, I was tasked w/ finding and identify the filesize of some files located in some directory. SQL doesn’t really have a function to do this and neither does a straight up DOS’ dir command. Forunately, the Internet is a big place and someone wrote a one line syntax to do what I needed. @echo [...]
Reviewing Job History
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 [...]
SQL sp_add_jobschedule
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: