Shrinking a SQL Transaction Log File

SQL2000Logo Every so often, I run out of drive space on my SQL Server.  The culprit ends up being the Transaction Log File (LDF) expanding to consume all available space.

My simple workaround is to shrink the log file with these SQL commands.
USE <database name>
GO
DBCC SHRINKFILE (<name of log file>,1)
GO
BACKUP LOG <database name> WITH TRUNCATE_ONLY
DBCC SHRINKFILE
(<name of log file>, 1)
GO

References:
DBCC SHRINKFILE (Transact-SQL)
SQL SERVER – Shrinking Truncate Log File – Log Full

Learning T-SQL – Revisited

Teach Yourself Microsoft T-SQL In my current job, I get to write data extracts.
eg. extract data from an SQL database, using Microsoft Access as the front-end. Not my preferred tool, but it’s what I’ve got.

Thought it would be useful to brush up on my T-SQL, so I purchased the book on the right.

Best darn little book I’ve found on Transaction SQL. The 10 minutes refers to each lesson. And there are 30 lessons all up. This will be a book which I’d going to end up using as a quick reference.

Now if you want to teach yourself T-SQL, I’d suggest the following:

  1. Sams Teach Yourself Microsoft SQL Server T-SQL
  2. SQL Server 2005 Express Edition with Advanced Services
  3. .Net Framework version 2
    (if you don’t already have it)
  4. Membership of SqlServerCentral.com (it’s free), the question of the day is worth it for knowledge reinforcement.

Update August 2009:
Writing SQL Queries: Let’s Start with the Basics, by Microsoft is worth reading as well.

Bookmark and Share

Should I compress my SQL Database when backing up? – Maybe …

SQL Server 2008 Backup Compression
– by Varun Dhawan, over at MSDN blogs.

It’s a useful option for saving space with backing up to disk, but I would disagree with the guy over tape backup savings.

Some tape units have built-in hardware compression which already compresses the data being stored on tape.
Which means there is no advantage in compressing data if you are going to be writing it straight to tape.

As I found when testing compressing large ISAM databases back in the 90’s.  The data was already compressed so the tape drive couldn’t compress any further.

So the answer is:
Yes, if backing up to disk.
No, if backing up straight to tape.

Details on tape compression from an old Sony FAQ:

Q: What is the tape capacity of the SDT- 5000/5200 tape drive?

A: With the SDT-5000, tape capacity will vary depending on the length of the tape and whether hardware compression is enabled as follows:

Tape Native Capacity Typical Capacity with Compression
60m 1.3 GB 2.6 GB
90m 2.0 GB 4.0 GB
120m 4.0 GB 8.0 GB

NOTE: The SDT-5200 supports native capacity only.
The typical performance of the compression IC in the SDT-5000 drive is shown in the following table:

Data Type Compression
Bitmaps 6.9 to 1
Database 3.9 to 1
CAD 3.8 to 1
English 2.9 to 1
Source 2.9 to 1
Spreadsheet 2.5 to 1
Desktop Pubs 2.5 to 1
Binary 1.7 to 1

As the table indicates, the more random the data is, the less compression is possible. This is due to the fact that data compression operates on the principle of reducing the redundancy in the date string and random data has very little redundancy.

Bookmark and Share