SQL Server Performance Forum – Threads Archive
Tlog larger than database. Does not shrinkI inherited management of the SQL Server yet I am far from a SQL admin. (Gimme a router switch or server anyday <G><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ />.<br /><br />Anyway SQL2000. The database is currently 537 meg and it is using 525 of it. The Tlog is 3.5 Gig. I ran shrinkfile from the all tasks on the database and selected the Tlog to shrink. I have done this on other databases to regain disk space when needed (every few months after backing up the Tlog). In this instance the Tlog did not shrink at all and it states that it is using 3.4 gig of the 3.5 gig of space.<br /><br />How can I see what is in this log? Why would the log be larger than the database. I run backups and both regularly. The database is set to grow with no size limits.<br /><br />It seems like a hung transaction may cause this from what I am reading. I mentioned that I read about a method to reduce the size by detaching the database, remove the Tlog and reattach but the developers are afraid to do that (with no explanation as to why).<br /><br />I also felt that we should be in simple mode on this database especially since the data is just tracking of image reads.<br /><br />Any assistance is appreciated. If you need more info tell me what to supply.<br /><br />Oh did I say THANKS??? IF not TIA.<br /><br />Regards<br /><br />Doug
Hi Doug, If you are using Enterprise Manager then in the Shrink dialog, tick the box "Move pages to beginning of file before shrinking" Simple mode is kinder on log file size, but does mean that your recoverability is back to the last database backup and no logs can be applied Cheers
Thanks so much Now it shows the data used to be about 140mb (changing constantly of course). I am having trouble shrinking it though. I ran a Tlog backup again and tried shrinking it about 10 times (it took 5 tries on my 10 gig log on another database) but it is not shrinking. Any ideas what I am missing. To shrink it in EM I right click on the database, all tasks, shrink database. Go to Files and select the Tlog then put in a size of about double the space it is using, 280Mb, and run the shrink. It takes only 1 second now and does nada. Any more ideas. Doug
Hi Doug, and everyone has logged out of the database while you do this?
try running checkpoint in a SQL session against that database, then try again. also try choosing the option "Compress pages and then truncate free space from the file" Cheers
Run from QA
BACKUP LOG db_name WITH TRUNCATE_ONLY
DBCC SRHINKFILE(log_file_name, 10)
You can find the file name running sp_helpdb ‘db_name’. And you can change the 10 to
another value if it suits you better. Bambola.
you said >> and everyone has logged out of the database while you do this?<< Very funny. No EVERY IS NOT Logged out. Is that neccessary???? The WEB site is live so I am sure there are queries going on while I do this. Getting the bosses to listen when we have issues to deal with is my biggest problem that can not be solved on this or any forum. You can imagine what I went through to get these servers behind a firewall. They currentluy have shares on the DB server and have it set to auto logon. It is sick to put it mildly. I don’t know if I am battling hackers or management sometimes<G>.
I GOT IT to shrink. I ran a full backup of the TLOG and then I thought that I should also backup the database. Once I completed the database backup I again went to all tasks and shrink database. Selected to TLOG and this time it shrank instantly. Could be no one was logged in at that second I guess or is it possible the database backup helped something?? Either way the disk space is released. Now I need to fugure out what the source of all their problems is. I will be back….. Doug
The backup will have done a checkpoint too, which allows the log to be freed Cheers
Twan, how is checkpoint related to size of log? I believe checkpoint will write additional entry in log. Gaurav
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Checkpoint forces dirty pages written to the disk and in turn causes Tlog to be freed with uncommitted transactions. FOr instance if anytime Tlog has uncommitted transactions, if you use CHECKPOINT it will clear out the transaction. _________