Category Archives: SQL Server

Hostname Column in SQL Profiler 2005

If you are using SQL Profiler and you want to know which workstation is using the db you should use the hostname column in sql profiler.
While this was relatively straight forward in SQL Server 2000, users of SQL Server 2005 may find that the hostname column is not displayed when running the SQL Profiler. By default, SQL Server 2005 is hiding this column (and others) and if you want to see it or filter by it, you should activly choose it.
In order to display the hostname column in SQL Server Profiler you should:

    1) Open SQL Server Profiler: SQL Server Management Studio -> Tools –> SQL Server Profiler
    2) Start a New Trace:  File –> New Trace
    3) Connect to your server.
    4) Go to the “Events Selection” tab.
    5) Check the “Show all columns” box
    6) Scroll to the hostname column
    7) Check the event you want to trace in the hostname column.

In order to filter the results, you can click “Column filters” and choose the hostname on the left panel. This will allow you to set a “Like” or “Not Like” filter in the right panel.

Deleting large number of rows without filling the transaction log

Sometimes, when you try to delete a large number of rows from a table the transaction file may grow to the point that you will run out of disk space. In other occasions, when the transaction log growth is limited, the delete can fail with the following message:

The transaction log for database ‘database name’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
There are several ways to overcome this issue:

1)    If you want to delete all rows in the table, consider using TRUNCATE TABLE instead of delete. Truncate will not fill the transaction log. Remember that TRUNCATE will also reset any auto incrementing keys.
2)    In case you need to keep some of the data in the table, depending on the ratio between the numbers of rows you are keeping to the number of rows you wish to delete, you may want to:

a)    Copy to the rows you want to keep to another table

b)   Truncate the original table

c)    Copy the data from the temporary table back to the original table.

3)    Delete manually in chunks. You can run: delete TOP (10000) from [your big table]. Remember to commit between deletes.

4)    Use script to delete in chunks with commit every number of rows.
This script for example commits every 100000 rows. Change it to fit your specific scenario:


DECLARE @Count INT
Declare @for_delete INT
Declare @chunk_size INT
SELECT @chunk_size=100000
SELECT @Count = 0
select @for_delete=count(*) from [Your big table] where  [Your Where Clause]

While (@Count < @for_delete)
BEGIN
SELECT @Count = @Count + @chunk_size
BEGIN TRAN
DELETE top(@chunk_size) FROM  [Your big table] where  [Your Where Clause]

COMMIT TRAN
END

What to do when ldf file grows

If you noticed that the free space on your hard drive is shrinking and you found that the log LDF file is taking most of it (and growing) don’t panic.
it is possible that the reason is that your recovery mode is is set to full.
to change this:

1) Right click on the db (in SQL server Managment Studio) and click properties.

2) Navigate to the option tab and make Make sure that the recovery model is set to simple.

If a full recovery mode is needed:

Issue regular backups (in the transaction log shipping tab)
and if Auto Shrink is enabled, the file will eventually shrink (though not immediatly)

If you need to shrink the file immediately:

•    Open MS SQL Server Management Studio, connect to Database Engine

•    Select New Query and type:  backup log <db_name> with truncate_only

(this is discontinued in SQL SERVER 2008 – Microsoft recommends using Simple mode instead)
•    Execute this query (press F5)
•    Right click on Database name and navigate to Tasks->Shrink ->Files: