| Author |
Topic |
|
cn
Starting Member
3 Posts |
 Posted - 2007-11-07 : 21:50:47
|
| Hello All,We don't have a DBA and are faced with an issue for one of our databases.Early this week, we ran out of disk space where our transaction logs live. For one particular database, the transaction log grew to 16gig and the database at that time was 9gig. Our database live in a separate drive to the transaction log files and backup files.After cleaning up the disk space, I followed instructions from our software support to run a database backup then "dump tran (db-name> with no_logs" then went to enterprise manager and ran a "database shrink".This reduced the transaction log (.ldf) to about 100meg, which was great, but the next day I could not see that the .ldf file changed at all, and it still has the same timestamp of when I ran the "dump tran" and shrink database command.The other problem is that the database file (.mdf) for this particular database has grown 10 gig overnight and is currently at 22gig. (on that day, we *did* have over 2000 transactions processed for a customer)I have since found out that I shouldn't have done the "dump tran" command from browsing the web, and don't really want to get back to that software support dude who gave this advice.Can someone explain to me is happening to this database now?Is there a way to fix this - ie, get the transaction log to work again, and can the database size be reduced so that I can get some disk space back as we will run out of space very quickly if this keeps up.thanks in advanced,cn |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-07 : 22:00:10
|
| It isn't quite that simple.How is the server configured?Is it set to auto grow, if so by what % or how many MB?Is auto shrink on or off? (hopefully OFF)Auto statistics etc?How often is the database backed up?Is there a maintenance plan scheduled? how often?You will need to provide some more information, but do a search of DBCC UpdateUsage and sp_SpaceUsedbyTables and DBCC CheckdbFor the size to DOUBLE due to 2000 new transactions wreaks of potential design or other flaws in the process. |
 |
|
|
cn
Starting Member
3 Posts |
Posted - 2007-11-07 : 22:28:21
|
| Hi Dataguru1971,the database is set to auto grow by 10%,autoshrinking is *not* turned on,how do I find out "auto statistics"?The database and transaction log is backed in a maintenance plan every night except for sunday night,a separate maintenance plan optimises and does the indexing every sunday night.Database snapshots using symantec backup exec sql agent every night also.I'll check out the dbcc commands you advised.(also, in the last few nights the transaction logs backup via the maintenance planfails according to event viewer, but all other databases runs successfully.)thanks,cn |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-07 : 22:44:25
|
| the failing of the tran log needs to be investigated. That is causing part of the problem.Something has clearly gone awry and it may take more than a few posts, and probably someone with more experience troubleshooting via message board than I to chime in. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-07 : 23:54:14
|
| File's timestamp will change when restart sql or change file size, don't worry if you can still insert rows in the db. But you do need backup log more often. |
|
|
|
cn
Starting Member
3 Posts |
Posted - 2007-11-08 : 00:46:10
|
| Thanks for the suggestions.I found out why the transaction log backup has stopped working. The software support advised me to change the recovery model from full to simple, but didn't explain the implications of this.I have just changed it back, after finding this in a knowledgebase article.Now what the database size, is it possible to bring down the size and reclaim some disk space?if so, how?and with the transaction log backup, if i schedule more frequency in the maintenance plan during the day - will it affect any processing of anything else?thanks,cn |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-08 : 01:01:01
|
| "The other problem is that the database file (.mdf) for this particular database has grown 10 gig overnight and is currently at 22gig. (on that day, we *did* have over 2000 transactions processed for a customer)"That worries me. Database doubled in size ..."the database is set to auto grow by 10%,"I think 250MB would be a better figure (SQL 200 is slow to extend database, 10% of 22GB is 2GB - on a busy server that could take 30 minutes and cause timeouts etc.) We use 50MB here on database around 10GB - but we do periodic physical defrag of the files as well."Now what the database size, is it possible to bring down the size and reclaim some disk space?"You can check if it contains "free space" or not. It might be that the added data badly fragmented the indexes, and that took a lot of disk space. You weekly reindex will sort that out (but that in itself can use a lot of TLog space and can extend the Data file.Using the SHRINK option in Enterprise Manager with "move data to start of file" should take care of that. I recommend that you do that late on Friday/Saturday so that your reidnex on Sunday sorts it out (and if it regrows dramatically then leave it at that size - install more disk space if necessary, you won't be able to get it under that size for peak-usage)After any truncate of the logs, or Shrink, you should take a FULL backup so that the backup chain is not broken."and with the transaction log backup, if i schedule more frequency in the maintenance plan during the day - will it affect any processing of anything else?"No, it will backup the same "volume" of data, but in more smaller files. If you have to restore the database there will be more files to restore (so a bit annoying! but no other real difference).In a disaster you may only be able to restore to your latest TLog backup, so making the Tlog backups every 10-15 minutes is the general advice. You do need to copy them off the machine for safety, or at the very least put them on a different drive (assuming that they DO get copied to tape each night or something like that)Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-08 : 16:31:39
|
| When you change recovery mode from full to simple, you have to run 'backup log db_name with truncate_only'. Otherwise, log file will keep growing. |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-09 : 04:27:11
|
| "The software support advised me to change the recovery model from full to simple, but didn't explain the implications of this."That was a good one! |
|
|
|
|
db and logs growth issue