• Shrinking Database Log file

    by Venkata Koppaka | Aug 16, 2010
    To just shrink the size of a database log file, use the following code:
     
    1 backup log database_name with truncate_only 
    2 dbcc shrinkfile (database_log_name) 
    3 GO 

     
    The backup log statement marks all of the transactions listed in the log Inactive, and then the dbcc shrinkfile command will remove the Inactive transactions from the file, thereby shrinking the size of the database.
     
    If the database_log_name is unknown, you can also use the File_ID instead of the name, and that can be found by using:
     
    1 select * from sysfiles 

     
    when you are in the desired database that you want to delete the log from.
     

    There is a shrink database command called dbcc shrinkdatabase (database_name) that takes a database name as an input and will attempt to shrink the size of the database files in the same manor as described above.  

     

    Hope this helps,

    Cheers,

    Venkata


    Go comment!

  • Finding Duplicates using SQL

    by Venkata Koppaka | Jan 25, 2010

    Here is a handy script to find duplicates in a table using SQL -

    1 select column, 
    2     count(column) as numofoccurrences 
    3 from table 
    4 group by column 
    5 having count(column) > 1 

    Cheers,

    Venkata

    Go comment!

  • Update Data in one table with a new or Other table

    by Venkata Koppaka | Dec 14, 2009
    Consider the following scenario - 

    You have data in one table which is old, and then you have data in a newer table which is all good.
    If you have to update the old table with new tables data you can take one of these two approaches

        1. Write a SQL Query that does that
        2. Write a program to iterate and update (ah, I know no one does this except for a purely programming guy who doesn't know any of SQL)

    I am going to focus on writing a SQL Query to update the data - 
    I am going to present you with two ways of writing this query

    Here goes the first one-

    1 update OldTable 
    2 set OldTable.DataToBeUpdated = NewTable.DataToBeUpdated 
    3 from 
    4 OldTable, NewTable 
    5 where 
    6 OldTable.SomeId = NewTable.SomeId 


    And then the second,

    1 update 
    2 ( 
    3 select NewTable.DataToBeUpdated as new_value, 
    4        OldTable.DataToBeUpdated as old_value 
    5 from      
    6        OldTable inner join NewTable  on OldTable.SomeId = NewTable.SomeId 
    7 where 
    8       NewTable.DataToBeUpdated is not Null --Some Criteria 
    9 ) 
    10 set old_value = new_value; 


    Hope this Helps,
    Cheers
    Venkata


    Go comment!