Have you seen this type of an informational message in your SQL errorlog before? If so, then I hope the timestamp associated with it was not during the middle of your peak processing time. This is a good indication that someone has been tinkering with some of the SQL configuration settings. SQL Server offers a lot of wonderful configuration options; many of which can be changed dynamically using sp_configure without the need of restarting the SQL Server service. The downside to the dynamic changes is sometimes they can have a detrimental effect on performance, and that’s what this “informational” message is tell...
Continue reading...How to Use xp_dirtree to List All Files in a Folder – Part 2
In a previous blog post, I demonstrated how you can use an undocumented stored procedure, master.sys.xp_dirtree, to list all files in a folder. The previous posting mostly talked about how the stored procedure worked and the output it generated. For this posting, I have expanded upon the script to parse all subdirectories and list all files in all subfolders from the starting point. Just for a quick review, xp_dirtree has three parameters: directory – This is the directory you pass when you call the stored procedure; for example ‘D:\Backup’. depth – This tells the stored procedure how many subfolder levels to display. ...
Continue reading...SQL Saturday #143 – Washington, DC
Last week, I attended my very first SQL Saturday event in Washington, DC. Although I don’t live anywhere near Washington, I was able to make the short trek from Charlotte, NC. It was well worth the time and effort. I missed the previous one in Charlotte back in October, but I have to say now I really hate that I did. I’ve been to SQL Connections in Las Vegas, and SQL Saturday reminded me of a one-day version of it. I wish I could have attended every single session, but at least I was able to hear the presentations that...
Continue reading...T-SQL Terminator
You may have noticed in my code examples, I always include a semicolon “;” at the end of my TSQL statements. If you’re not doing this, then you need to start now. Actually, if you’re not writing TSQL code as a DBA then you need to start now. Starting in SQL Server 2005, Microsoft introduced the TSQL terminator. This syntax is not required for most statements, but at some point in the future it will be required for all. You can read the details on MSDN. As with all new changes to SQL, you should be future-proofing your code. Some statements...
Continue reading...Get Performance Counters from within SQL Server
I think most DBAs know that you can use the Windows Performance Monitor to capture performance metrics for your SQL Server. But did you know that you also have access to all the SQL Server performance counters from within SQL Server? By using using the DMV sys.dm_os_performance_counters, you can view that performance data. As with all DMVs, there are a few drawbacks. The data collected is only valid since the last time SQL Server was started, and this DMV does not allow access to any of the Window OS performance counters. For example, we can query the DMV to find...
Continue reading...