Database Size question

Hi DBA's -

Please comment on my understanding of this concept -

The current size of the database I am building is 321 GB. Of the 321 GB, 175 GB is the size of the actual data. The remainder of the size is the unallocated space (Space that is not reserved for any objects). I used the sp_spaceused to obtain the statistics.

When a full backup occurs it is the portion containing the data (175 GB) that gets backed up along with the size information of the overall database (321 GB).

Now say I want to test my backup solution on a test machine.

When a restore of the database would occur, the size of the restored database would be 321 GB and not 175 GB which is the size of the actual data. So restore the database successfully I would need a test machine that would accommodate the database size (321 GB) and not just the actual data size (175 GB).

Please add your expert comments.

Thanks.utterly true, the size of the backup set will be around 175 GB, but the size of restored db on test server will be exactly the same as on production machine.
mojza|||If you're looking to save space you could so in one of two ways, shrink your source database before you back it up - won't reduce the size of your backup file, but will reduce the size of the restored database - or just shrink the restored database. I'm not a big proponent of shrinking databases - if it needed that space once, it will typically need it again, if it's shrunk, it will have to spend processor and memory to get it back to the proper size. Just my $.02 :)

Nick

Database Size Limitations

Hey all,
Is there any way that I can find the current size limitation (eg, MSDE = 2gb
limit) of a database from a db query / DMO dll call etc?
ThanksYou can get the edition and product information with these:
SELECT SERVERPROPERTY('productversion') AS [ProductVersion]
SELECT SERVERPROPERTY('ProductLevel') AS [ProductLevel]
SELECT SERVERPROPERTY('Edition') AS [Edition]
But there is no bit that I know of that you can see for the size limitation.
It's pretty simple though if it is MSDE it is 2GB, if it is SQLExpress it is
4GB otherwise there is no limit.
Andrew J. Kelly SQL MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:9F961CBD-4303-49DA-A8D8-8E2C63296BB5@.microsoft.com...
> Hey all,
> Is there any way that I can find the current size limitation (eg, MSDE =
> 2gb
> limit) of a database from a db query / DMO dll call etc?
> Thanks|||Thanks Andrew, but which bit out of the server results would show what
version the DB is running on?
I understand which DB's have limits, but I cant establish a way to find out
which type of DB system is being used by our customers in order to impose a
limit check.
Any help would be great! Thanks!
"Andrew J. Kelly" wrote:

> You can get the edition and product information with these:
>
> SELECT SERVERPROPERTY('productversion') AS [ProductVersion]
> SELECT SERVERPROPERTY('ProductLevel') AS [ProductLevel]
> SELECT SERVERPROPERTY('Edition') AS [Edition]
> But there is no bit that I know of that you can see for the size limitatio
n.
> It's pretty simple though if it is MSDE it is 2GB, if it is SQLExpress it
is
> 4GB otherwise there is no limit.
> --
> Andrew J. Kelly SQL MVP
>
> "-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
> news:9F961CBD-4303-49DA-A8D8-8E2C63296BB5@.microsoft.com...
>
>|||I am not sure i am understanding you properly. Are you asking how do you
determine if they are running MSDE or not? Is so then just run
SERVERPROPERTY('Edition') to see. You can check BooksOnLine under this
command for the proper results.
Andrew J. Kelly SQL MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:44A7095B-F895-49DD-AA34-28FFE7F4DC1E@.microsoft.com...
> Thanks Andrew, but which bit out of the server results would show what
> version the DB is running on?
> I understand which DB's have limits, but I cant establish a way to find
> out
> which type of DB system is being used by our customers in order to impose
> a
> limit check.
> Any help would be great! Thanks!
> "Andrew J. Kelly" wrote:
>|||Yeah, thats what im after.
When trying it out, ive got an MSDE installation locally and a full SQL 2000
on a server, but both return 'Developer Edition' from the 'Edition' server
property, so I assumed that this wasn't specific enough?
"Andrew J. Kelly" wrote:

> I am not sure i am understanding you properly. Are you asking how do you
> determine if they are running MSDE or not? Is so then just run
> SERVERPROPERTY('Edition') to see. You can check BooksOnLine under this
> command for the proper results.
>
> --
> Andrew J. Kelly SQL MVP
>
> "-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
> news:44A7095B-F895-49DD-AA34-28FFE7F4DC1E@.microsoft.com...
>
>|||Hmmm. I can see the Full version showing Developer if that what it is but I
would expect MSDE to show DeskTop. Are you sure you were pointing to the
correct instance? What does "engine edition" show?
Andrew J. Kelly SQL MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:B68E0408-2DE2-4CEA-886A-00338CBA8645@.microsoft.com...
> Yeah, thats what im after.
> When trying it out, ive got an MSDE installation locally and a full SQL
> 2000
> on a server, but both return 'Developer Edition' from the 'Edition' server
> property, so I assumed that this wasn't specific enough?
> "Andrew J. Kelly" wrote:
>|||Ok.. im a dumbass! (Looking at the wrong DB!)
Now my 'Engine Edition' Shows 1 (Desktop Edition) which is what I would
expect.
Do we know if the 'SERVERPROPERTY(EngineEdition)' returns similar results
for SQL 2005 & SQLExpress?
"Andrew J. Kelly" wrote:

> Hmmm. I can see the Full version showing Developer if that what it is but
I
> would expect MSDE to show DeskTop. Are you sure you were pointing to the
> correct instance? What does "engine edition" show?
> --
> Andrew J. Kelly SQL MVP
>
> "-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
> news:B68E0408-2DE2-4CEA-886A-00338CBA8645@.microsoft.com...
>
>|||These are the possible results from both the Edition and Engine Edition
properties in 2005 BOL:
'Desktop Engine'
'Developer Edition'
'Enterprise Edition'
'Enterprise Evaluation Edition'
'Personal Edition'
'Standard Edition'
'Express Edition'
'Workgroup Edition'
'Windows Embedded SQL'
---
Database Engine edition of the instance of SQL Server installed on the
server.
1 = Personal or Desktop Engine
2 = Standard
3 = Enterprise (This is returned for Enterprise, Enterprise Evaluation, and
Developer.)
4 = Express
Andrew J. Kelly SQL MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:24868DAD-04FA-42B2-BF4E-7571BF6DCB06@.microsoft.com...
> Ok.. im a dumbass! (Looking at the wrong DB!)
> Now my 'Engine Edition' Shows 1 (Desktop Edition) which is what I would
> expect.
> Do we know if the 'SERVERPROPERTY(EngineEdition)' returns similar results
> for SQL 2005 & SQLExpress?
> "Andrew J. Kelly" wrote:
>sql

database size and raid configuration

Hi
I need to provide following information:
1. Size of all the current databases on all sql server (2000 and 2005). Is
these a query I can use to get this information.
2. Database size requirement for next three years.
3. Backup space requirements (I know which database require simple and which
transactional log database backups).
4. Test database space requirements (I know which databases require test
database).
5. New SQL server reporting services (Server space requirement), I know
which databases require a reporting server.
6. Recommendation for RAID for live and test databases including logging.
Thanks
ontario, canada
When i select size of files using sql server using
"select name,filename,size from sysaltfiles" I get size of files as
File one size: file1.mdf = 4976
File two size: file2.ldf = 2504
File three size file3.mdf = 1360
File four size file4.ldf = 13408
When I see the size of files in the disk using windows explorer I get
different size
File one size: 39804 kb
File two size: 20032 kb
File three size:10,880 kb
File four size: 107,264 KB
Why is that difference in file sizes?
ontario, canada
"db" wrote:

> Hi
> I need to provide following information:
> 1. Size of all the current databases on all sql server (2000 and 2005). Is
> these a query I can use to get this information.
> 2. Database size requirement for next three years.
> 3. Backup space requirements (I know which database require simple and which
> transactional log database backups).
> 4. Test database space requirements (I know which databases require test
> database).
> 5. New SQL server reporting services (Server space requirement), I know
> which databases require a reporting server.
> 6. Recommendation for RAID for live and test databases including logging.
> Thanks
> --
> ontario, canada
|||Thanks Tibor.
1. I am using sysaltfiles and sp_databases to get the Size of all the
current databases on all sql server (2000 and 2005). Looks like it works.
2. Database size requirement for next three years. For last 1.5 years size
of databases have increased by 50%. What do you think i should project for
next three years assuming no new applications?
3. Can I use a sql script to find the size of all backup files (.bak) for
the databases on the servers? If yes what?
4. Test database space requirements (I know which databases require test
database). What is the ideal size?
5. We will have new SQL server reporting server. How should i decide size
of the reporting server?
6. Recommendation for RAID for live and test databases. I would like to go
with maximum performance... ?
ontario, canada
"Tibor Karaszi" wrote:

> The unit for sysaltfiles is in pages (one page is 8KB).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:BCE5FEFC-57F7-44B8-B23F-10CED667E7FB@.microsoft.com...
>
|||2) I would plan on 50-75% growth per year based on your very limited
information.
3) I would use vbscript to scan directories and gather backup size
information. If you have never cleaned out msdb, you can find sizes for
backups there in one of the backupset... tables. See BOL for backupset and
it's related tables to get details.
4) We cannot guide you in this area without a good deal more information.
5) Again, need much more information.
6) Maximum performance would probably be RAID10, with lots of 15Krpm
spindles. You could perhaps get better read performance with RAID5, but
update/insert/delete performance will suffer. There is a LOT more to
disk/file configuration, btw!
BTW, I strongly recommend you hire an expert for a day or three to assist
you in your project. LOTS of ways to go astray here, and LOTS of variables
come into play.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"db" <db@.discussions.microsoft.com> wrote in message
news:CDA4EAF9-424E-4E0F-8164-EBE289ECC407@.microsoft.com...[vbcol=seagreen]
> Thanks Tibor.
> 1. I am using sysaltfiles and sp_databases to get the Size of all the
> current databases on all sql server (2000 and 2005). Looks like it works.
> 2. Database size requirement for next three years. For last 1.5 years size
> of databases have increased by 50%. What do you think i should project for
> next three years assuming no new applications?
> 3. Can I use a sql script to find the size of all backup files (.bak) for
> the databases on the servers? If yes what?
> 4. Test database space requirements (I know which databases require test
> database). What is the ideal size?
> 5. We will have new SQL server reporting server. How should i decide size
> of the reporting server?
> 6. Recommendation for RAID for live and test databases. I would like to go
> with maximum performance... ?
> --
> ontario, canada
>
> "Tibor Karaszi" wrote:

database size and raid configuration

Hi
I need to provide following information:
1. Size of all the current databases on all sql server (2000 and 2005). Is
these a query I can use to get this information.
2. Database size requirement for next three years.
3. Backup space requirements (I know which database require simple and which
transactional log database backups).
4. Test database space requirements (I know which databases require test
database).
5. New SQL server reporting services (Server space requirement), I know
which databases require a reporting server.
6. Recommendation for RAID for live and test databases including logging.
Thanks
--
ontario, canadaWhen i select size of files using sql server using
"select name,filename,size from sysaltfiles" I get size of files as
File one size: file1.mdf = 4976
File two size: file2.ldf = 2504
File three size file3.mdf = 1360
File four size file4.ldf = 13408
When I see the size of files in the disk using windows explorer I get
different size
File one size: 39804 kb
File two size: 20032 kb
File three size:10,880 kb
File four size: 107,264 KB
Why is that difference in file sizes?
--
ontario, canada
"db" wrote:
> Hi
> I need to provide following information:
> 1. Size of all the current databases on all sql server (2000 and 2005). Is
> these a query I can use to get this information.
> 2. Database size requirement for next three years.
> 3. Backup space requirements (I know which database require simple and which
> transactional log database backups).
> 4. Test database space requirements (I know which databases require test
> database).
> 5. New SQL server reporting services (Server space requirement), I know
> which databases require a reporting server.
> 6. Recommendation for RAID for live and test databases including logging.
> Thanks
> --
> ontario, canada|||The unit for sysaltfiles is in pages (one page is 8KB).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"db" <db@.discussions.microsoft.com> wrote in message
news:BCE5FEFC-57F7-44B8-B23F-10CED667E7FB@.microsoft.com...
> When i select size of files using sql server using
> "select name,filename,size from sysaltfiles" I get size of files as
> File one size: file1.mdf = 4976
> File two size: file2.ldf = 2504
> File three size file3.mdf = 1360
> File four size file4.ldf = 13408
> When I see the size of files in the disk using windows explorer I get
> different size
> File one size: 39804 kb
> File two size: 20032 kb
> File three size:10,880 kb
> File four size: 107,264 KB
> Why is that difference in file sizes?
> --
> ontario, canada
>
> "db" wrote:
>> Hi
>> I need to provide following information:
>> 1. Size of all the current databases on all sql server (2000 and 2005). Is
>> these a query I can use to get this information.
>> 2. Database size requirement for next three years.
>> 3. Backup space requirements (I know which database require simple and which
>> transactional log database backups).
>> 4. Test database space requirements (I know which databases require test
>> database).
>> 5. New SQL server reporting services (Server space requirement), I know
>> which databases require a reporting server.
>> 6. Recommendation for RAID for live and test databases including logging.
>> Thanks
>> --
>> ontario, canada|||Thanks Tibor.
1. I am using sysaltfiles and sp_databases to get the Size of all the
current databases on all sql server (2000 and 2005). Looks like it works.
2. Database size requirement for next three years. For last 1.5 years size
of databases have increased by 50%. What do you think i should project for
next three years assuming no new applications'
3. Can I use a sql script to find the size of all backup files (.bak) for
the databases on the servers? If yes what?
4. Test database space requirements (I know which databases require test
database). What is the ideal size'
5. We will have new SQL server reporting server. How should i decide size
of the reporting server?
6. Recommendation for RAID for live and test databases. I would like to go
with maximum performance... ?
--
ontario, canada
"Tibor Karaszi" wrote:
> The unit for sysaltfiles is in pages (one page is 8KB).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:BCE5FEFC-57F7-44B8-B23F-10CED667E7FB@.microsoft.com...
> > When i select size of files using sql server using
> > "select name,filename,size from sysaltfiles" I get size of files as
> >
> > File one size: file1.mdf = 4976
> > File two size: file2.ldf = 2504
> > File three size file3.mdf = 1360
> > File four size file4.ldf = 13408
> >
> > When I see the size of files in the disk using windows explorer I get
> > different size
> >
> > File one size: 39804 kb
> > File two size: 20032 kb
> > File three size:10,880 kb
> > File four size: 107,264 KB
> >
> > Why is that difference in file sizes?
> > --
> > ontario, canada
> >
> >
> > "db" wrote:
> >
> >> Hi
> >>
> >> I need to provide following information:
> >> 1. Size of all the current databases on all sql server (2000 and 2005). Is
> >> these a query I can use to get this information.
> >> 2. Database size requirement for next three years.
> >> 3. Backup space requirements (I know which database require simple and which
> >> transactional log database backups).
> >> 4. Test database space requirements (I know which databases require test
> >> database).
> >> 5. New SQL server reporting services (Server space requirement), I know
> >> which databases require a reporting server.
> >> 6. Recommendation for RAID for live and test databases including logging.
> >>
> >> Thanks
> >> --
> >> ontario, canada
>|||2) I would plan on 50-75% growth per year based on your very limited
information.
3) I would use vbscript to scan directories and gather backup size
information. If you have never cleaned out msdb, you can find sizes for
backups there in one of the backupset... tables. See BOL for backupset and
it's related tables to get details.
4) We cannot guide you in this area without a good deal more information.
5) Again, need much more information.
6) Maximum performance would probably be RAID10, with lots of 15Krpm
spindles. You could perhaps get better read performance with RAID5, but
update/insert/delete performance will suffer. There is a LOT more to
disk/file configuration, btw!
BTW, I strongly recommend you hire an expert for a day or three to assist
you in your project. LOTS of ways to go astray here, and LOTS of variables
come into play.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"db" <db@.discussions.microsoft.com> wrote in message
news:CDA4EAF9-424E-4E0F-8164-EBE289ECC407@.microsoft.com...
> Thanks Tibor.
> 1. I am using sysaltfiles and sp_databases to get the Size of all the
> current databases on all sql server (2000 and 2005). Looks like it works.
> 2. Database size requirement for next three years. For last 1.5 years size
> of databases have increased by 50%. What do you think i should project for
> next three years assuming no new applications'
> 3. Can I use a sql script to find the size of all backup files (.bak) for
> the databases on the servers? If yes what?
> 4. Test database space requirements (I know which databases require test
> database). What is the ideal size'
> 5. We will have new SQL server reporting server. How should i decide size
> of the reporting server?
> 6. Recommendation for RAID for live and test databases. I would like to go
> with maximum performance... ?
> --
> ontario, canada
>
> "Tibor Karaszi" wrote:
>> The unit for sysaltfiles is in pages (one page is 8KB).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "db" <db@.discussions.microsoft.com> wrote in message
>> news:BCE5FEFC-57F7-44B8-B23F-10CED667E7FB@.microsoft.com...
>> > When i select size of files using sql server using
>> > "select name,filename,size from sysaltfiles" I get size of files as
>> >
>> > File one size: file1.mdf = 4976
>> > File two size: file2.ldf = 2504
>> > File three size file3.mdf = 1360
>> > File four size file4.ldf = 13408
>> >
>> > When I see the size of files in the disk using windows explorer I get
>> > different size
>> >
>> > File one size: 39804 kb
>> > File two size: 20032 kb
>> > File three size:10,880 kb
>> > File four size: 107,264 KB
>> >
>> > Why is that difference in file sizes?
>> > --
>> > ontario, canada
>> >
>> >
>> > "db" wrote:
>> >
>> >> Hi
>> >>
>> >> I need to provide following information:
>> >> 1. Size of all the current databases on all sql server (2000 and
>> >> 2005). Is
>> >> these a query I can use to get this information.
>> >> 2. Database size requirement for next three years.
>> >> 3. Backup space requirements (I know which database require simple and
>> >> which
>> >> transactional log database backups).
>> >> 4. Test database space requirements (I know which databases require
>> >> test
>> >> database).
>> >> 5. New SQL server reporting services (Server space requirement), I
>> >> know
>> >> which databases require a reporting server.
>> >> 6. Recommendation for RAID for live and test databases including
>> >> logging.
>> >>
>> >> Thanks
>> >> --
>> >> ontario, canadasql

Database Size - how to get

Could anybody remind please, how can I get the current database size having
the full access to this database and server? What's the SQL query?
What else helpful about the current database can I get using queries? Load,
statistics, what else?
Thanks,
Just D.Check out sp_spaceused in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Just D." <no@.spam.please> wrote in message
news:D_o%d.57888$xt.46304@.fed1read07...
Could anybody remind please, how can I get the current database size having
the full access to this database and server? What's the SQL query?
What else helpful about the current database can I get using queries? Load,
statistics, what else?
Thanks,
Just D.

Database server will not expand mdf or ndf files

Has anyone had an issue with SQL Server not being able to expand
against a RAID 5 file system? My current configuration is that the
server is started and stopped using the local system account. I have
only one database (besides the master, model,etc)on the server. What
has happend to me several times is that the primary database in
question try's to expand the main datafile for the database (.mdf). I
setup the database to not expand automatically initially so that I can
be sure that we have enough file system space. Becuase of problems with
the application I decided to automatically expand. The other day the
developers came to me indicating that the databse was full and needed
to be expanded. Knowing that the database was in automatic expanding
more I was surprise to hear this. I went into EM and attempted to
expand first the log and it would not indicating that it there was an
issue in attempting to do so. I have never heard of a database not
being able to expand. I ran DBCC's, etc and it came up clean. I tried
to back the database up to disk and it would not backup. I finally had
to rename the datbase and rebuild it using DTS and scripts. I thought
I had fixed it only to find out today that it (again) won't expand. I
renamed the datbase and then tried taking an older backup file and
restore it and it would not restore. This problem seems to be related
to the file system but how I do not know.

So, I am ready to run rebuild master but I have sone this before only
to have this come back on me. I am at a complete loss. In the past I
have had to rebuild the entire server and database from scratch. The
only problem is that this has been done 3 times now with no complete
solution or explaination. If any of you have seen this type of
behavior and know whats going on please, please let me know what you
think the case and solution is!"2centbob" wrote:

> Has anyone had an issue with SQL Server not being able to expand
> against a RAID 5 file system? My current configuration is that the
> server is started and stopped using the local system account. I have
> only one database (besides the master, model,etc)on the server. What
> has happend to me several times is that the primary database in
> question try's to expand the main datafile for the database (.mdf). I
> setup the database to not expand automatically initially so that I can
> be sure that we have enough file system space. Becuase of problems with
> the application I decided to automatically expand. The other day the
> developers came to me indicating that the databse was full and needed
> to be expanded. Knowing that the database was in automatic expanding
> more I was surprise to hear this. I went into EM and attempted to
> expand first the log and it would not indicating that it there was an
> issue in attempting to do so. I have never heard of a database not
> being able to expand. I ran DBCC's, etc and it came up clean. I tried
> to back the database up to disk and it would not backup. I finally had
> to rename the datbase and rebuild it using DTS and scripts. I thought
> I had fixed it only to find out today that it (again) won't expand. I
> renamed the datbase and then tried taking an older backup file and
> restore it and it would not restore. This problem seems to be related
> to the file system but how I do not know.

<snip
I don't know of issues specifically with RAID 5 (unless your RAID card has
gone bonkers), but here's a few guesses (mostly based on my trying to figure
out why the file system or something else would stop a file from expanding).

- Are you sure you have enough disk space? (I'm pretty that's not it and you
would have seen it, but better safe than sorry.) One place to look is
programs that might create huge temp files that eventually go away: we had a
server that ran multiple concurrent server processes. We had a heck of a
time figuring out why disk space seemingly came and went in huge chunks
until we realized that 3rd party code in our services was creating *huge*
temp files (because a few programmers didn't code for users requesting
reports with 4 million lines before control breaks :).

- Is your file system NTFS or FAT? Not being able to expand and then not
being able to backup or restore sounds fishy: could you be bumping into
FAT's file size limit? If I recall it's 4GB in FAT32 and 2GB in earlier FAT
versions.

- Are disk quotas enabled on the server? I've never even touched these in
Windows, so I have no idea where you would look... For that matter, does
your RAID hw/sw combo allow for any kind of quota?

- I'm pretty sure you already have, but in case you haven't, have you
checked the SQL Server logs and the OS event logs?

Good Luck,

Craig|||Thanks for your reply. In these cases its allways novce to have a
complete picture and that doesn't necessarly get conveyed sometimes.
So, a little more information is warrented. The application that uses
the database is a Java app sitting on a different server. The database
server has no application running on it. The application was written by
a vendor. Thier requirements require that the datbase owner have full
rights to the database, i.e., using sp_changedbowner to that user. If I
did not use that approach then the application had problems upon
installation and therefore would not properly install. So, as I said I
changed it. Prior to this expereince the database was left to expand as
it needed and it did with no issues. The two circumstances that I
refered in my earlier email: the file system filled up and the database
could not expand. In addition, the server could not be reached and so
we had to shut it down hard. When it came back up we could not use the
database nor could we back it up. We were forced to rebuild the server:
OS and SQL Server. Later, a similar incident happened again and we were
forced (again) to rebuild. This last time, I had an additional 40 GB
added so we would not have a file system space problem again. I put the
database and log into a non-expansion mode so that when the application
would not accidently consume all of the disk space. However, the
database hit the high water mark on the datafile and could not expand.
I was notofied and of the problem and went to expand the file and it
would not expand again. No you most of the information.

At this point I am starting to think that as long the database has file
space to expand into and is not resitricted in any way the application
would probably work alright. However, because "sa" does not own the
database, the database owner probably needs "sa" rights. This is just
conjecture at this point. Funny thing, when this happend, the last
time, the "TaskPad" information came up with an error saying it could
not display the information and wanted to me to stop running the rest of
the script. I am concered that OS files are being walked on somehow.

Boy, I never had this expereince using Sybase and I have never seen
anyting like it in Oracle as well. But then again those were Unix
databases that I worked on, and not Windows.

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Bob Schmitz (bschmitz4@.wi.rr.com) writes:
> Thanks for your reply. In these cases its allways novce to have a
> complete picture and that doesn't necessarly get conveyed sometimes.
> So, a little more information is warrented. The application that uses
> the database is a Java app sitting on a different server. The database
> server has no application running on it. The application was written by
> a vendor. Thier requirements require that the datbase owner have full
> rights to the database, i.e., using sp_changedbowner to that user. If I
> did not use that approach then the application had problems upon
> installation and therefore would not properly install. So, as I said I
> changed it. Prior to this expereince the database was left to expand as
> it needed and it did with no issues. The two circumstances that I
> refered in my earlier email: the file system filled up and the database
> could not expand. In addition, the server could not be reached and so
> we had to shut it down hard. When it came back up we could not use the
> database nor could we back it up. We were forced to rebuild the server:
> OS and SQL Server. Later, a similar incident happened again and we were
> forced (again) to rebuild. This last time, I had an additional 40 GB
> added so we would not have a file system space problem again. I put the
> database and log into a non-expansion mode so that when the application
> would not accidently consume all of the disk space. However, the
> database hit the high water mark on the datafile and could not expand.
> I was notofied and of the problem and went to expand the file and it
> would not expand again. No you most of the information.

A lots of words, but, frankly, not very much information.

First of all, who owns the database does not matter. Auto-grow will
work anyway.

Since you seem to have difficulties to explain what is going on, I would
like you to run sp_helpdb on your database and post the output. That
will at least give some minimum of information for us to work from.

I would also like you do a DIR on the disks where the data and log files
reside, and post the bottom lines from that output.

In your previous message you said that you could not backup the database,
but you never explained why. Did you get an error message? Or how did
you conclude that the backup wasn't working?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

Even though you mention EM! Are you using MSDE?
Do you have disk quotas?
Are you using mount points?

It may help if you posted the version
http://www.aspfaq.com/show.asp?id=2160.

John

"Bob Schmitz" <bschmitz4@.wi.rr.com> wrote in message
news:4204f360$1_2@.127.0.0.1...
> Thanks for your reply. In these cases its allways novce to have a
> complete picture and that doesn't necessarly get conveyed sometimes.
> So, a little more information is warrented. The application that uses
> the database is a Java app sitting on a different server. The database
> server has no application running on it. The application was written by
> a vendor. Thier requirements require that the datbase owner have full
> rights to the database, i.e., using sp_changedbowner to that user. If I
> did not use that approach then the application had problems upon
> installation and therefore would not properly install. So, as I said I
> changed it. Prior to this expereince the database was left to expand as
> it needed and it did with no issues. The two circumstances that I
> refered in my earlier email: the file system filled up and the database
> could not expand. In addition, the server could not be reached and so
> we had to shut it down hard. When it came back up we could not use the
> database nor could we back it up. We were forced to rebuild the server:
> OS and SQL Server. Later, a similar incident happened again and we were
> forced (again) to rebuild. This last time, I had an additional 40 GB
> added so we would not have a file system space problem again. I put the
> database and log into a non-expansion mode so that when the application
> would not accidently consume all of the disk space. However, the
> database hit the high water mark on the datafile and could not expand.
> I was notofied and of the problem and went to expand the file and it
> would not expand again. No you most of the information.
> At this point I am starting to think that as long the database has file
> space to expand into and is not resitricted in any way the application
> would probably work alright. However, because "sa" does not own the
> database, the database owner probably needs "sa" rights. This is just
> conjecture at this point. Funny thing, when this happend, the last
> time, the "TaskPad" information came up with an error saying it could
> not display the information and wanted to me to stop running the rest of
> the script. I am concered that OS files are being walked on somehow.
> Boy, I never had this expereince using Sybase and I have never seen
> anyting like it in Oracle as well. But then again those were Unix
> databases that I worked on, and not Windows.
> Thanks.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Thats becuase this was a very difficult and weird situation. I knew
that I would not be able to explain it all and some would have
questions. sp_helpdb is not the problem becuase it shows the database.
There are no errors in the logs except when I try to backup or if i
tried to restore the database in question. When I ran a dir on the
filesystem the database files and there sizes show that they have
expanded but the databsae does no reflect this.

Now, what I have doen since then is to blow away the master, model,
msdb, and tempdb. I then ran the rebuild.exe program. That seems to
have fixed the problem as after I reattached the database I was able to
expand but log and data.

2centbob

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Bob Schmitz (bschmitz4@.wi.rr.com) writes:
> Thats becuase this was a very difficult and weird situation. I knew
> that I would not be able to explain it all and some would have
> questions. sp_helpdb is not the problem becuase it shows the database.
> There are no errors in the logs except when I try to backup or if i
> tried to restore the database in question. When I ran a dir on the
> filesystem the database files and there sizes show that they have
> expanded but the databsae does no reflect this.
> Now, what I have doen since then is to blow away the master, model,
> msdb, and tempdb. I then ran the rebuild.exe program. That seems to
> have fixed the problem as after I reattached the database I was able to
> expand but log and data.

I strongly suspect that you put far more work into fix this that was
required.

However, since your choice is not to share the information I asked you
to, I'm afraid I can't help you with advice of what you should have done.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You can suspect all you want ... Unless you had been there working side
by side you don't know anything. Not only that, I resent your attitude
as though you know more than anyone else on this site. Please, in the
future, if you dont have something say other than criticize someone,
please refrain from responding. I don't need it and suspect others
don't need it as well.

For others: The end users were screaming to have this system back and so
my time was limited in responding. THE ONLY THING THAT HAS WORKED HAS
BEEN TO REBUILD THE MASTER DATABASE. Be that as it may, it works now,
thanks to all that replied.

2centbob

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Bob Schmitz (bschmitz4@.wi.rr.com) writes:
> You can suspect all you want ... Unless you had been there working side
> by side you don't know anything. Not only that, I resent your attitude
> as though you know more than anyone else on this site. Please, in the
> future, if you don't have something say other than criticize someone,
> please refrain from responding. I don't need it and suspect others
> don't need it as well.

You appeared to ask for help. And that's basically what I do here. Try
to help people. But often, I need more information about the case, so I
ask for that. It's true, that I have not been on your site, so I don't
know what happened. I have however been trying to find out, but you have
been very willing to give me the information that I have asked for. Of
course, you may do as you please. But you cannot really expect to get any
useful assistence that way.

And that is a piece of advice for the future when you have a need to
ask for help.

> For others: The end users were screaming to have this system back and so
> my time was limited in responding.

It may be better in a situation like this to open a case with Microsoft
support. It's certainly more expensive than a free forum like this one.
Then again, if they can help to reduce downtime, you get the money back
that way. Of course, also the support engineers will ask you questions
about the configuration, error messages etc.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

database restore date

How can I find when current database was restored?The restorehistory table (in msdb database) contains one row for each
restore operation.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Vodochnik" <vodochnik@.list.ru> wrote in message
news:8a63d75a.0306301506.1d86f9f0@.posting.google.com...
How can I find when current database was restored?

Database Options

What is the real difference between setting the database
options using A SET statement which applies to the current
connection only or specifying the setting as a database-
level default with ALTER DATABASE?When the option is set at the database level, that is the default setting
for all new connections.
The Set statement overrides the DB setting however...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"van" <vandenm2@.yahoo.com> wrote in message
news:16abe01c4480f$e8afa130$a501280a@.phx.gbl...
> What is the real difference between setting the database
> options using A SET statement which applies to the current
> connection only or specifying the setting as a database-
> level default with ALTER DATABASE?|||Well, one change is temporary for the current session, the other change is
permanent?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"van" <vandenm2@.yahoo.com> wrote in message
news:16abe01c4480f$e8afa130$a501280a@.phx.gbl...
> What is the real difference between setting the database
> options using A SET statement which applies to the current
> connection only or specifying the setting as a database-
> level default with ALTER DATABASE?