Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2002
    Posts
    13

    Angry Unanswered: Help shrinking database doesn't 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. What I'm I doing wrong?

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

    GO

    Please, Help Me

  2. #2
    Join Date
    Aug 2002
    Location
    india
    Posts
    41
    try 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

  3. #3
    Join Date
    Aug 2002
    Posts
    2

    Re: Help shrinking database doesn't work..

    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. What I'm I doing wrong?

    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..

  4. #4
    Join Date
    Aug 2002
    Location
    india
    Posts
    41

    Re: Help shrinking database doesn't work..

    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. What I'm I doing wrong?

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

    GO

    Please, Help Me

  5. #5
    Join Date
    Aug 2002
    Location
    india
    Posts
    41

    Re: Help shrinking database doesn't work..

    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.

  6. #6
    Join Date
    Aug 2002
    Posts
    13
    <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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •