I am getting error The transaction log for database 'AfterMail_TEMP' is full due to 'LOG_BACKUP'.
I checked the SQl server and it seems the disk where i placed after_mail log files has grown large and is full. How to move or generate new files in another disk location ?
To move or generate new log files in another disk location, you can follow these steps:
Detach the database.
Move the log files to the new location.
Attach the database, specifying the new location of the log files.
Here are the detailed steps:
Detach the database.
To detach the database, you can use the following SQL statement:
SQL
ALTER DATABASE [AfterMail_TEMP] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db [AfterMail_TEMP];
GO
Move the log files to the new location.
Once the database is detached, you can move the log files to the new location. The log files are typically located in the \MSSQL\DATA\<database_name> directory.
Attach the database, specifying the new location of the log files.
To attach the database, you can use the following SQL statement:
SQL
CREATE DATABASE [AfterMail_TEMP]
ON PRIMARY (
NAME = N'<new_log_file_location>',
FILENAME = N'<new_log_file_name>',
SIZE = <new_log_file_size> MB
);
GO
ALTER DATABASE [AfterMail_TEMP] SET MULTI_USER;
GO
For example, if you are moving the log files to the D:\LOGS directory, you would use the following SQL statement:
SQL
CREATE DATABASE [AfterMail_TEMP]
ON PRIMARY (
NAME = N'D:\LOGS\AfterMail_TEMP_log',
FILENAME = N'D:\LOGS\AfterMail_TEMP_log.ldf',
SIZE = 100 MB
);
GO
ALTER DATABASE [AfterMail_TEMP] SET MULTI_USER;
GO
Once the database is attached, you can check the transaction log to make sure that it is empty. You can do this by using the following SQL statement:
SQL
SELECT FILE_ID, NAME, TYPE_DESC, SIZE, MAX_SIZE, FILEGROWTH, LOG_GROWTH, USED_SPACE
FROM sys.database_files;
GO
The USED_SPACE column should be zero for the transaction log file.
I hope this helps!
To move or generate new log files in another disk location, you can follow these steps:
Detach the database.
Move the log files to the new location.
Attach the database, specifying the new location of the log files.
Here are the detailed steps:
Detach the database.
To detach the database, you can use the following SQL statement:
SQL
ALTER DATABASE [AfterMail_TEMP] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db [AfterMail_TEMP];
GO
Move the log files to the new location.
Once the database is detached, you can move the log files to the new location. The log files are typically located in the \MSSQL\DATA\<database_name> directory.
Attach the database, specifying the new location of the log files.
To attach the database, you can use the following SQL statement:
SQL
CREATE DATABASE [AfterMail_TEMP]
ON PRIMARY (
NAME = N'<new_log_file_location>',
FILENAME = N'<new_log_file_name>',
SIZE = <new_log_file_size> MB
);
GO
ALTER DATABASE [AfterMail_TEMP] SET MULTI_USER;
GO
For example, if you are moving the log files to the D:\LOGS directory, you would use the following SQL statement:
SQL
CREATE DATABASE [AfterMail_TEMP]
ON PRIMARY (
NAME = N'D:\LOGS\AfterMail_TEMP_log',
FILENAME = N'D:\LOGS\AfterMail_TEMP_log.ldf',
SIZE = 100 MB
);
GO
ALTER DATABASE [AfterMail_TEMP] SET MULTI_USER;
GO
Once the database is attached, you can check the transaction log to make sure that it is empty. You can do this by using the following SQL statement:
SQL
SELECT FILE_ID, NAME, TYPE_DESC, SIZE, MAX_SIZE, FILEGROWTH, LOG_GROWTH, USED_SPACE
FROM sys.database_files;
GO
The USED_SPACE column should be zero for the transaction log file.
I hope this helps!
Thanks. I have a AM database size of 3 TB and AfterMail_TEMP db of 26 GB. How much should i dedicate or what should be configuration of my transaction logs i am creating in new location? should i go with the default values?
