| Author |
Topic |
|
tribune
Posting Yak Master
105 Posts |
 Posted - 2004-03-03 : 14:58:07
|
| I cant seem to get the log to go to a reasonable size for this database. The database is in SIMPLE recovery mode, I've backed it up multiple times and the log file is still 1.5GB. I thought that in simple mode the transaction log is automatically truncated? |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-03-03 : 15:05:55
|
| Look atDBCC SHRINKFILEBrett8-) |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-03-03 : 15:06:08
|
| Did you do a shrink database on that database?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-03-03 : 15:35:39
|
| Damn, sniped by 13 seconds!Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-03-03 : 15:39:31
|
  I think the record is still at 2 seconds....Brett8-)EDIT: That would be a cool advanced search feature...definetly would be a scan.... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-03 : 15:48:34
|
| Yes the log is truncated when in SIMPLE recovery model mode. Do you have free space in the file? You can check this by running sp_spaceused or by left clicking on your database in Enterprise Manager, then right click on it, go to view then to taskpad. If you have free space, then DBCC SHRINKFILE will work for you.Tara |
|
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-03-03 : 16:16:27
|
| Yes there is space free in the transaction log, amost 99% is free. I tried running dbcc drinkfile:Cannot shrink log file 2 (intranet_Log) because all logical log files are in use.DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages ------ ------ ----------- ----------- ----------- -------------- 8 2 180288 63 180288 56(1 row(s) affected)DBCC execution completed. If DBCC printed error messages, contact your system administrator.Do I need to take the database offline to perform this operation? If so, how do I kill all the users accessing the database through t-sql? Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-03 : 16:17:48
|
| No you do not need to take the database offline nor do you have to KILL the users. What is the command that you ran?Tara |
|
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-03-03 : 16:21:55
|
| use intranetgodbcc shrinkfile (2, TRUNCATEONLY) |
|
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-03-03 : 16:22:50
|
| select * from sysfilesshows that the file id is 2 for the log: "C:\Program Files\Microsoft SQL Server\MSSQL\Data\Intranet_log.LDF" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-03 : 16:30:39
|
| Never used TRUNCATEONLY as I wanted the file to have some room. Try this:DBCC SHRINKFILE (LogicalNameofLDF, 5)GOChange LogicalNameofLDF to the logical name of the transaction log. 2 should work though as well. 5 means leave 5MB of space, which is a good size for a 10MB database. In a lot of environments, the log will typically be about 25% of the MDF file size.Also, is the file really on the C drive? That is a highly not recommended configuration. Databases should be put on an array that does not have the OS installed on it. Also, the page file should be on its own array.Tara |
|
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-03-03 : 16:39:28
|
| I tried "dbcc shrinkfile (intranet_Log, 5)" and got the same error "Cannot shrink log file 2 (intranet_Log) because all logical log files are in use."The database is on a raid 5 array with the OS. We dont have enough drives in the server to place the OS or the transaction logs on their own array unfortunately. |
|
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-03-03 : 16:40:45
|
| I got it tduggan thank you for the help |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-03 : 16:41:42
|
| At least move your LDFs to the raid 5 array then. Just a recommendation of course.Tara |
|
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-03-03 : 16:41:43
|
| Oh this is great, a 5MB transaction log :-)1.5GB of raid storage isn't very cheap now days heh |
|
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-03-03 : 16:42:11
|
| I thought LDFs should be on a raid 1 for faster writes? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-03 : 16:44:08
|
quote:
Originally posted by tribune
I thought LDFs should be on a raid 1 for faster writes?
Yes that is true. But it not recommended for it to be on the C drive array. I think that in your configuration, the MDFs and LDFs on the same array would be more recommended than the LDFs on the C drive array.Tara |
|
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-03-03 : 16:45:57
|
| Out of curiosity, what kind of machine are you running your databases on. I've got an HP380 and a Dell 2650. The HP supports up to six drives while the Dell only supports five.Ideally I'd like to have at least 3 mirrors, two raid 1s and a raid 5 for the MDFs, that would require seven drives though... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-03 : 16:50:36
|
| Here's what we are purchasing these days:HP DL580 with 4 CPUs and at least 4GB of RAM. We hook up the server to a SAN (Compaq's EVA solution). The databases run on the SAN. So we only run the OS and the page file on the local drives, although they are on their own array.Tara |
|
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-03-03 : 16:55:54
|
| Wow thats some pretty big (and expensive) equipment. Pardon my ignorance, but how does the sql server interface with the storage area network? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-03 : 16:57:53
|
| Via fiber optics and software.We have mission critical databases that need to perform well and be highly available. That is why we have gone down that approach. We also are using Enterprise Edition and clustering.Tara |
|
|
|
Next Page
|
10mb database file with a 1.5GB transacation log