-
StatPress TopPosts
Author Archives: David Levy
SQL University Troubleshooting Week: Having a Plan for Every Situation
Today’s SQL University post will highlight the need to have a methodology to address issues that we as IT professionals may encounter in the course of our day. We will start off by looking at why we need to have a plan for every situation, and then we will dig into a methodology that I have developed by stealing bits and pieces of other people’s approaches over my career. Continue reading
SQL University Troubleshooting Week: Keeping an Open Mind
Having a good attitude is key to success in the information technology field. Keeping an open mind is central to that. By going with the flow and looking at issues from the right perspective we can solve problems faster while becoming known for our skill and professionalism. Continue reading
SQL University Troubleshooting Week: Communication
It should come as no surprise that the first topic I am covering this week is communication because the first thing I think anyone should do is communicate that they are troubleshooting an issue. This post will cover why we should communicate then dig into how to put together an initial alert. The rest of the post will be spent talking about how to communicate updates and the resolution. Continue reading
SQL University Troubleshooting Week: Syllabus
I am honored to close out the final week of the Spring 2011 semester with a topic that I really enjoy: Troubleshooting. I really enjoy solving problems and that has caused me to get pulled into many situations where I could use and develop my troubleshooting skills. I hope to share the things I have learned here this week to speed everyone along in the process, hopefully avoiding some of the pitfalls that I had along the way. Continue reading
SQL Saturday 67 Slides Are Now Available
I recently debuted a new presentation, “What To Do When It All Goes So Wrong”. The presentation is designed to give Database Administrators a basic overview of the skills they need to handle virtually any crisis that may arise. While the target audience is DBAs, I feel that most IT Professionals can benefit from the concepts. Continue reading
Looking Up Email Addresses with PowerShell
A week or so ago Aaron Nelson put out a call for help on Twitter looking for anyone that could help with adding autocomplete for looking up an email address to a PowerShell forms application. The original request was to look up the user in the Global Address List (GAL) via Outlook but after some thought we decided to switch to looking up the recipients Active Directory (AD), allowing the search to work on machines that do not have Outlook installed…like servers. I will walk through both functions then provide a quick and dirty test application so you can try out the code for yourself.
Continue reading
Would You Like To Play A Game?
Do you have a favorite line from a holiday movie? Can you change it around slightly to make it SQL related? If so then this is the game for you. Starting this morning and going until we get bored or run out of ideas we will be playing #tsqlHolidayMovieLines on Twitter.
Continue reading
Certificate Based Application Roles
What if I told you that you could sign an assembly that your Windows application uses with a certificate, load that certificate into SQL Server and then define rights on that user-assembly combination. The perfect world where a user would have different rights based on the application they are running but still act under their Windows identity. The perfect world where things work just the way they should. Continue reading
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
How Do I Change SSRS Report Credentials with Powershell?
Ever had to change the login information for all reports in a particular folder? In my case I was going from SQL authentication to Windows authentication for 4 folders with 15-20 reports each. I had done this before, manually, but I broke a few reports and it took hours to complete. Having to do the whole thing a second time meant a script was in order. Continue reading
Careful with the New MCM Requirements
Be careful with the new MCM requirements. You only need the SQL 2008 certifications now. If you have already started down the 2005 path then at least you know you are not alone. I should add that I have been in contact with the right people (not naming here since they may or may not be part of the first line support) to get the incorrect page updated. It may take a couple of weeks to get the incorrect page updated with the coming holiday but I am confident they will get it taken care of. Continue reading
Want to Make Your SQL Server Run Faster for Free?
Want to Make Your SQL Server Run Faster for Free? The trick is to properly manage your VLFs. What are VLFs? The short anser is that VLF stands for virtual log file. SQL Server database log files are made up of many smaller virtual log files that make it easier for SQL Server to manage the log files. Continue reading
Get Drive Space Including Mount Points
Today’s post is a quick one that came out of a conversation on Twitter. To make a long story short, somebody was having trouble with mount points filling up because they were not being caught by the current monitoring script. I offered to look up how my monitoring was figuring out this data and post it here. Continue reading
Troubleshooting SQL Server Error: 18456, Severity: 14, State: 16
I recently struggled with tracking down a rogue process that was failing to log in on one of my servers. It took a bit of fiddling but I eventually found that you can trace login failed due to missing database errors with Profiler. The trick is to look for the User Error Message filtered for TextData Like ‘Cannot open database%’. 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
When is that Restore Going to Finish?
This post describes a query to get the estimated completion time of a process on SQL Server. Continue reading
Posted in General
Tagged dm_exec_requests, estimated_completion_time, percent_complete, SQL 2005, SQL 2008, SQL 2008 R2, SSC, SSP
5 Comments
Stop Forgetting the DAC
We no longer have to blindly restart SQL, hoping that we are not setting up a situation where our most important database will be in recovery for minutes or hours. We now have the tools to accurately diagnose a problem the first time it happens. We just have to remember to use them. 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
Can Deadlocks be Resolved by Adding an Index?
Can Deadlocks be Resolved by Adding an Index? Yes. Really, I’m not kidding. Here, I’ll show you how. Continue reading
Posted in Troubleshooting
Tagged DBCC PAGE, Deadlock, DELETE, R2, SQL 2005, SQL 2008, SSC, SSP
4 Comments
A Little Help with Azure Please
Buying into the cloud was the easy part. Getting to the point where I can convince people in my organization that they should put their application in the cloud is another story. This is where I could use your help today. Continue reading
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”. 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
Quote of the Day
Today is the first day of the rest of your life. Make it yours, own the day. Repeat tomorrow. This has been your wake up call. Continue reading
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 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. Continue reading
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. 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
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. 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
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. 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
Is Anybody Using That Stored Procedure?
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. Continue reading
Posted in Tracing and Profiler
Tagged Profiler, SSC, sys.dm_exec_procedure_stats, Tracing
5 Comments
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). Continue reading
Posted in User Management
Tagged DAC, Dedicated Admin Connection, Dedicated Administrator Connection, SSC
1 Comment
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. Continue reading
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. Continue reading
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. Continue reading
Why Do My SQL 2008 Compressed Backups 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. Continue reading
Why Do I Need All These Servers?
Working in a large corporate environment I am frequently getting requests for a new server to do this or that. Running through the usual questions to determine scope and scale, the price can get high pretty quickly. Inevitably, talk turns to ideas on how to cut the cost while still delivering a robust platform. Continue reading
Posted in General
Tagged Development, Disaster Recovery, DR, HA, High Availability, Production, QA, Release Management, SSC, Staging, Testing
2 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
5 Comments
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. Continue reading
Script Individual User Rights in a Database with PowerShell
Introduction Tonight on Twitter the call went out on #SqlHelp looking for a way to script just database permissions for a specific user. I wrote a script once that I was pretty sure did that so I jumped in to … Continue reading
A Busy/Accidental DBA’s Guide to Managing VLFs
Introduction Properly managing VLFs can make or break the performance of your databases. There is a ton of information out there on the proper management of VLFs, but nothing I have found that tries to boil it down to the … Continue reading
How to Shrink TempDB in SQL 2005
Introduction From time to time you find yourself needing to shrink some space out of TempDB. Shrinking database files is never my first choice but sometimes it is the best I have. Many people think that you cannot shrink TempDB … Continue reading
Posted in File Management
Tagged DBCC FREEPROCCACHE, DBCC SHRINKFILE, Shrink, SSC, TempDB
7 Comments