Generated by All in One SEO v4.9.3, this is an llms.txt file, used by LLMs to index the site.

# Adventures In SQL

Random thoughts, rants, discoveries and things I wish I had done better

## Sitemaps

- [XML Sitemap](https://adventuresinsql.com/sitemap.xml): Contains all public & indexable URLs for this website.

## Posts

- [A Busy/Accidental DBA's Guide to Managing VLFs](https://adventuresinsql.com/2009/12/a-busyaccidental-dbas-guide-to-managing-vlfs/) - A quick primer on managing Virtual Log Files (VLFs) in SQL 2005 transaction logs.
- [What Did that Geek Just Say?](https://adventuresinsql.com/2010/09/what-did-that-geek-just-say/) - The National Institute of Standards and Technology, NIST, maintains a Dictionary of Algorithms and Data Structures. The dictionary is a great place to go to find out what people are talking about. It is also a great place to looking for different ways of doing things. I sometimes even go to the site just to look for entertaining technical concepts, like the "Cactus Stack", "Stooge Sort" or even "Big-O Notation".
- [How is Fill Factor Impacting My Indexes?](https://adventuresinsql.com/2010/09/how-is-fill-factor-impacting-my-indexes/) - The theme for this month's T-SQL Tuesday is indexes so it seemed like the perfect excuse to blog about a script that I have written to see what choices for fill factors on indexes actually does to the structure of those indexes.
- [A Stored Procedure to Move SSIS Packages Between Servers](https://adventuresinsql.com/2010/09/a-stored-procedure-to-move-ssis-packages-between-servers/) - This post provides the code for a stored procedure to move SSIS packages between SQL 2005 SSIS servers.
- [Great News! SSMS Tools Pack 1.9 is Coming Out!](https://adventuresinsql.com/2010/08/great-news-ssms-tools-pack-1-9-is-coming-out/) - Mladen Prajdic recently announced that the newest version of SSMS Tools Pack is coming out and I am excited.
- [How Can I Quickly Script Out Replication?](https://adventuresinsql.com/2010/07/how-can-i-quickly-script-out-replication/) - This script takes a distribution server name and an output directory as parameters then works through all publications on each of the servers that connects to the distribution server, scripting them out.
- [What is an Easy Way to Return Results from a CLR Stored Procedure?](https://adventuresinsql.com/2010/07/what-is-an-easy-way-to-return-results-from-a-clr-stored-procedure/) - This post describes a helper class that I came up with to handle returning values from a CLR stored procedure.
- [Why Would a Delete Make My Database Grow?](https://adventuresinsql.com/2010/06/why-would-a-delete-make-my-database-grow/) - A while back I had a developer come to me complaining that every time they ran a large delete statement on a certain database the delete would fail with a message claiming the database was full. My first instinct was that they were doing something wrong so I asked for the script so I could try it myself. To my surprise, running the delete actually did fill the database.
- [What is a Good Way to Run CheckDB on a VLDB?](https://adventuresinsql.com/2010/05/what-is-a-good-way-to-run-checkdb-on-a-vldb/) - Today's script is one that I wrote based on the logic outlined in a post by Paul Randal. This script is written for SQL 2000 but, as Paul notes, the logic will work on SQL 2005.
- [How Do I Move SQL Database Files Around?](https://adventuresinsql.com/2010/05/how-do-i-move-sql-database-files-around/) - Here is a script that will generate a script to move database files around in SQL 2005/2008.
- [Where Do I Start with PowerShell?](https://adventuresinsql.com/2010/05/where-do-i-start-with-powershell/) - This morning I set out to get some information about getting started in PowerShell for a coworker. Rather than spend a bunch of time searching for different sites I threw the question out to the SQL Community on Twitter via #SqlHelp. The response was so overwhelming that I decided I at owed it to the SQL Community to get it all written down.
- [How Can I Tell if SQL Agent is Running via SQL?](https://adventuresinsql.com/2010/05/how-can-i-tell-if-sql-agent-is-running-via-sql/) - Today's post is a continuation in my on-going effort to document all of the scripts I use to manage my environment. To date it has been a great exercise both in getting things written down and in getting feedback from others on better ways I could be doing things. To that end, here is a stored procedure that I use to tell if SQL Agent is running before trying to start a job programmatically.
- [What is a Good Way to Get the SQL Server Log Directory?](https://adventuresinsql.com/2010/05/what-is-a-good-way-to-get-the-sql-server-log-directory/) - The script for today is one that I use in all of my job creation scripts as well as a script I have to go back and clean up job output file paths. It gets the location of master on a SQL 2005 or SQL 2008 server, using that to find the Log directory.
- [What is a Good Way to Quickly Disable SQL Agent Job Schedules?](https://adventuresinsql.com/2010/04/what-is-a-good-way-to-quickly-disable-sql-agent-job-schedules/) - This script has 2 major parts, disable the job schedules then notify SQL Agent of the change.
- [Whats a Good Rule for Max Degree of Parallelism?](https://adventuresinsql.com/2010/04/whats-a-good-rule-for-max-degree-of-parallelism/) - I am one of those people that believes that believes that anything worth doing is worth having a script to do it. Setting the Max Degree of Parallelism is no exception. In this post I will go through the script I use as well as the metrics I watch to make sure the setting is correct.
- [How Do I Find SSIS Packages that Exist in Multiple Folders?](https://adventuresinsql.com/2010/04/how-do-i-find-ssis-packages-that-exist-in-multiple-folders/) - A script to find duplicate packages deployed to different folders on the same server.
- [How Do I Kill All Sessions from a Windows Group?](https://adventuresinsql.com/2010/04/how-do-i-kill-all-sessions-from-a-windows-group/) - The stored procedure I am posting today will kill all sessions for users that are members of a given domain group. I use this stored procedure to keep ad-hoc (write down Access) users out of the way of nightly builds on my data warehouse.
- [T-SQL Tuesday #005 – Self Service Performance Information](https://adventuresinsql.com/2010/04/t-sql-tuesday-005-self-service-performance-information/) - My T-SQL Tuesday #005 entry allows selected users of a server to see what is going on with it and react accordingly, freeing members of the DBA team from constant questions about why the server is running slow. Admittedly it does often just lead to more informed complaints but at least the time to resolution is quicker.
- [How Can I Tell if a Windows Login has Rights to My Server?](https://adventuresinsql.com/2010/04/how-can-i-tell-if-a-windows-login-has-rights-to-my-server/) - This is a great stored procedure to use for things like server dashboards where you only want people to see the dashboard for servers that they have access to without granting them all the rights that would go with the dashboard.
- [Script to Create A TempDB File Per Processor](https://adventuresinsql.com/2010/04/script-to-create-a-tempdb-file-per-processor/) - To satisfy my need to be lazy and protect me from myself I have written a script to add a TempDB file per processor.
- [What Do I Need to Do After Moving a Database to SQL 2005/2008?](https://adventuresinsql.com/2010/04/what-do-i-need-to-do-after-moving-a-database-to-sql-20052008/) - I recently moved a database from SQL 2000 to SQL 2005. It was really just a matter of detaching the database, copying the files, attaching it on the new server, flip the compatibility mode and run a few queries. Thats it? Could it have been so easy? It turns out the answer is no. There are some steps that need to be taken after moving a database to SQL 2005 or 2008 from SQL 2000.
- [How Do I Identify Invalid or Unneeded Logins?](https://adventuresinsql.com/2010/04/how-do-i-identify-invalid-or-unneeded-logins/) - I was looking at one of my servers and was caught off-guard by how many individual users had been granted rights. To make it worse I recognized a few user names as former coworkers. Clearly time for some house cleaning. Here is the script I came up with to do the analysis.
- [Is Anybody Using That Stored Procedure?](https://adventuresinsql.com/2010/04/is-anybody-using-that-stored-procedur/) - From time to time I get asked to check if a stored procedure is still used in preparation for dropping it. This post outlines some different ways to get after that information and how sometimes it can go horribly wrong.
- [Get Users By Connection](https://adventuresinsql.com/2010/04/get-users-by-connection/) - Here is some TSQL to get who is connected to a SQL 2005 server by connection type, including a filter to focus on the Dedicated Administrator Connection (DAC).
- [Managing the Size of the Security Cache (TokenAndPermUserStore) in SQL 2005](https://adventuresinsql.com/2010/03/managing-the-size-of-the-security-cache-tokenandpermuserstore-in-sql-2005/) - In an earlier post I walked through the diagnosis of a problem with TokenAndPermUserStore bloat in SQL 2005. In this post I will go through what I did to work through the issues and the results of those efforts.
- [My Adventures with the SQL 2005 Security Cache](https://adventuresinsql.com/2010/03/my-adventures-with-the-sql-2005-security-cache/) - This post describes the process of diagnosing a lesser known performance issue in SQL 2005 and higher on servers with a lot of physical memory. The performance issue is caused by security cache bloat and is very difficult to miss unless you know what you are looking for.
- [Community Based Training](https://adventuresinsql.com/2010/02/community-based-training/) - There is a real un-served niche in the market for DBA delivered community training materials. The idea would be to work together as a community to create common course materials and delivery scripts that would be presented by individual DBAs to the teams they support. Everyone would get roughly the same experience while having a trainer that can speak directly to their specific questions or even look at examples using their specific data.
- [Why Do My SQL 2008 Compressed Backups Shrink?](https://adventuresinsql.com/2010/02/why-do-my-sql-2008-compressedbackups-shrink/) - Recently a coworker was doing some research with SQL 2008 Backup Compression on a very large database. During the testing they noticed that the backup file shrunk rather than grew. It turns out this behavior is intentional and beneficial.
- [Why Do I Need All These Servers?](https://adventuresinsql.com/2010/02/why-do-i-need-all-these-servers/) - Builiding out server environments can get pricey quickly. I try to outline the basic information to educate users as to what they really need.
- [Stored Procedure to Sequentially Run SQL Agent Jobs](https://adventuresinsql.com/2010/01/stored-procedure-to-sequentially-run-sql-agent-jobs/) - This stored procedure is used to run a series of SQL Agent jobs sequentially.
- [Stored Procedure to Get SQL Job Execution Status](https://adventuresinsql.com/2010/01/stored-procedure-to-get-sql-job-execution-status/) - Ever need a programmatic way to figure out if a SQL Agent job is running? I did. After spending a while searching on Google I did not turn up anything I liked so I turned to Profiler and started looking at what calls SQL Management Studio uses. After a bit of poking around I was able to come up with a stored procedure that I could call.
- [Using sp_ExecuteSql to Run Dynamic SQL](https://adventuresinsql.com/2010/01/using-sp_executesql-to-run-dynamic-sql/) - Dynamic SQL should be considered a highly specialized tool of last resort and used properly. Misuse can lead to your server and the data stored on it no longer belonging to you.
- [Stored Procedures to Store and Get Database Users with All Permissions](https://adventuresinsql.com/2009/12/stored-procedures-to-store-and-get-database-users-with-all-permissions/) - Stored Procedures to Store and Get Database Users with All Permissions
- [Script Individual User Rights in a Database with PowerShell](https://adventuresinsql.com/2009/12/script-individual-user-rights-in-a-database-with-powershell/) - Script Individual User Rights in a Database with PowerShell
- [How to Shrink TempDB in SQL 2005](https://adventuresinsql.com/2009/12/how-to-shrink-tempdb-in-sql-2005/) - A quick way to shrink TempDB in SQL 2005.

## Pages

- [Privacy Policy](https://adventuresinsql.com/privacy-policy/) - Who we are Our website address is: https://adventuresinsql.com. Comments When visitors leave comments on the site we collect the data shown in the comments form, and also the visitor’s IP address and browser user agent string to help spam detection. An anonymized string created from your email address (also called a hash) may be provided
- [What To Do When It All Goes So Wrong](https://adventuresinsql.com/presentations/what-to-do-when-it-all-goes-so-wrong/) - As IT Professionals we inevitably will see situations where everything goes wrong. At times we are somewhat lucky and this just means diminished functionality or a slow system. Other times our organization is temporarily out of business. Regardless of the scope of the issue, how we react can have a direct impact on how quickly things are returned to normal. This session will cover how to communicate issues, including what to say, who to say it to and when to say it. Part of managing communication is to get everyone into a room, forcing them to talk, so time will be spent on designing an effective war room. The session will also cover how by setting out to prove that an issue is ours we are able to more quickly get at a root cause.
- [Getting Out from Behind the Curtain - The New DBA](https://adventuresinsql.com/presentations/getting-out-from-behind-the-curtain-the-new-dba/) - This presentation will provide a high level overview of how to boost your value to your organization by automating the mundane operational tasks and focusing on supporting new development.
- [About](https://adventuresinsql.com/about/) - I am a Senior SQL Server DBA at with more than 10 years of IT experience, mostly as a software developer building web and windows based applications (VB, VB.NET, C#, C++ and a smidge of Java). I have always found database design and set based logic interesting, so 3 years ago I took the plunge and became a DBA, soon after I discovered people would tell anyone who would listen all about the SQL Server internals. I was hooked. I have not looked back since.
- [Presentations](https://adventuresinsql.com/presentations/) - This page contains links to the presentations that I have given at local user groups and SQL Saturdays. Click the link below the presentation name to see the abstract and slide deck. Getting Out from Behind the Curtain – The New DBA https://adventuresinsql.com/presentations/getting-out-from-behind-the-curtain-the-new-dba/ Delivered at SQL Saturday 31 - Chicago, SQL Saturday 40 - South

## Categories

- [File Management](https://adventuresinsql.com/category/file-management/)
- [General](https://adventuresinsql.com/category/general/)

## Tags

- [SSC](https://adventuresinsql.com/tag/ssc/)
- [SSP](https://adventuresinsql.com/tag/ssp/)
- [Virtual Log Files](https://adventuresinsql.com/tag/virtual-log-files/)
- [VLF](https://adventuresinsql.com/tag/vlf/)