Used Space for TLog - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Used Space for TLog

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2007-07-26 : 16:13:38

Hi all

Is there anyway to find out using TSQL for the used space VS what is available for TLOG file? Cause the GUI won't allow me [Database] > Report > Disk Usage. It said that Unable to display the report becuase the database has a compatibility level of 80 ?!?!

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-26 : 16:20:14

i guess you restored the sql 2000 backup to a sql 2005 server, no?
this transforms the database to 2005 format but retains the 80 (sql 2000) compatibilty level.

just change it to 90 and you'll be fine.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

dewacorp.alliances

452 Posts

Posted - 2007-07-26 : 17:56:41

quote:
Originally posted by spirit1

i guess you restored the sql 2000 backup to a sql 2005 server, no?
this transforms the database to 2005 format but retains the 80 (sql 2000) compatibilty level.
just change it to 90 and you'll be fine.

We didin't restore the SQL2000 backup to SQLServer 2005. We've setup the collacation to suit with SQL2000. This might the problem is?

Anyway ... I prefer to get the TSQL so I can run it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-26 : 18:08:15

I don't understand what this means: "collacation to suit with SQL2000".

Right click on your database, go to properties, options, what does it say for compatibility level?

According to the error, you need it to say 90, which means 2005.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-26 : 18:23:47

Run this code in a query window to get the size, used space, and unused space for every database file.


select
	[FileSizeMB]	=
		convert(numeric(10,2),round(a.size/128.,2)),
	[UsedSpaceMB]	=
		convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
	[UnusedSpaceMB]	=
		convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
	[DBFileName]	= a.name
from
	sysfiles a

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2007-07-27 : 01:50:33

"just change it to 90 and you'll be fine"

Assuming no application inconsistencies at compatibility level

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-27 : 04:53:30

> Assuming no application inconsistencies at compatibility level

like?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-07-27 : 05:37:06

I was meaning that if the application has been developed and tested at compatibility level 80 there is no guarantee that it will perform identically at compatibility level 90.

By the by, restoring a SQL 2000 to SQL 2005, forgetting to change the compatibility level to 90, performing a full set of regression tests and THEN realising that the compatibility level should be changed to 90 is an equally unfortunate scenario!

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-27 : 05:49:00

> I was meaning that if the application has been developed and tested at compatibility level 80 there is no guarantee that it
> will perform identically at compatibility level 90.

identically perf-wise or logicaly-wise? perf-wise i understand that it's behave differently but logicaly it must be the same.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-07-27 : 06:24:27

"logicaly it must be the same"

I assumed that there were some code variations to retain backwards compatibility on certain features, rather than just preventing access to new features in compatibility level 90

I read that using compatibility level < 90 has significant performance implications, and I read that to mean it was because legacy code had to be invoked (and that code was not available in as efficient a manner as the latest stuff). Some of that might be different query plan optimisation (which could effect behaviour), and maybe also old-code for features where new-code has been added where current version has been re-factored etc.

I may be talking twaddle of course!

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-27 : 10:35:41

quote:
Originally posted by Kristen
...I may be talking twaddle of course!...

But it feels true, so you must be right.

http://en.wikipedia.org/wiki/Truthiness

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2007-07-27 : 10:55:37

Unlike

http://en.wikipedia.org/wiki/Falsies

dewacorp.alliances

452 Posts

Posted - 2007-07-29 : 06:12:53

quote:
Originally posted by tkizer

I don't understand what this means: "collacation to suit with SQL2000".

Hi Tara

When we installed the SQL2005, it's asking about Server Collation and we've selected the following SQL_Latin1_General_CP1_CI_AS due to for compatibilty of SQL2000.

Thanks

dewacorp.alliances

452 Posts

Posted - 2007-07-29 : 06:17:11

quote:
Originally posted by Michael Valentine Jones

Run this code in a query window to get the size, used space, and unused space for every database file.

select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from
sysfiles a

Thanks Michael, that is the code that I want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources