When I go to Shrink a file, the screen shows me the currently allocated and
the free space available for the selected database. Is there a view or
system stored procedure that I can use to retrieve this information?
I am working with SQL 2005. I have been playing with the sp_spaceused
stored procedure but can't seem to get just the data file info.
Thanks,
Will this work with SQL 2000 also?
Thanks,
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...[vbcol=seagreen]
> Hello,
> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
> you need info.
> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
> UsedExtents int, Name sysname, FileName nchar(520))
> Insert #tmpspc EXEC ('dbcc showfilestats')
>
> SELECT
> s.name AS [Name],
> s.physical_name AS [FileName],
> s.size * CONVERT(float,8) AS [Size],
> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
> s.file_id AS [ID]
> FROM
> sys.filegroups AS g
> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id =
> db_id()
> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
> WHERE
> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
> ORDER BY
> [ID] ASC
> Thanks
> Hari
> "Tim Kelley" wrote:
|||No.
For SQL 2000; just enable the profiler and open the shrink file window from
enterprise manager. This will give the commands which executes
from backend.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ex6Mn6qLHHA.4992@.TK2MSFTNGP04.phx.gbl...
> Will this work with SQL 2000 also?
> Thanks,
>
> "Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
> news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
>
Showing posts with label available. Show all posts
Showing posts with label available. Show all posts
Thursday, March 29, 2012
Get Available free space for data file
When I go to Shrink a file, the screen shows me the currently allocated and
the free space available for the selected database. Is there a view or
system stored procedure that I can use to retrieve this information?
I am working with SQL 2005. I have been playing with the sp_spaceused
stored procedure but can't seem to get just the data file info.
Thanks,Will this work with SQL 2000 also?
Thanks,
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
> Hello,
> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
> you need info.
> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
> UsedExtents int, Name sysname, FileName nchar(520))
> Insert #tmpspc EXEC ('dbcc showfilestats')
>
> SELECT
> s.name AS [Name],
> s.physical_name AS [FileName],
> s.size * CONVERT(float,8) AS [Size],
> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
> s.file_id AS [ID]
> FROM
> sys.filegroups AS g
> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id => db_id()
> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
> WHERE
> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
> ORDER BY
> [ID] ASC
> Thanks
> Hari
> "Tim Kelley" wrote:
>> When I go to Shrink a file, the screen shows me the currently allocated
>> and
>> the free space available for the selected database. Is there a view or
>> system stored procedure that I can use to retrieve this information?
>> I am working with SQL 2005. I have been playing with the sp_spaceused
>> stored procedure but can't seem to get just the data file info.
>> Thanks,
>>|||No.
For SQL 2000; just enable the profiler and open the shrink file window from
enterprise manager. This will give the commands which executes
from backend.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ex6Mn6qLHHA.4992@.TK2MSFTNGP04.phx.gbl...
> Will this work with SQL 2000 also?
> Thanks,
>
> "Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
> news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
>> Hello,
>> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the
>> database
>> you need info.
>> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
>> UsedExtents int, Name sysname, FileName nchar(520))
>> Insert #tmpspc EXEC ('dbcc showfilestats')
>>
>> SELECT
>> s.name AS [Name],
>> s.physical_name AS [FileName],
>> s.size * CONVERT(float,8) AS [Size],
>> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
>> s.file_id AS [ID]
>> FROM
>> sys.filegroups AS g
>> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id =>> db_id()
>> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
>> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
>> WHERE
>> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
>> ORDER BY
>> [ID] ASC
>> Thanks
>> Hari
>> "Tim Kelley" wrote:
>> When I go to Shrink a file, the screen shows me the currently allocated
>> and
>> the free space available for the selected database. Is there a view or
>> system stored procedure that I can use to retrieve this information?
>> I am working with SQL 2005. I have been playing with the sp_spaceused
>> stored procedure but can't seem to get just the data file info.
>> Thanks,
>>
>
the free space available for the selected database. Is there a view or
system stored procedure that I can use to retrieve this information?
I am working with SQL 2005. I have been playing with the sp_spaceused
stored procedure but can't seem to get just the data file info.
Thanks,Will this work with SQL 2000 also?
Thanks,
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
> Hello,
> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
> you need info.
> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
> UsedExtents int, Name sysname, FileName nchar(520))
> Insert #tmpspc EXEC ('dbcc showfilestats')
>
> SELECT
> s.name AS [Name],
> s.physical_name AS [FileName],
> s.size * CONVERT(float,8) AS [Size],
> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
> s.file_id AS [ID]
> FROM
> sys.filegroups AS g
> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id => db_id()
> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
> WHERE
> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
> ORDER BY
> [ID] ASC
> Thanks
> Hari
> "Tim Kelley" wrote:
>> When I go to Shrink a file, the screen shows me the currently allocated
>> and
>> the free space available for the selected database. Is there a view or
>> system stored procedure that I can use to retrieve this information?
>> I am working with SQL 2005. I have been playing with the sp_spaceused
>> stored procedure but can't seem to get just the data file info.
>> Thanks,
>>|||No.
For SQL 2000; just enable the profiler and open the shrink file window from
enterprise manager. This will give the commands which executes
from backend.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ex6Mn6qLHHA.4992@.TK2MSFTNGP04.phx.gbl...
> Will this work with SQL 2000 also?
> Thanks,
>
> "Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
> news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
>> Hello,
>> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the
>> database
>> you need info.
>> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
>> UsedExtents int, Name sysname, FileName nchar(520))
>> Insert #tmpspc EXEC ('dbcc showfilestats')
>>
>> SELECT
>> s.name AS [Name],
>> s.physical_name AS [FileName],
>> s.size * CONVERT(float,8) AS [Size],
>> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
>> s.file_id AS [ID]
>> FROM
>> sys.filegroups AS g
>> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id =>> db_id()
>> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
>> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
>> WHERE
>> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
>> ORDER BY
>> [ID] ASC
>> Thanks
>> Hari
>> "Tim Kelley" wrote:
>> When I go to Shrink a file, the screen shows me the currently allocated
>> and
>> the free space available for the selected database. Is there a view or
>> system stored procedure that I can use to retrieve this information?
>> I am working with SQL 2005. I have been playing with the sp_spaceused
>> stored procedure but can't seem to get just the data file info.
>> Thanks,
>>
>
Get Available free space for data file
When I go to Shrink a file, the screen shows me the currently allocated and
the free space available for the selected database. Is there a view or
system stored procedure that I can use to retrieve this information?
I am working with SQL 2005. I have been playing with the sp_spaceused
stored procedure but can't seem to get just the data file info.
Thanks,Hello,
You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
you need info.
create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
UsedExtents int, Name sysname, FileName nchar(520))
Insert #tmpspc EXEC ('dbcc showfilestats')
SELECT
s.name AS [Name],
s.physical_name AS [FileName],
s.size * CONVERT(float,8) AS [Size],
CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id = db_id()
and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
WHERE
(CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
ORDER BY
[ID] ASC
Thanks
Hari
"Tim Kelley" wrote:
> When I go to Shrink a file, the screen shows me the currently allocated an
d
> the free space available for the selected database. Is there a view or
> system stored procedure that I can use to retrieve this information?
> I am working with SQL 2005. I have been playing with the sp_spaceused
> stored procedure but can't seem to get just the data file info.
> Thanks,
>
>|||Will this work with SQL 2000 also?
Thanks,
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...[vbcol=seagreen]
> Hello,
> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
> you need info.
> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
> UsedExtents int, Name sysname, FileName nchar(520))
> Insert #tmpspc EXEC ('dbcc showfilestats')
>
> SELECT
> s.name AS [Name],
> s.physical_name AS [FileName],
> s.size * CONVERT(float,8) AS [Size],
> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
> s.file_id AS [ID]
> FROM
> sys.filegroups AS g
> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id =
> db_id()
> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
> WHERE
> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
> ORDER BY
> [ID] ASC
> Thanks
> Hari
> "Tim Kelley" wrote:
>|||No.
For SQL 2000; just enable the profiler and open the shrink file window from
enterprise manager. This will give the commands which executes
from backend.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ex6Mn6qLHHA.4992@.TK2MSFTNGP04.phx.gbl...
> Will this work with SQL 2000 also?
> Thanks,
>
> "Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
> news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
>
the free space available for the selected database. Is there a view or
system stored procedure that I can use to retrieve this information?
I am working with SQL 2005. I have been playing with the sp_spaceused
stored procedure but can't seem to get just the data file info.
Thanks,Hello,
You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
you need info.
create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
UsedExtents int, Name sysname, FileName nchar(520))
Insert #tmpspc EXEC ('dbcc showfilestats')
SELECT
s.name AS [Name],
s.physical_name AS [FileName],
s.size * CONVERT(float,8) AS [Size],
CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id = db_id()
and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
WHERE
(CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
ORDER BY
[ID] ASC
Thanks
Hari
"Tim Kelley" wrote:
> When I go to Shrink a file, the screen shows me the currently allocated an
d
> the free space available for the selected database. Is there a view or
> system stored procedure that I can use to retrieve this information?
> I am working with SQL 2005. I have been playing with the sp_spaceused
> stored procedure but can't seem to get just the data file info.
> Thanks,
>
>|||Will this work with SQL 2000 also?
Thanks,
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...[vbcol=seagreen]
> Hello,
> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
> you need info.
> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
> UsedExtents int, Name sysname, FileName nchar(520))
> Insert #tmpspc EXEC ('dbcc showfilestats')
>
> SELECT
> s.name AS [Name],
> s.physical_name AS [FileName],
> s.size * CONVERT(float,8) AS [Size],
> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
> s.file_id AS [ID]
> FROM
> sys.filegroups AS g
> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id =
> db_id()
> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
> WHERE
> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
> ORDER BY
> [ID] ASC
> Thanks
> Hari
> "Tim Kelley" wrote:
>|||No.
For SQL 2000; just enable the profiler and open the shrink file window from
enterprise manager. This will give the commands which executes
from backend.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ex6Mn6qLHHA.4992@.TK2MSFTNGP04.phx.gbl...
> Will this work with SQL 2000 also?
> Thanks,
>
> "Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
> news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
>
Get All Servers and Databases from MSSQL Server 2005 Express
Is there any way to get all available servers and databases from Microsoft SQL Server 2005 Express without connecting to it at all? I am trying to write an application that uses SS05E and I would like to have an option to display all available servers and databases.
check SQLCMD. Form command prompt ...
for all the sql server instance in a network - sqlcmd -L
to get all the database name from a sql server instance -- sqlcmd -q"Select *from sys.databases' -SserverName -Ppassword
Madhu
|||There is no way to pull a list of databases from the servers without connecting to the databases. sqlcmd with the -L switch can output all the SQL Servers on your Windows network. You would need to log into each one and pull the database list from the master.dbo.sysdatabases or sys.databases objects to see what databases are available on each.|||That will work fine. Is there any way I can run sqlcmd -L in a vb.net program and get the results put into a string array? I am writing an application that uses SQL Server and I want the servers/databases to be put into combo boxes or list boxes to make it easier to select which server/database to use.|||I found something google that will work perfectly for what I need. Here is the code:
Code Snippet
Try
Dim myprocess As New Process
Dim StartInfo As New System.Diagnostics.ProcessStartInfo
StartInfo.FileName = "cmd" 'starts cmd window
StartInfo.RedirectStandardInput = True
StartInfo.RedirectStandardOutput = True
StartInfo.UseShellExecute = False 'required to redirect
StartInfo.CreateNoWindow = True 'creates no cmd window
myprocess.StartInfo = StartInfo
myprocess.Start()
Dim SR As System.IO.StreamReader = myprocess.StandardOutput
Dim SW As System.IO.StreamWriter = myprocess.StandardInput
SW.WriteLine("sqlcmd -L") 'the command you wish to run.....
SW.WriteLine("exit") 'exits command prompt window
Me.txtOutput.Text = SR.ReadToEnd 'returns results of the command window
SW.Close()
SR.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Thanks for your help in getting me this far! I really appreciate it
Tuesday, March 27, 2012
Geocoding and Geodata capabilities of SQL Server
Can anyone point me to where I can read about the Geocoding capabilities
available with SQL Server?
I am also looking for information about how Geodata is stored in SQL Server.
Is it only possible to use Geodata with third-party GIS systems?There are no built-in GIS capabilities, and SQL Server lacks some of the
features that would make them easier to implement (e.g. R-Tree indexes).
MapInfo does have a SQL Server-based product that looks very interesting,
but I haven't tried it yet.
"Doug Wittich" <Doug Wittich@.discussions.microsoft.com> wrote in message
news:0E391A36-4598-4923-A83E-54E360ECDB7E@.microsoft.com...
> Can anyone point me to where I can read about the Geocoding capabilities
> available with SQL Server?
> I am also looking for information about how Geodata is stored in SQL
Server.
> Is it only possible to use Geodata with third-party GIS systems?|||I have some clients which have used the ESRI Mapping products with SQL in a
real time ( ambulances) environment... they are OKwith it..
--
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
"Doug Wittich" <Doug Wittich@.discussions.microsoft.com> wrote in message
news:0E391A36-4598-4923-A83E-54E360ECDB7E@.microsoft.com...
> Can anyone point me to where I can read about the Geocoding capabilities
> available with SQL Server?
> I am also looking for information about how Geodata is stored in SQL
Server.
> Is it only possible to use Geodata with third-party GIS systems?
available with SQL Server?
I am also looking for information about how Geodata is stored in SQL Server.
Is it only possible to use Geodata with third-party GIS systems?There are no built-in GIS capabilities, and SQL Server lacks some of the
features that would make them easier to implement (e.g. R-Tree indexes).
MapInfo does have a SQL Server-based product that looks very interesting,
but I haven't tried it yet.
"Doug Wittich" <Doug Wittich@.discussions.microsoft.com> wrote in message
news:0E391A36-4598-4923-A83E-54E360ECDB7E@.microsoft.com...
> Can anyone point me to where I can read about the Geocoding capabilities
> available with SQL Server?
> I am also looking for information about how Geodata is stored in SQL
Server.
> Is it only possible to use Geodata with third-party GIS systems?|||I have some clients which have used the ESRI Mapping products with SQL in a
real time ( ambulances) environment... they are OKwith it..
--
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
"Doug Wittich" <Doug Wittich@.discussions.microsoft.com> wrote in message
news:0E391A36-4598-4923-A83E-54E360ECDB7E@.microsoft.com...
> Can anyone point me to where I can read about the Geocoding capabilities
> available with SQL Server?
> I am also looking for information about how Geodata is stored in SQL
Server.
> Is it only possible to use Geodata with third-party GIS systems?
Sunday, February 19, 2012
General: Audit Dimension
What is the best practice for setting up audit dimensions in 2005? I know that we have the audit transform available, but should we be capturing extra information beyond this?
I had seen one of the design tip articles by kimball on what should be added, however, this article was from some time ago... (http://kimballgroup.com/html/designtipsPDF/DesignTips2001/KimballDT26AddingAnAudit.pdf)
Also, I try to capture the audit guid in the record and then, later, capture the rest of the audit information... I notice that the guid's are different between the audit transforms... is there a way to capture all of the information but not write it all out to each record? i.e. only capture the guid to the record and the rest of the information to the audit table.
Take a look at the Kimball Webcast on the first page of this forum:. Joy talks about creating auditing features:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=534505&SiteID=1|||
Thanks phil. I have watched that video in the past, I might take another look at it. I was, however, hoping for a bit of discussion on what others in the industry were doing. I have looked through the Microsoft Data Warehouse Toolkit, Practical B.I. for SQL Server 2005, Delivering B.I. with Microsoft 2005, as well as a few of the articles on the web. I was just curious, since each has a different level of detail which they capture, what is acceptable down the road (I was told at one of the tdwi conferences that when building the warehouse, the first thing most people would do if they had the oppurtunity to start over is set up a good mdm / audit system, so that is what I'm trying to do)...
|||I implement a version of what Joy illustrated in her Webcast.
Subscribe to:
Posts (Atom)