| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-10-06 : 07:47:42
|
| Brad writes "I have a 30 GB. mission critical database running in a cluster attached to an EMC disk volume. OS is NT4, MS SQL version 7.0 running on service pack 1.( I know we should be caught up with service packs, we are testing service pack 4 right now).Someone fat fingered our transaction log allocation from around 500 MB up to 18 GB!. I cannot get the transaction log file allocation reduced for the life of me.I have tried dbcc Shrinkfile and was able to reduce the data in the trans. log but the 18 GB. of allocated space didn't budge. I even tried creating a second transaction log file, emptying the original log file with dbcc, and then tried removing the original log with the Alter database remove file option.No Luck, I wasn't able to remove the log file since it was the primary transaction log. Can I change the second transaction log file to be the primary? Then delete the original with the 18 GB allocation? Or better yet, is there another way for me to reduce a file extent allocation from 18 GB to something more reasonable like 750 MB?Any information would be greatly appreciated!Thanks,Brad." |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-06 : 12:39:01
|
| Have a look at sp_detach_db and sp_attach_single_file_db in SQL Server Books Online. Just make sure that you delete your current LDF file after you run sp_detach_db so that it can create a small brand new one when you use sp_attach_single_file_db. Just be aware that your database will be down during this method.Tara |
 |
|
|
sschwarze
Starting Member
15 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-06 : 12:43:04
|
| If you've got to run backup log with truncate_only or no_log, then I've got to ask... What is your database recovery model set to?Tara |
|
|
|
sschwarze
Starting Member
15 Posts |
Posted - 2003-10-06 : 12:49:31
|
| Full This is a policy we have set when thing get out of hand. After the log is reduced in size you need to do a complete FULL backup.LOOK your out of space, it won't shrink, no place big enough back it up, server is going to crash because your out of hard disk. You got any better options with out taking it off line? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-06 : 12:56:08
|
| First, I was asking Brad which recovery model he is using.Second, when things get out of hand? If you are performing regular frequent backups of the transaction log, this should never happen. How often do you backup the transaction log?Tara |
|
|
|
sschwarze
Starting Member
15 Posts |
Posted - 2003-10-06 : 13:12:58
|
| Well that is good ques. We(not me, the networking team) use a tape backup system that does not use sql backups it has it's own backup agent to backup databases. And since the log files NEVER get any smaller and they back up databases every night. I would tend to say NEVER! Which they don't seem to think is a problem.......OK sorry now I am just venting. Forgive me. Just got here and want out! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-06 : 13:16:48
|
| They NEED to be backing up the transaction log at least a couple of times per day. Using a tape backup system is not enough for the transaction logs. They need to be performing transaction log backups. We do them every 15 minutes. There is no point of using FULL recovery model in your scenario. They need to do some reading in SQL Server Books Online so that they understand this. They might as well use SIMPLE recovery model as it stands right now.Tara |
|
|
|
|
Can't Reduce Log File Allocation down from 18 GB!