Data files in Flash Drive

Hi friends,
i want to know something abt sql server installation and database properties as under.
1) how much disk space reqd by multiuser sqlserver 2000 db to install ?
2) is it possible to install sql 2K on Flash Drive ( gen connect at USB) ?
3) When v create a database user Enterprise Manager , which files created by database ?
4) If i Know datafiles name of a database e.g. pubs , and if v copied on cd and paste on other system which has sql 2K install , does those database works ?
5) is it possible to create database on flash drive ?

thanx in advance.Come on...are these trick questions?|||I suppose it could be done, but you would have to be very sure that you properly detach the database before someone goes and unplugs the USB drive. I think you would set yourself up for a heck of a lot more user errors than SQL Server errors this way.

Data File Used Space

Using the master.sysperfinfo table in a SQL Server database you can obtain
the "Log file(s) Used sizes (KB)".
I would like to determine the "Database file(s) Used Sizes (KB)".
How can I determine this value?
Thank You,Lookup the usage and details of the system procedure sp_spaceused in SQL
Server Books Online.
Anith

Data File Used Space

How is it, that when I delete records in my Test environment that the "data
used" within the data file decreases, but not so when I delete records from
my production environment?
Message posted via http://www.droptable.com
See my reply to your other message.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:2cc2a3ac2e7643468a1c4107212c0865@.droptable.co m...
> How is it, that when I delete records in my Test environment that the
> "data
> used" within the data file decreases, but not so when I delete records
> from
> my production environment?
> --
> Message posted via http://www.droptable.com
|||hi
why dont you post it again so that others can go through the post aswell
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
*** Sent via Developersdex http://www.codecomments.com ***

Data File Used Space

How is it, that when I delete records in my Test environment that the "data
used" within the data file decreases, but not so when I delete records from
my production environment?
Message posted via http://www.droptable.comSee my reply to your other message.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:2cc2a3ac2e7643468a1c4107212c0865@.SQ
droptable.com...
> How is it, that when I delete records in my Test environment that the
> "data
> used" within the data file decreases, but not so when I delete records
> from
> my production environment?
> --
> Message posted via http://www.droptable.com|||hi
why dont you post it again so that others can go through the post aswell
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.codecomments.com ***sql

Data File Used Space

How is it, that when I delete records in my Test environment that the "data
used" within the data file decreases, but not so when I delete records from
my production environment?
--
Message posted via http://www.sqlmonster.comSee my reply to your other message.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:2cc2a3ac2e7643468a1c4107212c0865@.SQLMonster.com...
> How is it, that when I delete records in my Test environment that the
> "data
> used" within the data file decreases, but not so when I delete records
> from
> my production environment?
> --
> Message posted via http://www.sqlmonster.com

Data file space used calculations

Hi,

Is there any table I can query from to get the space used by a data or log file? I can get this info from TaskPad but I want to query from tables. The sp_spaceused procedure gets the info for a database or table but not the 'data or log' files.

Thanks for any help.

VinnieYou can do this

select cast(size * 8 as int) as Size from dbo.sysfiles

The sysfiles table stores the size as size of 8kb pages so you have to multiply. (the statement output is in KB, if you wanted it different you can change the math)

HTH|||Originally posted by rhigdon
You can do this

select cast(size * 8 as int) as Size from dbo.sysfiles

The sysfiles table stores the size as size of 8kb pages so you have to multiply. (the statement output is in KB, if you wanted it different you can change the math)

HTH

Thanks a lot but the sysfiles can give me only size whereas I need the space used and space free for a data file /log file. Any other suggestions are appreciated.

Thanks in advance.|||For log file you can do:

dbcc sqlperf('logspace')

I'm not sure about how to do for the data file, I know sp_spaceused is pretty close.

HTH|||Originally posted by rhigdon
For log file you can do:

dbcc sqlperf('logspace')

I'm not sure about how to do for the data file, I know sp_spaceused is pretty close.

HTH

Hi,

That is exactly what I am looking for is 'data file'. I could get for log file in sysperfinfo table. I was unsuccessful to find any info for datafile to calculate space used or space free. How does the Taskpad display these things? They may be doing from table. What is it is the question? If any one can help It will be a great help.

Thanks
Vinnie|||True, you can use SP_SPACEUSED to get the data, index space usage and DBCC SQLPERF(LOGSPACE) for the %age of Tlog used for any database.

Run SP_HELPFILE to get the information for physical files associated to that database.

Refer to Vyas's link (http://vyaskn.tripod.com/track_sql_database_file_growth.htm) for more information.

Data file size used space

In the SQL EM, we are able to get the data file size with
used space and free space details. Similarly for the log
file also.
We have the dbcc sqlperf command to find out the log file
used space details. Is there any way to find out the
data file size? sp_helpfile just gives the total size of
the data file wherein sp_spaceused is also not
distinguishing between used and free space.
Could anyone help me out in this?Do a google search on DBCC SHOWFILESTATS. This is what EM is using. Not documented, so all usual
warnings apply.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mangai" <anonymous@.discussions.microsoft.com> wrote in message
news:08fc01c3ad95$4cf28380$a301280a@.phx.gbl...
> In the SQL EM, we are able to get the data file size with
> used space and free space details. Similarly for the log
> file also.
> We have the dbcc sqlperf command to find out the log file
> used space details. Is there any way to find out the
> data file size? sp_helpfile just gives the total size of
> the data file wherein sp_spaceused is also not
> distinguishing between used and free space.
> Could anyone help me out in this?

Data File Remaining Space

I am running SQL 2000. I have a data file set up with restricted growth. Is
there a stored procedure or script available that I can schedule or run
when I want manually that will give me the space remaining in the data file?
Message posted via http://www.sqlmonster.com
Checkout sp_spaceused in BOL.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQLMonster.c om...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.sqlmonster.com
|||sp_helpdb YourDBNameHere
gives some information regarding database and file size.
Keith
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQLMonster.c om...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.sqlmonster.com

Data File Remaining Space

I am running SQL 2000. I have a data file set up with restricted growth. Is
there a stored procedure or script available that I can schedule or run
when I want manually that will give me the space remaining in the data file?
Message posted via http://www.droptable.comCheckout sp_spaceused in BOL.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQ
droptable.com...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.droptable.com|||sp_helpdb YourDBNameHere
gives some information regarding database and file size.
Keith
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQ
droptable.com...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.droptable.com

Data File Remaining Space

I am running SQL 2000. I have a data file set up with restricted growth. Is
there a stored procedure or script available that I can schedule or run
when I want manually that will give me the space remaining in the data file?
--
Message posted via http://www.sqlmonster.comCheckout sp_spaceused in BOL.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQLMonster.com...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.sqlmonster.com|||sp_helpdb YourDBNameHere
gives some information regarding database and file size.
--
Keith
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQLMonster.com...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.sqlmonster.comsql

data base size, help!

The size of an sql server database has is 1gig and the log file is 1 gig.
The server I'm on is running out of space and was wondering if you have ever
run into this problem and what I should do about it?
How can I move previous year records off the database?
Should I make a backup and then delete them from the current database?
Do I need the log file?
How do I reduce the size?
> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
> ever
> run into this problem and what I should do about it?
Buy a bigger disk? Why does your server have a 2GB volume?

> How can I move previous year records off the database?
You can use dozens of methods, including DTS them to another database and
then delete.

> Do I need the log file?
Yes, you need the log file. It's not there because it's pretty.

> How do I reduce the size?
http://www.aspfaq.com/2471
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||LU
http://support.microsoft.com/default...650-- how
to shrink tr log
http://support.microsoft.com/default...72318-- the
same shrink on sql2000
"LU" <LU@.discussions.microsoft.com> wrote in message
news:9765408F-F850-4531-8257-9F1A19867049@.microsoft.com...
> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
ever
> run into this problem and what I should do about it?
> How can I move previous year records off the database?
> Should I make a backup and then delete them from the current database?
> Do I need the log file?
> How do I reduce the size?

data base size, help!

The size of an sql server database has is 1gig and the log file is 1 gig.
The server I'm on is running out of space and was wondering if you have ever
run into this problem and what I should do about it?
How can I move previous year records off the database?
Should I make a backup and then delete them from the current database?
Do I need the log file?
How do I reduce the size?> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
> ever
> run into this problem and what I should do about it?
Buy a bigger disk? Why does your server have a 2GB volume?
> How can I move previous year records off the database?
You can use dozens of methods, including DTS them to another database and
then delete.
> Do I need the log file?
Yes, you need the log file. It's not there because it's pretty.
> How do I reduce the size?
http://www.aspfaq.com/2471
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||LU
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q256650-- how
to shrink tr log
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q272318-- the
same shrink on sql2000
"LU" <LU@.discussions.microsoft.com> wrote in message
news:9765408F-F850-4531-8257-9F1A19867049@.microsoft.com...
> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
ever
> run into this problem and what I should do about it?
> How can I move previous year records off the database?
> Should I make a backup and then delete them from the current database?
> Do I need the log file?
> How do I reduce the size?

data base size, help!

The size of an sql server database has is 1gig and the log file is 1 gig.
The server I'm on is running out of space and was wondering if you have ever
run into this problem and what I should do about it?
How can I move previous year records off the database?
Should I make a backup and then delete them from the current database?
Do I need the log file?
How do I reduce the size?> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
> ever
> run into this problem and what I should do about it?
Buy a bigger disk? Why does your server have a 2GB volume?

> How can I move previous year records off the database?
You can use dozens of methods, including DTS them to another database and
then delete.

> Do I need the log file?
Yes, you need the log file. It's not there because it's pretty.

> How do I reduce the size?
http://www.aspfaq.com/2471
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||LU
http://support.microsoft.com/defaul...6650-- h
ow
to shrink tr log
http://support.microsoft.com/defaul...272318-- the
same shrink on sql2000
"LU" <LU@.discussions.microsoft.com> wrote in message
news:9765408F-F850-4531-8257-9F1A19867049@.microsoft.com...
> The size of an sql server database has is 1gig and the log file is 1 gig.
> The server I'm on is running out of space and was wondering if you have
ever
> run into this problem and what I should do about it?
> How can I move previous year records off the database?
> Should I make a backup and then delete them from the current database?
> Do I need the log file?
> How do I reduce the size?

Daily backup problem

My SQL 2000 server's daily (full) backup started failing due to a lack of
disk space. This daily backup has been running for quite a while so im
surprised. The file system was full but it seems the old backup are not
being deleted even though the maintenance plan is set to ""Remove files older
than 1 day". Any ideas why this would happen? Solutions?
Paul
Old fulls, or old t-log backups? If t-log make sure every db in the plan is
set to full recovery model. If you have databases in Simple recovery, make
a separate plan for them without the t-log backup step
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>
> My SQL 2000 server's daily (full) backup started failing due to a lack of
> disk space. This daily backup has been running for quite a while so im
> surprised. The file system was full but it seems the old backup are not
> being deleted even though the maintenance plan is set to ""Remove files
> older
> than 1 day". Any ideas why this would happen? Solutions?
>
> Paul
>
|||Old fulls.
I only do full backups.
"Kevin3NF" wrote:

> Old fulls, or old t-log backups? If t-log make sure every db in the plan is
> set to full recovery model. If you have databases in Simple recovery, make
> a separate plan for them without the t-log backup step
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>
>
|||Paul,
The delete happens after the backup is taken. If you do not have enough
space for the backup it will not delete the old backup. Could this be your
problem?
Chris
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...[vbcol=seagreen]
> Old fulls.
> I only do full backups.
> "Kevin3NF" wrote:
|||Check to see if the account running the SQL Server Agent has permissions to
delete those fulls. Look for the maintenance plan logs in the \LOG
directory (same location as the ERRORLOG file(s) )
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...[vbcol=seagreen]
> Old fulls.
> I only do full backups.
> "Kevin3NF" wrote:
|||No there is enough space for 3 days of backups
"Chris Wood" wrote:

> Paul,
> The delete happens after the backup is taken. If you do not have enough
> space for the backup it will not delete the old backup. Could this be your
> problem?
> Chris
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
>
>
|||The account has full admin permission so that not a problem. Like I said this
has not been a problem for months. I don't see a log specifacally for
maintenance logs.
"Kevin3NF" wrote:

> Check to see if the account running the SQL Server Agent has permissions to
> delete those fulls. Look for the maintenance plan logs in the \LOG
> directory (same location as the ERRORLOG file(s) )
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
>
>
|||The account have the permissions to delete these file. No problem there. I
don't see a maintenance plan specific log file.
Paul
"Kevin3NF" wrote:

> Check to see if the account running the SQL Server Agent has permissions to
> delete those fulls. Look for the maintenance plan logs in the \LOG
> directory (same location as the ERRORLOG file(s) )
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
>
>
|||Add a "text file" option in the Reporting tab of the Database maintenance
GUI (SQL 2000)
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:8670769E-BF6B-4C1F-BE67-84C830DBF144@.microsoft.com...[vbcol=seagreen]
> The account have the permissions to delete these file. No problem there. I
> don't see a maintenance plan specific log file.
> Paul
> "Kevin3NF" wrote:

Daily backup problem

My SQL 2000 server's daily (full) backup started failing due to a lack of
disk space. This daily backup has been running for quite a while so im
surprised. The file system was full but it seems the old backup are not
being deleted even though the maintenance plan is set to ""Remove files older
than 1 day". Any ideas why this would happen? Solutions?
PaulOld fulls, or old t-log backups? If t-log make sure every db in the plan is
set to full recovery model. If you have databases in Simple recovery, make
a separate plan for them without the t-log backup step
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>
> My SQL 2000 server's daily (full) backup started failing due to a lack of
> disk space. This daily backup has been running for quite a while so im
> surprised. The file system was full but it seems the old backup are not
> being deleted even though the maintenance plan is set to ""Remove files
> older
> than 1 day". Any ideas why this would happen? Solutions?
>
> Paul
>|||Old fulls.
I only do full backups.
"Kevin3NF" wrote:
> Old fulls, or old t-log backups? If t-log make sure every db in the plan is
> set to full recovery model. If you have databases in Simple recovery, make
> a separate plan for them without the t-log backup step
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
> >
> >
> > My SQL 2000 server's daily (full) backup started failing due to a lack of
> > disk space. This daily backup has been running for quite a while so im
> > surprised. The file system was full but it seems the old backup are not
> > being deleted even though the maintenance plan is set to ""Remove files
> > older
> > than 1 day". Any ideas why this would happen? Solutions?
> >
> >
> >
> > Paul
> >
>
>|||Paul,
The delete happens after the backup is taken. If you do not have enough
space for the backup it will not delete the old backup. Could this be your
problem?
Chris
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> Old fulls.
> I only do full backups.
> "Kevin3NF" wrote:
>> Old fulls, or old t-log backups? If t-log make sure every db in the plan
>> is
>> set to full recovery model. If you have databases in Simple recovery,
>> make
>> a separate plan for them without the t-log backup step
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
>> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>> >
>> >
>> > My SQL 2000 server's daily (full) backup started failing due to a lack
>> > of
>> > disk space. This daily backup has been running for quite a while so im
>> > surprised. The file system was full but it seems the old backup are
>> > not
>> > being deleted even though the maintenance plan is set to ""Remove files
>> > older
>> > than 1 day". Any ideas why this would happen? Solutions?
>> >
>> >
>> >
>> > Paul
>> >
>>|||Check to see if the account running the SQL Server Agent has permissions to
delete those fulls. Look for the maintenance plan logs in the \LOG
directory (same location as the ERRORLOG file(s) )
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> Old fulls.
> I only do full backups.
> "Kevin3NF" wrote:
>> Old fulls, or old t-log backups? If t-log make sure every db in the plan
>> is
>> set to full recovery model. If you have databases in Simple recovery,
>> make
>> a separate plan for them without the t-log backup step
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
>> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>> >
>> >
>> > My SQL 2000 server's daily (full) backup started failing due to a lack
>> > of
>> > disk space. This daily backup has been running for quite a while so im
>> > surprised. The file system was full but it seems the old backup are
>> > not
>> > being deleted even though the maintenance plan is set to ""Remove files
>> > older
>> > than 1 day". Any ideas why this would happen? Solutions?
>> >
>> >
>> >
>> > Paul
>> >
>>|||No there is enough space for 3 days of backups
"Chris Wood" wrote:
> Paul,
> The delete happens after the backup is taken. If you do not have enough
> space for the backup it will not delete the old backup. Could this be your
> problem?
> Chris
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> > Old fulls.
> > I only do full backups.
> >
> > "Kevin3NF" wrote:
> >
> >> Old fulls, or old t-log backups? If t-log make sure every db in the plan
> >> is
> >> set to full recovery model. If you have databases in Simple recovery,
> >> make
> >> a separate plan for them without the t-log backup step
> >>
> >> --
> >>
> >> Kevin3NF
> >> SQL Server dude
> >>
> >> You want fries with that?
> >> http://kevin3nf.blogspot.com/
> >>
> >> I only check the newsgroups during work hours, M-F.
> >> Hit my blog and the contact links if necessary...I may be available.
> >>
> >>
> >>
> >> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> >> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
> >> >
> >> >
> >> > My SQL 2000 server's daily (full) backup started failing due to a lack
> >> > of
> >> > disk space. This daily backup has been running for quite a while so im
> >> > surprised. The file system was full but it seems the old backup are
> >> > not
> >> > being deleted even though the maintenance plan is set to ""Remove files
> >> > older
> >> > than 1 day". Any ideas why this would happen? Solutions?
> >> >
> >> >
> >> >
> >> > Paul
> >> >
> >>
> >>
> >>
>
>|||The account has full admin permission so that not a problem. Like I said this
has not been a problem for months. I don't see a log specifacally for
maintenance logs.
"Kevin3NF" wrote:
> Check to see if the account running the SQL Server Agent has permissions to
> delete those fulls. Look for the maintenance plan logs in the \LOG
> directory (same location as the ERRORLOG file(s) )
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> > Old fulls.
> > I only do full backups.
> >
> > "Kevin3NF" wrote:
> >
> >> Old fulls, or old t-log backups? If t-log make sure every db in the plan
> >> is
> >> set to full recovery model. If you have databases in Simple recovery,
> >> make
> >> a separate plan for them without the t-log backup step
> >>
> >> --
> >>
> >> Kevin3NF
> >> SQL Server dude
> >>
> >> You want fries with that?
> >> http://kevin3nf.blogspot.com/
> >>
> >> I only check the newsgroups during work hours, M-F.
> >> Hit my blog and the contact links if necessary...I may be available.
> >>
> >>
> >>
> >> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> >> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
> >> >
> >> >
> >> > My SQL 2000 server's daily (full) backup started failing due to a lack
> >> > of
> >> > disk space. This daily backup has been running for quite a while so im
> >> > surprised. The file system was full but it seems the old backup are
> >> > not
> >> > being deleted even though the maintenance plan is set to ""Remove files
> >> > older
> >> > than 1 day". Any ideas why this would happen? Solutions?
> >> >
> >> >
> >> >
> >> > Paul
> >> >
> >>
> >>
> >>
>
>|||The account have the permissions to delete these file. No problem there. I
don't see a maintenance plan specific log file.
Paul
"Kevin3NF" wrote:
> Check to see if the account running the SQL Server Agent has permissions to
> delete those fulls. Look for the maintenance plan logs in the \LOG
> directory (same location as the ERRORLOG file(s) )
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> > Old fulls.
> > I only do full backups.
> >
> > "Kevin3NF" wrote:
> >
> >> Old fulls, or old t-log backups? If t-log make sure every db in the plan
> >> is
> >> set to full recovery model. If you have databases in Simple recovery,
> >> make
> >> a separate plan for them without the t-log backup step
> >>
> >> --
> >>
> >> Kevin3NF
> >> SQL Server dude
> >>
> >> You want fries with that?
> >> http://kevin3nf.blogspot.com/
> >>
> >> I only check the newsgroups during work hours, M-F.
> >> Hit my blog and the contact links if necessary...I may be available.
> >>
> >>
> >>
> >> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> >> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
> >> >
> >> >
> >> > My SQL 2000 server's daily (full) backup started failing due to a lack
> >> > of
> >> > disk space. This daily backup has been running for quite a while so im
> >> > surprised. The file system was full but it seems the old backup are
> >> > not
> >> > being deleted even though the maintenance plan is set to ""Remove files
> >> > older
> >> > than 1 day". Any ideas why this would happen? Solutions?
> >> >
> >> >
> >> >
> >> > Paul
> >> >
> >>
> >>
> >>
>
>|||Add a "text file" option in the Reporting tab of the Database maintenance
GUI (SQL 2000)
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:8670769E-BF6B-4C1F-BE67-84C830DBF144@.microsoft.com...
> The account have the permissions to delete these file. No problem there. I
> don't see a maintenance plan specific log file.
> Paul
> "Kevin3NF" wrote:
>> Check to see if the account running the SQL Server Agent has permissions
>> to
>> delete those fulls. Look for the maintenance plan logs in the \LOG
>> directory (same location as the ERRORLOG file(s) )
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
>> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
>> > Old fulls.
>> > I only do full backups.
>> >
>> > "Kevin3NF" wrote:
>> >
>> >> Old fulls, or old t-log backups? If t-log make sure every db in the
>> >> plan
>> >> is
>> >> set to full recovery model. If you have databases in Simple recovery,
>> >> make
>> >> a separate plan for them without the t-log backup step
>> >>
>> >> --
>> >>
>> >> Kevin3NF
>> >> SQL Server dude
>> >>
>> >> You want fries with that?
>> >> http://kevin3nf.blogspot.com/
>> >>
>> >> I only check the newsgroups during work hours, M-F.
>> >> Hit my blog and the contact links if necessary...I may be available.
>> >>
>> >>
>> >>
>> >> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
>> >> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>> >> >
>> >> >
>> >> > My SQL 2000 server's daily (full) backup started failing due to a
>> >> > lack
>> >> > of
>> >> > disk space. This daily backup has been running for quite a while so
>> >> > im
>> >> > surprised. The file system was full but it seems the old backup are
>> >> > not
>> >> > being deleted even though the maintenance plan is set to ""Remove
>> >> > files
>> >> > older
>> >> > than 1 day". Any ideas why this would happen? Solutions?
>> >> >
>> >> >
>> >> >
>> >> > Paul
>> >> >
>> >>
>> >>
>> >>
>>

CXPACKET, NETWORKIO, PAGELATCH_EX on server

I have a server with 300Gb+ of disk space on a SAN, with a database 55Gb for data and 15Gb for tranx log. SQL Server 2000 w/SP3 is installed. Whenever I run any query (simple, complex, short, long, using indexes or not) the server current activity shows many instances of NETWORKIO, PAGELATCH_EX, CXPACKET and other wait types on several processes. In Profiler, 500,000+ events were recorded within a 60 second timeframe. Someone else is responsible for "infrastructure" so before I go with recommendations to fix my issues, I would like as much accurate info as possible about the cause. This is supposed to be a relatively low-activity server. My understanding is serious disk subsystem issues would cause all these wait types together. But why would this happen when running a "select x, y, z from table" where the table contains < 250000 records and x,y,z make up a covering index. Additionally, I ran a relatively simple query, two tables, inner join, with properly defined indexes and went home to let it run. I returned and it had been running for 18 hours - I finally ran out of patience and stopped it. Any ideas?And you just went home? 2 possibilities:

1. You've been blocked
2. Too many rows to return and the bottleneck is network and your workstation memory|||yes, i went home. :)

the query that ran for 18 hours was an update, and would have affected fewer than 300k records, which is not an issue when running on my local instance of sql.

there are no deadlocks for any of my processes; just the wait types.|||Can you post the query and the table(s) DDL?|||I've also had this kind of issue on a multiprocessor system. In my case, the update/select just took a *very* long time (but it did finish), while sysprocesses was throwing cxpackets and all that. Sometimes I could rewrite the query, in others I just had to wait.

Next to that: I found that sometimes it helps a lot to recreate the indices of a table. I found this out when it took 15 minutes to return on a simple query. Considering the query, I thought it took way too long. Showplan indicated a full table scan, it didn't use an index while I had a perfect index for that query. Hinting didn't help. So, I reindexed. After that, the query started using the index and voila... just a sec.