Shrink SQL Database
I have made a copy of an SQL database. the reason for the copy is to experiment with queries and stored procedures. It is not uncommon to empty and fill up a table with 70,000 records many times a day while I am experimenting.
How can I drop the Transaction log. I have found as the Transaction log gets large the speed of the database slows. I assume the log grows because of my large deletes queries and inserting queries.
Is it correct that if I make a backup of the database that SQL will truncate the tranaction log file, but it will not shrink the size of the log on the harddrive?
How can I drop the Transaction log. I have found as the Transaction log gets large the speed of the database slows. I assume the log grows because of my large deletes queries and inserting queries.
Is it correct that if I make a backup of the database that SQL will truncate the tranaction log file, but it will not shrink the size of the log on the harddrive?
You can change the recovery model of your database to simple recovery model.
You can also force SQL Server to truncate the log by:
backup log <your database name> with tuncate_only
You can also force SQL Server to truncate the log by:
backup log <your database name> with tuncate_only
After executing the command:
backup log <your database name> with tuncate_only
You can manually shrink the file by:
dbcc shrinkfile (<the log file name>, <resulting file size>)
backup log <your database name> with tuncate_only
You can manually shrink the file by:
dbcc shrinkfile (<the log file name>, <resulting file size>)
You are right that it is you mass inserts and updates that are filling up the transaction log.
Another thing you can do is temporarily change the recovery model while doing them.
In this example my database is CGL, replace that with your database name.
USE [master]
GO
ALTER DATABASE [CGL] SET RECOVERY BULK_LOGGED
GO
USE [CGL]
GO
-- Do your mass inserts or updates here
USE [master]
GO
ALTER DATABASE [CGL] SET RECOVERY FULL WITH NO_WAIT
GO
Another thing you can do is temporarily change the recovery model while doing them.
In this example my database is CGL, replace that with your database name.
USE [master]
GO
ALTER DATABASE [CGL] SET RECOVERY BULK_LOGGED
GO
USE [CGL]
GO
-- Do your mass inserts or updates here
USE [master]
GO
ALTER DATABASE [CGL] SET RECOVERY FULL WITH NO_WAIT
GO
ASKER
Is there an advantage to truncate the transaction Log but not shrink the log file size. This will prevent SQL from having to manage increasing the size of the log file.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. Gave you a complete answer.
http://www.codeproject.com/KB/database/truncate_log_SQL_server.aspx