25 seconds for query on empty table (SS 2005)

And this is on a dual Xeon with 4 Gig of memory.
Here is the script that's taking so long.
Would someone be so nice and try it on their SQL Server 2005 and tell me if
they have the same issue? Thx.
create table #t (tid int, t2 int)
select tid, count(*)
from #t
group by tid with rollup
drop table #tSpeed of light on both my instances (2005 and 2000 with sp3).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pat" <pat@.online.nospam> wrote in message
news:ACCC8738-BAAC-4D17-9E6D-98DC9444741B@.microsoft.com...
> And this is on a dual Xeon with 4 Gig of memory.
> Here is the script that's taking so long.
> Would someone be so nice and try it on their SQL Server 2005 and tell me if
> they have the same issue? Thx.
> create table #t (tid int, t2 int)
> select tid, count(*)
> from #t
> group by tid with rollup
> drop table #t|||Thanks for that.
"Tibor Karaszi" wrote:
> Speed of light on both my instances (2005 and 2000 with sp3).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pat" <pat@.online.nospam> wrote in message
> news:ACCC8738-BAAC-4D17-9E6D-98DC9444741B@.microsoft.com...
> > And this is on a dual Xeon with 4 Gig of memory.
> > Here is the script that's taking so long.
> > Would someone be so nice and try it on their SQL Server 2005 and tell me if
> > they have the same issue? Thx.
> >
> > create table #t (tid int, t2 int)
> >
> > select tid, count(*)
> > from #t
> > group by tid with rollup
> >
> > drop table #t
>

25 seconds for query on empty table (SS 2005)

And this is on a dual Xeon with 4 Gig of memory.
Here is the script that's taking so long.
Would someone be so nice and try it on their SQL Server 2005 and tell me if
they have the same issue? Thx.
create table #t (tid int, t2 int)
select tid, count(*)
from #t
group by tid with rollup
drop table #t
Speed of light on both my instances (2005 and 2000 with sp3).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pat" <pat@.online.nospam> wrote in message
news:ACCC8738-BAAC-4D17-9E6D-98DC9444741B@.microsoft.com...
> And this is on a dual Xeon with 4 Gig of memory.
> Here is the script that's taking so long.
> Would someone be so nice and try it on their SQL Server 2005 and tell me if
> they have the same issue? Thx.
> create table #t (tid int, t2 int)
> select tid, count(*)
> from #t
> group by tid with rollup
> drop table #t
|||Thanks for that.
"Tibor Karaszi" wrote:

> Speed of light on both my instances (2005 and 2000 with sp3).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pat" <pat@.online.nospam> wrote in message
> news:ACCC8738-BAAC-4D17-9E6D-98DC9444741B@.microsoft.com...
>

25 seconds for query on empty table (SS 2005)

And this is on a dual Xeon with 4 Gig of memory.
Here is the script that's taking so long.
Would someone be so nice and try it on their SQL Server 2005 and tell me if
they have the same issue? Thx.
create table #t (tid int, t2 int)
select tid, count(*)
from #t
group by tid with rollup
drop table #tSpeed of light on both my instances (2005 and 2000 with sp3).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pat" <pat@.online.nospam> wrote in message
news:ACCC8738-BAAC-4D17-9E6D-98DC9444741B@.microsoft.com...
> And this is on a dual Xeon with 4 Gig of memory.
> Here is the script that's taking so long.
> Would someone be so nice and try it on their SQL Server 2005 and tell me i
f
> they have the same issue? Thx.
> create table #t (tid int, t2 int)
> select tid, count(*)
> from #t
> group by tid with rollup
> drop table #t|||Thanks for that.
"Tibor Karaszi" wrote:

> Speed of light on both my instances (2005 and 2000 with sp3).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pat" <pat@.online.nospam> wrote in message
> news:ACCC8738-BAAC-4D17-9E6D-98DC9444741B@.microsoft.com...
>

21 gig Transaction Log

Team,
I have a 21 gig Transaction Log and need to reduce the size. Can someone
tell me the best way to reduce this? We are using SQL7 and my knowledge of
it is limited since it was forced upon me. Thanks for any help you can
provide.
ChrisRefer to this KB articles.
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
--
-Vishal
"Chris S" <csemones@.nuvox*nospam*_.com> wrote in message
news:vks4vqkm56o5f9@.corp.supernews.com...
> Team,
> I have a 21 gig Transaction Log and need to reduce the size. Can someone
> tell me the best way to reduce this? We are using SQL7 and my knowledge
of
> it is limited since it was forced upon me. Thanks for any help you can
> provide.
> Chris
>|||More info on this...I do a full backup every night and tried doing a log
backup today to see if it would reduce it but it did now work. The database
size is about 120 meg.
Thanks agian,
Chris
"Chris S" <csemones@.nuvox*nospam*_.com> wrote in message
news:vks4vqkm56o5f9@.corp.supernews.com...
> Team,
> I have a 21 gig Transaction Log and need to reduce the size. Can someone
> tell me the best way to reduce this? We are using SQL7 and my knowledge
of
> it is limited since it was forced upon me. Thanks for any help you can
> provide.
> Chris
>|||SQL Server can not truncte the log if the active portion of the transaction
log contains transactions that are still running and have not yet
completed.Backup log command does not physically shrink the size of the
transaction log file of the database. When old log records no longer
necessary for recovering or restoring a database they are deleted to make
way for new log records.This process of deleting these log records is
truncating the log.
To physically reduce the size of the tran. log you will have to run dbcc
shrinkfile.
You have various options to keep transaction log size manageable:
1.Keep taking transaction log backups at particular intervals this will help
you keep optimal transaction log size.
When SQL Server finishes backing up the transaction log, it automatically
truncates the inactive portion of the transaction log.
2.Keep transaction size as small as possible.
--
-Vishal
"Chris S" <csemones@.nuvox*nospam*_.com> wrote in message
news:vks5b26nrik79a@.corp.supernews.com...
> More info on this...I do a full backup every night and tried doing a log
> backup today to see if it would reduce it but it did now work. The
database
> size is about 120 meg.
> Thanks agian,
> Chris
>
> "Chris S" <csemones@.nuvox*nospam*_.com> wrote in message
> news:vks4vqkm56o5f9@.corp.supernews.com...
> > Team,
> > I have a 21 gig Transaction Log and need to reduce the size. Can
someone
> > tell me the best way to reduce this? We are using SQL7 and my knowledge
> of
> > it is limited since it was forced upon me. Thanks for any help you can
> > provide.
> > Chris
> >
> >
>

2005 - Need to Shrink Log File

Is there a way to shrink the log file below it's Initial Size?
Somehow the initial size for a log file was accidentally set to 25 Gig. This
extra large log file is keeping the backups from working (no disk space).
I've tried Shrinking the log file, but I'm not allowed to shrink it below
it's Intial Size.
I've tried changing it's Initial Size through it's properties, but that
doesn't seem to work either. No message, no indication that it failed.
Is there some way to perhaps replicate the database to another database with
a smaller initial log file setting and then copy it back or something?
Is there some way that I can get the Initial Size back down to a more
manageable size?
Thanks for any help you can offer.
Terry
Thank you, Tibor, for your quick reply.
DBCC SHRINKFILE didn't work. I received the message: "Cannot shrink log file
2 (PAIR_log) because all logical log files are in use."
When I use the SMS to select the Shrink Files option, I can see that the log
file is 99% empty.
I will need to read over your suggested website more carefully to see if
that will help.
Terry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%233MNF0qWHHA.1180@.TK2MSFTNGP05.phx.gbl...
> Did you try DBCC SHRINKFILE? That command should be able to get it below
> initial size. Also, see
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp for general tips
> when shrink don't seem to work.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
|||Thanks again, Tibor.
Following your suggestions on your website:
I only had to do the Backup Log once and that freed the last page that had
the status of 2. Now everything is back to being a much more manageable
size!
Thanks again!
Terry

> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%233MNF0qWHHA.1180@.TK2MSFTNGP05.phx.gbl...
>

2005 - Need to Shrink Log File

Is there a way to shrink the log file below it's Initial Size?
Somehow the initial size for a log file was accidentally set to 25 Gig. This
extra large log file is keeping the backups from working (no disk space).
I've tried Shrinking the log file, but I'm not allowed to shrink it below
it's Intial Size.
I've tried changing it's Initial Size through it's properties, but that
doesn't seem to work either. No message, no indication that it failed.
Is there some way to perhaps replicate the database to another database with
a smaller initial log file setting and then copy it back or something?
Is there some way that I can get the Initial Size back down to a more
manageable size?
Thanks for any help you can offer.
TerryDid you try DBCC SHRINKFILE? That command should be able to get it below initial size. Also, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for general tips when shrink don't seem to
work.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Terry Carnes" <wbcarnes3@.yahoo.com> wrote in message news:uKxilsqWHHA.4624@.TK2MSFTNGP03.phx.gbl...
> Is there a way to shrink the log file below it's Initial Size?
> Somehow the initial size for a log file was accidentally set to 25 Gig. This extra large log file
> is keeping the backups from working (no disk space).
> I've tried Shrinking the log file, but I'm not allowed to shrink it below it's Intial Size.
> I've tried changing it's Initial Size through it's properties, but that doesn't seem to work
> either. No message, no indication that it failed.
> Is there some way to perhaps replicate the database to another database with a smaller initial log
> file setting and then copy it back or something?
> Is there some way that I can get the Initial Size back down to a more manageable size?
> Thanks for any help you can offer.
> Terry
>|||Thank you, Tibor, for your quick reply.
DBCC SHRINKFILE didn't work. I received the message: "Cannot shrink log file
2 (PAIR_log) because all logical log files are in use."
When I use the SMS to select the Shrink Files option, I can see that the log
file is 99% empty.
I will need to read over your suggested website more carefully to see if
that will help.
Terry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%233MNF0qWHHA.1180@.TK2MSFTNGP05.phx.gbl...
> Did you try DBCC SHRINKFILE? That command should be able to get it below
> initial size. Also, see
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp for general tips
> when shrink don't seem to work.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi|||Thanks again, Tibor.
Following your suggestions on your website:
I only had to do the Backup Log once and that freed the last page that had
the status of 2. Now everything is back to being a much more manageable
size!
Thanks again!
Terry
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%233MNF0qWHHA.1180@.TK2MSFTNGP05.phx.gbl...
>> Did you try DBCC SHRINKFILE? That command should be able to get it below
>> initial size. Also, see
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp for general tips
>> when shrink don't seem to work.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>

2005 - Need to Shrink Log File

Is there a way to shrink the log file below it's Initial Size?
Somehow the initial size for a log file was accidentally set to 25 Gig. This
extra large log file is keeping the backups from working (no disk space).
I've tried Shrinking the log file, but I'm not allowed to shrink it below
it's Intial Size.
I've tried changing it's Initial Size through it's properties, but that
doesn't seem to work either. No message, no indication that it failed.
Is there some way to perhaps replicate the database to another database with
a smaller initial log file setting and then copy it back or something?
Is there some way that I can get the Initial Size back down to a more
manageable size?
Thanks for any help you can offer.
TerryDid you try DBCC SHRINKFILE? That command should be able to get it below ini
tial size. Also, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for general tips when
shrink don't seem to
work.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Terry Carnes" <wbcarnes3@.yahoo.com> wrote in message news:uKxilsqWHHA.4624@.TK2MSFTNGP03.phx
.gbl...
> Is there a way to shrink the log file below it's Initial Size?
> Somehow the initial size for a log file was accidentally set to 25 Gig. Th
is extra large log file
> is keeping the backups from working (no disk space).
> I've tried Shrinking the log file, but I'm not allowed to shrink it below
it's Intial Size.
> I've tried changing it's Initial Size through it's properties, but that do
esn't seem to work
> either. No message, no indication that it failed.
> Is there some way to perhaps replicate the database to another database wi
th a smaller initial log
> file setting and then copy it back or something?
> Is there some way that I can get the Initial Size back down to a more mana
geable size?
> Thanks for any help you can offer.
> Terry
>|||Thank you, Tibor, for your quick reply.
DBCC SHRINKFILE didn't work. I received the message: "Cannot shrink log file
2 (PAIR_log) because all logical log files are in use."
When I use the SMS to select the Shrink Files option, I can see that the log
file is 99% empty.
I will need to read over your suggested website more carefully to see if
that will help.
Terry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%233MNF0qWHHA.1180@.TK2MSFTNGP05.phx.gbl...
> Did you try DBCC SHRINKFILE? That command should be able to get it below
> initial size. Also, see
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp for general tips
> when shrink don't seem to work.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi|||Thanks again, Tibor.
Following your suggestions on your website:
I only had to do the Backup Log once and that freed the last page that had
the status of 2. Now everything is back to being a much more manageable
size!
Thanks again!
Terry

> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%233MNF0qWHHA.1180@.TK2MSFTNGP05.phx.gbl...
>