SQL 2005: Truncating Log Files and Recovering Space

A common issue for users of SQL Server databases is disk space and the size of the physical log file and database. While we’re not going to attempt to make “one size fits all” statement on database maintenance plans, we though it would be helpful to provide a few suggestions that will help you trim the size of your files when you are in a pinch.

Steps to truncating log files and shrinking your database:

1. Get the physical names of your database file (MDF) and log file (LDF):
Run the following system stored procedure:

use [yourdatabasename]
exec sp_helpfile

This command will return a variety of information, including the physical size (the “size” column) and the path and name of your database and log files (in the “filename” column).

Important:
Record the name of the file from the “filename” colunm, excluding the path and file extension (e.g. if filename contains “C:\sqldatabases\yourdatabase_data.mdf” you want to save the string “yourdatabase_data”)

2. Truncate the database and shrink the database
The following set of SQL will shrink your database and “truncate” the log file. File in the parmaters surrounded by […]. Note that you’ll need the two filename values from step 1, one for the data file and the other one for the log file, be very careful when typing in the file names:

USE [yourdatabasename]
GO
BACKUP LOG [yourdatabasename] WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE ([yourdatabaselogfilename], 1)
GO
DBCC SHRINKFILE ([yourdatabasedatafilename], 1)
GO
exec sp_helpfile

When complete, this script will output the same information as in step 1. Compare the new size with the old one.

If you get an error like:
"Cannot shrink log file because all logical log files are in use"
I solved doing the following:

1. open enterprise manager.
2. right click on the database you wanna shrink.
3. click on properties.
4. from the data properties go to options.
5. in the middle you will see recovery model. Make it "simple" then click on "ok" and try again.

Once done, it's recommended to take the parameter back to Recovery model = Full.
You can also setup the maximum log file size at this point, so you won't be facing the same problem again !

Change name of server, after install SQL Server 2005

If you change the name of a server / computer, after installing SQL server 2005 (it happened to me also for SQL 7.0 server and SQL 2000 server), some of the programs that have access to the database, will have problems, because of the default instance was using the old name.
A long time ago, I had to backup my databases, uninstall SQL server, change the server name, reinstall SQL server, and restore the databases ....
But the solution is very simplistic: change the name of the server, and after restarting it, launch the SQL Management Studio (Enterprise Manager or Query Analyzer if using SQL server 2000), then execute the following queries:

1) select @@servername
It will show you the actual server name used by SQL server

2) sp_dropserver OLDNAME
GO
It will erase this parameter

3) sp_addserver NEWNAME, local
GO
It will configure the SQL server parameter with the new name

4) Restart SQL server services

5) select @@servername
It will show you the actual NEW server name used by SQL server. Try at least twice.

You're all done.
It works for the default instance.
If you need to read further, go to http://msdn.microsoft.com/en-us/library/ms143799.aspx