DBCC SHRINKDATABASE

We deleted alot of Temp tables in our DB that we did not need anylonger. Afterwards, I ran DBCC SHRINKDATABASE and left the leave space at 10%. The DB shrank from 8GB to 6.5GB. Sunday nights we run the Maintenance plan wizard which rebuilds the Reorganizes Data and Index pages and changes free space to 10%. When I cam in on Monday the DB grew back to 8GB. The DB on our Test environment is not setup to run the Maint. plan and stays at 6.5GB. Has anyone ever heard of this or might know a solution to keep the DB at 6.5GB as on the Test server? Thank you."Don" <donolwert@.hotmail.com> wrote in message
news:243A6EFD-2F1A-44D3-80C9-57409859E59B@.microsoft.com...
> We deleted alot of Temp tables in our DB that we did not need anylonger.
Afterwards, I ran DBCC SHRINKDATABASE and left the leave space at 10%. The
DB shrank from 8GB to 6.5GB. Sunday nights we run the Maintenance plan
wizard which rebuilds the Reorganizes Data and Index pages and changes free
space to 10%. When I cam in on Monday the DB grew back to 8GB. The DB on
our Test environment is not setup to run the Maint. plan and stays at 6.5GB.
Has anyone ever heard of this or might know a solution to keep the DB at
6.5GB as on the Test server? Thank you.
Perhaps you could schedule the DBCC SHRINKDATABASE to run after your index
rebuild process? It's highly likely that the database expansion is required
to handle the index rebuilds.
Stevesql

DBCC SHRINKDATABASE

We deleted alot of Temp tables in our DB that we did not need anylonger. Af
terwards, I ran DBCC SHRINKDATABASE and left the leave space at 10%. The DB
shrank from 8GB to 6.5GB. Sunday nights we run the Maintenance plan wizard
which rebuilds the Reorgan
izes Data and Index pages and changes free space to 10%. When I cam in on M
onday the DB grew back to 8GB. The DB on our Test environment is not setup
to run the Maint. plan and stays at 6.5GB. Has anyone ever heard of this or
might know a solution to k
eep the DB at 6.5GB as on the Test server? Thank you."Don" <donolwert@.hotmail.com> wrote in message
news:243A6EFD-2F1A-44D3-80C9-57409859E59B@.microsoft.com...

quote:

---

> We deleted alot of Temp tables in our DB that we did not need anylonger.

---

Afterwards, I ran DBCC SHRINKDATABASE and left the leave space at 10%. The
DB shrank from 8GB to 6.5GB. Sunday nights we run the Maintenance plan
wizard which rebuilds the Reorganizes Data and Index pages and changes free
space to 10%. When I cam in on Monday the DB grew back to 8GB. The DB on
our Test environment is not setup to run the Maint. plan and stays at 6.5GB.
Has anyone ever heard of this or might know a solution to keep the DB at
6.5GB as on the Test server? Thank you.
Perhaps you could schedule the DBCC SHRINKDATABASE to run after your index
rebuild process? It's highly likely that the database expansion is required
to handle the index rebuilds.
Steve

dbcc opentran with tableresults

sql2k sp3
How do I get the results of "dbcc opentran with
tableresults" into an actual table? Something along the
lines of;
create table #temp(c1 varchar(50),c2 varchar(50))
insert into #temp (c1,c2) values
(dbcc opentran with tableresults)
select * from #temp
drop table #temp
TIA, ChrisHi Chris
Once you have the table created, you can use the INSERT/EXEC syntax to
populate it:
insert into #temp
exec ('dbcc opentran(pubs) with tableresults')
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:09d701c3ce6b$37e9d640$a001280a@.phx.gbl...
> sql2k sp3
> How do I get the results of "dbcc opentran with
> tableresults" into an actual table? Something along the
> lines of;
> create table #temp(c1 varchar(50),c2 varchar(50))
> insert into #temp (c1,c2) values
> (dbcc opentran with tableresults)
> select * from #temp
> drop table #temp
> TIA, Chris|||create table #temp(c1 varchar(50),c2 varchar(50))
insert into #temp (c1,c2)
exec ('dbcc opentran with tableresults')
select * from #temp
drop table #temp
>--Original Message--
>sql2k sp3
>How do I get the results of "dbcc opentran with
>tableresults" into an actual table? Something along the
>lines of;
>create table #temp(c1 varchar(50),c2 varchar(50))
>insert into #temp (c1,c2) values
>(dbcc opentran with tableresults)
>select * from #temp
>drop table #temp
>TIA, Chris
>.
>

Dbcc Checkident

Dear All,

I'm trying to run the DBCC CHECKIDENT procedure to reset identity seed to 1 as I need it in a temp table I create in a stored procedure.

Is there a way of running this procedure or one which resets identity seed under an account which is not part of the sys-admin group?From BOL about DBCC CHECKIDENT:

Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

Meaning that the user that created the temporary table also has permission to execute DBCC CHECKIDENT on it.|||Thanks for that. It works when I'm logged on as Administrator on the test server and also with an admin account on the live server.

However I get the following:

User '______.Gorgenyi' does not have permission to run DBCC CHECKIDENT for object '#tblRsWeek'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

All users get the same message. This is an ASP.NET web app.

Any suggestions?|||It seems like DBCC CHECKIDENT behaves differently on temporary tables. I haven't tested it myself so I can't give you any details on how and why.

A workaround to reset the identity to 1 is to truncate the temprary table.|||Thanks, TRUNCATE TABLE sorted the problem, no need to use DBCC Checkident