USE DatabaseName
	GO
	            DBCC SHRINKFILE(<TransactionLogName>, 1)
	    BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
	            DBCC SHRINKFILE(<TransactionLogName>, 1)
	 
	

SELECT file_id, name FROM sys.database_files

If target_size is specified, DBCC SHRINKFILE tries to shrink the file to the specified size. If not specified, DBCC SHRINKFILE reduces the size to the default file size.

However, please note that NO_LOG or TRUNCATE_ONLY options with BACKUP command truncates the transaction log. When you use these options, you might not receive a full database backup.

But the trick is that SQL Server 2005 is quite different from SQL Server 2000. To truncate log file is one thing which is different from SQL Server 2000. In SQL Server 2000, you just use Shrink to whatever file size you like. In SQL Server 2005, sometime you cannot shrink the log file at all.

However, there are some tricks to truncate log file for a database in SQL Server 2005. The work environment is MS SQL Server Management Studio.

I. Shrink the log file size at the right time

Immediately after I use the SSIS package or Import the data to the database ( highlight the database->Tasks->Import data … ), or Export the data from the database ( highlight the database->Tasks->Export data … ), I can shrink the log file to the desired size, for example, 1MB. That is, highlight the database->Tasks->Shrink->Files

set the file size, say, 1MB.

Then, click OK and you are done.

II. Eliminate the log file completely

Sometimes we just do not need the big log file. For example, I have 40GB log file. I am sure I do not need this log file and want to get rid of it completely to free up the hard drive space. The logic is

a. Detach the database

b. Rename the log file

c. Attach the database without the log file

d. Delete the log file

Let’s say, the database name is testDev. In the SQL Server Management Studio,

Highlight the database-> Tasks->Detach..-> Click OK Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf, Highlight Databases->Attach…-> Click Add -> add the database testDev, highlight the log file and click the ‘Remove’ button. This means you only attach testDev.mdf After this is done, you can verify the contents of the attached database and then delete the log file.

View Triggers in SQL Server:

To list triggers in SQL server database, you can use following simple query:

select * from sys.triggers

Alternatively, use the system stored procedure sp_helptrigger as well, to list triggers associated with the table:

Exec sp_helptrigger 'dbo.OrderHeader'

The server returns the list of triggers associated with the specified table and displays the type of trigger found in the isupdate, isdelete, isinsert, isafter, and isintead of columns.

You can obtain the code for a trigger using the system stored procedure sp_helptext:

Exec sp_helptext 'dbo.trOrderStatus_U'

Deleting DML Triggers

A trigger can be deleted, as can all other database objects, using the appropriate Drop statement:

if exists (select * from sys.objects

           where object_id = object_id(N'[dbo].[trEquipment_IU]')

           and type in (N'TA', N’TR'))

DROP TRIGGER [dbo].[trEquipment_IU]

Deleting DDL Triggers

The statement for dropping DDL triggers contains a reference to the scope (level) of the trigger:

if  exists (select * from master.sys.server_triggers

            where name = N'trdTestCreateLogin2' and parent_class=100)

DROP TRIGGER [trdTestCreateLogin2] ON ALL SERVER

GO

if  exists (select * from sys.triggers

           where name = N'trdAuditTableChanges' and parent_class=0) DROP TRIGGER [trdAuditTableChanges] ON DATABASE 

Here is the simple query you can use to fetch column information from a given table:

SELECT
ORDINAL_POSITION
,COLUMN_NAME
,DATA_TYPE
,CHARACTER_MAXIMUM_LENGTH
,IS_NULLABLE
,COLUMN_DEFAULT
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'Product' -- place your table in question here in quotes
ORDER BY
ORDINAL_POSITION ASC;

Another more simpler option could be to use sp_help as follows:

sp_help 'Product' -- place your table in question here in quotes

• A clustered index is used on fields that increase continuously, e.g., auto number integer fields. Because SQL Server physically arranges rows in the database file based on a clustered index field, if I choose some fields that do not continuously increase, it will be rearrange too many pages during the INSERT and DELETE steps.
  
• Foreign key fields are non clustered index types because they are not added as increasing values.

Gregg Stark has created a very nice query that is a great tool for optimizing indexes (indices) in SQL Server 2005. 

Here's the query:

SELECT    o.name AS object_name, i.name AS index_name

   , i.type_desc, u.user_seeks, u.user_scans, u.user_lookups

, u.user_updates, u.last_user_seek, u.last_user_scan

, 'Drop index ' + i.name + ' on ' + o.name as DropIndexStatement

FROM sys.indexes i

JOIN sys.objects o ON  i.object_id = o.object_id

LEFT JOIN  sys.dm_db_index_usage_stats u ON i.object_id = u.object_id

          AND    i.index_id = u.index_id

          AND    u.database_id = DB_ID()

WHERE    o.type <> 'S'    

and isnull(u.user_updates,0) > 0

and i.type_desc <> 'HEAP'

ORDER BY    (convert(decimal(19,4),ISNULL(u.user_seeks, 0)) 

+ ISNULL(u.user_scans, 0) 

+ ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc

, user_updates desc, o.name, i.name

The results of this will show you how many times each index has been used, and how often it's been updated.  If you have 0 seeks, scans, and lookups but a ton of updates, it's a good bet that the index in question is a waste of time and can be deleted.

The query even includes the DROP command as part of the results, so all you need to do is cut, paste, and execute.  Beautiful!

On the other end of the spectrum, the query will show you which indices are really earning their paycheck, so its a must have tool for any SQL developer or administrator.

If your database's log file size reaches its limit, you can truncate and then shrink it to its minimum size by using the following commands:

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)

1: Set your database's recovery mode to "SIMPLE". By default, SQL Server 2005 sets a database's recovery mode to "FULL". Now, before changing the recovery mode, you should know what you're willing to recede. When a database's recovery mode is "FULL", it means when you restore or when the database gets corrupted, SQL Server will restore it to a point right before it got corrupted! On the other hand, if you use "SIMPLE" recovery mode, it will simply restore the database to the last point the backup was made. For instance, you took a database's backup at, let's say, 10 am, and your database got corrupted at 5 pm the same day. With FULL recovery mode, you'll be able to restore your batabase to a state it was in right before 5 pm. With SIMPLE recovery mode, you'll be able to restore your database to a state it was in at 10 am!

2: Take periodic backup of your database's log file! When you take its backup, the committed transactions in the log file go in overwrite mode.

Hope it helps. 

If your SQL Server 2005 is not starting because of modellog.ldf file as it got corrupted or something, all you have to do is replace modellog.ldf AND model.mdf files, and your SQL Server will be on its way. You can copy these two files from another location and paste them in the directory of your SQL Server.

Hope it helps.