-
Recent Posts
Personal Links
SQL Server Links
Windows OS
Meta
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
Tagged AlwaysOn
Comments Off on AlwaysOn Availability Group Aware SQL Agent Job Step
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
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
