| Author |
Topic |
|
rardales
Starting Member
32 Posts |
 Posted - 2005-01-07 : 12:42:13
|
| We have 5.3 GB left of our 20 GB on our SQL Server and I found one of the culprits that's taking up a lot of space. It's a file called "convert_log" and it's a "database file" type. It was last modified on 12/31/04 at 11:30pm. That file is 11.3gb! I'd like to delete it. Is that a file that we need? FYI, we back up our database via tape drive every night. Let me know guys. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-07 : 13:33:57
|
| I certainly would not delete it. It is probably in use by a database named convert. Check each database's properties (or check out the system tables) to see which one is using it. It sounds like your database recovery model is set to FULL though and you aren't performing transaction log backups. Let us know if this is the case.Tara |
 |
|
|
rardales
Starting Member
32 Posts |
Posted - 2005-01-10 : 13:22:25
|
| Ok, I won't delete it, but can I move that file to a network drive and delete it from the SQL server? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-10 : 13:28:34
|
| Only if you find no database is using it. You'll need to make sure of this otherwise a database will become suspect if you make this change.Did you check your database recovery model on your databases? Are they set to FULL? Are you performing transaction log backups on these? This is why the tlog file is so big.Tara |
|
|
|
rardales
Starting Member
32 Posts |
Posted - 2005-01-10 : 13:35:10
|
| From my understanding, there is only one database that we use. I'm still a little ignorant here so how do you check the database recovery model and check to see if they're "full"? I'd appreciate your guidance. We're using MS SQL server and I have access to Enterprise manager. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-10 : 13:39:33
|
| Right click on each database and go to properties. Then go to Options tab.Tara |
|
|
|
rardales
Starting Member
32 Posts |
Posted - 2005-01-10 : 13:48:05
|
| In "enterprise manager," there is no "properties" when I right click a table. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-10 : 13:50:49
|
| Right click the database, not a table. Is there a DBA there that can help you out with this?Tara |
|
|
|
rardales
Starting Member
32 Posts |
Posted - 2005-01-10 : 13:58:29
|
| I'm sorry, I did right click the database and not the table. There's no other DBA here. Can I look somewhere else? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-10 : 14:05:37
|
| Not sure what to tell you as when I right click a database there is a properties option. I then get a new window and click options tab. You could also run sp_helpdb to check it out.Tara |
|
|
|
rardales
Starting Member
32 Posts |
Posted - 2005-01-10 : 14:17:54
|
| Is that a stored procedure script? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-10 : 14:21:02
|
| Yes. Check out SQL Server Books Online for details.Tara |
|
|
|
rardales
Starting Member
32 Posts |
Posted - 2005-01-10 : 15:41:20
|
| I'm pretty sure that file communicates with a database we're currently using; so it sounds like we should not even move it either??? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-10 : 15:44:25
|
| Yep. But then you've still got the problem of the 13GB log file. Can't help you out until we know the recovery model though and whether or not transaction log backups are occurring. The default is FULL. When you use this recovery model, you MUST perform tlog backups otherwise your tlog is just going to grow and grow. If you don't want the ability to restore to a point in time, then change it to SIMPLE.Tara |
|
|
|
rardales
Starting Member
32 Posts |
Posted - 2005-01-10 : 16:06:08
|
| Well, I have access to Enterprise Manager on my local computer. Perhaps I need to go to the actual SQL Server where Enterprise Manager is installed? I have to ask IT for access. Well, when I right click on the database, here's the drop down list I get:NewAll TaskViewNew Window from hereRefreshExport ListHelpShould "properties" appear here as well? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-10 : 16:10:05
|
| You apparently do not have sysadmin on the SQL Server to view the properties. That menu option will appear when you have sufficient privileges. Maybe you only need db_owner.Tara |
|
|
|
rardales
Starting Member
32 Posts |
Posted - 2005-01-10 : 17:10:26
|
| Ok, I have access to the server and I noticed that the recovery model is set to "Full." What's the next step? Can I change it to simple and by doing that, will it decrease the size of the file? I also checked the "Transaction Log" tab and it's checked to "automatically grow" and the max file size is "unrestricted to growth." File growth is by "10 percent." |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-10 : 17:12:59
|
| It just depends on what your business needs are. Does your company require point in time recovery? Meaning, if a full backup is performed at 1am nightly and a failure occurs at 2pm, do they need to be able to restore to 1:59pm. If yes, then you need to start performing transaction log backups. So your backup solution will need to be adjusted. If this is not your requirement, then change it to simple. You'll then need to shrink the file down using DBCC SHRINKFILE, details of which can be found in SQL Server Books Online.Tara |
|
|
|
rardales
Starting Member
32 Posts |
Posted - 2005-01-10 : 17:23:39
|
| By "failure," what do you mean? Well, we back up the entire server daily and before doing anything crucial like running an update script, we usually back the database up right before running a script. With that said, it sounds like we can just switch it to simple. Also, by using "DBCC SHRINKFILE," how significant of a reduction will the file come down to? 50%? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-10 : 17:27:08
|
| Failure, meaning corrupted databse, hardware problems, etc...We can't afford to lose 24 hours of data, which is what will happen if you only perform backups at night. We perform transaction log backups every 15 minutes as that's the maximum amount of data we can lose due to its criticality.Once changed to SIMPLE and then shrunk using DBCC SHRINKFILE, it should be rather small, less than 100MB I would guess.Tara |
|
|
|
rardales
Starting Member
32 Posts |
Posted - 2005-01-10 : 17:31:43
|
| Well, the transaction log summary is a good idea since we constantly make changes. How can I set this up and can I make it run automatically? I really appreciate your help with this. |
|
|
|
Next Page
|
Can I delete this Database File????