How to force DB shrink?

I have DB 13GB in size. After we deleted some old tables
from it it's still using 13GB on hard drive. In properties
it was always set up "Auto shrink", but after a week it's
still 13GB... :(
How to force shrinking manual?Try running DBCC OPENTRANS to see if you have any long running open
transactions preventing the SHRINK.
Also have a look at DBCC SHRINKFILE in BOL
HTH
Ryan Waight, MCDBA, MCSE
"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> I have DB 13GB in size. After we deleted some old tables
> from it it's still using 13GB on hard drive. In properties
> it was always set up "Auto shrink", but after a week it's
> still 13GB... :(
> How to force shrinking manual?
>|||Is it data or log file which is big? If log, check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> I have DB 13GB in size. After we deleted some old tables
> from it it's still using 13GB on hard drive. In properties
> it was always set up "Auto shrink", but after a week it's
> still 13GB... :(
> How to force shrinking manual?
>|||If you are trying to shrink the data file then you can use DBCC SHRINKFILE.
You will be unable to shrink the data file if there are allocated extents at
the end. You can move indexes and tables with clustered indexes by doing a
DBCC DBREINDEX. If the extents are from a table with non clustered index or
text/image data, then you have more of an issue. Unfortunately these do not
move with the DBCC DBREINDEX command. You will have to create a new table
and copy the data back in to physically move the extents. A better option
is to create a new file group for your non clustered tables and text/image
data and move it there.
To find the offending tables and indexes download the free tool below.
Select from the tool bar Table/Index View. This will show you a list of
tables and indexes and where they are allocated in the data file. Scroll to
the end of the data file click on an allocated extent (green). This will
highlight the index that is allocated to the extent. You can then run DBCC
DBREINDEX on that index.
Hope this helps
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> I have DB 13GB in size. After we deleted some old tables
> from it it's still using 13GB on hard drive. In properties
> it was always set up "Auto shrink", but after a week it's
> still 13GB... :(
> How to force shrinking manual?
>|||"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> I have DB 13GB in size. After we deleted some old tables
> from it it's still using 13GB on hard drive. In properties
> it was always set up "Auto shrink", but after a week it's
> still 13GB... :(
> How to force shrinking manual?
>
Autoshrink shrinks a database if it has 25% space in it. If the table took
up less space than that , then you have to manually shrink it
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003|||This is the main question! How manually shrink DB?!
>--Original Message--
>"Alexandre Rybalov" <alex@.covers.com> wrote in message
>news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
>> I have DB 13GB in size. After we deleted some old tables
>> from it it's still using 13GB on hard drive. In
properties
>> it was always set up "Auto shrink", but after a week
it's
>> still 13GB... :(
>> How to force shrinking manual?
>Autoshrink shrinks a database if it has 25% space in it.
If the table took
>up less space than that , then you have to manually
shrink it
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.518 / Virus Database: 316 - Release Date:
11/09/2003
>
>.
>|||Alexandre,
You can use DBCC SHRINKFILE for data files also.If it still doesnt shrink,
check for data fragmentation using DBCC SHOWCONTIG.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Alexandre Rybalov" <alex@.covers.com> wrote in message
news:52fe01c37d30$3ab07190$a501280a@.phx.gbl...
> This is the main question! How manually shrink DB?!
> >--Original Message--
> >"Alexandre Rybalov" <alex@.covers.com> wrote in message
> >news:04e701c37c4a$904aa120$a001280a@.phx.gbl...
> >> I have DB 13GB in size. After we deleted some old tables
> >> from it it's still using 13GB on hard drive. In
> properties
> >> it was always set up "Auto shrink", but after a week
> it's
> >> still 13GB... :(
> >>
> >> How to force shrinking manual?
> >>
> >Autoshrink shrinks a database if it has 25% space in it.
> If the table took
> >up less space than that , then you have to manually
> shrink it
> >
> >
> >--
> >Outgoing mail is certified Virus Free.
> >Checked by AVG anti-virus system (http://www.grisoft.com).
> >Version: 6.0.518 / Virus Database: 316 - Release Date:
> 11/09/2003
> >
> >
> >.
> >

How to find out which physical node the virtual server is using

Wing,
The clustered SQL depends on the hard drive you have
installed it on. So whichever cluster owns the hard drive
at the momenent- that is the node Cluster is using.
Also, you can check it through the Cluster Administrator
utility.
Log on to OS of Cluster A or B (does not matter) and start
the Cluster Administrator. You will see your Cluster A
and B servers on the left. Click + to expand teh view and
choose Active Groups.
If you see your SQL cluster group under the Active Groups
of Cluster A- then virtual server is using Clsuter A. Same
goes for Cksuter B.
Hope this helps,
Nerses

>--Original Message--
>Hi,
>I have a question regarding how to find out which
physical node the
>virtual server is using.
>Currently my SQL Server 2000 setup is as follows:
>Physical Nodes:
>--
>ClusterA
>ClusterB
>Virtual Server:
>--
>SQLCLUST
>I know if I use Cluster Administrator or cluster.exe
command line
>tool, I can find out which node the resource is using but
is there a
>stored procedure from the database i can use to find out
which node
>it's using?
>Thank you in advance.
>Wing
>.
>
You can use xp_cmdshell to run cluster.exe (command line cluster administrator)
To see the parameters for cluster.exe type
cluster/?
Search Microsoft Knowledge Base articles for examples with cluster.exe
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

How to find Free disk space available from SQL query

Hi all,
I have to find free disc space in a drive from SQL Query and i am using MSSQL200. Does any body know the command for the same.

Also if i want to find hard disk space of a different machine on network, it is possible to get the data??

Also can i get the CPU/Memory usage data of our machine by some SQL/C++ commands??

Please let me know if u know any of the answer...

thanks
Alokxp_fixeddrives for free disk space...looking for others...