-
StatPress TopPosts
Category Archives: Utilities
Using DMVs to Find the Ports that SQL Server is Listening On
The other day I was asked to provide the port number that a SQL Server instance was listening on. As luck would have it, the log file from the last time SQL Server started had rolled off so I was left with either using remote desktop to log onto the server and all the risk that includes or finally figuring out how to query it from the DMVs. I opted for the second option. Continue reading
A Brute Force Way to Compress a Database
There are a handful of scripts out there to compress all of of the objects in your SQL 2008 database using Row, Page or a smart combination of both compression types. This is not one of those scripts.
Continue reading
How Do I Spot Identity Columns That Are About to Max Out?
Every so often, usually in the middle of the night or on a holiday weekend, an identity column will hit the maximum size for it’s data type and stop allowing new values to be inserted into the table. I needed a way to identify the identity columns that are in danger of maxing out before they did, so I wrote one. Continue reading
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. Continue reading
Posted in Utilities
Tagged fill factor, fill_factor, SQL 2005, SQL 2008, SQL 2008 R2, SSC, SSP, sys.dm_db_index_physical_stats
13 Comments
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. Continue reading
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.
Continue reading
Posted in Utilities
Tagged RegEx, Regular Expressions, SQL 2005, SQL 2008, SSC, SSMS Tools Pack
2 Comments
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. Continue reading
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. Continue reading
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. Continue reading
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. Continue reading
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. Continue reading
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. Continue reading
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. Continue reading
Posted in Utilities
Tagged CXPACKET, Max Degree of Parallelism, MAXDOP, NUMA, SOS_SCHEDULER_YIELD, sp_configure, SSC, sys.dm_os_sys_info
5 Comments
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. Continue reading
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. Continue reading
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. Continue reading
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. Continue reading
What Do I Need to Do After Moving a Database to SQL 2005/2008?
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. Continue reading
Posted in Utilities
Tagged DATA_PURITY, DBCC, DBCC CHECKDB, DBCC UPDATEUSAGE, sp_updatestat, SSC
Leave a comment
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. Continue reading
Posted in User Management, Utilities
Tagged logins, sp_validatelogins, SSC, validation, xp_logininfo
5 Comments
Stored Procedure to Sequentially Run SQL Agent Jobs
Here is another one of my utility scripts. Lately it seems like all I am blogging about lately is utility scripts, but I have a ton of them and need to get them documented. This stored procedure is used to run a series of SQL Agent jobs sequentially. It is one of my favorite stored procedures because it clears up a lot of headaches for me without having to spend a bunch of money or turn my maintenance schedules over to another team. Continue reading
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. Continue reading
Posted in Utilities
Tagged Agent, Job, SQL 2005, SQL Agent, SQL Agent Job Status, SSC, tsql
6 Comments