DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

SQL Server Error Log Consuming Lots of Disk Space

Posted on June 17, 2012 Written by Andy Hayes Leave a Comment

I came across a problem this week with one of our SQL Servers whereby one of the drives was very low on space. Whenever I come across a disk space problem, I use my trusty friend Treesize which is a free download and it enables me to quickly find where the space is being consumed.

Having run the tool, I quickly found the culprit. It was the SQL Server error log consuming gigabytes of disk space.

Thankfully this is relatively easy to resolve.

Here are my notes…..

Where is the SQL Server Error Log?

Typically inside the “Log” directory of your SQL Server instance so for example on my laptop it is here.

C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\Log

Recycle the SQL Server Error Log using sp_cycle_errorlog

You can view the logs and their contents in Management Studio by accessing the “Management” node and by either double clicking or right clicking the logs

sql server error log

So in order to get my disk space back, I ran a stored procedure called sp_cycle_errorlog which will create you a new log and move the old log to a new file called ErrorLog.1 inside your error log directory. sp_cycle_errorlog is installed with SQL Server.

Each time you run this command, a new log is created and the existing logs are cycled until the max number of allowed error logs is reached.  So you get ErrorLog.1, ErrorLog.2 etc in your log directory. When the max logs is reached, the oldest file is removed.

I decided that I did not want to keep the logs as this was a development server and I was aware of what messages were consuming the space. For a production server, you may want to harvest the logs onto another drive before running sp_cycle_errorlog to completely remove the log files.

You can adjust the number of log files to be retained, the minimum is 6 and the maxium is 99 and this can be configured by right clicking the SQL Server Logs node and choosing the configure option.

For more information on sp_cycle_errorlog, you can visit this link

Filed Under: All Articles, SQL Server Administration Tagged With: sql server, troubleshooting

Using DBCC INPUTBUFFER for SQL Server troubleshooting

Posted on May 27, 2012 Written by Andy Hayes Leave a Comment

dba toolboxIn my last post on sp_who2 I demonstrated how this handy utility can help you in your SQL Server troubleshooting efforts.

Continuing on from this, I am going to cover DBCC INPUTBUFFER

What is DBCC INPUTBUFFER ?

It is a command used to identify the last statement executed by a particular SPID. You would typically use this after running sp_who2. The great thing about this DBCC command is that it shows the last statement executed, whether running or finished.

How to use

DBCC INPUT BUFFER will generate errors so ensure that the space between INPUT and BUFFER is omitted.

In this example, I have just run run sp_who2 and identified that my lead blocker is SPID number 54 so I would run as follows:

T-SQL:

DBCC INPUTBUFFER(54)

Here is a simple demonstration. In a new query window run this

BEGIN
UPDATE Orders SET Amount = 500 WHERE OrderID = 1;

Now in a second query window run this

UPDATE Orders SET Amount = 200 WHERE OrderID = 1;

Now run sp_who2 to identify the lead blocker followed by DBCC INPUTBUFFER using the lead blocker SPID. Here are my results with the column “EventInfo” providing the detail that I need on the T-SQL which is causing the problem. It is possible to copy and paste the code into a query window for analysis.

using dbcc inputbuffer for sql server troublshooting

Permissions needed for DBCC INPUTBUFFER

You need to be a sysadmin or have the VIEW SERVER STATE permission to be able to run this command.

Conclusion

Again, like sp_who2, this handy command should be in your “DBA Toolbox”.

UJNZMQQN5JCV

Filed Under: All Articles, SQL Server Administration Tagged With: dbcc, sql server, troubleshooting

Using exec sp_who2 to help with SQL Server troubleshooting

Posted on May 20, 2012 Written by Andy Hayes 4 Comments

If you haven’t used sp_who2 before then it is a great utility to help in diagnosing a problem with your database application.

What is sp_who2 ?

It’s a stored procedure which is installed with SQL Server which when run, outputs a row for each “SPID”. SPID stands for Server Process ID and one is created every time an application needs to open a connection to SQL Server.

Each row contains a number of useful columns including information on things like the status of the SPID, what its resource usage is in terms of CPU/IO and what login is currently executing the command.

Permissions needed for sp_who2

A login can run sp_who2 and obtain information about its own connection. For a full list of SPID’s, either the login has to have sysadmin permission or VIEW SERVER STATE permission.

Using sp_who2 to help identify blocking queries

Lets say for example that the phone rings and everyone in the department using the sales system is complaining that their copy of the application is freezing when trying to access the customer sales order data.

So for this blocking demo, I have created a simple table in my database called “Orders” and I am going to start a transaction and leave it open without committing or rolling it back.

BEGIN TRAN
INSERT INTO Orders(AccountID, DatePlaced, Amount, Currency)
VALUES(1, GETDATE(), 100, '$');

Now I will try and read the orders table via another query

SELECT * FROM Orders;

and again via another query…

SELECT * FROM Orders;

Now I will run exec sp_who2 to check what is happening to these connections and it tells me that I have two blocked SPID’s (55 and 56) and that they are being blocked by SPID 54
Using exec sp_who2 to identify blocked queries
The DBA now has to decide how best to proceed with resolving this problem based on the cause and effect of taking action.

They will try and work out what the lead blocker is doing and one way they might do this would be to use “DBCC INPUTBUFFER(SPID)” where SPID is the number of the connection to be analysed and the results returned tell the DBA what the command/query was executing as that SPID.

Instead of using sp_who2, another way would be to look at the dm_exec_requests DMV. I’m going to look at these in a future post.

In my demo it is easy to resolve this as I can kill the connection, force a commit or force a rollback and it will be instantly resolved.

It might not be as clear cut as this in another scenario. Lets say that a process has been running for some time with a large update and this causes the blocking problem described in the demo.

When the DBA views the results of sp_who2 and notices that the process has been running for many hours, they know that by killing the process, a rollback has to finish before the resource will become available again for other queries to be able to complete.

The rollback could also take many hours in this instance and so such decisions cannot be taken lightly. The solution will vary depending on the scenario.

With this tool, you can check a specific connection. It takes an optional parameter SPID, for example:


exec sp_who2 54


Something I get asked is whether there is a way to check for active connections by running a filter – something like exec sp_who2 ‘active’. Sadly this doesn’t work but there is a simple way and that is to capture the results to a temporary table or temporary variable and then query that.

There is a nice piece on this over at Stack Overlow which is worth a read and demonstrates a few solutions to this.

Conclusion

sp_who2 should be part of every DBA’s troubleshooting toolbox.

It provides a great overview of what the connections are doing on the SQL Server and can quickly help the DBA find reasons for increases in application timeouts, high disk IO or high CPU pressure.

 

Filed Under: All Articles, SQL Server Administration Tagged With: performance, sql server, troubleshooting

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • How to shrink tempdb
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright © ‘2021’ DBA Diaries built on the Genesis Framework

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close