can not delete tempdb

Hi,
after a huge ODBC operation to read some data from a DB table, my tempdb
is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
right mouse click and delete does not work, dbcc checks does not work. I
even stopped the service and started it again, but tempdb did not
regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
log file but after I started the MSSQLSERVER service, they are all
recovered as 2.8 GB.
How can I get rid of this tempdb?
regards,
atxTempdb can not be dropped. In fact, your concern is just to reduce size of
the tempdb. You can use DBCC Shrinkdatabase or DBCC Shrinkfile for that.
"atx" wrote:

> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx
>|||Look at trying "Method 1 to Shrink Tempdb" in the KB article below
http://support.microsoft.com/?id=307487
"atx" <atx@.xerox.com> wrote in message
news:uBQHOwC9FHA.2832@.TK2MSFTNGP14.phx.gbl...
> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx

can not delete tempdb

Hi,
after a huge ODBC operation to read some data from a DB table, my tempdb
is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
right mouse click and delete does not work, dbcc checks does not work. I
even stopped the service and started it again, but tempdb did not
regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
log file but after I started the MSSQLSERVER service, they are all
recovered as 2.8 GB.
How can I get rid of this tempdb?
regards,
atxTempdb can not be dropped. In fact, your concern is just to reduce size of
the tempdb. You can use DBCC Shrinkdatabase or DBCC Shrinkfile for that.
"atx" wrote:
> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx
>|||Look at trying "Method 1 to Shrink Tempdb" in the KB article below
http://support.microsoft.com/?id=307487
"atx" <atx@.xerox.com> wrote in message
news:uBQHOwC9FHA.2832@.TK2MSFTNGP14.phx.gbl...
> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx

can not delete tempdb

Hi,
after a huge ODBC operation to read some data from a DB table, my tempdb
is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
right mouse click and delete does not work, dbcc checks does not work. I
even stopped the service and started it again, but tempdb did not
regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
log file but after I started the MSSQLSERVER service, they are all
recovered as 2.8 GB.
How can I get rid of this tempdb?
regards,
atx
Tempdb can not be dropped. In fact, your concern is just to reduce size of
the tempdb. You can use DBCC Shrinkdatabase or DBCC Shrinkfile for that.
"atx" wrote:

> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx
>
|||Look at trying "Method 1 to Shrink Tempdb" in the KB article below
http://support.microsoft.com/?id=307487
"atx" <atx@.xerox.com> wrote in message
news:uBQHOwC9FHA.2832@.TK2MSFTNGP14.phx.gbl...
> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx

can i/should i worry about fragmentation in catalog files?

Hello all,
I have an application that grew to 1.5 million records and saw poor
performance for a variety of reasons, but one huge 'doh' reason was that I
didn't think to pre-allocate the database and the poor database file was
fragmented as it grew from its default size in increments When we moved to a
new server I pre-allocated the file and made sure it was contiguous.
Is there a correllary with full-text indexing? Are there full-text catalog
files I can or should be creating as contiguous files, or should i be
running defrag on them?
thanks,
john
John,
Yes, there is. It is call "Master Merge" and smaller index files (shadow
files) are merged together at Midnight (controlled via registry key). While
this is not directly controllable in SQL Server 2000, can be managed via the
following TSQL code in SQL Server 2005:
Reorganize the full-text catalog using ALTER FULLTEXT CATALOG REORGANIZE. It
is important to do this before performance testing because it results in a
master merge of the full-text indexes in that catalog.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:%23lsVUjy0FHA.612@.TK2MSFTNGP10.phx.gbl...
> Hello all,
> I have an application that grew to 1.5 million records and saw poor
> performance for a variety of reasons, but one huge 'doh' reason was that I
> didn't think to pre-allocate the database and the poor database file was
> fragmented as it grew from its default size in increments When we moved to
> a
> new server I pre-allocated the file and made sure it was contiguous.
> Is there a correllary with full-text indexing? Are there full-text catalog
> files I can or should be creating as contiguous files, or should i be
> running defrag on them?
> thanks,
> john
>
|||Thank you for your response. Unfortunately this is SQL Server 2000. Does it
make enough of a difference that its worth defragging the individual catalog
files with something like www.defragmentor.com?
john
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23A7C4vy0FHA.560@.TK2MSFTNGP12.phx.gbl...
> John,
> Yes, there is. It is call "Master Merge" and smaller index files (shadow
> files) are merged together at Midnight (controlled via registry key).
While
> this is not directly controllable in SQL Server 2000, can be managed via
the
> following TSQL code in SQL Server 2005:
> Reorganize the full-text catalog using ALTER FULLTEXT CATALOG REORGANIZE.
It[vbcol=seagreen]
> is important to do this before performance testing because it results in a
> master merge of the full-text indexes in that catalog.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "John Mott" <johnmott59@.hotmail.com> wrote in message
> news:%23lsVUjy0FHA.612@.TK2MSFTNGP10.phx.gbl...
I[vbcol=seagreen]
to[vbcol=seagreen]
catalog
>
|||You're welcome, John,
I suspected that you were using SQL Server 2000 as the SQL 2005 info was
just FYI as Microsoft now recognizes this as a 'tunable' knob... As for
using Defragmentor or other utilities, in a word: No. The FT Catalog files
are managed by the "Microsoft Search" (mssearch.exe) service and are
compressed internally, so these utilities will do more harm than good.
For SQL Server 2000, the Master Merge" and smaller index files (shadow
files) are merged together at Midnight, so you may want to monitor your
server's app log for this recorded event at midnight.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:OFouh$y0FHA.2884@.TK2MSFTNGP09.phx.gbl...
> Thank you for your response. Unfortunately this is SQL Server 2000. Does
> it
> make enough of a difference that its worth defragging the individual
> catalog
> files with something like www.defragmentor.com?
> john
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23A7C4vy0FHA.560@.TK2MSFTNGP12.phx.gbl...
> While
> the
> It
> I
> to
> catalog
>

Can I shrink Trans log even if doing log-shipping?

I have a 2000 DB which is log-shipping to another server. The log has grown huge and needs to be made smaller. Can I use DBCC SHRINKFILE without damaging the log-shipping? I know that truncating the log destroys log-shipping, but shrinking should logically not cause any harm. Do you know? Is there a better way to reduce the size of the log file without damaging log-shipping?

Thanks!

Hi,

DBCC shrinkfile with default options should be ok to shrink the transaction log.

Jag

|||

I don't know if this is functionally the same, but I selected "AutoShrink" in EM / database/properties/Options and the trans log file shrank and log shipping appears to be functioning well.

Thanks.

|||

Hi

The database property autoshrink allows a database to shrink automatically at 30-minute intervals. The effect is the same as doing a DBCC SHRINKDATABASE (dbname, 25). This option leaves 25 percent free space in a database after the shrink, and any free space beyond that is returned to the operating system.

This can be a bit dangerous for transaction logs, as at any point you might have empty transaction log, so the autoshrink will make the transaction log really small. This will result in transaction log full errors or increased amount of shrinks and growths , if you have autogrowth on, which can result in poor perfromance.

The best option is to use DBCC Shrinkfile and specify the required file size.

Jag

|||

jag,

Thank you for a very nice reply. It is very helpful and gives me a lot to follow-up on.

Michael