| Author |
Topic |
|
laddu
Constraint Violating Yak Guru
332 Posts |
 Posted - 2007-12-17 : 22:24:41
|
| In sql server 2005 server, we are facing space issue on log drive.There is only 9 MB is space is avialable out of 111 GB. Upon investigation I found that 1 user database log files is occupying huge space(109 GB). Please let me know how to proceed now? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-17 : 22:31:13
|
| Did you ever backup log? You should backup log for the db then shrink the log file. Also need to set job to do log backup in schedule. |
 |
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2007-12-17 : 22:49:16
|
| I did't backup log, Could you please explain me in steps?In job step I have to give backup log dbname truncate only ?? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-17 : 23:46:52
|
| Run 'backup log dbname truncate_only' in query windows then shrink log file. Don't use truncate only in log backup job. |
|
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2007-12-18 : 02:33:44
|
| I ran above command and dbcc shrinkfile('logfilename', 500) and soon after I took full backup of db, now there is 101 GB free space is there. Is anything is left to do? we are into 24x7 production environment.One question what is 500 stands for? |
|
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2007-12-18 : 04:40:20
|
| we need to give the complete path? or only that log file name is enough?VinodEven you learn 1%, Learn it with 100% confidence. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-18 : 21:13:07
|
| Just log file's logical name.>> One question what is 500 stands for?It's 500mg. Did you check log file size after shrinking? Do you backup log during the day? Do you rebuild index before full backup? |
|
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2007-12-18 : 23:01:00
|
| Yes I took full backup soon after shrinking the log file, But rebuild index job is failedwhy we need rebuild index?Is there any need to change tlog file growth by percent or set to restricted(unrestricted) file growth??? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-19 : 00:22:25
|
| Rebuild index is logged process, so need enough space in log file for that. |
|
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2007-12-23 : 23:07:19
|
| still I have this space issue problem, please let me know how to proceed ? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-24 : 16:01:24
|
| Don't shrink db files.>> why we need rebuild index?Defrag tables to improve query performance. |
|
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2007-12-25 : 22:11:50
|
| Could you please tell me in steps how to defrag the tables to improove the performance. We are into 24x7 environment. I am new to SQL server. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-25 : 22:48:26
|
| Rebuild index. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2007-12-26 : 01:06:15
|
| Thanks alot |
|
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2007-12-26 : 03:59:42
|
| i've tried to create this procedure in my database, but i got the errorMsg 102, Level 15, State 1, Procedure isp_ALTER_INDEX, Line 73Incorrect syntax near '('.please let me know where i'm wrongVinodEven you learn 1%, Learn it with 100% confidence. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-26 : 09:58:04
|
| Did you get the error on the create proc or when you tried to execute it? What is your database compatibility level set to?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
space issue