Manually truncate MS SQL 2005 log file (.LDF)


If MS SQL database Recover Model is set to "FULL" and regular full backups are not performed databases log files (.LDF) can grow very large.

If you are working with a production database make sure you know what you are doing as you may loose ability to restore your database to any point in time since your last backup. If production database backups and storage management are set up properly you should never need to truncate logs manually.


To manually truncate log file on test/development SharePoint_Config database:

1. Open SQL Server Management Studio and run following SQL query:
USE SharePoint_Config
BACKUP LOG SharePoint_Config WITH TRUNCATE_ONLY

This will truncate log file but won't reduce actual file size.

2. To shrink actual file run:
USE SharePoint_Config
DBCC SHRINKFILE (N'SharePoint_Config_log', 50)
50 - Log file target size in MB.
OR
SQL Server Management Studio > Databases > SharePoint_Config > Tasks > Shrink > Files

3. To prevent future growth of the log file run:
USE SharePoint_Config
ALTER DATABASE SharePoint_Config SET RECOVERY SIMPLE

OR
SQL Server Management Studio > Databases > SharePoint_Config > Properties > Options > Recovery Model > Simple


Microsoft SQL Server 2005


Troubleshooting SQL 2005 SP4 and other Windows Update installation errors


This article uses a specific MS SQL 2005 Service Pack 4 installation error as an example, but may help in troubleshooting other Microsoft and Windows Update problems.

Installation of SQL server 2005 Service Pack 4 fails and Event ID 20 Error is recorded in Windows System Logs:

Type: Error
Event ID: 20
Description:
Installation Failure: Windows failed to install the following update with error 0x80070643: Microsoft SQL Server 2005 Service Pack 4 (KB2463332).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

This error is not specific to SQL 2005 SP4 and is just a generic code telling that an error was encountered by Windows Installer.
To get more specific code download offline Service Pack or Hotfix installation file from Microsoft. Just search MS download centre for KBxxxxxxx as displayed in Event ID 20 error. In case of SQL 2005 SP4 it is KB2463332.

Try installing your update running downloaded .exe or .msi. It will probably fail again, but this time you should be able to find more specific info in Windows Application Logs.
In case of SQL 2005 SP4 it can be something like this:

Type: Error
Event ID: 1023
Description:
Product: Microsoft SQL Server 2005 - Update 'Service Pack 4 for SQL Server Database Services 2005 ENU (KB2463332)' could not be installed. Error code 1603. Additional information is available in the log file C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB2463332_sqlrun_sql.msp.log.

Again, this is quite generic error, however this times it gives a path to a setup log file which will have much more information. Open the file in a text editor. Don't get puzzled by the amount of information. Just search for "return value 3" and examine text just above this string. One of the lines should have a specific error which caused setup to fail. Going back to our SQL example third line above "return value 3" is:
FTECa.DLL: ERROR: FTE: InstallFTERef: Fail to create FTERef file: C:\SQL\MSSQL.1\MSSQL\FTData\noiseCHS.txt, Err=3
In this case installation failed because path C:\SQL\MSSQL.1\MSSQL\FTData did not exist. Simply recreating missing folder fixes the problem. Obviously in your case error may be different.


Windows Server 2003
SQL 2005




ntbackup logs missing


Normally when you run ntbackup logged on as "Administrator" backup logs are saved in "C:\Documents and Settings\Administrator\Local Settings\Application Data\Microsoft\Windows NT\NTBackup\data"
If backup logs are not in the expected location check following:

If you run ntbackup from the command line or a script make sure you include log option: /l:s (summary log) or /l:f (full log).

If you run ntbackup as a scheduled task, check "Run As" option in scheduled task properties. For example if you run the scheduled task as "backup-operator" your ntbackup logs will be saved in "C:\Documents and Settings\backup-operator\Local Settings\Application Data\Microsoft\Windows NT\NTBackup\data".


Windows Server 2003

List all files on the server

Sometimes it could be useful to have a text file listing all files on your server or in a particular directory. If content is relatively static this can be used to perform a
quick file searches based on file names. Similar search using Windows search toll could take hours, whereas search inside a large text file is pretty much instant.

You can use robocopy utility to create this list:

1. Download and extract robocopy.exe. If you use Windows 7 - robocopy is already on your system.
2. Run robocopy.exe in command prompt (CMD) with following command:
robocopy.exe "\\path\to\your\folder" "TMP" /E /CREATE /TEE /LOG:FileList.txt
Basically this command will "copy" all files in "\\path\to\your\folder" and all subfolders to the folder "TMP", however instead of copying actual files it will only create a folder structure and zero lenght files. Additionally, it will create a log file "FileList.txt" listing all "copied" files and directories.
3. Delete "TMP" directory.
If you having problem deleting "TMP" due to long file/path names, delete it in command prompt:
rmdir "\\path\to\your\folder\TMP" /Q /S

Newsletter

Subscribe to receive occasional updates on new posts.
Your email will not be used for any other purpose and you can unsubscribe at any time.
Please wait