Category Archives: T-SQL

AlwaysOn Availability Group Aware SQL Agent Job Step

The SQL Agent job step that I present here is intended to make a job execute only if the node currently owns a specified replica. The T-SQL for this job step leverages a function that was published in SQL Server … Continue reading

Posted in SQL Server, T-SQL, Uncategorized | Leave a comment

Leveraging SP_Whoisactive for Monitoring and Troubleshooting BI/DW Workloads

Wait stats analysis is often my tool of first resort for triaging performance problems. Adam Machanic’s SP_Whoisactive utility is my tool of choice for getting insight into what is happening on a server. Data Warehouse ETL processes often occur overnight. … Continue reading

Posted in SQL Server, T-SQL | Comments Off on Leveraging SP_Whoisactive for Monitoring and Troubleshooting BI/DW Workloads

Deduplicating files with LogParser and SQL Server

Perhaps this should be categorized as a stupid people trick. On the other hand, it’s a good, cheap way to deduplicate files assuming you’re running SQL Server and LogParser. This post assumes at least a basic knowledge of T-SQL and … Continue reading

Posted in T-SQL, Utilities, Windows OS | Comments Off on Deduplicating files with LogParser and SQL Server

SQL Generators for moving database files

Here are several trivial scripts for updating the system catalog, moving files via robocopy, and adjusting filegrowth values. I hope someone finds them useful. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 … Continue reading

Posted in T-SQL | 1 Comment

Index to Filegroup mapping

Here is a trivial script to show where a particular index resides. It saves clicking around the SSMS GUI. 1 2 3 4 5 6 7 SELECT i.name, i.type_desc, i. is_primary_key, i.is_unique, s.name AS [Filegroup] FROM sys.indexes i INNER JOIN … Continue reading

Posted in SQL Server, T-SQL | Comments Off on Index to Filegroup mapping

Trigger Mass Enable / Disable

Pretty trivial, really: SELECT ‘ALTER TABLE ‘ + OBJECT_NAME(PARENT_ID) + ‘ ENABLE TRIGGER ‘ + [NAME] FROM sys.triggers     …add a where clause if needed.  Paste the output back into the query window and modify as needed.  You can also slap … Continue reading

Posted in T-SQL | Comments Off on Trigger Mass Enable / Disable

Getting data file space usage

Here is a simple query to get space usage by data file:  SELECT     a.FILEID,     [FILE_SIZE_MB] = CONVERT(decimal(12,2),ROUND(a.size/128.000,2)),     [SPACE_USED_MB] = CONVERT(decimal(12,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.000,2)),     [FREE_SPACE_MB] = CONVERT(decimal(12,2),ROUND((a.size–FILEPROPERTY(a.name,'SpaceUsed'))/128.000,2)) ,     NAME = LEFT(a.NAME,24),     FILENAME = LEFT(a.FILENAME,55)     FROM dbo.sysfiles a 

Posted in SQL Server, T-SQL | Comments Off on Getting data file space usage

Useful Multipliers

Multiply SQL database pages by 0.0078125 to get space in Megabytes Multiply SQL database pages by 0.00000762939453125 to get space in Gigabytes Here are a couple of examples of where this is useful: This query uses a DMV to return … Continue reading

Posted in SQL Server, T-SQL | Comments Off on Useful Multipliers

Dynamic SQL – must declare scalar variable

I ran into a situation today where I was generating dynamic SQL that appeared to be valid.  I could take the generated SQL strings and execute them with no issues, but when executed dynamically, I got the error message “Must … Continue reading

Posted in T-SQL | Comments Off on Dynamic SQL – must declare scalar variable

Shrinking all log files

I’m not a big proponent of shrinking files. There are a lot of postings on DBA forums citing why this practice should be avoided. Tibor Karzai in particular has an article on why this is a bad idea. Shrinking log … Continue reading

Posted in SQL Server, T-SQL | Comments Off on Shrinking all log files

The Good Books

I used several books to study for the ICCP DBA exam including: Modern Database Management, 8th Edition by Hoffer, Prescott and McFadden Database Systems: Design, Implementation & Management 6th Edition by Rob and Coronel Database Administration: The complete Guide to … Continue reading

Posted in T-SQL | Comments Off on The Good Books

Getting HH:MM:SS value from Seconds

Here is another handy date manipulation function. This one converts the number of seconds, expressed as an int, into the HH:MM:SSS format SELECT CASE WHEN @seconds/3600<10 THEN '0' ELSE ” END + RTRIM(@seconds/3600) + ':' + RIGHT('0'+RTRIM((@seconds % 3600) / … Continue reading

Posted in T-SQL | Comments Off on Getting HH:MM:SS value from Seconds

Simple SQL Server Log Filter / Review Script

Here’s a very basic log filtering /review script that you can run on a daily basis to review your SQL Server logs. It shows the last two days of log entries filtering out logon and backup entries. — daily log … Continue reading

Posted in T-SQL | Comments Off on Simple SQL Server Log Filter / Review Script

First stab at a database inventory script

Compatible with sql server 2k5: USE master CREATE TABLE #tempresults ( [name] sysname, db_size NVARCHAR(13), [owner] sysname, [dbid] smallint, created NVARCHAR(11), [status] NVARCHAR(600), compatibility_level tinyint ) INSERT INTO #tempresults EXEC sp_helpdb SELECT db.name, tr.db_size, db.compatibility_level,is_auto_shrink_on, state_desc, recovery_model_desc, page_verify_option_desc FROM sys.databases … Continue reading

Posted in SQL Server, T-SQL | Comments Off on First stab at a database inventory script

Searching for a column name in a SQL database

Here are a couple of different ways to search for a column name within a SQL Server database: — using information_schema views (preferred method) SELECT sc.table_name    FROM information_schema.columns sc    INNER JOIN information_schema.tables st      ON sc.table_name = st.table_name … Continue reading

Posted in SQL Server, T-SQL | Comments Off on Searching for a column name in a SQL database

Script to Return Space Used for All Tables in a Database

Here’s a handy script to return space used by table:  spaceused.html

Posted in SQL Server, T-SQL | Comments Off on Script to Return Space Used for All Tables in a Database