SQL Server

Truncate SQL Transaction Log and Shrink Database

tom.hundley@elegantsoftwaresolutions.com — Sun, 15 Jun 2008 01:48:00 GMT

I only need to do this every so often, but I always forget how to and end up looking it up. Thus, I'm posting it here for reference.

USE DatabaseName

BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

DBCC SHRINKFILE(<TransactionLogName>, 1)

Tom Hundley
Elegant Software Solutions, LLC

Rebuild all Indexes in SQL Database

tom.hundley@elegantsoftwaresolutions.com — Sun, 15 Jun 2008 01:39:00 GMT

Here is a script to rebuild all of your indexes on your database. I find that indexes are often ignored once an application moves to production. It's a good idea to evaluate your usage patterns and setup automated maintenance jobs to proactively rebuild indexes before fragmentation has a chance to adversely effect performance. This is especialy important if you are using guids as primary keys and don't move the clustered index to a more logically sequential column.

-- Show fragmentation for all tables
EXEC sp_MSforeachtable @command1="print '?' DBCC SHOWCONTIG('?')"

--Rebuild all indexes (note this method locks the tables while the indexes are being rebuilt)

USE [myDatabase]

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

Tom Hundley
Elegant Software Solutions, LLC

Connecting to SQL Server 2005 with SSL

tom.hundley@elegantsoftwaresolutions.com — Sun, 03 Feb 2008 21:12:00 GMT

Did you know you can connect to SQLServer from your application using SSL?

Microsoft SQL Sever 2005 Encryption

tom.hundley@elegantsoftwaresolutions.com — Sun, 03 Feb 2008 21:10:00 GMT

Quick reference to the Microsoft SQL Server 2005 Whitepaper

Editing existing SSIS Packages in SQL Server 2005

tom.hundley@elegantsoftwaresolutions.com — Sat, 21 Apr 2007 05:44:00 GMT

Viewing and editing an existing SSIS package is limited to SQL Server Business Intelligence Development Studio (SSBIDS) or Visual Studio 2005 Team Suite (VSTS).

Finding SSIS Packages in SQL Server Management Studio (SSMS)

tom.hundley@elegantsoftwaresolutions.com — Sat, 21 Apr 2007 05:42:00 GMT

I used SQL Server Integration Services (SSIS) Import & Export Wizard from SQL Server Management Studio (SSMS) to create an SSIS package and saved it to the server. I beat my head against the wall trying to find it via SSMS after I saved it.