Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rpl
Starting Member
2 Posts |
 Posted - 2007-07-23 : 12:52:12
|
| Hi, Currently my db size is only 6 GB but the transaction log file initial size was set to 20 GB and has grown much way beyond the db size with the autogrowth feature turn on. The database was originally a test/development DB and was migrated to a production server including the log file. This probably caused the accumulation of transactions on the log. We run backup everyday and tried to shrinkfile and file size did not change. Can I change the "initial size" setting of the transaction log without causing any problems? Do I need to stop the service before I made the change assuming I made the change after the backup run? Or can I change it on the fly?Thanks in advance. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-23 : 13:37:31
|
| You have to backup log periodically if the db is in full or bulk-logged recovery mode. What you can do are:1. backup log with truncate only option.2. set the db to simple recovery mode.3. shrink log file.4. set db recovery mode back. |
 |
|
|
rpl
Starting Member
2 Posts |
Posted - 2007-07-26 : 08:58:49
|
| Thanks rmiao. What I did so far is create a test environment similar to my production. I did run the backup with truncate option and then shrinkfile on the test environment. Did not work - I mean the file size is the same. I change the initial size to about 25% of the db size and run the shrink and I was able to recover 2 GB. The log is still big though but a bit better than before. Should I start with a new transacation log? Is this advisable / practical? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-26 : 09:01:58
|
Neither LOG nor DATA can be shrinked to a size less than original setting.
E 12°55'05.76"N 56°04'39.42" |
|
|
|
DMcCallie
Yak Posting Veteran
62 Posts |
Posted - 2007-07-26 : 11:38:36
|
| Yes you can shrink the data or log smaller than the original size!You can also create a new log and then delete the old log. There are multiple ways to skin that cat!Also, you can create a new data extent then migrate the data from the old file to the new one then delete the old one. I always prefer to shrink; however, you might need to backup the DB and log and shrink several times before you are successfull.Go to a test machine or just restore the DB as a new DB on the existing server (if you can find the disk space) then play with the shrink before you tackle the production DB...DeWayne |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-26 : 11:57:05
|
Has this changed since SQL Server 2000?
E 12°55'05.76"N 56°04'39.42" |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-26 : 22:57:26
|
| Yes, you can shrink to smaller than initial size now. |
|
|
|
|
|
|
Transaction Log - Initial Size Too Big