Jephe Wu - http://linuxtechres.blogspot.com
Objective: understanding different scenario for disk space issues
scenario 1: How to reduce transaction log file size
Problem: transaction log file is huge (more than 100GB), due to weekly maintenance job running on production database
Environment: MS SQL server 2005, recovery mode is 'full', transaction log backup is not enabled, logshipping is not enabled.
Solution: change database recovery mode from full to simple, then shrink transaction log to free up unused space, you cannot free up space if the unused space is 0%.
scenario 2: How to move database files to free up space
Problem: D drive has not enough space, you need to move one database to F drive on the same server instance.
Envrionment: MS SQL server 2005. recovery mode is 'simple'. not transaction log backup, no logshipping.
Solution:
For moving database files within same instance server
a. alter database modify (apply to move database files within same instance server)
For example:
USE master;
GO
-- Return the logical file name.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'AdventureWorks2012')
AND type_desc = N'LOG';
GO
ALTER DATABASE AdventureWorks2012 SET OFFLINE;
GO
-- Physically move the file to a new location.
-- In the following statement, modify the path specified in FILENAME to
-- the new location of the file on your server.
ALTER DATABASE AdventureWorks2012
MODIFY FILE ( NAME = AdventureWorks2012_Log,
FILENAME = 'C:\NewLoc\AdventureWorks2008R2_Log.ldf');
GO
ALTER DATABASE AdventureWorks2012 SET ONLINE;
GO
--Verify the new location.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'AdventureWorks2012')
AND type_desc = N'LOG';
For moving database files within same instance or different instance/servers:
a. use database detach and reattach, this actually works for same server instance and also different server or different instance.
b. backup and restore database with move option to relocate files
References:
Posts
