| Author |
Topic |
|
jung1975
Aged Yak Warrior
503 Posts |
 Posted - 2006-10-06 : 00:08:40
|
| Can you take a look at the below script which is for shrinking the log file. Please let me know if everything looks ok. Anything i need to aware of it?USE dbnamegoDBCC shrinkfile(1,notruncate)DBCC shrinkfile(2,truncateonly)IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 't1' AND XTYPE = 'u') DROP TABLE t1CREATE TABLE t1 (char1 char(4000))goDECLARE @i intSELECT @i = 0WHILE (1=1)BEGINWHILE (@i<100)BEGININSERT INTO t1 values('a')SELECT @i = @i+1ENDTRUNCATE TABLE t1USE masterBACKUP LOG dbname WITH TRUNCATE_ONLYUSE dbnameEND--dbcc loginfo --dbcc sqlperf(logspace) |
|
|
Kristen
Test
22859 Posts |
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-10-06 : 00:24:54
|
| you meant don;t run DBCC shrinkfile(1,notruncate)?I've alrady ran this script and only log file size got shrinked?the data file size is the same...Do i have to worry about it? |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-06 : 01:12:32
|
| No point shrinking the file if its only going to grow back again tomorrow, it just gets fragmented and takes time to re-extend, which on a busy server can lead to timeouts.There are exceptions of course - like you just did a one-off exceptional delete of 20+% of the database data - but they are quite few.More details in the link I posted.Kristen |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-10-06 : 01:12:53
|
| "Don't shrink your database. Shrink causes index fragmentation - I've specifically documented this in the BOL for SQL Server 2005."In this case, rebuilding index will fix this problem?I won;t shrink the database file again~~~ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-06 : 01:17:21
|
| "In this case, rebuilding index will fix this problem?"Nope. Defragging the physical file will, but no point until it has grown back to "equilibrium size" again.Kristen |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-10-06 : 01:35:21
|
| Thanks but i am quite sure what you mean by this?"There are exceptions of course - like you just did a one-off exceptional delete of 20+% of the database data - but they are quite few." Did i actually delete the physical data ?????? |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-10-06 : 02:16:33
|
| One more thing, I only ran for 10 min and cancel the query... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-06 : 03:27:26
|
| "Thanks but i am quite sure what you mean by this?"There are exceptions to the rule about not shrinking the database ...Did you read the link I posted yet?Kristen |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-10-06 : 10:55:06
|
| Thank you you are right. it grow back again today ( the same size). I really need to find some free spaces becasue we are out of disk space... we can not add the physical spaces at the moment...What if i detach the db and attach it back..will it create a new log file start from 2mb? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-06 : 11:16:28
|
| Whatever you do its going to be the same size tomorrow I reckon!Kristen |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-10-06 : 11:53:10
|
| "Whatever you do its going to be the same size tomorrow I reckon!"So there is no solution for this? anthing i can do at this point? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-06 : 12:25:42
|
| Delete stale data to reduce the size of the database, THEN shrink it (just the once!)Increase the frequency of the TLog backups so that the LDF file does not grow so much - but if the LDF file is < 120% of the MDF file this will be pointless - in very general terms. No point doing this if you are using RECOVERY MODEL = SIMPLEInstall bigger disks.Scavenge your disk for obsolete junk you can delete.Sounds to me like your database has grown to the size it has. It would be product to monitor the size of such files so you have a chance to predict when they will become uncomfortably big for the machine/disks they are on. That includes monitoring CPU activity too really.EDIT: If you are NOT doing routine maintenance then Reindexing may reclaim some inefficiently used space. However, in the short term it will make the files bigger and probably need a dramatic amount of Log space (but you can SHRINK after that to get that back again - its not a good policy though as you are just fir fighting, but on the off chance that there has been NO routine maintenance this may help.If your database is NOT set to Recovery Model = SIMPLE and you are NOT making TLog backups then that will be having a major MAJOR effect on things. In that case your LDF file will probably be MUCH bigger than your MDF file.Kristen |
|
|
|
|
Shrink log file scrip