Remove Transaction Log in SQL Server
-
-
I have a SQL Server database. It is too large now. How can I remove the transaction log without backing up the database? In fact, I do not need any logs. Can I prevent the database from making transaction logs? Can SQL Server automaticlly remove the logs?
Due to architecture of sql server each database file has a transaction log file for transaction support. Support of transactions are needed for core database functionality. Therefore you can not prevent sql server making transaction logs. Nevertheless the size of transaction log file can be controlled on database creation time with the CREATE DATABASE statement in TRANSACT-SQL. The parameter SIZE, MAXSIZE and FILEGROWTH controls initial size, maximun size and filegrowth for each database file and for log file. Setting the log file size to a fix maximum value is a recommended way not to have a filegrowth over all limits. But this needs maintenance from time to time when MAXSIZE is reached. To reduce the size of an existent transaction log file the TRANSACT-SQL statement (not available from the sql server enterprise manager) DBCC SHRINKFILE can be used. To be secure no other user is modifying database in this time it is a good idea to set the database into the single user mode during this operation. The following are TRANSACT-SQL statements for sql server query analyzer for secure shrinking of the database log file (the log file cannot be lesser than the value given for SIZE during database create time) USE master EXEC sp_dboption myDatabase, 'Single User', TRUE DBCC SHRINKFILE (myDatabaseLog, TRUNCATEONLY) EXEC sp_dboption myDatabase, 'Single User', FALSE GO myDatabase is to replace with the name of the database. myDatabaseLog is to replace with name of the logical name of the logfile of the database. Alternatively to TRUNCATEONLY a target size can specified (which must be equal or greater than SIZE value on create time) A sql server database can consist of multiple data and log files which are grouped in the same or different filegroups. If you have multiple log files you can use EMPTYFILE instead of TRUNCATEONLY to move all used pages to other log files in the same filegroup (on default the filegroup PRIMARY is used; user defined filegroup(s) can exist) to remove this or all log file except the last one needed for each database. For details of the given TRANSACT-SQL command please use the sql server onlinehelp for reference. I Hope this helps.
-
I have a SQL Server database. It is too large now. How can I remove the transaction log without backing up the database? In fact, I do not need any logs. Can I prevent the database from making transaction logs? Can SQL Server automaticlly remove the logs?
fat888 wrote: How can I remove the transaction log without backing up the database? Well, now that you're in trouble, DUMP TRAN database WITH NO_LOG but that's not a recommended procedure. On a development machine, this can be excusable. Backups are way better. "In an organization, each person rises to the level of his own incompetence." Peter's Principle