compact sql server database

Question

compact sql server database

Frances L

SSCarpal Tunnel

Points: 4727
More actions
December 26, 2006 at 8:50 am

#170798

What is the equivalent procedure for SQL server 2000 like MS ACCESS compact the database ? Thx.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 1

Sql databases do not need compacting like Access databases, because they are not flat file db's. That being said, I'm assuming you're having a space issue with your database. Either you deleted a bunch of data from your database and you want to release that space to the OS, or your T-log has grown to big and you would like to shrink it and release that space to the OS. In either situation, you can use dbcc shrinkdatabase (dbname). This shrinks the physical size of the file and releases what it cleans up back to the OS. Make sure on the T-log you've either backed it up or truncated it in order for it to release to the OS.

Tom

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 2

I do not know why any activities in the sql server is slow. connected to the sql enterprise manager. run the query. open the table, design the table...

Thx.

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 3

Are you having a performance problem with the database?

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 4

Yes. but the problem with other database, too. I am defragment the pc right now.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

Is this SQL Server 2000, Standard or Enterprise?

Please let us know which version and then what the issue is. If this is a performance issue, has it always been this way? What is the size of the host Windows system and what activity is taking place (# users, load, etc).

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 6

it is standard version. I am the only user in the computer. it used to work fine. Last week I imported a lot data into the database. it clogged the whole server. The computer run window 2000 professional version. it would be only activity it has right now. it is pentium pc. I rebooted the pc a couple. it did not work. I could not view the other info in the pc right now.

I do not know What is the size of the host Windows system ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

I know you're not counting this, but congrats on the 8000th post .

Rafal Czyzewski Newbie Points: 7 More actions · Answer 8

- take care of your transaction log, instructions here:

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

- rebuild or reorganize indexes (Management Studio)

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 9

It took long time to do any operation in the SQL Enterprise manager.

Btw, my computer 4cpu 1.8 ghz 523.28 kb Ram.

Here is the message after I shrink the database.

I do not know why it said log file have all logical log files are in use.

DBCC SHRINKDATABASE (MedStats, 20)

Cannot shrink log file 2 (MedStats_Log) because all logical log files are in use.

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages

------ ------ ----------- ----------- ----------- --------------

14 1 27440 128 22104 22104

14 2 114544 128 114544 128

(2 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHRINKDATABASE (MedStats, 30)

if I run Cannot shrink log file 2 (MedStats_Log) because all logical log files are in use.

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages

------ ------ ----------- ----------- ----------- --------------

14 2 115976 128 115976 128

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Here is the property inof about the database:

size:1120.44 mb space available is 41.81 mb.

I went to window explore: it still 219.520 kb for mdf file. 927.808 for the log file after I shrink the database.