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 |
|
adlo
Posting Yak Master
108 Posts |
 Posted - 2007-03-22 : 14:11:06
|
| Hi,Users were experiencing performance problems with a production database so I first shrunk the database shrinking with truncating space from the end of the file but that didn't free up enough space. So I selected shrink database files individualy and saw the amount of free space was 27 GB for the mdf and the minimum space allowed is 20Gb. I typed in 24GB (which would leave 4Gb free space) and shrunk the file. Now when clicking on the database properties it shows space available is size is 24Gb. Performance of that database is still an issue.My question:1) did I worsen the problem2) Is shrinking a file individually to a smaller size a good thing when its significanlty bigger than the minimum size?3) Is 0 mb space available a bad thingthanks |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-03-22 : 15:43:40
|
| 1) Probably2) Not necessarily3) Depends on whether you will need that space later on.How did you arrive at the idea that shrinking the database would solve a performance problem, anyway?What should be done, is first identify the bottleneck. Is your SQL Server Disk Bound? CPU Bound? Memory Bound? If it is none of these things, what is the front end application doing? Break out perfmon, and start comparing this box to a box that users are not complaining about. |
 |
|
|
adlo
Posting Yak Master
108 Posts |
Posted - 2007-03-22 : 22:34:31
|
| Lots of rows were deleted from a table and I wanted to decrease the database to a smaller size. Company policy is also to make backups during the day(company policy before certain users update the DB) so I wanted to minimize the amount of time it took to take the backups but still have a alot of free space left. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-23 : 07:32:48
|
| "I wanted to minimize the amount of time it took to take the backups"Backup will only "extract" the used part of the file, so having a large file with lots of empty space won't effect the backup filesize.You should check that your scheduled database maintenance includes REINDEX or INDEXDEFRAG, and UPDATEs STATISTICS. That is probably where your performance problem lies.Also, the cached query plans may be out of date if you have deleted lots of data, so stop-starting the SQL Server (which will flush the cache and recompile all SProc etc.) might help (there are ways of doing this without restarting SQL Server if your server needs to be 24/7)In general you should not Shrink a database unless you know that the file will not grow back - if you Shrink, and then it expands through normal use, all that happens is that the files become more fragemented.Next time you have some scheduled downtime you might look at defragging the physical MDF and LDF files (e.g. using CONTIG.EXE from Sysinternals). SQL Service will have to be "stopped" for you to be able to do that (or the database detached)Kristen |
|
|
|
john.burns
Posting Yak Master
100 Posts |
Posted - 2007-03-23 : 14:38:38
|
| I thought contig.exe could be run while system is up? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-24 : 01:56:11
|
You may well be right, John, but I never thought to try! (and now I think about it I think I will still be more comfortable doing it in a maintenance window, backed up, etc.  )Kristen |
|
|
|
|
|
|
|
|
Did I shrink the database correctly (File shrink)?