| Author |
Topic |
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
 Posted - 2007-11-26 : 14:38:07
|
| We have a issue where, one of the database we had did'nt have any kind of database or transaction log backup jobsand we did'nt realise this until we had a disk space issue and also in about 2 days the mdf and ldf files have doubled:Currently the following are the sizes of the mdf and ldf files of the db:mdf file: 41.4 GBldf file: 16.5 GBI created database and tran log backup jobs using the maintenance plan wizard and took the backup of the database and the transaction log backup, here are their sizes:BAK file size: 14.8 GBTRN file size: 15.2 GBAfter taking the backup's I checked the database sizes and still the mdf and ldf file sizes are the same, so how can I truncate those files, please help.Thanks. |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-11-26 : 15:21:55
|
| you will need to shrink the databases. From Enterprise Manager you can right-click the database and select All Tasks --> Shrink Database. There are a couple options on that page, one will try to reorganize all the data, the other truncates the unused portion of the current mdf and/or ldf file. Sometimes the truncate method doesn't shrink the files at all since the used portion of the file might be at the very end of the file.Anyway, shrinking the db is something that will cause some interruption to the database so you would want to do this during off hours. Also, make sure to have a good backup prior to running a big maintenance job like this just in case something goes wrong.-ec |
 |
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-26 : 15:22:04
|
| You can shrink the database (right click on database -> shrink)So it may be a good idea to shrink the log.However, for the data you should check first how much space do you have inside these 41.4Gb |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2007-11-26 : 15:33:16
|
| Is it okay if I shrink from the enterprise manager, by right-clicking the database, or do I need to run DBCC shrink file...which is the best practice in shrinking the database and log. Thanks. |
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2007-11-26 : 16:00:21
|
| I use DBCC Shrink File, but I don't think it matters. I also have an issue with log size, when I run a DBREINDEX, my logs grow to about the size of the database. This reindex job occurs right after midnights at the heal of a daily backup. This particular database, doesn't have much activity after midnight, so I have an alert set up on log size, that triggers a backup on the log file using the with no log option, and then it runs the DBCC shrink file. Not a good practice, but until I can add drives, or figure out why it is happening, I guess it works. Anyone know why a DBCC DBREINDEX explodes the size of the log file? |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-11-26 : 16:19:47
|
quote:
Originally posted by TSQLMan
I use DBCC Shrink File, but I don't think it matters. I also have an issue with log size, when I run a DBREINDEX, my logs grow to about the size of the database. This reindex job occurs right after midnights at the heal of a daily backup. This particular database, doesn't have much activity after midnight, so I have an alert set up on log size, that triggers a backup on the log file using the with no log option, and then it runs the DBCC shrink file. Not a good practice, but until I can add drives, or figure out why it is happening, I guess it works. Anyone know why a DBCC DBREINDEX explodes the size of the log file?
From Microsoft support:Operations: DBCC DBREINDEX and CREATE INDEXBecause of the changes in the recovery model in SQL Server 2000, when you use the Full recovery mode and you run DBCC DBREINDEX, the transaction log may expand significantly more compared to that of SQL Server 7.0 in an equivalent recovery mode with the use of SELECT INTO or BULK COPY and with "Trunc. Log on chkpt." off.Although the size of the transaction log after the DBREINDEX operation might be an issue, this approach provides better log restore performance.Recommendation is:To defragment the indexes to benefit the workload performance in your production environment, use the DBCC INDEXDEFRAG Transact-SQL statement instead of the DBCC DBREINDEX Transact-SQL statement. If you run the DBCC DBREINDEX statement, the transaction log may expand significantly when your SQL Server database is in Full recovery mode. Additionally, the DBCC INDEXDEGRAG statement does not hold the locks for a long time, unlike the DBCC DBREINDEX statement.
Future guru in the making. |
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2007-11-26 : 16:32:25
|
| Actually I read that article on support.microsoft.com, but my problems began well after the SQL 2000 upgrade, and we have always used the full recovery model. I have a new server on the way next month, so the issue will be short lived, but it is still a puzzle I would like to solve. |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-26 : 16:47:10
|
| I dont think it is a surpriseBy making DBREINDEX, you literally recreate a table (if it has a clustered index), so you have a very big transaction, involving may be millions of rows. |
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2007-11-26 : 16:56:38
|
| The mystery I am trying to solve is why did it all of the sudden start happening, when there was no significant change in the Database size, Amount / Size of Transactions, or any change in Recovery Mode. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-11-26 : 22:28:53
|
quote:
Originally posted by TSQLMan
The mystery I am trying to solve is why did it all of the sudden start happening, when there was no significant change in the Database size, Amount / Size of Transactions, or any change in Recovery Mode.
did you implement a maint plan to optimize the database? that would explain the rapid database growth.-ec |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2007-11-27 : 00:17:22
|
quote:
Originally posted by eyechart
you will need to shrink the databases. From Enterprise Manager you can right-click the database and select All Tasks --> Shrink Database. There are a couple options on that page, one will try to reorganize all the data, the other truncates the unused portion of the current mdf and/or ldf file. Sometimes the truncate method doesn't shrink the files at all since the used portion of the file might be at the very end of the file.Anyway, shrinking the db is something that will cause some interruption to the database so you would want to do this during off hours. Also, make sure to have a good backup prior to running a big maintenance job like this just in case something goes wrong.-ec
I was able to shrink the log file using dbcc shrinkfile from 21 GB to 2MB, currently for this database, for the transaction log:Maximum file size has unrestricted file growth, is that okay OR shall I have restrict file growth(and to what MB).Also how can I shrink the mdf data file?Please let me know. Thanks. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-27 : 02:28:11
|
| Same way as shrinking log file. By the way, how often do you backup log? |
|
|
|
austdev
Starting Member
3 Posts |
Posted - 2007-11-27 : 02:42:58
|
| If you are not planning on making regular transaction log backups, it may be easier to switch recovery model to "Simple" to prevent the build up of huge log files in future.Db Properties / Options |
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2007-11-27 : 08:00:59
|
quote:
Originally posted by eyechart
quote:
Originally posted by TSQLMan
The mystery I am trying to solve is why did it all of the sudden start happening, when there was no significant change in the Database size, Amount / Size of Transactions, or any change in Recovery Mode.
did you implement a maint plan to optimize the database? that would explain the rapid database growth.Actually, when the issue started a Maintenance Plan was being used for optimization, but when the problem started, I wrote all of the backup and optimization scripts by hand and put them into a job. Didn't make any impact.-ec
|
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2007-11-27 : 09:52:10
|
quote:
Originally posted by austdev
If you are not planning on making regular transaction log backups, it may be easier to switch recovery model to "Simple" to prevent the build up of huge log files in future.Db Properties / Options
The logs are getting backed up once a day. Since there were no maint jobs on that database, Created optimizations and integrity checks jobs using maint plan wizard:both these scheduled for every sunday.And also scheduled the db and log backup jobs.I am thinking of waiting till this sunday as optimiz job would run and see the behaviour of the log files and then decide accrodingly.Will that be ok? Thanks. |
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2007-11-27 : 09:56:40
|
quote:
Originally posted by sqlserverdeveloper
quote:
Originally posted by austdev
If you are not planning on making regular transaction log backups, it may be easier to switch recovery model to "Simple" to prevent the build up of huge log files in future.Db Properties / Options
The logs are getting backed up once a day. Since there were no maint jobs on that database, Created optimizations and integrity checks jobs using maint plan wizard:both these scheduled for every sunday.And also scheduled the db and log backup jobs.I am thinking of waiting till this sunday as optimiz job would run and see the behaviour of the log files and then decide accrodingly.Will that be ok? Thanks.
You won't go wrong by backing up Logs Hourly. Even if you don't have a high traffic Database, I would recommend hourly backups. |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2007-11-27 : 10:31:46
|
| I checked this morning and the mdf file size has been reduced from41GB to 19.7GB, I did'nt shrink that file and not sure how the size was reduced. The only thing I did last night was the follow:1. BACKUP LOG DBname WITH TRUNCATE_ONLY2. DBCC SHRINKFILE of the tran logActually I was thinking tonight to shrink the mdf file, but kind of surprised how mdf file automatically was shrinked.Any ideas?Thx. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-28 : 03:08:49
|
| Did you set db option 'auto shrink'? |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2007-11-28 : 09:51:31
|
| Yes, auto shrink option was checked. Thx. |
|
|
|
Arpan
Starting Member
3 Posts |
Posted - 2007-12-01 : 02:18:19
|
| Instead of using auto shrink , just execute the follwing statements.1st: BACKUP LOG DBname WITH TRUNCATE_ONLY 2nd: DBCC SHRINKFILE (LOgFilename,fileid) |
|
|
|
|
mdf and ldf files of the DB have almost doubled