CODEWITHSUNDEEP
sql-serversql-server-2008logfile
user641812
user641812

Reputation: 335

Huge size log file in SQL server

I have SQL database and I have sent the same database to other place for further development (there may be or may not changes in Schema). I created a a backup file and restored it on another SQL Server.

I found the log file size is very huge (14GB) even though I only have 4 tables with 1000 rows and will not grow two much. Currently I run query and found (Size is in MB).

 data_size  data_used_size  log_size    log_used_size
 801.00     2.06            14220.75    55.63

What I did:

I have shrunk the log files using SQL Management Studio and also kept SIMPLE mode recovery, as we have only few updates to this database that can be done again if transaction fails at any point. I created a backup and restored same and found size of log file has decreased considerably and here is here under.

 total_size data_size   data_used_size  log_size    log_used_size
 802.00     801.00      2.06            1.00         0.46

Upvotes: 0

Views: 999

Answers (1)

Vinay Kulkarni
Vinay Kulkarni

Reputation: 300

Answer to Q1: It is always a good idea to estimate your data growth and set an initial size for your database. The reason is simply to avoid the SQL Data file from performing Auto Growth operations which are very expensive. If you are not expecting any data growth, then whether or not you set Initial Size, it does not matter.

Answer to Q2: You can send the Backup file to any location as long as your SQL Server version on which you restore the file is of similar or higher version. Only point to note is the data in the backup file - consider encryption if you have sensitive data.

Upvotes: 1

Related Questions