I don't know much about SQL server databases and\or administering them so I hope someone with more knowledge can assist me in shrinking the log file*.LDF file which is about 15GB in size and taking up space.
I was hoping someone could give me the correct commands so I can use Query Analyser to run the command.
I'm running SQL Server 2000.
Date File: Export_data
Transaction log: Export_log
Also, will running this command have any risks? Do I need to stop any service or take the database offline whilst I do this?
Your help will be appreciated.
Firstly you should be certain WHY you are needing to shrink. What recovery model is your database using? Are you doing transaction log backups and how often are you doing them? Whoever is responsible for taking backups of your database ought to be able to answer those questions.
A word of caution about the suggestion made by Sanoj. BACKUP with the TRUNCATE_ONLY option will invalidate your chain of log backups. That means you are at high risk of losing data until you do the next FULL BACKUP. This is not to be recommended on a production system.
Similarly, if your database is set to auto-grow, DBCC SHRINK will probably cause unnecessary and expensive file growth operations to happen later on and will cause fragmentation of your data files. For those reasons database performance may be adversely affected by shrinking.
The best policy is to avoid expensive file growing and shrinking operations. To do that, you need to determine what problem you have, if any, and fix that problem rather than just try to mask the symptoms.
Thanks guys, I've made a backup of the transaction log and database. There is currently a maintenance plan to backup the transaction log file and to purge it but the reason why the maintenance plan is not running is because there is not enough space.
So I made a backup of the database and created another disk partition with additional space. Next I want to move the database and transaction file so that the maintenance plan can run and do it's bit, is there any easy way of doing this as apposed to what Microsoft recommend below? How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server
I really do not want to stop any service or take the database offline.
Use ALTER DATABASE to add a new file.
Use DBCC SHRINKFILE with the EMPTYFILE option to empty the existing file.
Then you should be able to remove the old one. You can't remove the primary file though but you can turn off autogrow so that it isn't used.
That shouldn't require any downtime but test it out for yourself on another server before you try it for real.