Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
askmahesh
Starting Member
1 Post |
 Posted - 2007-11-26 : 11:17:12
|
Hi everybody,I have a unique problem. I monitor about 29 instances of production SQL server spread out entire North America.I have a maintenance plan that runs every alternate day that does following, -- Backup all user database -- re-index -- update statistics -- shrink database keeping 10% spaceI was under impression this will be enough. There is a disk space problem that keeps surfacing again and again. I ran a few DBCC commands like 'DBCC sqlperf (logspace) /DBCC SHOWFILESTATS'. I found the following table as a result. Apparently the db-shrink don't work always and we have a lot of empty space sometime as big as 99% and spanning 1-18 GB. So far I tried few commands and only 'db shrink file' to a specific size is the only command that works.I want to automate this as I can't manually shrink all the files. I will greatly appreciate any ideas in this regardsThanks everyone.
dbname DATA_SIZE DATA_USED logsize logspaceused ----------- ----------- ----------- ----------- ------------ master 4 2 0 0 tempdb 406 6 19 8 model 1 1 10 10 msdb 12 8 14 4 DDb1 8895 8449 6582 184 DDb2 3929 1006 1801 27 DDb3 3929 120 502 0 DDb4 3929 165 502 0 DDb5 1245 63 297 131 DDb6 3929 62 502 0 DDb7 1244 149 33600 30556 DDb8 3929 2 502 0 DDb9 230 3 2861 2602 DDb10 550 4 11248 10227 DDb11 1244 38 8317 33 DDb12 1244 93 8317 929 DDb13 1244 8 717 656 DDb14 550 4 199 29 DDb15 2500 19 340 318 DDb16 6127 1 1323 9 DDb17 12907 3 5373 13 DDb18 230 4 827 756 DDb19 2500 57 1341 1224 DDb20 2500 31 1086 992 DDb21 2500 17 492 14 DDb22 230 3 591 19 DDb23 1244 33 9036 32 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-26 : 11:20:27
|
| shrink database effectivly negates your index rebuild..._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-27 : 02:30:32
|
| You have to backup log if db is not in simple recovery model. |
|
|
|
|
|
|
Reclaiming database space in SQL 2005