| Author |
Topic |
|
kirknew2SQL
Posting Yak Master
194 Posts |
 Posted - 2007-12-27 : 04:46:23
|
| I installed SQL 2005 bout six months ago. I have noticed that my disc space is slowly being eaten up. I am guessing the transaction logs something need to be purged. I have not set up any processes or changed any of the default settings. 1. What maintenance should I be doing? 2. And where are the transaction logs I have been reading about?Thanks |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-27 : 22:36:05
|
| You have to do log backup if the db is not in simple recovery model. |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-12-28 : 02:54:55
|
| if you have a default install, the datafiles (.mdf and .ldf files) will be in the Data directory. usually something like C:\program files\microsoft sql server\mssql.1\mssql\datayou should immediately create a maintenance plan to perform full backups on a regular basis. It also sounds like your databases are in FULL recovery mode, so the log files (the .ldf files) will continually grow because you are not backing up and truncating the log file. To fix this you will also need to schedule regular log file or incremental backups of your databases.The easiest method is to do this with a maintenance plan. A more sophisticated appoach is to use TSQL scripts like the ones Tara has on her weblog. She has half a dozen or so really useful maintenance scripts that you can schedule via the SQLAgent to take care of most maintenance tasks. http://weblogs.sqlteam.com/tarad/Again, since you do not have a full backup your data is at risk. The first step for you is to get a full backup taken. -ec |
|
|
|
kirknew2SQL
Posting Yak Master
194 Posts |
Posted - 2007-12-28 : 12:17:42
|
| Thank you for the information. Based on your input I could do more research. I have changed to simple recovery for the time being since the server is backed up every night.ALTER DATABASE <database_name> SET RECOVERY SIMPLETo free the space used by transactions committed before the conversion, I execute the following statements: BACKUP LOG <database_name> WITH TRUNCATE_ONLYDBCC SHRINKFILE (<log_file_name>, TRUNCATEONLY)This freed a lot of space. Now I have some time to look at creating a maintenance plan or TSQL script as you suggested.You have gotten me on track. :)Is there any other maintenance I should know about or be doing before it pops up and bites me like this did? |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-12-28 : 12:41:47
|
quote:
Originally posted by kirknew2SQL
Thank you for the information. Based on your input I could do more research. I have changed to simple recovery for the time being since the server is backed up every night.
backing up the server with standard netbackup/backup exec/etc. will not properly backup a database that is open. You need to run specific database backup commands in order to backup your databases.-ec |
|
|
|
kirknew2SQL
Posting Yak Master
194 Posts |
Posted - 2007-12-28 : 13:46:03
|
| I am using Yosemity and doing a full (incrimental) disc backup every night. Theat should get everything right? |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-12-28 : 15:25:36
|
quote:
Originally posted by kirknew2SQL
I am using Yosemity and doing a full (incrimental) disc backup every night. Theat should get everything right?
have you tested a recovery? -ec |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-12-28 : 15:30:52
|
quote:
Originally posted by kirknew2SQL
I am using Yosemity and doing a full (incrimental) disc backup every night. Theat should get everything right?
You have to do SQL Server backups to be sure that you can recover your databases.CODO ERGO SUM |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-12-28 : 15:35:05
|
| and what is yosemity? |
|
|
|
kirknew2SQL
Posting Yak Master
194 Posts |
Posted - 2007-12-28 : 15:42:16
|
| Yosemity is the name of the back up software i am using.I have not tested a restore yet. But from the comments here it sounds like I backing upthe drives is no assurence that i will be able to recover my database. I will create a maintenance plan. But I do not understand why the restoring a disc backup will not recover my DB's? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-28 : 23:31:15
|
| You have to test it before telling people you can recover db from that backup when needed. Most of time you only need to recover specific db instead of whole disk. |
|
|
|
|
Transaction log bloat