Help shrinking database doesnt work..

On ower server (ms sql 2000) we have 5 databases who have to much unused space allocated. I all ready tried with the following script to shrink the database and release the allocated unused space to the Operating System. But it doesn't work. :confused: What I'm I doing wrong? :mad:

backup log [public] with no_log
DBCC shrinkdatabase ( [public],0,truncateonly)
dump transaction [public] with no_log

GO

Please, Help Metry with this command on query analyzer
I hope this will solve ur problem

to get more explanation on this pl go through the help menu.

EXEC sp_dboption 'databasename', 'trunc. log on chkpt.', 'TRUE'

pl inform wheather it is working or not|||Originally posted by natas
On ower server (ms sql 2000) we have 5 databases who have to much unused space allocated. I all ready tried with the following script to shrink the database and release the allocated unused space to the Operating System. But it doesn't work. :confused: What I'm I doing wrong? :mad:

backup log [public] with no_log
DBCC shrinkdatabase ( [public],0,truncateonly)
dump transaction [public] with no_log

GO

Please, Help Me

I do not know whether your log file or data file is the problem. usually if the data file is larger than what you require ..
a checkdb followed by shrinkdatabase should take care of it..

if your log file is the problem then try shrinkfile instead of shrinkDB.. moreover make sure that you have no open transactions.. or if you are replicating your distributor is working.. before you try this.. even a lot of views ,being constantly used can cause the shrink to fail..

wish you luck..|||Originally posted by natas
On ower server (ms sql 2000) we have 5 databases who have to much unused space allocated. I all ready tried with the following script to shrink the database and release the allocated unused space to the Operating System. But it doesn't work. :confused: What I'm I doing wrong? :mad:

backup log [public] with no_log
DBCC shrinkdatabase ( [public],0,truncateonly)
dump transaction [public] with no_log

GO

Please, Help Me|||check with ur tempdb size if it is comparativly larger in size right click select shrinkfile both (temp log and tempdata ) select compress pages and then truncate free space from the file.

have u observed with any temporary cursors tables etc u have created for manipulation and forgot to close it.

have u selected autoshrink option .

if possible observe with the table's if index is corupted or fragmented to check this u can use dbcc showcontig option and observe scan density%.

if it is not nearing 100% u can run dbcc indexfrag for de-fragmenting the table this is related with tr log file.

reply me the result.|||<quote>
by vishy
I do not know whether your log file or data file is the problem. usually if the data file is larger than what you require ..
a checkdb followed by shrinkdatabase should take care of it..

if your log file is the problem then try shrinkfile instead of shrinkDB.. moreover make sure that you have no open transactions.. or if you are replicating your distributor is working.. before you try this.. even a lot of views ,being constantly used can cause the shrink to fail..

wish you luck..
</quote>

The problem is the data file i wil try out you're solution. Thnks.

I will reply soon.