release database unused space

Hi,
I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile & DBCC shrinkdatabase to have the database release the space. I see no reduction in the database size.
I have run the following;
DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
GO
DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
GO
BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
GO
DBCC SHRICKDATABASE (VIRTEN, 20)
GO
Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
GO
Please can anyone tell me, where I am making a mistake, or provide me with another method to release the space.
Many thanks.
DanielPerhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC INDEXDEFRAG.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Daniel" <danieljantony@.hotmail.com> wrote in message
news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> Hi,
> I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile & DBCC shrinkdatabase to
have the database release the space. I see no reduction in the database size.
> I have run the following;
> DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> GO
> DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> GO
> BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> GO
> DBCC SHRICKDATABASE (VIRTEN, 20)
> GO
> Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> GO
> Please can anyone tell me, where I am making a mistake, or provide me with another method to release the
space.
> Many thanks.
> Daniel|||What Tibor is alluding to is that fact that just because you deleted a bunch
of records, it doesn't mean that pages have been freed. If the pattern of
your deletes is such that only one record per page is deleted, no pages will
be deallocated and so no space can be reclaimed by shrink.
You can compact such pages using DBCC DBREINDEX or DBCC INDEXDEFRAG (if you
have a clustered index). There's no such mechanism for heaps except bcp
out/in.
You should also be aware that if you shrink the database it will most likely
grow again if there is any insert/update activity on it.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evs41PvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Perhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC
INDEXDEFRAG.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Daniel" <danieljantony@.hotmail.com> wrote in message
> news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> > Hi,
> >
> > I have deleted 100K records from a table in a database. I ran the DBCC
shrinkfile & DBCC shrinkdatabase to
> have the database release the space. I see no reduction in the database
size.
> >
> > I have run the following;
> > DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> > GO
> > DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> > GO
> > BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> > GO
> > DBCC SHRICKDATABASE (VIRTEN, 20)
> > GO
> > Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> > GO
> >
> > Please can anyone tell me, where I am making a mistake, or provide me
with another method to release the
> space.
> >
> > Many thanks.
> >
> > Daniel
>

release database unused space

Hi,
I have deleted 100K records from a table in a database. I ran the DBCC shrin
kfile & DBCC shrinkdatabase to have the database release the space. I see no
reduction in the database size.
I have run the following;
DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
GO
DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
GO
BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
GO
DBCC SHRICKDATABASE (VIRTEN, 20)
GO
Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
GO
Please can anyone tell me, where I am making a mistake, or provide me with a
nother method to release the space.
Many thanks.
DanielPerhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC INDEXD
EFRAG.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Daniel" <danieljantony@.hotmail.com> wrote in message
news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> Hi,
> I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile
& DBCC shrinkdatabase to
have the database release the space. I see no reduction in the database size.">
> I have run the following;
> DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> GO
> DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> GO
> BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> GO
> DBCC SHRICKDATABASE (VIRTEN, 20)
> GO
> Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> GO
> Please can anyone tell me, where I am making a mistake, or provide me with another
method to release the
space.
> Many thanks.
> Daniel|||What Tibor is alluding to is that fact that just because you deleted a bunch
of records, it doesn't mean that pages have been freed. If the pattern of
your deletes is such that only one record per page is deleted, no pages will
be deallocated and so no space can be reclaimed by shrink.
You can compact such pages using DBCC DBREINDEX or DBCC INDEXDEFRAG (if you
have a clustered index). There's no such mechanism for heaps except bcp
out/in.
You should also be aware that if you shrink the database it will most likely
grow again if there is any insert/update activity on it.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evs41PvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Perhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC
INDEXDEFRAG.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Daniel" <danieljantony@.hotmail.com> wrote in message
> news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
shrinkfile & DBCC shrinkdatabase to[vbcol=seagreen]
> have the database release the space. I see no reduction in the database
size.
with another method to release the[vbcol=seagreen]
> space.
>sql

Release Database Transaction Log Used Space After Database Backup

What are the SQL Server commands that will shrink or release database
transaction log used disk space afer the database backup has completed?
Thank You,These two things are not related. A database backup will not empty the log.
A log backup will,
however. To then shrink the log file, use DBCC SHRINKFILE. But read
http://www.karaszi.com/SQLServer/info_dont_shrink.asp first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:85D71BCF-F22C-4035-8A02-16771C713B58@.microsoft.com...
> What are the SQL Server commands that will shrink or release database
> transaction log used disk space afer the database backup has completed?
> Thank You,

Release Database Transaction Log Used Space After Database Backup

What are the SQL Server commands that will shrink or release database
transaction log used disk space afer the database backup has completed?
Thank You,These two things are not related. A database backup will not empty the log. A log backup will,
however. To then shrink the log file, use DBCC SHRINKFILE. But read
http://www.karaszi.com/SQLServer/info_dont_shrink.asp first.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:85D71BCF-F22C-4035-8A02-16771C713B58@.microsoft.com...
> What are the SQL Server commands that will shrink or release database
> transaction log used disk space afer the database backup has completed?
> Thank You,

Release database lock

Actually I'd like to know how I got the lock and then you can tell me how to release it. Here's the problem (this is sql server 2000 with visual studio 2005)

I have a windows service that wakes up every 5 minutes and does some database work (reads rows from a table, process them, inserts them into another table). When the inserts are complete, the process should (I would think) release the database lock, but it doesn't. The database is accessed via a dataset object and that object is disposed of via the using/end using statement. Is there something I have to put in the connection string to avoid this behavior?

Thanks,

Bob

If a connection is open from the client it will maintain an S lock on the database. This is true even if the client is doing no work. The purpose of the lock is to prevent certain operations that might change the state of the database (like say dropping it) while there are connections relying on the current state.

Is this causing you some problem?

|||I just don't understand how the connection could possibly still be open. I would assume that by using VS 2005's generated dataset code, that disposing of a dataset object would close the connection. I certainly don't want the connection open while the process sleeps for 5 minutes (or more) at a time.|||

If you are using SqlClient (and it sounds like you are) it could very well be taking advantage of connection pooling.

You can tell for certain by using profiler to monitor the Audit Login/logout events.

|||

Well this statement is no doubt in the code:

imports System.Data.SqlClient

Is that what you mean? If so, can I close the connection manually?

|||

I figured it out shortly after my last post. I added this to my connection string:

pooling=false;timeout=45;

seems to work just fine.

Release A Install Problems

We have packaged MSDE 2000 release A with our applicaiton. A user ran the
install, but when MSDE goes to start it says, MSDE is either currport or has
been tampered with, please uninstall and reinstall MSDE, invalid package ID.
We go to unstill MSDE and it says MSDE is not installed... We tried
reinstalling, since it thought it wasn't installed and when the installer
starts, we get the same invaled package id error... Does anyone have any
ideas, or thoughts on what is happening or what I can do to fix this? Thanks,
Brian
Did you define a strong password for SA using the SAPWD parameter? MSDE SP3A
setup.exe will not install a new instance without it.
joe.
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:C8D287B9-BCD6-4423-AF48-21455901D596@.microsoft.com...
> We have packaged MSDE 2000 release A with our applicaiton. A user ran the
> install, but when MSDE goes to start it says, MSDE is either currport or
> has
> been tampered with, please uninstall and reinstall MSDE, invalid package
> ID.
> We go to unstill MSDE and it says MSDE is not installed... We tried
> reinstalling, since it thought it wasn't installed and when the installer
> starts, we get the same invaled package id error... Does anyone have any
> ideas, or thoughts on what is happening or what I can do to fix this?
> Thanks,
> Brian
>

Relay Error Using Database Mail

SQL 2005 SP1
Windows Server 2003 SP1
Exchange 2000
I am getting this error sending a Maintenance Plans report to an email
recipient. It only happens sending to an external email address.
The mail could not be sent to the recipients because of the mail server
failure. (Sending Mail using Account 2 (2007-02-06T08:01:25). Exception
Message: Cannot send mails to mail server. (Mailbox unavailable. The server
response was: 5.7.1 Unable to relay for myaddress@.domain.com).
)
This is an issue correct-able at your SMTP server; it has nothing to do with
database mail (unless you have a more open internal SMTP server available,
that you could use instead).
A
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:A35C82BD-00DA-414F-8071-2DD5F11EF010@.microsoft.com...
> SQL 2005 SP1
> Windows Server 2003 SP1
> Exchange 2000
> I am getting this error sending a Maintenance Plans report to an email
> recipient. It only happens sending to an external email address.
> The mail could not be sent to the recipients because of the mail server
> failure. (Sending Mail using Account 2 (2007-02-06T08:01:25). Exception
> Message: Cannot send mails to mail server. (Mailbox unavailable. The
> server
> response was: 5.7.1 Unable to relay for myaddress@.domain.com).
> )
|||Thanks for the reply. So what do I have to do on Exchange to correct this?
|||Well, you could open up external SMTP relay from the IP address of your SQL
Server(s). I am not an Exchange administrator. So for more specific
details, if you don't get a quick enough response here, you'll ghave better
luck in an Exchange newsgroup.
If you have a simple SMTP server running on an IIS box somewhere inside your
network, that you can use for this, I can help with specific instructions
there. Database Mail does not require Exchange, and depending on your
requirements, you may not want to mix the two.
A
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:D989979C-3692-4C0D-9BA9-87523863ED45@.microsoft.com...
> Thanks for the reply. So what do I have to do on Exchange to correct
> this?
sql