Bayshield

Shrinking a SQL Server Log file should be an easy task, however choosing Shrink file in the SQL Adminstrator tool won't get the job done.

The sql script below works every time and will ensure your log file is reduced to the smallest possible size (worth doing before you do a complete DB backup).

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

Most of the utilities I use in my day to day development are open source tools, however one exception to this is Redgate softwares SQL Compare tool.

Effectively what the tool allows you to do is to compare two SQL Server databases to identify the differences.  It will then allow you to choose what changes you want to promote from environment A to environment B and will produce an SQL script to implement the changes.

Using this software, database migrations from DEV -> UAT -> Production now only take a matter of minutes.  One thing this software doesn't do is move the data in your tables.  Redgate have another tool for that, however I haven't yet used it.

All up this is one of my favourite database tools and is well worth the £245 that it costs.  There is also a 14 day free trial you can download if you want to try it (or just have a one off migration to do).

P.S.  In case this sounds like a sales pitch I can confirm that I am in no way employed or sponsered by Redgate!  I just like the tool.

One of the final tasks I undertake with all applications I develop is to run SQL Server Profiler on the database to look for any potential opportunities to optimise my stored procedures or indexes.

For those who have never used SQL Server Profiler before it is actually very easy to use and is one of the most helpful tools you will come across.  To give you a brief overview of the tool the first thing you do is start a new 'Trace' session.  What this entails is selecting the database you want to profile and then selecting the type of events you want to record (SQL Statement executing, stored procedure executing etc...).  You can then start the trace and go about using your application as normal.  After you have finished playing with your application you can stop the 'trace', and all the trace data will be saved to a file or database table (you can choose when you setup the trace).

Now that you have the usage data you can look through the list for any queries which are taking a long time to run.  Once I identify the longest running queries I first look at the stored procedures to see if there is anyway I can optimise them.

After that you can then run the 'Performance Tuning' function of SQL Server Profiler and feed in the trace file you created.  Profiler will then analyse the workload and automatically suggest new indexes which will improve your systems performance.

All up the process of setting up a trace, running it and analysing the results usually takes less than 30 minutes and can result in huge improvements to your applications speed.

For further information on using SQL Server Profiler, please check the link below:

• SQL Server Profiler - MSDN

Indexes are an essential part of any good database design.  In SQL Server the database engine analyses every query and develops an execution plan to return data in the shortest possible time.  Generally speaking this means making use of the indexes you have created (SQL Query Profiler is the best tool to use to figure out what indexes you need).

What a lot of people don't realise however is that as databases grow, indexes can become fragmented (just like a hard disk) which can seriously effect the performance of your queries.

The best way around this is to create a SQL Server maintenance plan to rebuild the database indexes on a regular basis (similar to the way a hard drive defrag would word).  If however you would just like to be able to reindex your database on an adhoc basis then the script below will recreate the indexes for all tables in the specified database.

USE DatabaseName --Enter the name of the database you want to reindex 

DECLARE @TableName varchar(255) 

DECLARE TableCursor CURSOR FOR 
SELECT table_name FROM information_schema.tables 
WHERE table_type = 'base table' 

OPEN TableCursor 

FETCH NEXT FROM TableCursor INTO @TableName 
WHILE @@FETCH_STATUS = 0 
BEGIN 
DBCC DBREINDEX(@TableName,' ',90) 
FETCH NEXT FROM TableCursor INTO @TableName 
END 

CLOSE TableCursor 

DEALLOCATE TableCursor

SQL Express doesn't ship with SQL Server Agent which makes it difficult to schedule automated backups.  There is however a very good post on MSDN explaining how to get around this using windows Task Scheduler, SQLCommand (a command line tool for executing SQL) and a simple stored procedure.

The article can be found here.