Author Archives: admin

24 Hours of PASS Spring 2017

The Spring 2017 24HOP focused on data security, a subject near and dear to me. One of favorite presentations was Bob Pusateri’s Passive Security for Hostile Environments. He showed what measures you can take when you have a situation where … Continue reading

Posted in Uncategorized | Leave a comment

Controlling Large Deletions and Transaction Log Growth

Deleting from very large tables can cause uncontrolled transaction log growth.  The way around this is to perform an iterative TOP(n) delete where you limit the number of rows to be deleted in each batch.  I got this idea from … Continue reading

Posted in Uncategorized | Leave a comment

Troubleshooting beyond the database

Users were seeing transient performance slowdowns every day between 15:00 and 16:00.  The slowdowns would typically last for 2-3 minutes and occur one or more times during this time window.  After looking at things on the SQL Server and database … Continue reading

Posted in Performance | Comments Off on Troubleshooting beyond the database

Way too Many Virtual Log Files

Holy cow! Today I encountered a database with nearly 10,000 VLFs! I should have taken a screenshot. I used Kimberly Tripp’s guidance to get things under control

Posted in Uncategorized | Comments Off on Way too Many Virtual Log Files

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

Free SQL Server training videos

The people over at SQLSkills.com have made their MCM training videos available online for free.  They’re pretty incredible.  Subject areas include database internals and performance, high availability and disaster recovery, performance tuning,  security and development support.  Link here:  http://www.sqlskills.com/T_MCMVideos.asp   … Continue reading

Posted in Performance, SQL Server | 1 Comment

PowerShell Script to Clean Up Old Files Based on Age

Here’s an extremely simple PowerShell script to remove old files based on age. You could use it to enforce a policy that keeps 90 days of ETL input files on a server. Files older than that would purged with this … Continue reading

Posted in Utilities, Windows OS | 3 Comments

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

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

Moving

I originally blogged on Yahoo 360, but it is a lame duck, so I’m moving my postings over to WordPress.

Posted in Uncategorized | Comments Off on Moving

Basic Database Inventory Script

CREATE TABLE #tempresults2 ( database_name NVARCHAR(128), backup_start_date DATETIME ) INSERT INTO #tempresults2 SELECT database_name, backup_start_date FROM msdb.dbo.backupset AS b1 WHERE backup_start_date = ( SELECT MAX(backup_start_date) FROM msdb.dbo.backupset AS b2 WHERE b1.database_name = b2.database_name AND TYPE IN (‘D’, ‘I’) ); WITH … Continue reading

Posted in SQL Server | Comments Off on Basic Database Inventory Script

Query to get last database backup dates

Here’s a simple query to find the last backup dates:   SELECT backup_start_date, database_name FROM msdb.dbo.backupset AS b1 WHERE backup_start_date = ( SELECT MAX(backup_start_date) FROM msdb.dbo.backupset AS b2 WHERE b1.database_name = b2.database_name AND type = ‘D’ )

Posted in SQL Server | Comments Off on Query to get last database backup dates

Windows shell script: putting date & time into a file

SET mydate=%DATE:~4,2%_%DATE:~7,2%_%DATE:~10,4% echo mydate >> somefile.txt

Posted in Windows OS | Comments Off on Windows shell script: putting date & time into a file

Earlier Postings

…stuff imported from an earlier journal 2/3/07  I stumbled across a number of useful SQL security tools at SQLSecurity.com: http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx 1/23/07  I ran into a problem configuring SQL XML support in IIS 6.0. When I went to do a query … Continue reading

Posted in Uncategorized | 1 Comment