You get a bonus - 1 coin for daily activity. Now you have 1 coin

Truncating the transaction log in MS SQL 2008

Practice



So, we need to truncate the transaction log in Microsoft SQL Server 2008. The "WITH TRUNCATE_ONLY" option in the "BACKUP" directive, familiar to administrators of earlier editions, no longer works - it was removed from SQL Server 2008.

So how do we truncate the transaction log file?

1) The database backup type needs to be set to "Simple". To do this:

1a) open SQL Management Studio, select our database, right-click on it and choose "Properties".

1b) go to the "Options" tab and change the "Recovery model" value to "Simple" (by default it's set to "Full").


2) To truncate the transaction log, run this T-SQL query:

2a) Click "Database Engine Query" in SQL Management Studio (the button at the top).

2b) Enter the following query:

USE mydatabase
GO

DBCC SHRINKFILE (N'mydatabase_Log' , 1)
GO


where instead of "mydatabase" substitute the name of your own database, and instead of "mydatabase_Log" substitute the name of the log file (transaction log) for that database.

You can find the log file name by opening the Properties of that database on the "Files" tab.

2c) Click "Execute" - and voila - the transaction log file is truncated.


What we end up with:

1) The transaction log file is truncated and has a small size.

2) You should read up on "Recovery mode" modes - here's an English-language link to the Microsoft MSDN site, which explains what you lose and what you gain when applying the "Simple" type:

http://msdn.microsoft.com/en-us/library/ms189275.aspx

Let me add a couple of words myself (as I understood it from Microsoft):
- You lose: the ability to restore the database from the transaction log file in the event of a database file crash, down to the precision of the last uncompleted transaction (essentially - to the nearest second/minute).
- You gain: no continuously growing transaction log file that only grows but never shrinks, holding everything and anything for the entire lifetime of the database (as I understand it)... For a database of, say, 2GB, you can end up with a log weighing 10GB, 20GB, or even 100GB - uhh... wouldn't it be better to do a Full backup of your database more often?

Applicable to: MS SQL Server 2008

Comments

To leave a comment

If you have any suggestion, idea, thanks or comment, feel free to write. We really value feedback and are glad to hear your opinion.
To reply

Lectures and tutorial on "Databases - MySql (Maria DB)"

Terms: Databases - MySql (Maria DB)

MYSQL Interview Questions and Answers
the cumulative total of mysql and total for each column
MySQL: the differences between MyISAM and InnoDB
Views (VIEW) in MySQL
Setting the correct time zone in php and mysql. Time Zones in MYSQL
Correct resizing of the innodb log (innodb_log_file_size)
Partitioning tables in mySQL
Regular expressions in mysql RLike REGEXP, operation or, and, escaping special characters
Working with hierarchical data in MySQL (Adjacency List List of Adjacent Vertices, Nested Set Nested Set, Materialized Path Materialized Path)
Using and analyzing indexes in MySQL (in WHERE and in JOIN queries)
Exploding UTF-8 Russian (Cyrillic) letters in MySQL
MySQL aggregate functions and GROUP BY
Virtual columns in MySQL and their use cases
How to change the system character set and collation for a MySQL server
MS SQL: migrated databases attach in read-only mode only
MySQL: how to get a list of stored procedures or functions
SQL Server Express is not accessible over the network (via TCP/IP)
How to clear MySQL logs (transaction log or log table)
MySQL: how to copy a table (including to another database)
MySQL: how to set AUTO_INCREMENT to start from a value other than 1
SQL Server Express: scheduled jobs or automatic backups
Installing BDE (Borland Database Engine) on Windows Server 2008 R2 (or any x64 system)
bash/sh: working with MySQL from the command line or scripts
MySQL: how to change the maximum query length, or the "ERROR 1153 (08S01) at line 1: Got a packet bigger than 'max_allowed_packet' bytes" error
MySQL: the "too many connections" error, or how to change the maximum number of connections
IIS: ASP.NET не подключается с ошибкой "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path..."
MySQL: how to enable query logging on a MySQL server
MySQL: importing a CSV file directly into a database table
MySQL: finding out database size from the console (bash/sh script or command line)
MySQL: optimizing the server with the mysqltuner script
Repairing a corrupted Firebird database
MySQL: restoring a database from a backup
MySQL: where is the my.cnf file located on Debian (Ubuntu), CentOS (RedHat), and FreeBSD
MySQL: how to back up a database
MySQL: the syntax of the CASE WHEN THEN ELSE query
How to move a MySQL table from one database to another
Truncating the transaction log in MS SQL 2008
Microsoft SQL Server error referencing sys.databases
MySQL GUI on Windows
Installing MySQL 5.1 on Windows Server
MySQL: searching for a value in a delimited string array
Concatenating SELECT output into a single string
MySQL: how to view processes (connections) or kill a specific process (connection)
MySQL: extracting a substring by index between delimiters
Counting specific characters in a MySQL column
DTS and MS SQL Server 2008 (on Windows Server 2008 R2)
Changing the MySQL root password (especially when it's lost)
Changing the MySQL data directory
Changing a MySQL user's password
Accessing MySQL over the network
MySQL: listing users, databases, tables, and their structure
How to get the total result count of a query with LIMIT in MySQL
Query with variables doesn't work in the MySQL + dbExpress + Delphi stack
Foreign key constraint error when adding a foreign key in MySQL
Exporting products from PrestaShop to OpenCart
MySQL equivalent of Excel's COUNTIF() function
Aggregate functions and nested aggregate functions or a subquery in the FROM clause, the difference between WHERE and HAVING, and query execution order
MySQL equivalent of Excel's AVERAGE(number1, number2, ...) function
Field value won't change to negative in dbForge Studio
MySQL SELECT query stopped working after moving to another hosting provider
How to temporarily disable triggers in MySQL
How to save SELECT INTO into multiple variables in a MySQL procedure
Installing MySQL with screenshots (a step-by-step guide)
Selecting the latest posts and comments in MySQL from a single table with parent and child rows
What is an ORM for? Thoughts on Doctrine
How to assign a clean URL to all products in OpenCart based on product name
Friends, friends of friends, and similar recommended products
Neo4j basics: creating, inserting, adding keys, deleting, and editing
How to select all users with at least one message in MySQL, along with each user's last message
Delete a parent post and all its child comments in a single MySQL query
MySQL server won't start: Plugin InnoDB registration as a STORAGE ENGINE failed
How to create a MySQL trigger that fires on UPDATE when a specific field changes
How to find out who is using the MySQL server and in what state
Cant update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger
How to relate entities in Doctrine, the equivalent of an SQL JOIN
Foreign key won't create in dbForge Studio for MySQL: Cannot add or update a child row, a foreign key constraint fails
How to find the size of databases in MySQL
How to export all products from ShopFreeScript to OpenCart
Is it possible to write a MySQL query to find a chain of friends in a relational database?
MySQL errors
A MySQL error started appearing: Got error 28 from storage engine
MySQL hangs on UPDATE
How to get the last executed query in Symfony Doctrine
How to assign values to rows in MySQL based on rating
Deleting duplicate records in a MySQL table
Field id doesnt have a default value, SQL1.sql line 1
Rave Reports stopped working
How to determine rank (position) in MySQL based on the rating of users or products
Selecting the category with the most nested parent-child subcategories and building the category path in MySQL for OpenCart, an analog of Oracle's SYS_CONNECT_BY_PATH
How to select all users in MySQL along with their three latest photos (or groups and three latest photos per group)
How to write a MySQL audit trigger to log table changes
MySQL Error: Can't Create Table '...' (errno: 121)
How to install Oracle the easy way
How to Enter NULL into a Field in dbForge Studio
SELECT with different conditions
The client-server connection is very slow
MySQL discount table: a separate discount per client for each product group
Selecting a Random Record from MySQL with PHP and Smarty
MySQL query for the number of unread messages between two users
MySQL: How to view the last query and the full query log
How to write a MySQL query selecting the maximum value from each group with conditions
How to dump all MySQL tables with PHP on hosting without OUTFILE privileges
Creating a new database with mysqlcc: an illustrated guide
PDO PHP MySQL UPDATE Not Working
Alias not working in a MySQL INSERT statement
Page view increment not working: UPDATE table SET view=view+1
Can't delete a MySQL database via phpMyAdmin in Denwer
I want my own Postgres: a quick start with PostgreSQL and Foreign Data Wrappers
A Bash script for creating a database dump
Quick Start with PostgreSQL
NoSQL data stores and their use in .NET
MySQL SUM aggregate function returning wrong results when used with JOIN
Ways to merge several tables into one so record IDs match chronological order (consolidation)
The specifics of legal protection for software and databases