.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

T-SQL for database size and space available (like on Database Properties page on SQL 2008 SSMS)

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :Sql Server

How can i (using T-SQL) get the database size and free space like the database properties page of SQL 2008 SSMS shows? I want to be able to display that information in our software.


View Complete Post

More Related Resource Links

Missing Database Node using SSMS 2008 Express

Hello,It works perfectly the first time I install SSMS 2008 Express and I can access my remote database. But, after I restart my machine and open an instance of SSMS the database node is missing.Before:After: I tried refreshing the database folder but still nothing. I even uninstall but it's still the same.Thank You for your help,-imperialx

Missing Database Node in SSMS 2008 Express

Hello, It works perfectly the first time I install SSMS 2008 Express and I can access my remote database. But, after I restart my machine and open an instance of SSMS and login back, the database node is missing. I have asked this question over at asp.net forums with images (http://forums.asp.net/t/1600718.aspx).   Thanks, -imperialx

Database diagram failur - Invalid class string SSMS 2008

Hi There -  Help needed In SSMS 2008 (not express) the Database Diagram  cast the exeption "Invalid Class String" (MS Visual Database Tools). Why is this happening. I have used the diagram designer before succesfully My OS W7 - SqlServer 2008 - every thing updated  - every service pack installed I have tryed reinstall seceral times - not positiv What is going on - It had been working (1 month ago) Please help Freddy

Error while connecting to Database Engine within SSMS 2008

Error text is as below:- any suggestions ?   ===================================   Cannot connect to TEJ-PC.   ===================================   A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)   ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476   ------------------------------ Error Number: 2 Severity: 20 State: 0     ------------------------------ Program Location:      at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)    at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)    at System.Data.SqlClient.SqlInternalC

Cannot attach database using SSMS 2008

Install SQL Express 2008 Advanced Services RC0 and launched SSMS2008 to attach some database files from previous 2008 install. But was unable to do so becuase of the following error...

Ho do I fix, reinstall or ?

Code Snippet


Cannot show requested dialog.

Parameter name: nColIndex

Actual value was -1. (Microsoft.SqlServer.GridControl)

Program Location:

SSMS 2008 - Is there a better way to display/group tables in multiple scheams under single database


In SQL server 2008 we have 30 users schemas setup under a single database (say "UserDB")

Each user is set up with access to his/her own schema

So when you login to server and drill down to Databases >>UserDb >> Tables,  you will see the tables that belong to your schema


Now I have to give cross Schema access. User 1 wants to see User 2's schema tables.

User 1 tables shows up as user1.table1 and user 2 tables show up as user2.table2


Question is: Instead of listing ALL the tables, differentiated by prefix, is there a way to show them like windows folders.

Can not create table via SSMS 2008 database designer (GUI) even with db_ddladmin role


I have SSMS 2008 (version - 10.0.2531.0)
Back end Microsoft SQL Server 2008 (SP1) - 10.0.2723.0 (X64)
I have DB_ddlAdmin role on a database

I can create/alter a table via sql script.
In the SSMS Database Designer (GUI), When I right click on the database >> Tables and select New Table I get the following error.
"You are not logged in as Database Owner or System Administrator. You might be able to save chnages..."

Then i get a screen where SSMS asks me to enter column names. When I enter column names and I can click on Save I get "Not a Table Owner" error. The table does get created but after that i can not alter it via Database designer (I can alter it via a alter SQL).

So Long story short, I can create tabels via DDL sql but not via the SSMS database designer. This from a technical view point might not be a big deal but for a business person not knowing SQL syntax will be a critical issue.

This was reported back on 06/2009 but can't imagine it still being a active bug

sqlexpress 2008 database size


For sqlexpress 2008 R2, various Microsoft web pages indicate 4GB and 10GB database size limits. I'm happy with sqlexpress 2005 but would upgrade to 2008 if indeed it had a 10GB database limit. So, the question is, which is correct - 4GB or 10GB?



WSS 3 Storage Space vs. Database File Size


Our SharePoint (WSS 3.0) databases are on a SQL-2000 server that's starting to get low on disk space. In order to buy some time to move content into either our MOSS or SP-2010 environment we did some cleanup in SharePoint by archiving & removing several old sites and removing older document versions in a couple of libraries. These changes were made 3 days ago. The Site Settings | Storage Space Allocation page indicates that content size is a little under 37 GB, down from a bit more than 40 GB. (I did make sure that the deleted files / versions were also removed from the site collection recycle bin.)

What has us puzzled is that the physical database file on the SQL Server has not gotten smaller and the amount of "free space" that can be reclaimed has actually gone down - it's currently at about 200 MB. The database file size is over 57 GB; 20 GB larger than SharePoint reports.

So, I have a few questions:

  1. Is it normal for the content database file size to be significantly (50-60%) larger than the amount of content that SharePoint reports?
  2. Is it normal for the database not to get smaller (or at least have more internal free space) when a large amount of content is removed?
  3. Is there any way that we can recover some of this space to get some breathing room on the SQL Server?



sql server 2008 adventureworks database


dear Friends

For my MCTS 70 -433 examination, I recently installed sql server 2008 and installed the sample databases too. but my issue is, in that book they have mentioned a database called "AdventureWorks2008" and the nice thing is I don't have a such a database with me in my sql server 2008. 

And the query is 

-- MERGE Statement
USE AdventureWorks2008
MERGE INTO	Sales.SalesOrderDetailHistory AS SODH
USING Sales.SalesOrderDetail AS SOD
ON SODH.SalesOrderID = SOD.SalesOrderID
AND SODH.SalesOrderDetailID = SOD.SalesOrderDetailID
INSERT (LineTotal, SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty
, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount
, rowguid, ModifiedDate, Cancelled)
VALUES(LineTotal, SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty
, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount
, rowguid, ModifiedDate, DEFAULT)
UPDATE SET SOHD.Cancelled = 'True'
OUTPUT $action , inserted.* ;	

Please help me to overcome this issue and let me know where can I download that database too.

Thanking you

happy Coding

Crystal Report Asking for Database Authentication each time when I view Page.


Whenever I open my Crystal Report page, I am taken to the Database Authentication page where I am asked for

User name
Data Name

each time.

Is there a way to avoid this and I save these authentication in my page once.

I am using VS2005, C#.

Any Help will be appriciated.


How to reflecting database changes immediately on asp.net page


Hi Everyone.

I have a web site that is highly dependant on a SQL Server 2008 database.  I am using linq to sql to connect to the database and require the website to reflect database changes immediately.  Currently the changes are only reflected once a postback occurs and the database is requeried.

Is there a way to get the asp.net page(s) to be alerted of an database change and then to reflect it immediately, or within seconds, on the display?  I am aware that one can use the SQLDependency class and ADO.NET to do this, but am not aware of any linq to sql possibilities.

Any help will be greatly appreciated.


XML size cost to a database

I am in the process of integrating a procedure that imports data into an xml format. The question I have what is the cost on disk versus just importing each colunm. Say I have 10 columns of data, how much more space does an xml column take than just 10 individual columns with the same data. I have searched everywhere and I can not find a definite answer that addresses this question. Any help is greaty appreciated.

Single-page restore/recover in an online database

I am working with SQL Server 2008 64-bit (10.0.2531) on Windows Server Datacenter 2008 SP2 (64-bit). The problems I am having almost certainly stem from my lack of understanding of how SQL Server's transaction log is handled during recoveries. I am using this document as a rough guide. Here' what I am doing: 1. I intentionally corrupt a single data page in a normal table, so that I can practice single-page recovery. 2. I attempt to read the corrupt block in SQL. As expected I receive an error. 1> select top 10 firstname from ds2.dbo.customers 2> go Msg 824, Level 24, State 2, Server IP-0AF647E0, Line 1 SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 4:896; actual 3:0). It occurred during a read of page (4:896) in database ID 5 at offset 0x00000000700000 in file 'C:\sql\dbfiles\cust1.ndf'. Additional messages in the SQL Server error log or system event log may provide more detail. 3. I take a log tail backup before I do anything to try to fix the corruption: 1> backup log ds2 to disk = 'c:\sqlbackup\ds2.bak' with norecovery 2> go Processed 5 pages for database 'ds2', file 'ds_log' on file 1. BACKUP LOG successfully processed 5 pages in 0.041 seconds (0.857 MB/sec). 4. I  restore the corrupt page from backup: 1> restore database ds2 page='4:896' 2> from disk='c:\sqlbackup\ds2.bak' 3> with norecovery 4&

attach sample database failed in sql 2008 express RTM

I'm having a trouble attaching sample database northwnd database,screen show me the error attach database failed for server 'computername\sqlexpress' additional inforamtion: the database 'northwind' cannot be opended because it is version 661.this server supports version 655 and earlier.a downgrade path is not supported.could not open new database 'northwind',create database is aborted,()microsoft sql server,error:948) my os is xp sp3,sql express version is  Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition with Advanced Services on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)  I 'm sure database 'northwind' is ok.I can used database 'northwind' on my other computer.the other computer install express 2008R2 .    install RTM computer  ,database route is C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS,not C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data. actually I 'm install sample database in sql express RTM on my computer.I just wonder why attach good database failed,how to solved this issue. http://sqlserversamples.codeplex.com/releases/view/45923 this link is avialable   thanks for you help

SQL database display in visual studio 2008 express?

I jsut installed SQL server and management studio on my PC. For management studio, I created a database with tables. However, When I have data connection for SQL server in visual studio express, I couldn' t see the database I created. Any recommendations?   Xinguo  
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend