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 Sql Server properties programatically

I am writing a program in .Net to retrieve properties of Sql Server.
How can I find the following properties without querying the database:

1. Replication enabled
2. Default language
3. Processors and threads running.

Thanks,
verveYou're probably looking for SQLDMO, which is a COM interface to MSSQL -
see Books Online for the details.

1. You can use the SQLDMO Replication object to enumerate replicated
databases, individual publications and articles etc.

2. Use the SQLServer.Language property

3. I'm not sure exactly what you want, but WMI might be a better option
for hardware and OS information. If by "threads" you really mean SQL
Server processes, then you can use the SQLServer.EnumProcesses method

In many cases, SQLDMO executes TSQL commands behind the scenes, so you
still need an authenticated connection to the server (and possibly a
user mapping in individual databases) to use it.

Simon|||Hi,
Thanks for the prompt reply but can also tell me which classes I should
be querying in WMI or which interface?

Thanks in advance :)
Verve|||The full WMI reference is on MSDN, but the TechNet scripting library
might be a faster way to get started:

http://www.microsoft.com/technet/sc...c/hwbavb03.mspx
http://www.microsoft.com/technet/sc...d/pcthvb07.mspx

Simon

How to find key property without using functions or procedure?

I make simple application for view main properties of selected database (using systables). I have to find relation between systables that lets me find property of each key, without using functions or procedures. For example this SQL code:

*******example1

SELECT dbo.sysobjects.name, dbo.sysindexes.name AS IndexName, dbo.syscolumns.name AS ComponentName, dbo.syscolumns.length,
dbo.systypes.name AS Type
FROM dbo.systypes RIGHT OUTER JOIN
dbo.syscolumns ON dbo.systypes.xusertype = dbo.syscolumns.xusertype RIGHT OUTER JOIN
dbo.sysindexkeys INNER JOIN
dbo.sysobjects ON dbo.sysindexkeys.id = dbo.sysobjects.id INNER JOIN
dbo.sysindexes ON dbo.sysindexkeys.indid = dbo.sysindexes.indid AND dbo.sysobjects.id = dbo.sysindexes.id ON
dbo.syscolumns.id = dbo.sysobjects.id AND dbo.syscolumns.colid = dbo.sysindexkeys.colid
WHERE (dbo.sysindexes.name NOT LIKE '_WA_%')
ORDER BY dbo.sysobjects.name, IndexName

*******

make view showing all keys components (columns that each key is made of). It works pretty good. But now I want find properties of each key (I mean is key ascending or descending). I can do it using simple function INDEXKEY_PROPERTY like that :

******example2

SELECT dbo.sysindexes.id, dbo.sysindexes.name AS Indexname, dbo.syscolumns.name AS Components, dbo.syscolumns.length,
dbo.systypes.name AS Type, (CASE WHEN INDEXKEY_PROPERTY(dbo.sysobjects.id, dbo.sysindexes.indid, dbo.sysindexkeys.keyno,
'IsDescending') = 0 THEN 'Ascending' ELSE 'Descending' END) AS SortProperty
FROM dbo.systypes RIGHT OUTER JOIN
dbo.syscolumns ON dbo.systypes.xusertype = dbo.syscolumns.xusertype RIGHT OUTER JOIN
dbo.sysindexkeys INNER JOIN
dbo.sysobjects ON dbo.sysindexkeys.id = dbo.sysobjects.id INNER JOIN
dbo.sysindexes ON dbo.sysindexkeys.indid = dbo.sysindexes.indid AND dbo.sysobjects.id = dbo.sysindexes.id ON
dbo.syscolumns.id = dbo.sysobjects.id AND dbo.syscolumns.colid = dbo.sysindexkeys.colid
WHERE (dbo.sysindexes.name NOT LIKE '_WA_%')
ORDER BY dbo.sysobjects.name, Indexname

*******

This is what I want but I have to do it without using function (like in exp.1) So I need to find good relation between some systables.

Please!!! Somebody help me!

Sorry about my english

I would like to know why you want to go in this route, as it is easy to manage the code with such functions in the release. Using system tables directly is not a good practice.|||

I work in Clarion, so I don't want to use SQL code at all. I just need information where in system tables can I find this, and then I can use Clarion interface to connect with matching table and get all information I need.

I can use SQL code, but it's real hardcore in Clarion. I have been askin' before how to find all components of index/key without functions and everybody suggest, that I should use function, but it's not so easy. I founded, how to do it my way. Now the same problem with key properties. Where is it? If you know something about it please help.

How to filter a table with an "OR" condition

I'd like to set the Filters in the Filters tab of the Table Properties dialog to say:

=Fields!WT_TO.Value > 0 OR

=Fields!WT_TO_PREV.Value > 0

but teh And/Or column is permanently disabled, and its sticking in a default value of AND

what's up with that?

Hello,

I don't think there is a way to change the AND/OR, and the only way to get an OR is to have the exact same expression in your filter.

Try this instead:

Expression: =Fields!WT_TO.Value > 0 or Fields!WT_TO_PREV.Value > 0

Operator: =

Value: =True

Hope this helps.

Jarret

|||your technique works great - i'm surprised they bother with the multiple filters and the and/or column since you can't really use it and all you really need is a single expression. Anyway, thanks!|||

"I don't think there is a way to change the AND/OR, and the only way to get an OR is to have the exact same expression in your filter."

What about changing it directly in the XML? Is that a possibility?

The need for OR is obvious.

Maybe there's a similar solution for my situation--but the OR option is really it.

I need to be able to show some subset of the list of states (from the US) based on the selection from a multivalue parameter, "OR" the TOTALS row.

Suggestions?
|||I figured this out myself, thank you.

OR is only used if the Operator for the filter is an equal (=) sign. Otherwise, it's AND.

They assume that programmers/developers are retards and can't figure out the logic for themselves.

God forbid that I should have multiple, dynamic options to compare--at the same time that a static value is also a possibility.
|||OMG that sucks. still, lotta good stuff is SSRS