I had a terribly stressful evening at work last night. I brought a database offline to move a file and when I tried to bring […]
Continue readingCategory: SQL
SQL
Move Data to Another SQL Server in Batches
I ran into a problem where I was unable to move data from one database to another in one INSERT INTO statement. I used the […]
Continue readingFilter results from sp_who2
It can be very helpful to filter the results of sp_who2 in SQL 2008: DECLARE @sp_who2_temp_table TABLE( SPID INT, Status VARCHAR(100), LOGIN VARCHAR(150), HostName VARCHAR(100), […]
Continue readingQuery for All Database Backups Within the Last Week
I was asked to retrieve a list of all database backups within the last week. Unfortunately, I forgot about the backupset table within msdb. However, […]
Continue readingFind the Blocking Job
Sometimes it is useful to find which job is blocking. sp_who2 will show you the running processes, but you will not know which job started […]
Continue readingRename tables, columns, and objects in SQL
I started the New Year renaming some tables and columns to match a new schema. I forgot about a great stored procedure, so wrote this […]
Continue readingQuery for the last SQL database backup date
Helpful to determine when the last database backup occurred. List all databases and the last created date of the backup. SELECT SERVERPROPERTY(‘Servername’) AS [Server Name] ,msdb.dbo.backupset.database_name […]
Continue readingSQL Trace: Duration
A good trace to debug with an sp duration (to limit the results – which can be overwhelming – and check for long running queries): […]
Continue readingSearch for text within columns, tables, store procedures, and jobs in SQL 2008
Search the information_schema for columns, tables, stored procedures, and jobs by name: –Query to list stored procedures that match a pattern select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION […]
Continue readingIndexing a VARCHAR(MAX) column
A helpful article on using a computed column to index a VARCHAR(MAX) column: Indexing VARCHAR(MAX)
Continue reading