Friday, 14 June 2013

SQLServer dealing with space

1. Logon to the server and open Windows Explorer.
2. Right click on the drive in question, and click on 'Search'.
3. In the search field, enter *.* to search for everything.
4. When the results come back, sort into size order, largest at the top.
5. Look for .BAK or .ldf files. If you have multiple copies of .BAK files for one database, delete the oldest. However, if there are only two copies of backup files for one database, look elsewhere for space savings.
6. If you find large .ldf files (transaction log files) you can shrink them as follows:

a) logon to Enterprise Manager or Management Studio
b) open a Query Analyzer session
c) enter 'backup log <database_name> with truncate_only'
d) shut Query Analyzer and right click on the database name in the databases tree in Enterprise Manager
e) click 'All Tasks', then 'Shrink database', then click on 'Files' and shrink the transaction log file.

Popular Posts