Truncate Log Files and Recover Space

We have had many issues on our database server with space. The databases are growing faster than we can purchase space and there is also a lot of issues with people wanting to store backups locally for quick restores.

There is a really cool(free) tool that helps you analyze which file and directories are taking up all your space. It's called WinDirStat and you can download it here: http://sourceforge.net/projects/windirstat/

It will help you find where all your space is going and if there are any obscure files laying around (temp files) that no one uses, but are taking Gigs of space.

Using this tool, I was able to find that TempDB was taking over 94GB and it's log file was 20GB. We just use a Simple backup scheme and don't really need to recover databases with transaction files. We just restore the previous day's information.

I also noticed a few other database logs had grown large. Now, I know this tool doesn't take the place of a proper database maintenance plan, but we don't have any good DBAs. So, this will have to do.

   1:  USE DatabaseName

   2:  GO

   3:  BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

   4:  DBCC SHRINKFILE(<TransactionLogName>, 1) --LOGICAL NAME FOUND IN PROPERTIES

   5:  GO 

Deleting a Single Duplicate Row

I had a coworker just ask me this and I remember having to do this a while ago.

Case: You have a table that has 2 identical rows because there are no constraints to prevent this. You want to delete one of the entries (not both).

   1:  SET COUNT 1

   2:  DELETE FROM ProductTable WHERE product_name = 'Widget Model 3000'

   3:  SET COUNT 0

It will only delete one of the products with the name 'Widget Model 3000'

SqlServer 2008 Connection Refused to Grails

I was setting up a test application on my development machine and I had everything configured, but the application would not start.  After reviewing the error message, somewhere near the bottom, it said "Connection Refused".

The machine was running Windows 7, Sql Server 2008, and Grails 1.3.4. I was using the jTDS JDBC Driver 1.2.5 to connect to the database (just drop the jar in the lib directory).  After searching for about 30 minutes, I found that TCP/IP access in SQL Server 2005/2008 is disabled by default.

To enable it, you can do this:



SQL Server Configuration Manager
-->SQL Server 2005 Network Configuration
-->TCP/IP
-->IP  Address
-->TCP port


You'll need to enable it, set the port, set the IP, then restart the SQLServer service.


Here is what my DataSource.groovy looks like:

dataSource {
    pooled = true
    driverClassName ="net.sourceforge.jtds.jdbc.Driver"
    dialect = "org.hibernate.dialect.SQLServerDialect"
}

hibernate {
    cache.use_second_level_cache=true
    cache.use_query_cache=true
    cache.provider_class="com.opensymphony.oscache.hibernate.OSCacheProvider"
    connection.pool_size=10
}

// environment specific settings
environments {
    development {
        dataSource {
            url = "jdbc:jtds:sqlserver://laptop_win7:1433;databaseName=gpay_DEV"
            username = "GPayAdmin"
            password = "MyPassword123!"
            
        }
    }
}

native2ascii error in Grails

When I create a new Grails app, I always get the native2ascii error.

In the past, I've always on into Config.groovy and changed this line to false:

grails.enable.native2ascii = true


Now, I've found a way to make sure the error does not show up at all.

Copy the tools.jar file from the JDKPATH/lib directory to the JREPATH/lib/ext directory.

copy %Java_Home%/lib/tools.jar to %Java_Home%/jre/lib/ext/tools.jar fixed this problem..


Thanks to grailslog!

SQL NOLOCK Example

A co-worker just gave this to me to illustrate "nolock" and its pretty cool.

Purpose: If you are doing SELECTs and data accuracy is not extremely important, use "nolock" to avoid conflicts with other users.

If you have long running processes that do a lot of SELECTs, this can dramatically improve performance.

   1:  create table testdb.dbo.NoLockTest

   2:  (

   3:      ID int identity(1,1),

   4:      Product     varchar(20),

   5:      SalesDate   datetime,

   6:      SalesPrice  int

   7:  )

   8:   

   9:  INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('PoolTable', GETDATE(), 200)

  10:  INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('MyTable', GETDATE(), 500)

  11:  INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('GeoffTable', GETDATE(), 400)

  12:  INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('LarryTable', GETDATE(), 250)

  13:  INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('StephenTable', GETDATE(), 360)

  14:   

  15:  select * from testdb.dbo.NoLockTest

  16:   

  17:  -- Query 1

  18:  BEGIN TRANSACTION 

  19:  INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) 

  20:  VALUES ('PoolTable', GETDATE(), 1000)

  21:   

  22:  -- Query 2 will continue to run until ROLLBACK TRANSACTION OR COMMITE does not apply to any INSERT/UPDATE/DELETE stmt.

  23:  -- only way to stop the query is to use "Cancel Executing Query" from SSMS Menu.

  24:  select count(*) from testdb.dbo.NoLockTest 

  25:   

  26:  -- Query 3 will allow  you to pull data even though there is a lock on the table due to INSERT/UPDATE/DELETE stmt running

  27:  -- in this case we have from Query 1 

  28:  select count(*) from testdb.dbo.NoLockTest with(nolock)

  29:   

  30:  -- Query 4 -- execute below statement.

  31:  ROLLBACK TRANSACTION

  32:   

  33:  -- Query 5

  34:  select count(*) from testdb.dbo.NoLockTest