How to determine the free space in a datafile

Hi,
I'm trying to figure out the amount of free space in the datafiles but
since sys.dm_db_file_space_usage works only for temp, I dont know which
table to join on sys.database_files or sys.master_files.
Can anyone help me please?Hi
Have you tried
exec sp_helpfile ?
<ulli77@.web.de> wrote in message
news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...
> Hi,
> I'm trying to figure out the amount of free space in the datafiles but
> since sys.dm_db_file_space_usage works only for temp, I dont know which
> table to join on sys.database_files or sys.master_files.
> Can anyone help me please?
>|||for data files look at
dbcc showfilestats
This is undocumented dbcc command.
It shows total size and used size in extents.
If you multiply it by 64 you will get size in KB.
For log files
look at
dbcc sqlperf(logspace)
Regards
Amish shah
http://shahamishm.tripod.com
*** Sent via Developersdex http://www.codecomments.com ***|||Uri Dimant schrieb:

> Hi
> Have you tried
> exec sp_helpfile ?
Yes, I have but it doesnt show any information about the usage.
thank you[vbcol=seagreen]
> <ulli77@.web.de> wrote in message
> news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...|||Amish Shah schrieb:

> for data files look at
> dbcc showfilestats
> This is undocumented dbcc command.
> It shows total size and used size in extents.
> If you multiply it by 64 you will get size in KB.
> For log files
> look at
> dbcc sqlperf(logspace)
>
> Regards
> Amish shah
> http://shahamishm.tripod.com
> *** Sent via Developersdex http://www.codecomments.com ***
Thank you Amish,
that really does the job but how do i get this information into a
table?
like:
select into table ( dbcc showfilestat )
thats what i would like for capacity planning and charting.
Thank you very much, Ulli|||Hi Ulli
You have to create your own table. By looking at the output of dbcc
showfilestats you can determine the number of columns and an appropriate
datatype.
Once you have the table created, you can do the following:
INSERT INT MyFileStatsTable
EXEC ('DBCC showfilestats')
HTH
Kalen Delaney, SQL Server MVP
<ulli77@.web.de> wrote in message
news:1154272978.472934.27330@.b28g2000cwb.googlegroups.com...
> Amish Shah schrieb:
>
>
> Thank you Amish,
> that really does the job but how do i get this information into a
> table?
> like:
> select into table ( dbcc showfilestat )
> thats what i would like for capacity planning and charting.
> Thank you very much, Ulli
>

How to determine the free space in a datafile

Hi,
I'm trying to figure out the amount of free space in the datafiles but
since sys.dm_db_file_space_usage works only for temp, I dont know which
table to join on sys.database_files or sys.master_files.
Can anyone help me please?Hi
Have you tried
exec sp_helpfile ?
<ulli77@.web.de> wrote in message
news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...
> Hi,
> I'm trying to figure out the amount of free space in the datafiles but
> since sys.dm_db_file_space_usage works only for temp, I dont know which
> table to join on sys.database_files or sys.master_files.
> Can anyone help me please?
>|||Uri Dimant schrieb:
> Hi
> Have you tried
> exec sp_helpfile ?
Yes, I have but it doesnt show any information about the usage.
thank you
> <ulli77@.web.de> wrote in message
> news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...
> > Hi,
> >
> > I'm trying to figure out the amount of free space in the datafiles but
> > since sys.dm_db_file_space_usage works only for temp, I dont know which
> > table to join on sys.database_files or sys.master_files.
> >
> > Can anyone help me please?
> >sql

How to detect BCP success/fail in batch file?

We are calling BCP from a batch file but cannot figure out how to detect
whether it was successful or if it failed. With osql we use EXIT() with a
value to indicate success or failure. Is there a way to do this with BCP? Or
does it report success or failure to the calling process or set an operating
system error value?
TIA
Michael MacGregor
Database Architect
Michael MacGregor (macnoknifespam@.noemailspam.com) writes:
> We are calling BCP from a batch file but cannot figure out how to detect
> whether it was successful or if it failed. With osql we use EXIT() with
> a value to indicate success or failure. Is there a way to do this with
> BCP? Or does it report success or failure to the calling process or set
> an operating system error value?
BCP does indeed set %ERRORLEVEL%. However, your and BCP's idea of what
is an error may not be the same. BCP will return with an error status
if for instance the data file cannot be found. It may also set a return
status if the end of file is in the middle of a record (which usually
means that your format specification is wrong). But I seem to recall
that if one more rows fail to import, that it does not set ERRORLEVEL -
not even if all records fail.
You can use the -e option to get error information into a file, this
captures problems with individual records.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Aha! When I check the ERRORLEVEL value, whether the BCP IN succeeds or fails
(failure caused by PK constraint), the value is the same, 0. However I
realised that I might not be using the latest version of BCP and checked, it
was BCP v8, so I updated my PATH to use BCP v9 and then it sets the
ERRORLEVEL correctly.
So I guess it's best to use the latest version.
Michael MacGregor
Database Architect
|||Michael MacGregor (macnoknifespam@.noemailspam.com) writes:
> Aha! When I check the ERRORLEVEL value, whether the BCP IN succeeds or
> fails (failure caused by PK constraint), the value is the same, 0.
> However I realised that I might not be using the latest version of BCP
> and checked, it was BCP v8, so I updated my PATH to use BCP v9 and then
> it sets the ERRORLEVEL correctly.
Interesting. That's seems to be an improvement.
However, I would test a lot more error situations to see if all set
ERRORLEVEL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I've got round the immediate problem by using a SQL script incorporating
BULK INSERT. It does the job with the error handling I need.
Although I will take your advice under due consideration as I have another
process that needs to be "error aware" that also uses BCP right now, and
also is using v8.0. So I will have to update that to v9.0 and test it to
make sure it catches the types of errors that tend to occur.
Thanks.
Michael MacGregor
Database Architect

How to delete a table in Visual Studio 2005

I need to delete a database table from my database. I can not figure out how to do it though. I tried deleting individual columns but go an error message "Drop Failed for column 'TPListHistoryId ' (Microsoft.SqlServer.Smo)

If your DB is attached to your project in VS2005 then you can open it up in server explorer and rightclick onthe table and choose delete.

If you are using sqlserver express management studio then try DROP table <tablename>

|||

Do you need to do this from within an ASP page?

The syntax is:

DROP TABLE <tablename>

You can do this directly in the query analyzer if you open up a query window. Or you could do it from within an ASP.net page. In this case, the syntax is:

SqlConnection conn =new SqlConnection(string here>);
SqlCommand cmd =new SqlCommand("DROP TABLE <tablename>", conn);
cmd.ExecuteNonQuery();

Note: If this table is referenced by foreign constraints, then you cannot simply drop this table without disabling the constraints first. To do this, you must be absolutely positive that you know what you're doing, or you'll possibly end up breaking the integrity of your database.

Good luck!

|||

I am working strictly out of SQL Server 2005. Not visual studio. Sorry I made a mistake. So how can I delete the table? Do you know?

|||

Sorry. I meant to say that I am working directly out of SQL Server 2005. So I messed up in my post. I tried dropping the table in SQL Server 2005, but I get the following error message:

Msg 3726, Level 16, State 1, Line 1

Could not drop object 'Location' because it is referenced by a FOREIGN KEY constraint.

|||

You would need to drop the child tables first. What that message is telling you is that you can't drop the table because there is another table that has a foreign key constraint tide to it. This is put in place to help maintain data integrety.

|||

Check out this previous post for a discussion on how you do a cascading delete from the bottom up:

http://forums.asp.net/p/1144446/1854498.aspx