July 1, 2003 at 10:30 am
Hi all,
I have a sql server 2000 sp2 on windows 2000. My database is 40 GB with 4 GB free.
I created a maintenance plan doing the following:
1, Daily backup DB to disk D:
2, Optimizing DB every Sunday.
3, Integrity check every Sunday.
4, Transaction log backup every hour.
Friday I checked the server,
Data file is 40 GB,
Transaction log file is 102 MB,
Disk D: has 83 GB free spaces,
After Sunday's optimization, Monday morning
I got big problem:
Disk D: is full, no free space left,
Data file grows to 71 GB,
Transaction log file grows to 57 GB,
The system is down.
Is that normal for both files growing so
big after optimization?
How can I avoid this happened again?
Increse disk spaces? or doing something else?
Please help.
Right now every Monday morning I must shrink database to release the disk spaces.
Any response is welcome and appriciated.
Robert
July 1, 2003 at 10:36 am
Is there any bulk inserts before/After your maintenance? Bulk inserts cause log and datafile growth
Shas3
July 1, 2003 at 11:33 am
No.
Only thing I didn't mention is during the optimizing process our sys admin is doing veritas backup. because the transaction log file is full, so the veritas backup is failed.
I checked everywhere the only error message
is the tran log file is full, delete somefile to relaese spaces.
Thanks!
Robert
July 1, 2003 at 11:44 am
You said:
Data file grows to 71 GB,
Transaction log file grows to 57 GB,
are those the actual SQL Server files (.mdf and .ldf) or are they the backup files?
If they are the backup files, then you probably did not set your maintenance plan to OVERWRITE (WITH INIT).
-SQLBill
July 1, 2003 at 11:52 am
Those are .mdf and .ldf files. The .mdf file is allocared so big with 35 GB free.
I back up database when the backup completed then delete old backup so only keep one day backup on disk.
By the way Monday's backup is failed for the same reason.
Robert
July 2, 2003 at 10:56 am
To my knowledge, the .ldf grows so big is
normal. It could be 1.2 times database size.
but I can't understand why the .mdf file grows so large.
Another basic question: For such a server,
how many disk spaces are ideal?
Thanks!
Robert
July 2, 2003 at 12:06 pm
Check your optimizer changes the FILLFACTOR of the indexes, if yes uncheck it. I can't think any of other possibilities. Why don’t you launch a trace flag and see is there any other transactions going on before/after your maint. If you are using SQL 7.0 use the following query to get the transaction.
Exec master..xp_sqltrace Trace, @EventFilter = 115, @Fulltext = 1
Shas3
July 2, 2003 at 7:41 pm
I may be off base, but if you are using full logging on your database(I am not sure if you are or not), the log will remain intact until you back up the log. The following command will shrink up the log for you, but it will trash any replication or log shipping.
BACKUP LOG dbname WITH TRUNCATE_ONLY
dbcc shrinkfile (dbname_Log)
substitute the database name on your system wherever you see the string dbname.
I hope this helps, if I am off base, please post back and let me know.
July 3, 2003 at 11:56 am
Thanks for all your response.
1. The coming weekend I will launch a profiler.
2.Before, Monday morning first I should delete some files to release some disk spaces. Then I did what Bradley said. My question is, If I create a job and automate it. Will it work? Because after optimising there is no disk space at all.
I still want to know the .mdf file grows so large, is it normal? why it would happen.
Thanks!
Robert
July 3, 2003 at 12:54 pm
The dbcc shrinkfile command should also work for your datafile/mdf also. It will probably take a while, but it will shrink the file. All you would have to do is substiture the name of the datafile for the dbname_log string. Usually it will be dgbname_data. The code would read as follows:
dbcc shrinkfile(dbname_data)
July 29, 2003 at 11:44 pm
Just a thought but you may find that the growth is because you asked for it. The optimize component of the job may be adding 10% free to the .dbf hence its size increase. If you created the job via a std maint plan using EM then this is the default setting, worth a look.
July 30, 2003 at 5:21 pm
Long time ago I rebuilt indexes on largest table with filfactor 80%. Now I run optimizing every Sunday morning with 20% free space checked. But after optimizing the .mdf file grows too big, leave 100% free space. That's why I don't understand.
Thanks!
Robert
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply