We recommend that you have JavaScript enabled to enjoy the awesomeness of this site!

  • The page you are on right now   ⟶
  • EM Category   ⟶
  • SQL Server
  • ExposureMind SQL Server RSS Feed

T-SQL script to find transactions that are blocking

I was working on some code for another project at work today when a I got call from one of the users that I support. They had explained to me that the web application they use to pull reports wasn't working. Knowing the history of this particular application I had a feeling there as a blocked process on the SQL server that was preventing other processes or transactions from running. I wrote this script awhile back, and I thought I would share it. It queries the sys.dm_tran_locks table, then joins the sys.databases, and joins the sys.dm_os_waiting_tasks to see what transaction is causing the block. Before giving the query below are the details about each table we are going to query to get our results.

What is the sys.dm_tran_locks table?
...

View Article »

Finding Duplicate Records

This past weekend I had to find duplicate records in a table in one of my databases. I recalled staring at the screen thinking to myself, "Man, I know I have done this. How did I go about doing this. CRAP!" Then it clicked. I had an existing script that I did from a previous project tucked away in my SQL script bag of tricks. The script below is the most basic script used to find duplicates. The main components of this scripts are the count() function, group by clause, and the having clause.

select pc, count(pc) as pctotal
from locations_tbl
where pc != ''
...

View Article »

Reset your primary key column (identity) in your MS SQL table

I was working with a clients database earlier this morning, as they are in the process of cloning their primary web application for another client. Their internal DBA just took a backup of the database, and restored it to a new location. So as I was going through the database I realized that I would need to reset the primary keys for some of the tables in the database. I don't this very often, and I had to dive into my bag of script goodies to remember how to do this. So, I thought I would share:

DBCC CHECKIDENT('table_name', RESEED, 0)

Just replace the table_name with the actual name of the table in your database, and specify the number you want the primpary key (identity) to restart with, and BAM you are done.

View Article »

Updating Replica Server Mount Paths

This past weekend I was apart of a massive upgrade of Windchill 9.1 to Windchill 10. One the things I ran into, and I thought I would share, was that the Vault Configuration UI under the File Server Administration module within Windchill 10 doesn't allow you to update the mount paths. For this specific upgrade we needed to move the remote vault root folders and replica folders to a more central location on all of the remote cache servers. Prior to the upgrade the root and replica folders was located under the Windchill 9.1 Windchill folder (D:PTCWindchill_9.1WindchillVaults). For this upgrade we moved these folders to the root of the D drive (D:Windchill_Vaults).

After the upgrade of the master server  was complete all of the root and replica folder mounts were in...

View Article »

« Previous   1   Next »