Should I shrink my DB to free space before EXPANDING IT?

[mb22]

I have a 90mg database .... and it is showing 82mg used. I really don't think 82mg has been used ... I suspect there may be free space which is not available .... as I have deleted some large tables ... and I read somewhere in this forum .. it is better to truncate the table instead!

Anyway I want to expand the DB to about 150mg or 200mg... as the number of possible users is about to increase dramatically! Do I need to shrink the DB first, ... before expanding it ... to clear any unavailable areas? kinda like a "defrag" ... or is that not necessary?

Any suggestions will greatly help

 

[JayKusch]

You do not "really have" to. BUT ... Here is what I would suggest if you have some downtime w/ a quiet DB and system.

Go ahead and Shrink the DB by going into Enterprise Manager and drilling down to the target DB. From there, right-mouse click and choose "All Tasks".."Shrink Database".

This will show you how much free room is really avaiable. I have seen it take several minutes to a 1/2 hour for the file to full shrink and give up the free room to the OS. So keep refreshing the stats and checking. Just make sure the free room has been release back to the operating system.

Go ahead and Defrag the disc the database files live on.
This will provide SQL Server a very contiguous disk to expand the database(s) on and will reduce the thrash of disk reads had you just grown the DB on a possibly fragmented file system.

If possible, split your DB file(s)(MDF files) on one disk and your transaction log file(s)(LDF files) on another, You will see great speed benifits from this.

Then go ahead and expand your DB file out to as large as you possibly can make it. This will help in negating any issues of the DB locking up when it has to expand the physical size of the DB. This is called adding an extent.

It is always best practice to expand out your DB and TLogs to the largest size you can get away with.

Thanks

J. Kusch

 

[mb22]

If possible, split your DB file(s)(MDF files) on one disk and your transaction log file(s)(LDF files) on another, You will see great speed benifits from this.

i have both .MDF and .LDF on the same d: drive. I am having trouble moving the log .LDF to the C: drive. It will not allow me to change the location of transaction file ... or delete the transaction file.

What steps do I need to take to separate the 2 files onto d: and C; drives?

 

[JayKusch]

First off ... you may want to re-evaluate putting the TLog on the C: (system disc). It has more of a change to grow wildly and fill an entire disc before you know it. One rouge stored procedure or batch insert can cause this anomoly and thus crash your system due to the fact that no room exists on your system disc.

If you have to, put the MDF file on the C: drive and the TLog on the D:

Now make sure no one is attached to the DB and you have a quite system.

OK ... go into enterprise manager(EM), drill down to the DB we want to make the change to and right-click on it. Now choose "All Task".."Detach Database".

Once the DB is detached, move the MDF to its new home on the C: drive.

Go back into EM and on the Databases tab, right-click and choose "All Task".."Attach Database".

Point the options box to the new location where the MDF live and click verify. It may squack about the TLog not being in the right place so you need to change the path to the correct path where the TLog still lives.

You should be good to go after clicking OK.

Thanks

J. Kusch

 

[mb22]

thank you. it worked!

last question: yesterday after shrinking and expanding my DB ... later today .. my users complained later in the day that the DB had slowed down considerably. So I decided perhaps I need to re-index tonight... after checking out a few forum questions.

Anyway I have .. all my drop and create index statements. Problem is .... they are 3 important tables that each have an identity column ... as the primary key I use in my queries, but I do not have any index created on the identity columns. Since they are identity columns (autoincrement) I don't think they need to be indexed. Am I wrong? Will it rather help to index the identity columns ..... because I use it all the time in my select and update queries.