Is there currently a way to cleanup the tempdb?
Tried dbcc shrinkfile , but no luck.
Can't restart the server ,because it's on production.
What makes tempDB big ?
and tempDB is in Simple mode , but not truncating it .
ThanksDid you post to enough groups<g>? Tempdb is used for all sorts <pun
intended> of things during the normal operation of the database. What makes
it so big (by the way how big is it?) depends on what your doing but it is
most likely the result of a large join or sort operation. You might want to
check to see if you have a long running open tran as well. If it got that
big once it is most likely going to get that big again (unless it was a
mistake) so you shouldn't bother to shrink it until you know for sure. Next
time you see a lot of activity in it you can investigate to see what is
happening at the time with profiler, sp_who etc.
--
Andrew J. Kelly
SQL Server MVP
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:eADnDxBQDHA.1556@.TK2MSFTNGP10.phx.gbl...
> Is there currently a way to cleanup the tempdb?
> Tried dbcc shrinkfile , but no luck.
> Can't restart the server ,because it's on production.
> What makes tempDB big ?
> and tempDB is in Simple mode , but not truncating it .
> Thanks
>
Showing posts with label cleanup. Show all posts
Showing posts with label cleanup. Show all posts
Tuesday, March 27, 2012
cleanup the tempdb
Cleanup Task / xp_delete_file
Hello together,
I found some posts but not really a solution. Environment: SQL 2005 EE, SP1,
english version but german regional settings
The following code does nothing in the file system, but allways states
success:
EXECUTE master.dbo.xp_delete_file 0,N'D:\test\',N'.bak',N'06/03/2006 15:06:2
9'
nor does it work with 'bak" ,'trn, '*.trn' or '*.*' or the seeting to
include subfolders.
Any ideas ?
MichaelOne problem is that xp_delete_file belongs to the sys schema
not the dbo schema.
-Sue
On Tue, 6 Jun 2006 06:17:01 -0700, Michael Roedeske
<MichaelRoedeske@.discussions.microsoft.com> wrote:
>Hello together,
>I found some posts but not really a solution. Environment: SQL 2005 EE, SP1
,
>english version but german regional settings
>The following code does nothing in the file system, but allways states
>success:
>EXECUTE master.dbo.xp_delete_file 0,N'D:\test',N'.bak',N'06/03/2006 15:06:
29'
>nor does it work with 'bak" ,'trn, '*.trn' or '*.*' or the seeting to
>include subfolders.
>Any ideas ?
>Michael
>|||Dear Sue,
even when I call the sp with the sys schema it happes nothing in the
filesystem.
Any other idea?
Regards
Michael
"Sue Hoegemeier" wrote:
> One problem is that xp_delete_file belongs to the sys schema
> not the dbo schema.
> -Sue
> On Tue, 6 Jun 2006 06:17:01 -0700, Michael Roedeske
> <MichaelRoedeske@.discussions.microsoft.com> wrote:
>
>|||Others have had the same issue - and others do not. I
haven't seen anyone post what solved the issue.
Try removing the dot from the extension - for example have
it list as just BAK without the dot before BAK. Make sure
the extension is exactly what you are using for your
backups.
It was suppose to be fixed in SP1 but you may also want to
try creating a cleanup task for each subdirectory.
People sometimes spend too much time troubleshooting
maintenance plans when they can just write scripts that have
more flexibility in less time then they spend addressing
maintenance plan issue.
-Sue
On Wed, 7 Jun 2006 01:06:02 -0700, Michael Roedeske
<MichaelRoedeske@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Dear Sue,
>even when I call the sp with the sys schema it happes nothing in the
>filesystem.
>Any other idea?
>Regards
>Michael
>"Sue Hoegemeier" wrote:
>sqlsql
I found some posts but not really a solution. Environment: SQL 2005 EE, SP1,
english version but german regional settings
The following code does nothing in the file system, but allways states
success:
EXECUTE master.dbo.xp_delete_file 0,N'D:\test\',N'.bak',N'06/03/2006 15:06:2
9'
nor does it work with 'bak" ,'trn, '*.trn' or '*.*' or the seeting to
include subfolders.
Any ideas ?
MichaelOne problem is that xp_delete_file belongs to the sys schema
not the dbo schema.
-Sue
On Tue, 6 Jun 2006 06:17:01 -0700, Michael Roedeske
<MichaelRoedeske@.discussions.microsoft.com> wrote:
>Hello together,
>I found some posts but not really a solution. Environment: SQL 2005 EE, SP1
,
>english version but german regional settings
>The following code does nothing in the file system, but allways states
>success:
>EXECUTE master.dbo.xp_delete_file 0,N'D:\test',N'.bak',N'06/03/2006 15:06:
29'
>nor does it work with 'bak" ,'trn, '*.trn' or '*.*' or the seeting to
>include subfolders.
>Any ideas ?
>Michael
>|||Dear Sue,
even when I call the sp with the sys schema it happes nothing in the
filesystem.
Any other idea?
Regards
Michael
"Sue Hoegemeier" wrote:
> One problem is that xp_delete_file belongs to the sys schema
> not the dbo schema.
> -Sue
> On Tue, 6 Jun 2006 06:17:01 -0700, Michael Roedeske
> <MichaelRoedeske@.discussions.microsoft.com> wrote:
>
>|||Others have had the same issue - and others do not. I
haven't seen anyone post what solved the issue.
Try removing the dot from the extension - for example have
it list as just BAK without the dot before BAK. Make sure
the extension is exactly what you are using for your
backups.
It was suppose to be fixed in SP1 but you may also want to
try creating a cleanup task for each subdirectory.
People sometimes spend too much time troubleshooting
maintenance plans when they can just write scripts that have
more flexibility in less time then they spend addressing
maintenance plan issue.
-Sue
On Wed, 7 Jun 2006 01:06:02 -0700, Michael Roedeske
<MichaelRoedeske@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Dear Sue,
>even when I call the sp with the sys schema it happes nothing in the
>filesystem.
>Any other idea?
>Regards
>Michael
>"Sue Hoegemeier" wrote:
>sqlsql
Cleanup Task / xp_delete_file
Hello together,
I found some posts but not really a solution. Environment: SQL 2005 EE, SP1,
english version but german regional settings
The following code does nothing in the file system, but allways states
success:
EXECUTE master.dbo.xp_delete_file 0,N'D:\test\',N'.bak',N'06/03/2006 15:06:29'
nor does it work with 'bak" ,'trn, '*.trn' or '*.*' or the seeting to
include subfolders.
Any ideas ?
MichaelOne problem is that xp_delete_file belongs to the sys schema
not the dbo schema.
-Sue
On Tue, 6 Jun 2006 06:17:01 -0700, Michael Roedeske
<MichaelRoedeske@.discussions.microsoft.com> wrote:
>Hello together,
>I found some posts but not really a solution. Environment: SQL 2005 EE, SP1,
>english version but german regional settings
>The following code does nothing in the file system, but allways states
>success:
>EXECUTE master.dbo.xp_delete_file 0,N'D:\test\',N'.bak',N'06/03/2006 15:06:29'
>nor does it work with 'bak" ,'trn, '*.trn' or '*.*' or the seeting to
>include subfolders.
>Any ideas ?
>Michael
>|||Dear Sue,
even when I call the sp with the sys schema it happes nothing in the
filesystem.
Any other idea?
Regards
Michael
"Sue Hoegemeier" wrote:
> One problem is that xp_delete_file belongs to the sys schema
> not the dbo schema.
> -Sue
> On Tue, 6 Jun 2006 06:17:01 -0700, Michael Roedeske
> <MichaelRoedeske@.discussions.microsoft.com> wrote:
> >Hello together,
> >
> >I found some posts but not really a solution. Environment: SQL 2005 EE, SP1,
> >english version but german regional settings
> >
> >The following code does nothing in the file system, but allways states
> >success:
> >
> >EXECUTE master.dbo.xp_delete_file 0,N'D:\test\',N'.bak',N'06/03/2006 15:06:29'
> >
> >nor does it work with 'bak" ,'trn, '*.trn' or '*.*' or the seeting to
> >include subfolders.
> >
> >Any ideas ?
> >
> >Michael
> >
>|||Others have had the same issue - and others do not. I
haven't seen anyone post what solved the issue.
Try removing the dot from the extension - for example have
it list as just BAK without the dot before BAK. Make sure
the extension is exactly what you are using for your
backups.
It was suppose to be fixed in SP1 but you may also want to
try creating a cleanup task for each subdirectory.
People sometimes spend too much time troubleshooting
maintenance plans when they can just write scripts that have
more flexibility in less time then they spend addressing
maintenance plan issue.
-Sue
On Wed, 7 Jun 2006 01:06:02 -0700, Michael Roedeske
<MichaelRoedeske@.discussions.microsoft.com> wrote:
>Dear Sue,
>even when I call the sp with the sys schema it happes nothing in the
>filesystem.
>Any other idea?
>Regards
>Michael
>"Sue Hoegemeier" wrote:
>> One problem is that xp_delete_file belongs to the sys schema
>> not the dbo schema.
>> -Sue
>> On Tue, 6 Jun 2006 06:17:01 -0700, Michael Roedeske
>> <MichaelRoedeske@.discussions.microsoft.com> wrote:
>> >Hello together,
>> >
>> >I found some posts but not really a solution. Environment: SQL 2005 EE, SP1,
>> >english version but german regional settings
>> >
>> >The following code does nothing in the file system, but allways states
>> >success:
>> >
>> >EXECUTE master.dbo.xp_delete_file 0,N'D:\test\',N'.bak',N'06/03/2006 15:06:29'
>> >
>> >nor does it work with 'bak" ,'trn, '*.trn' or '*.*' or the seeting to
>> >include subfolders.
>> >
>> >Any ideas ?
>> >
>> >Michael
>> >
>>
I found some posts but not really a solution. Environment: SQL 2005 EE, SP1,
english version but german regional settings
The following code does nothing in the file system, but allways states
success:
EXECUTE master.dbo.xp_delete_file 0,N'D:\test\',N'.bak',N'06/03/2006 15:06:29'
nor does it work with 'bak" ,'trn, '*.trn' or '*.*' or the seeting to
include subfolders.
Any ideas ?
MichaelOne problem is that xp_delete_file belongs to the sys schema
not the dbo schema.
-Sue
On Tue, 6 Jun 2006 06:17:01 -0700, Michael Roedeske
<MichaelRoedeske@.discussions.microsoft.com> wrote:
>Hello together,
>I found some posts but not really a solution. Environment: SQL 2005 EE, SP1,
>english version but german regional settings
>The following code does nothing in the file system, but allways states
>success:
>EXECUTE master.dbo.xp_delete_file 0,N'D:\test\',N'.bak',N'06/03/2006 15:06:29'
>nor does it work with 'bak" ,'trn, '*.trn' or '*.*' or the seeting to
>include subfolders.
>Any ideas ?
>Michael
>|||Dear Sue,
even when I call the sp with the sys schema it happes nothing in the
filesystem.
Any other idea?
Regards
Michael
"Sue Hoegemeier" wrote:
> One problem is that xp_delete_file belongs to the sys schema
> not the dbo schema.
> -Sue
> On Tue, 6 Jun 2006 06:17:01 -0700, Michael Roedeske
> <MichaelRoedeske@.discussions.microsoft.com> wrote:
> >Hello together,
> >
> >I found some posts but not really a solution. Environment: SQL 2005 EE, SP1,
> >english version but german regional settings
> >
> >The following code does nothing in the file system, but allways states
> >success:
> >
> >EXECUTE master.dbo.xp_delete_file 0,N'D:\test\',N'.bak',N'06/03/2006 15:06:29'
> >
> >nor does it work with 'bak" ,'trn, '*.trn' or '*.*' or the seeting to
> >include subfolders.
> >
> >Any ideas ?
> >
> >Michael
> >
>|||Others have had the same issue - and others do not. I
haven't seen anyone post what solved the issue.
Try removing the dot from the extension - for example have
it list as just BAK without the dot before BAK. Make sure
the extension is exactly what you are using for your
backups.
It was suppose to be fixed in SP1 but you may also want to
try creating a cleanup task for each subdirectory.
People sometimes spend too much time troubleshooting
maintenance plans when they can just write scripts that have
more flexibility in less time then they spend addressing
maintenance plan issue.
-Sue
On Wed, 7 Jun 2006 01:06:02 -0700, Michael Roedeske
<MichaelRoedeske@.discussions.microsoft.com> wrote:
>Dear Sue,
>even when I call the sp with the sys schema it happes nothing in the
>filesystem.
>Any other idea?
>Regards
>Michael
>"Sue Hoegemeier" wrote:
>> One problem is that xp_delete_file belongs to the sys schema
>> not the dbo schema.
>> -Sue
>> On Tue, 6 Jun 2006 06:17:01 -0700, Michael Roedeske
>> <MichaelRoedeske@.discussions.microsoft.com> wrote:
>> >Hello together,
>> >
>> >I found some posts but not really a solution. Environment: SQL 2005 EE, SP1,
>> >english version but german regional settings
>> >
>> >The following code does nothing in the file system, but allways states
>> >success:
>> >
>> >EXECUTE master.dbo.xp_delete_file 0,N'D:\test\',N'.bak',N'06/03/2006 15:06:29'
>> >
>> >nor does it work with 'bak" ,'trn, '*.trn' or '*.*' or the seeting to
>> >include subfolders.
>> >
>> >Any ideas ?
>> >
>> >Michael
>> >
>>
Cleanup of unused database objects
Hi,
I've inherited a database with lots of unused objects (tables and
SPs). What I want to do is determine wich objects have not been used
for the last 30 days and remove them from the database.
Is there a way to determine the last time a table was accessed or a
Stored Procedure was run. I've looked at the system tables but haven't
seen anything that indicates this. I can put a trace on, but that
would consume too many cycles of the machine.
Any ideas or help would be greatly appreciated.
EdThe way I have handled this is to run a trace (SQL Profiler) to see which
objects are accessed.
Store the Object_id's ... you can store the trace in a table if you want and
select them later ... this will give you a list of objects that are being
used.
-Lars
"Edward Roepe" <edward@.roepe.com> wrote in message
news:d383b36b.0401271358.3ac36806@.posting.google.com...
I've inherited a database with lots of unused objects (tables and
SPs). What I want to do is determine wich objects have not been used
for the last 30 days and remove them from the database.
Is there a way to determine the last time a table was accessed or a
Stored Procedure was run. I've looked at the system tables but haven't
seen anything that indicates this. I can put a trace on, but that
would consume too many cycles of the machine.
Any ideas or help would be greatly appreciated.
EdThe way I have handled this is to run a trace (SQL Profiler) to see which
objects are accessed.
Store the Object_id's ... you can store the trace in a table if you want and
select them later ... this will give you a list of objects that are being
used.
-Lars
"Edward Roepe" <edward@.roepe.com> wrote in message
news:d383b36b.0401271358.3ac36806@.posting.google.com...
quote:
> Hi,
> I've inherited a database with lots of unused objects (tables and
> SPs). What I want to do is determine wich objects have not been used
> for the last 30 days and remove them from the database.
> Is there a way to determine the last time a table was accessed or a
> Stored Procedure was run. I've looked at the system tables but haven't
> seen anything that indicates this. I can put a trace on, but that
> would consume too many cycles of the machine.
> Any ideas or help would be greatly appreciated.
> Ed
Cleanup of unused database objects
Hi,
I've inherited a database with lots of unused objects (tables and
SPs). What I want to do is determine wich objects have not been used
for the last 30 days and remove them from the database.
Is there a way to determine the last time a table was accessed or a
Stored Procedure was run. I've looked at the system tables but haven't
seen anything that indicates this. I can put a trace on, but that
would consume too many cycles of the machine.
Any ideas or help would be greatly appreciated.
EdThe way I have handled this is to run a trace (SQL Profiler) to see which
objects are accessed.
Store the Object_id's ... you can store the trace in a table if you want and
select them later ... this will give you a list of objects that are being
used.
-Lars
"Edward Roepe" <edward@.roepe.com> wrote in message
news:d383b36b.0401271358.3ac36806@.posting.google.com...
> Hi,
> I've inherited a database with lots of unused objects (tables and
> SPs). What I want to do is determine wich objects have not been used
> for the last 30 days and remove them from the database.
> Is there a way to determine the last time a table was accessed or a
> Stored Procedure was run. I've looked at the system tables but haven't
> seen anything that indicates this. I can put a trace on, but that
> would consume too many cycles of the machine.
> Any ideas or help would be greatly appreciated.
> Ed
I've inherited a database with lots of unused objects (tables and
SPs). What I want to do is determine wich objects have not been used
for the last 30 days and remove them from the database.
Is there a way to determine the last time a table was accessed or a
Stored Procedure was run. I've looked at the system tables but haven't
seen anything that indicates this. I can put a trace on, but that
would consume too many cycles of the machine.
Any ideas or help would be greatly appreciated.
EdThe way I have handled this is to run a trace (SQL Profiler) to see which
objects are accessed.
Store the Object_id's ... you can store the trace in a table if you want and
select them later ... this will give you a list of objects that are being
used.
-Lars
"Edward Roepe" <edward@.roepe.com> wrote in message
news:d383b36b.0401271358.3ac36806@.posting.google.com...
> Hi,
> I've inherited a database with lots of unused objects (tables and
> SPs). What I want to do is determine wich objects have not been used
> for the last 30 days and remove them from the database.
> Is there a way to determine the last time a table was accessed or a
> Stored Procedure was run. I've looked at the system tables but haven't
> seen anything that indicates this. I can put a trace on, but that
> would consume too many cycles of the machine.
> Any ideas or help would be greatly appreciated.
> Ed
Cleanup of Expired Snapshots
Do you have anonymous subscriptions? If so, the snapshots
are kept for the retention period of the subscription to
given anonymous subscriptions time to synchronize.
HTH,
Paul Ibison
Bill,
sp_helpdistributor will confirm the value you report (default is 72 hours)
so it might be worth running it just to check.
If the directorynames/filenames have changed, or if the system clock has
altered then I can understand it.
Regards,
Paul Ibison
|||Paul,
From sp_helpdistributor the max distrib retention is 720
and the min is set to 0. This is 720 hours (30 days). I
believe the system clock has not been altered.
I think the retention is working perfectly related to the
distribution database transactions, so I don't think the
retention value is the issue. It is just the snapshot
files that are not being cleaned up.
Do you know which stored procedure removes the snapshot
files and how it invokes the OS to delete the files?
Bill
>--Original Message--
>Bill,
>sp_helpdistributor will confirm the value you report
(default is 72 hours)
>so it might be worth running it just to check.
>If the directorynames/filenames have changed, or if the
system clock has
>altered then I can understand it.
>Regards,
>Paul Ibison
>
>.
>
|||Bill,
I believe the procedure you're looking for is sp_MSDelete_publisherdb_trans.
You can follow the logic from sp_MSdistribution_cleanup and
sp_MSdistribution_delete.
HTH,
Paul Ibison
are kept for the retention period of the subscription to
given anonymous subscriptions time to synchronize.
HTH,
Paul Ibison
Bill,
sp_helpdistributor will confirm the value you report (default is 72 hours)
so it might be worth running it just to check.
If the directorynames/filenames have changed, or if the system clock has
altered then I can understand it.
Regards,
Paul Ibison
|||Paul,
From sp_helpdistributor the max distrib retention is 720
and the min is set to 0. This is 720 hours (30 days). I
believe the system clock has not been altered.
I think the retention is working perfectly related to the
distribution database transactions, so I don't think the
retention value is the issue. It is just the snapshot
files that are not being cleaned up.
Do you know which stored procedure removes the snapshot
files and how it invokes the OS to delete the files?
Bill
>--Original Message--
>Bill,
>sp_helpdistributor will confirm the value you report
(default is 72 hours)
>so it might be worth running it just to check.
>If the directorynames/filenames have changed, or if the
system clock has
>altered then I can understand it.
>Regards,
>Paul Ibison
>
>.
>
|||Bill,
I believe the procedure you're looking for is sp_MSDelete_publisherdb_trans.
You can follow the logic from sp_MSdistribution_cleanup and
sp_MSdistribution_delete.
HTH,
Paul Ibison
Cleanup / Foreign key
I have an address table I set up so that I can start our address campaign.
There is a field (open entry) called Business Type that I allowed the works
to enter to describe the business type. Now that the entry is complete ,
I'm in cleanup phase. Now, my question is: I know I need to make Business
Type a foreign key and select distinct to insert them into their own table,
but should I do that before I clean up the inconsistencies or after. There
are about 2000 different business types, some are minor like - Car Sales -
Used and Car Sales _ Used. I'm creating a system that allows users to
search based on business type.
Before or after, why?Before!
Why? Do not allow bad data into the database in the first place, it is a
bitch getting it out.
Jay
<programmingcodeATjards.com> wrote in message
news:uVedQl9%23HHA.320@.TK2MSFTNGP04.phx.gbl...
>I have an address table I set up so that I can start our address campaign.
>There is a field (open entry) called Business Type that I allowed the works
>to enter to describe the business type. Now that the entry is complete ,
>I'm in cleanup phase. Now, my question is: I know I need to make Business
>Type a foreign key and select distinct to insert them into their own table,
>but should I do that before I clean up the inconsistencies or after. There
>are about 2000 different business types, some are minor like - Car Sales -
>Used and Car Sales _ Used. I'm creating a system that allows users to
>search based on business type.
> Before or after, why?
>sqlsql
There is a field (open entry) called Business Type that I allowed the works
to enter to describe the business type. Now that the entry is complete ,
I'm in cleanup phase. Now, my question is: I know I need to make Business
Type a foreign key and select distinct to insert them into their own table,
but should I do that before I clean up the inconsistencies or after. There
are about 2000 different business types, some are minor like - Car Sales -
Used and Car Sales _ Used. I'm creating a system that allows users to
search based on business type.
Before or after, why?Before!
Why? Do not allow bad data into the database in the first place, it is a
bitch getting it out.
Jay
<programmingcodeATjards.com> wrote in message
news:uVedQl9%23HHA.320@.TK2MSFTNGP04.phx.gbl...
>I have an address table I set up so that I can start our address campaign.
>There is a field (open entry) called Business Type that I allowed the works
>to enter to describe the business type. Now that the entry is complete ,
>I'm in cleanup phase. Now, my question is: I know I need to make Business
>Type a foreign key and select distinct to insert them into their own table,
>but should I do that before I clean up the inconsistencies or after. There
>are about 2000 different business types, some are minor like - Car Sales -
>Used and Car Sales _ Used. I'm creating a system that allows users to
>search based on business type.
> Before or after, why?
>sqlsql
Subscribe to:
Posts (Atom)