Sql Activity log backup: size

Showing posts with label size. Show all posts

Thursday, March 29, 2012

adjusting size of templog does not remain

A bit bizarre I could be overlooking something since tempdb is not like a user database.
Our log size for tempdb (templog) is 15mb, I need to increase this to 20mb.
I do so through the SQL Enterprise Manager. Adjust the size, hit apply and the size is adjusted!
When I run a Financial apps that uses tempdb, I receive the message that the templog files are full backup and ...
When I go back to tempdb the templog file is back at 15mb.
Jeff
--
Message posted via http://www.sqlmonster.comPerhaps you restarted SQL Server in between. Size for tempdb at startup you set using ALTER
DATABASE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:c83bbdcd541d46a4bb16394dcf882151@.SQLMonster.com...
>A bit bizarre I could be overlooking something since tempdb is not like a user database.
> Our log size for tempdb (templog) is 15mb, I need to increase this to 20mb.
> I do so through the SQL Enterprise Manager. Adjust the size, hit apply and the size is adjusted!
> When I run a Financial apps that uses tempdb, I receive the message that the templog files are
> full backup and ...
> When I go back to tempdb the templog file is back at 15mb.
> Jeff
> --
> Message posted via http://www.sqlmonster.com|||actually i thought the same..but when i change it on one of my local machine
using EM and restart sql server i see size of tempdb after the modification...
"Tibor Karaszi" wrote:
> Perhaps you restarted SQL Server in between. Size for tempdb at startup you set using ALTER
> DATABASE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> news:c83bbdcd541d46a4bb16394dcf882151@.SQLMonster.com...
> >A bit bizarre I could be overlooking something since tempdb is not like a user database.
> >
> > Our log size for tempdb (templog) is 15mb, I need to increase this to 20mb.
> >
> > I do so through the SQL Enterprise Manager. Adjust the size, hit apply and the size is adjusted!
> >
> > When I run a Financial apps that uses tempdb, I receive the message that the templog files are
> > full backup and ...
> >
> > When I go back to tempdb the templog file is back at 15mb.
> >
> > Jeff
> >
> > --
> > Message posted via http://www.sqlmonster.com
>
>|||Ahh yes, the lightbulb went off. tempdb needs the ALTERDATABASE command to adjust its data/log files.
Thanks
Jeff
--
Message posted via http://www.sqlmonster.com|||Ok now I am stumped.
I did
use master
ALTER DATABASE tempdb
MODIFY FILE
(name = templog,
size = 20MB)
go
The templog is then adjusted to 20mb.
I run my financial stored procedure and receive the message templog ran out of space blah blah...
So I go back to review and increase so more, but lo and behold templog is Back at 15mb!!!!
A stored procedure that simply taking data from one database (less than 2000 records) and using temp tables to do some calculations make the log file shift back to 15mb.
Any ideas?
Jeff
--
Message posted via http://www.sqlmonster.com|||What size does master..sysaltfiles specify for the file? Also, perhaps you have autoshrink turned on
for the tempdb database?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:29ce9a8218c945548d497982ae85ee55@.SQLMonster.com...
> Ok now I am stumped.
> I did
> use master
> ALTER DATABASE tempdb
> MODIFY FILE
> (name = templog,
> size = 20MB)
> go
> The templog is then adjusted to 20mb.
> I run my financial stored procedure and receive the message templog ran out of space blah blah...
> So I go back to review and increase so more, but lo and behold templog is Back at 15mb!!!!
> A stored procedure that simply taking data from one database (less than 2000 records) and using
> temp tables to do some calculations make the log file shift back to 15mb.
> Any ideas?
> Jeff
> --
> Message posted via http://www.sqlmonster.com

Adjust Parameter Dropdown control Size/Length

Dear Anyone,

Is there a way to adjust Parameter Dropdown control Size/Length of a report so that it would no longer display a scrollbar?

Thanks,
JosephSorry, this is not a customizable property.

-Danielsql

Adjust Parameter Dropdown control Size/Length

Dear Anyone,

Is there a way to adjust Parameter Dropdown control Size/Length of a report so that it would no longer display a scrollbar?

Thanks,
JosephSorry, this is not a customizable property.

-Daniel

Tuesday, March 20, 2012

Adding table to merge replication - What is result of Snapshot?

I would like to add a table to an existing merge replication. The DB is
approximately 11GB in size. When I proceed through the steps to add the
table using Enterprise Manager, SQL Server 2000 responds as follows:
"After adding a new merge article, you must generate a new snapshot before
changes from any subscription can be merged."
"Although a snapshot of all articles must be generated, only the snapshot of
the new article will be used to synchronize existing subscriptions."
I remember generating an new snapshot for this DB... it took a Loooonnnnggg
time. When SQL2000 says it must generate a snapshot, does it mean for the
entire DB or just the new article?
I thought one of the features of SQL Server 2000 was the ability to add
articles "on the fly" without disturbing what exists.? It is NOT good if I
have to, first, generate a snapshot of the ENTIRE DB... AGAIN! This is not
the way I understand the documentation.
Someone please clarify.?
Brent
brent.erb@.philips.com
IIRC, just a snapshot for the new article will be generated, and distributed
along with metadata for this article.
"Berb" <berb1969@.earthlink.net> wrote in message
news:826193B1-ED96-4D11-B66D-81C4E17D80CF@.microsoft.com...
>I would like to add a table to an existing merge replication. The DB is
> approximately 11GB in size. When I proceed through the steps to add the
> table using Enterprise Manager, SQL Server 2000 responds as follows:
> "After adding a new merge article, you must generate a new snapshot before
> changes from any subscription can be merged."
> "Although a snapshot of all articles must be generated, only the snapshot
> of
> the new article will be used to synchronize existing subscriptions."
> I remember generating an new snapshot for this DB... it took a
> Loooonnnnggg
> time. When SQL2000 says it must generate a snapshot, does it mean for the
> entire DB or just the new article?
> I thought one of the features of SQL Server 2000 was the ability to add
> articles "on the fly" without disturbing what exists.? It is NOT good if
> I
> have to, first, generate a snapshot of the ENTIRE DB... AGAIN! This is
> not
> the way I understand the documentation.
> Someone please clarify.?
>
> Brent
> brent.erb@.philips.com
>

Monday, March 19, 2012

Adding sql datafile

My existing database size is 90GB. Due to this backup,restore and quering
take more time to execute. So I want to know have any chance to
split the existing database into two (or)
how to add one more datafile with existing datafile.
By adding datafile, is the performence will improve?Hi Durai,
You can try the following as the first step towards improving the database
performance...
(a) Place transaction logs on a drive different from datafiles, especially
if your database transaction includes lots of DML operations.
(b) Make use of FILEGROUP, and put files in different drive.
(c) If possible, Tempdb drive should be separated from datafile drive.
With filegroups in place, your backup management (especially backup time)
will be efficient. Try to read more on filegroups and how to create them.
I assume that you haven't placed your files in the RAID environment where a
physical disk is split into logical drive. Then place the files in different
logical drive will not give much of the performance boost.
--
- - - - - - - - -
Thanks
Yogish
"durai" wrote:
> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>|||Hi,
If you have 2 disk controllers, then placing new file in a different
controller will reduce the disk I/O and will increase the performance.
As well as when you backup you could in to multiple files... This will speed
up your backup timings.
BACKUP DATABASE xyz to DISK='F:\...', DISK = 'G:\...' etc.
Thanks
Hari
SQL Server MVP
"durai" <durai@.discussions.microsoft.com> wrote in message
news:55588715-1EE5-4593-A592-10E881A1A78A@.microsoft.com...
> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>

Adding sql datafile

My existing database size is 90GB. Due to this backup,restore and quering
take more time to execute. So I want to know have any chance to
split the existing database into two (or)
how to add one more datafile with existing datafile.
By adding datafile, is the performence will improve?Hi Durai,
You can try the following as the first step towards improving the database
performance...
(a) Place transaction logs on a drive different from datafiles, especially
if your database transaction includes lots of DML operations.
(b) Make use of FILEGROUP, and put files in different drive.
(c) If possible, Tempdb drive should be separated from datafile drive.
With filegroups in place, your backup management (especially backup time)
will be efficient. Try to read more on filegroups and how to create them.
I assume that you haven't placed your files in the RAID environment where a
physical disk is split into logical drive. Then place the files in different
logical drive will not give much of the performance boost.
- - - - - - - - -
Thanks
Yogish
"durai" wrote:

> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>|||Hi,
If you have 2 disk controllers, then placing new file in a different
controller will reduce the disk I/O and will increase the performance.
As well as when you backup you could in to multiple files... This will speed
up your backup timings.
BACKUP DATABASE xyz to DISK='F:\...', DISK = 'G:\...' etc.
Thanks
Hari
SQL Server MVP
"durai" <durai@.discussions.microsoft.com> wrote in message
news:55588715-1EE5-4593-A592-10E881A1A78A@.microsoft.com...
> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>

Adding sql datafile

My existing database size is 90GB. Due to this backup,restore and quering
take more time to execute. So I want to know have any chance to
split the existing database into two (or)
how to add one more datafile with existing datafile.
By adding datafile, is the performence will improve?
Hi Durai,
You can try the following as the first step towards improving the database
performance...
(a) Place transaction logs on a drive different from datafiles, especially
if your database transaction includes lots of DML operations.
(b) Make use of FILEGROUP, and put files in different drive.
(c) If possible, Tempdb drive should be separated from datafile drive.
With filegroups in place, your backup management (especially backup time)
will be efficient. Try to read more on filegroups and how to create them.
I assume that you haven't placed your files in the RAID environment where a
physical disk is split into logical drive. Then place the files in different
logical drive will not give much of the performance boost.
- - - - - - - - -
Thanks
Yogish
"durai" wrote:

> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>
|||Hi,
If you have 2 disk controllers, then placing new file in a different
controller will reduce the disk I/O and will increase the performance.
As well as when you backup you could in to multiple files... This will speed
up your backup timings.
BACKUP DATABASE xyz to DISK='F:\...', DISK = 'G:\...' etc.
Thanks
Hari
SQL Server MVP
"durai" <durai@.discussions.microsoft.com> wrote in message
news:55588715-1EE5-4593-A592-10E881A1A78A@.microsoft.com...
> My existing database size is 90GB. Due to this backup,restore and quering
> take more time to execute. So I want to know have any chance to
> split the existing database into two (or)
> how to add one more datafile with existing datafile.
> By adding datafile, is the performence will improve?
>

Thursday, February 16, 2012

Adding database name at runtime

I am copying the solution here, plz take a look and let me know if I am
wrong and if possible give me another solution. Also, can you tell me
is there any disadvantages of having temp tables in the query?
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
DELETE FROM #test2
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[DatabaseSize] [bigint],
[LogSize] [bigint]
)
DELETE FROM #test3
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pages bigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize float
,@.totalsize float
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = N'DECLARE @.dbsize1 bigint,@.logsize1 bigint;
SELECT @.dbsize1 = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize1 = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM ['+ @.DBname +'].dbo.sysfiles;
INSERT INTO #test3 SELECT @.dbsize1, @.logsize1;'
EXEC sp_executesql @.sql
SELECT @.dbsize=[DatabaseSize], @.logsize=[LogSize] FROM #test3
SET @.sql = N'DECLARE @.reservedpages1 bigint;
SELECT @.reservedpages1 = sum(a.total_pages)
FROM ['+ @.DBname +'].sys.partitions p join ['+ @.DBname
+'].sys.allocation_units a on p.partition_id = a.container_id
left join ['+ @.DBname +'].sys.internal_tables it on p.object_id =
it.object_id;
INSERT INTO #test3 SELECT @.reservedpages1, 0;'
EXEC sp_executesql @.sql
SELECT @.reservedpages=[DatabaseSize] FROM #test3
SELECT @.totalsize=(convert (dec (15,2),@.dbsize)*1.00 + convert (dec
(15,2),@.logsize))*1.00/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize)*1.00 -
convert (dec (15,2),@.reservedpages)*1.00) * 8192.00 / 1048576.00
SET @.str =
str((@.unallocatedsize*1.00/@.totalsize*1.00)*100.00, 15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Thanks,
Pramod
ipramod@.gmail.com wrote:[vbcol=seagreen]
> Hi Vt,
> I have tried the same with SQL Server 2000 also, but it is not working.
> Regards,
> Pramod
> vt wrote:

Adding database name at runtime

I am copying the solution here, plz take a look and let me know if I am
wrong and if possible give me another solution. Also, can you tell me
is there any disadvantages of having temp tables in the query?
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
DELETE FROM #test2
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[DatabaseSize] [bigint],
[LogSize] [bigint]
)
DELETE FROM #test3
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pagesbigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize float
,@.totalsize float
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = N'DECLARE @.dbsize1 bigint,@.logsize1 bigint;
SELECT @.dbsize1 = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize1 = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM ['+ @.DBname +'].dbo.sysfiles;
INSERT INTO #test3 SELECT @.dbsize1, @.logsize1;'
EXEC sp_executesql @.sql
SELECT @.dbsize=[DatabaseSize], @.logsize=[LogSize] FROM #test3
SET @.sql = N'DECLARE @.reservedpages1 bigint;
SELECT @.reservedpages1 = sum(a.total_pages)
FROM ['+ @.DBname +'].sys.partitions p join ['+ @.DBname
+'].sys.allocation_units a on p.partition_id = a.container_id
left join ['+ @.DBname +'].sys.internal_tables it on p.object_id =
it.object_id;
INSERT INTO #test3 SELECT @.reservedpages1, 0;'
EXEC sp_executesql @.sql
SELECT @.reservedpages=[DatabaseSize] FROM #test3
SELECT @.totalsize=(convert (dec (15,2),@.dbsize)*1.00 + convert (dec
(15,2),@.logsize))*1.00/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize)*1.00 -
convert (dec (15,2),@.reservedpages)*1.00) * 8192.00 / 1048576.00
SET @.str =
str((@.unallocatedsize*1.00/@.totalsize*1.00)*100.00, 15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Thanks,
Pramod
ipramod@.gmail.com wrote:[vbcol=seagreen]
> Hi Vt,
> I have tried the same with SQL Server 2000 also, but it is not working.
> Regards,
> Pramod
> vt wrote:

Adding database name at runtime

Hi,
I have below SQL query which calculates the database size for all
databases.
select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
end))
from dbo.sysfiles
But I am not able to substitute the database name which I am getting
from the cursor at runtime.
I want to place the database name in the following query instead of
'DBNAME'.
select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
end))
from >>DBNAME<<<.dbo.sysfiles
Can we replace the 'DBNAME' with the actual database name from the
cursor and retrieve the values?
Thanks,
Regards,
PramodHi
EXEC sp_MSForeachdb 'use [?]; select db_name();select
sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
?.dbo.sysfiles'
<ipramod@.gmail.com> wrote in message
news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> Hi,
> I have below SQL query which calculates the database size for all
> databases.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from dbo.sysfiles
> But I am not able to substitute the database name which I am getting
> from the cursor at runtime.
> I want to place the database name in the following query instead of
> 'DBNAME'.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from >>DBNAME<<<.dbo.sysfiles
> Can we replace the 'DBNAME' with the actual database name from the
> cursor and retrieve the values?
> Thanks,
> Regards,
> Pramod
>|||try this
declare @.dbname varchar(10)
set @.dbname='Northwind'
exec('select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from['+ @.dbname +'].[dbo].[sysfiles]')
Vt
<ipramod@.gmail.com> wrote in message
news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> Hi,
> I have below SQL query which calculates the database size for all
> databases.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from dbo.sysfiles
> But I am not able to substitute the database name which I am getting
> from the cursor at runtime.
> I want to place the database name in the following query instead of
> 'DBNAME'.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from >>DBNAME<<<.dbo.sysfiles
> Can we replace the 'DBNAME' with the actual database name from the
> cursor and retrieve the values?
> Thanks,
> Regards,
> Pramod
>|||Hi Uri,
Thanks for your feedback. It really worked.
Now, I have another question.
I have a variable @.dbsize to which I am assigning the value of database
size and I am using the variable value in the code
Below is my SQL query which returns the database free space in percent
for all the databases.
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[dbsize] [varchar] (1000),
[logsize] [varchar] (1000)
)
DELETE FROM #test2
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pages bigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize bigint
,@.totalsize bigint
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
--
--EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize =sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
@.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
0 end))FROM ?.dbo.sysfiles'
SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM dbo.sysfiles
SELECT @.reservedpages = sum(a.total_pages)
FROM sys.partitions p join sys.allocation_units a on p.partition_id
= a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
(15,2),@.logsize))/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
(dec (15,2),@.reservedpages)) * 8192 / 1048576
--
SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Now this code returns the free space value in percent only for one
database because I am unable to substitute the database name when I
calculate the @.dbsize.
Can you help me?
Thanks,
Regards,
Pramod
Uri Dimant wrote:
> Hi
> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
> ?.dbo.sysfiles'
>
> <ipramod@.gmail.com> wrote in message
> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> > Hi,
> >
> > I have below SQL query which calculates the database size for all
> > databases.
> >
> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> > end))
> > from dbo.sysfiles
> >
> > But I am not able to substitute the database name which I am getting
> > from the cursor at runtime.
> > I want to place the database name in the following query instead of
> > 'DBNAME'.
> >
> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> > end))
> > from >>DBNAME<<<.dbo.sysfiles
> >
> > Can we replace the 'DBNAME' with the actual database name from the
> > cursor and retrieve the values?
> >
> > Thanks,
> > Regards,
> > Pramod
> >|||What version of sql server you using..'
vt
<ipramod@.gmail.com> wrote in message
news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> Hi Uri,
> Thanks for your feedback. It really worked.
> Now, I have another question.
> I have a variable @.dbsize to which I am assigning the value of database
> size and I am using the variable value in the code
> Below is my SQL query which returns the database free space in percent
> for all the databases.
> SET nocount on
> DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> master..sysdatabases
> OPEN AllDatabaseInfo
> IF object_id('tempdb..#test2') IS NOT NULL
> BEGIN
> DROP TABLE #test2
> END
> CREATE TABLE #test2 (
> [Database Name] [varchar] (1000),
> [Database Space Available] [varchar] (1000)
> )
> IF object_id('tempdb..#test3') IS NOT NULL
> BEGIN
> DROP TABLE #test3
> END
> CREATE TABLE #test3 (
> [dbsize] [varchar] (1000),
> [logsize] [varchar] (1000)
> )
> DELETE FROM #test2
> DECLARE @.DBName nvarchar(1000)
> DECLARE @.sql nvarchar(1000)
> DECLARE @.str sysname
> SET @.sql = ''
> SET @.DBName = ''
> DECLARE @.pages bigint
> ,@.dbsize bigint
> ,@.logsize bigint
> ,@.reservedpages bigint
> ,@.unallocatedsize bigint
> ,@.totalsize bigint
> FETCH NEXT FROM AllDatabaseInfo into @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --
> --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> 0 end))FROM ?.dbo.sysfiles'
> SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> <> 0 then size else 0 end))
> FROM dbo.sysfiles
> SELECT @.reservedpages = sum(a.total_pages)
> FROM sys.partitions p join sys.allocation_units a on p.partition_id
> = a.container_id
> left join sys.internal_tables it on p.object_id = it.object_id
> SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> (15,2),@.logsize))/128.00
> SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> (dec (15,2),@.reservedpages)) * 8192 / 1048576
> --
> SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> 15,2)
> SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> @.str
> EXEC sp_executesql @.sql
> FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> END
> CLOSE AllDatabaseInfo
> DEALLOCATE AllDatabaseInfo
> SELECT * FROM #test2
> SET nocount off
>
> Now this code returns the free space value in percent only for one
> database because I am unable to substitute the database name when I
> calculate the @.dbsize.
> Can you help me?
> Thanks,
> Regards,
> Pramod
> Uri Dimant wrote:
>> Hi
>> EXEC sp_MSForeachdb 'use [?]; select db_name();select
>> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
>> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
>> ?.dbo.sysfiles'
>>
>> <ipramod@.gmail.com> wrote in message
>> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
>> > Hi,
>> >
>> > I have below SQL query which calculates the database size for all
>> > databases.
>> >
>> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
>> > end))
>> > from dbo.sysfiles
>> >
>> > But I am not able to substitute the database name which I am getting
>> > from the cursor at runtime.
>> > I want to place the database name in the following query instead of
>> > 'DBNAME'.
>> >
>> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
>> > end))
>> > from >>DBNAME<<<.dbo.sysfiles
>> >
>> > Can we replace the 'DBNAME' with the actual database name from the
>> > cursor and retrieve the values?
>> >
>> > Thanks,
>> > Regards,
>> > Pramod
>> >
>|||SQL Server 2005 RTM Version
Thanks,
Regards,
Pramod
vt wrote:
> What version of sql server you using..'
> vt
>
> <ipramod@.gmail.com> wrote in message
> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> > Hi Uri,
> >
> > Thanks for your feedback. It really worked.
> > Now, I have another question.
> >
> > I have a variable @.dbsize to which I am assigning the value of database
> > size and I am using the variable value in the code
> >
> > Below is my SQL query which returns the database free space in percent
> > for all the databases.
> >
> > SET nocount on
> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> > master..sysdatabases
> >
> > OPEN AllDatabaseInfo
> >
> > IF object_id('tempdb..#test2') IS NOT NULL
> > BEGIN
> > DROP TABLE #test2
> > END
> >
> > CREATE TABLE #test2 (
> > [Database Name] [varchar] (1000),
> > [Database Space Available] [varchar] (1000)
> > )
> >
> > IF object_id('tempdb..#test3') IS NOT NULL
> > BEGIN
> > DROP TABLE #test3
> > END
> >
> > CREATE TABLE #test3 (
> > [dbsize] [varchar] (1000),
> > [logsize] [varchar] (1000)
> > )
> >
> > DELETE FROM #test2
> > DECLARE @.DBName nvarchar(1000)
> > DECLARE @.sql nvarchar(1000)
> > DECLARE @.str sysname
> > SET @.sql = ''
> > SET @.DBName = ''
> > DECLARE @.pages bigint
> > ,@.dbsize bigint
> > ,@.logsize bigint
> > ,@.reservedpages bigint
> > ,@.unallocatedsize bigint
> > ,@.totalsize bigint
> >
> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > --
> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> > 0 end))FROM ?.dbo.sysfiles'
> >
> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> > <> 0 then size else 0 end))
> > FROM dbo.sysfiles
> >
> > SELECT @.reservedpages = sum(a.total_pages)
> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
> > = a.container_id
> > left join sys.internal_tables it on p.object_id = it.object_id
> >
> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> > (15,2),@.logsize))/128.00
> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
> > --
> >
> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> > 15,2)
> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> > @.str
> > EXEC sp_executesql @.sql
> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> > END
> >
> > CLOSE AllDatabaseInfo
> > DEALLOCATE AllDatabaseInfo
> >
> > SELECT * FROM #test2
> > SET nocount off
> >
> >
> >
> > Now this code returns the free space value in percent only for one
> > database because I am unable to substitute the database name when I
> > calculate the @.dbsize.
> >
> > Can you help me?
> >
> > Thanks,
> > Regards,
> > Pramod
> >
> > Uri Dimant wrote:
> >> Hi
> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
> >> ?.dbo.sysfiles'
> >>
> >>
> >>
> >> <ipramod@.gmail.com> wrote in message
> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> >> > Hi,
> >> >
> >> > I have below SQL query which calculates the database size for all
> >> > databases.
> >> >
> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> >> > end))
> >> > from dbo.sysfiles
> >> >
> >> > But I am not able to substitute the database name which I am getting
> >> > from the cursor at runtime.
> >> > I want to place the database name in the following query instead of
> >> > 'DBNAME'.
> >> >
> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> >> > end))
> >> > from >>DBNAME<<<.dbo.sysfiles
> >> >
> >> > Can we replace the 'DBNAME' with the actual database name from the
> >> > cursor and retrieve the values?
> >> >
> >> > Thanks,
> >> > Regards,
> >> > Pramod
> >> >
> >|||Sorry buddy.. still using 2000
<ipramod@.gmail.com> wrote in message
news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...
> SQL Server 2005 RTM Version
> Thanks,
> Regards,
> Pramod
> vt wrote:
>> What version of sql server you using..'
>> vt
>>
>> <ipramod@.gmail.com> wrote in message
>> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
>> > Hi Uri,
>> >
>> > Thanks for your feedback. It really worked.
>> > Now, I have another question.
>> >
>> > I have a variable @.dbsize to which I am assigning the value of database
>> > size and I am using the variable value in the code
>> >
>> > Below is my SQL query which returns the database free space in percent
>> > for all the databases.
>> >
>> > SET nocount on
>> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
>> > master..sysdatabases
>> >
>> > OPEN AllDatabaseInfo
>> >
>> > IF object_id('tempdb..#test2') IS NOT NULL
>> > BEGIN
>> > DROP TABLE #test2
>> > END
>> >
>> > CREATE TABLE #test2 (
>> > [Database Name] [varchar] (1000),
>> > [Database Space Available] [varchar] (1000)
>> > )
>> >
>> > IF object_id('tempdb..#test3') IS NOT NULL
>> > BEGIN
>> > DROP TABLE #test3
>> > END
>> >
>> > CREATE TABLE #test3 (
>> > [dbsize] [varchar] (1000),
>> > [logsize] [varchar] (1000)
>> > )
>> >
>> > DELETE FROM #test2
>> > DECLARE @.DBName nvarchar(1000)
>> > DECLARE @.sql nvarchar(1000)
>> > DECLARE @.str sysname
>> > SET @.sql = ''
>> > SET @.DBName = ''
>> > DECLARE @.pages bigint
>> > ,@.dbsize bigint
>> > ,@.logsize bigint
>> > ,@.reservedpages bigint
>> > ,@.unallocatedsize bigint
>> > ,@.totalsize bigint
>> >
>> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
>> > WHILE @.@.FETCH_STATUS = 0
>> > BEGIN
>> > --
>> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize =>> > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
>> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
>> > 0 end))FROM ?.dbo.sysfiles'
>> >
>> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
>> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
>> > <> 0 then size else 0 end))
>> > FROM dbo.sysfiles
>> >
>> > SELECT @.reservedpages = sum(a.total_pages)
>> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
>> > = a.container_id
>> > left join sys.internal_tables it on p.object_id = it.object_id
>> >
>> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
>> > (15,2),@.logsize))/128.00
>> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
>> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
>> > --
>> >
>> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
>> > 15,2)
>> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
>> > @.str
>> > EXEC sp_executesql @.sql
>> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
>> > END
>> >
>> > CLOSE AllDatabaseInfo
>> > DEALLOCATE AllDatabaseInfo
>> >
>> > SELECT * FROM #test2
>> > SET nocount off
>> >
>> >
>> >
>> > Now this code returns the free space value in percent only for one
>> > database because I am unable to substitute the database name when I
>> > calculate the @.dbsize.
>> >
>> > Can you help me?
>> >
>> > Thanks,
>> > Regards,
>> > Pramod
>> >
>> > Uri Dimant wrote:
>> >> Hi
>> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
>> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
>> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0
>> >> end))FROM
>> >> ?.dbo.sysfiles'
>> >>
>> >>
>> >>
>> >> <ipramod@.gmail.com> wrote in message
>> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
>> >> > Hi,
>> >> >
>> >> > I have below SQL query which calculates the database size for all
>> >> > databases.
>> >> >
>> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
>> >> > 0
>> >> > end))
>> >> > from dbo.sysfiles
>> >> >
>> >> > But I am not able to substitute the database name which I am getting
>> >> > from the cursor at runtime.
>> >> > I want to place the database name in the following query instead of
>> >> > 'DBNAME'.
>> >> >
>> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
>> >> > 0
>> >> > end))
>> >> > from >>DBNAME<<<.dbo.sysfiles
>> >> >
>> >> > Can we replace the 'DBNAME' with the actual database name from the
>> >> > cursor and retrieve the values?
>> >> >
>> >> > Thanks,
>> >> > Regards,
>> >> > Pramod
>> >> >
>> >
>|||Hi Vt,
I have tried the same with SQL Server 2000 also, but it is not working.
Regards,
Pramod
vt wrote:
> Sorry buddy.. still using 2000
>
> <ipramod@.gmail.com> wrote in message
> news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...
> > SQL Server 2005 RTM Version
> >
> > Thanks,
> > Regards,
> > Pramod
> >
> > vt wrote:
> >> What version of sql server you using..'
> >>
> >> vt
> >>
> >>
> >> <ipramod@.gmail.com> wrote in message
> >> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> >> > Hi Uri,
> >> >
> >> > Thanks for your feedback. It really worked.
> >> > Now, I have another question.
> >> >
> >> > I have a variable @.dbsize to which I am assigning the value of database
> >> > size and I am using the variable value in the code
> >> >
> >> > Below is my SQL query which returns the database free space in percent
> >> > for all the databases.
> >> >
> >> > SET nocount on
> >> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> >> > master..sysdatabases
> >> >
> >> > OPEN AllDatabaseInfo
> >> >
> >> > IF object_id('tempdb..#test2') IS NOT NULL
> >> > BEGIN
> >> > DROP TABLE #test2
> >> > END
> >> >
> >> > CREATE TABLE #test2 (
> >> > [Database Name] [varchar] (1000),
> >> > [Database Space Available] [varchar] (1000)
> >> > )
> >> >
> >> > IF object_id('tempdb..#test3') IS NOT NULL
> >> > BEGIN
> >> > DROP TABLE #test3
> >> > END
> >> >
> >> > CREATE TABLE #test3 (
> >> > [dbsize] [varchar] (1000),
> >> > [logsize] [varchar] (1000)
> >> > )
> >> >
> >> > DELETE FROM #test2
> >> > DECLARE @.DBName nvarchar(1000)
> >> > DECLARE @.sql nvarchar(1000)
> >> > DECLARE @.str sysname
> >> > SET @.sql = ''
> >> > SET @.DBName = ''
> >> > DECLARE @.pages bigint
> >> > ,@.dbsize bigint
> >> > ,@.logsize bigint
> >> > ,@.reservedpages bigint
> >> > ,@.unallocatedsize bigint
> >> > ,@.totalsize bigint
> >> >
> >> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
> >> > WHILE @.@.FETCH_STATUS = 0
> >> > BEGIN
> >> > --
> >> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => >> > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> >> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> >> > 0 end))FROM ?.dbo.sysfiles'
> >> >
> >> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> >> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> >> > <> 0 then size else 0 end))
> >> > FROM dbo.sysfiles
> >> >
> >> > SELECT @.reservedpages = sum(a.total_pages)
> >> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
> >> > = a.container_id
> >> > left join sys.internal_tables it on p.object_id = it.object_id
> >> >
> >> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> >> > (15,2),@.logsize))/128.00
> >> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> >> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
> >> > --
> >> >
> >> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> >> > 15,2)
> >> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> >> > @.str
> >> > EXEC sp_executesql @.sql
> >> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> >> > END
> >> >
> >> > CLOSE AllDatabaseInfo
> >> > DEALLOCATE AllDatabaseInfo
> >> >
> >> > SELECT * FROM #test2
> >> > SET nocount off
> >> >
> >> >
> >> >
> >> > Now this code returns the free space value in percent only for one
> >> > database because I am unable to substitute the database name when I
> >> > calculate the @.dbsize.
> >> >
> >> > Can you help me?
> >> >
> >> > Thanks,
> >> > Regards,
> >> > Pramod
> >> >
> >> > Uri Dimant wrote:
> >> >> Hi
> >> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> >> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> >> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0
> >> >> end))FROM
> >> >> ?.dbo.sysfiles'
> >> >>
> >> >>
> >> >>
> >> >> <ipramod@.gmail.com> wrote in message
> >> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> >> >> > Hi,
> >> >> >
> >> >> > I have below SQL query which calculates the database size for all
> >> >> > databases.
> >> >> >
> >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> >> >> > 0
> >> >> > end))
> >> >> > from dbo.sysfiles
> >> >> >
> >> >> > But I am not able to substitute the database name which I am getting
> >> >> > from the cursor at runtime.
> >> >> > I want to place the database name in the following query instead of
> >> >> > 'DBNAME'.
> >> >> >
> >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> >> >> > 0
> >> >> > end))
> >> >> > from >>DBNAME<<<.dbo.sysfiles
> >> >> >
> >> >> > Can we replace the 'DBNAME' with the actual database name from the
> >> >> > cursor and retrieve the values?
> >> >> >
> >> >> > Thanks,
> >> >> > Regards,
> >> >> > Pramod
> >> >> >
> >> >
> >|||Hi Vt,
I have sorted out the issue by using the temporary tables. I have used
your suggestion and in the 'exec' itself I have inserted the variable
values in the temporary table and it worked. Thanks for your feedback
guys :)
I am copying the solution here, plz take a look and let me know if I am
wrong and if possible give me another solution. Also, can you tell me
is there any disadvantages of having temp tables in the query?
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
DELETE FROM #test2
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[DatabaseSize] [bigint],
[LogSize] [bigint]
)
DELETE FROM #test3
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pages bigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize float
,@.totalsize float
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = N'DECLARE @.dbsize1 bigint,@.logsize1 bigint;
SELECT @.dbsize1 = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize1 = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM ['+ @.DBname +'].dbo.sysfiles;
INSERT INTO #test3 SELECT @.dbsize1, @.logsize1;'
EXEC sp_executesql @.sql
SELECT @.dbsize=[DatabaseSize], @.logsize=[LogSize] FROM #test3
SET @.sql = N'DECLARE @.reservedpages1 bigint;
SELECT @.reservedpages1 = sum(a.total_pages)
FROM ['+ @.DBname +'].sys.partitions p join ['+ @.DBname
+'].sys.allocation_units a on p.partition_id = a.container_id
left join ['+ @.DBname +'].sys.internal_tables it on p.object_id =it.object_id;
INSERT INTO #test3 SELECT @.reservedpages1, 0;'
EXEC sp_executesql @.sql
SELECT @.reservedpages=[DatabaseSize] FROM #test3
SELECT @.totalsize=(convert (dec (15,2),@.dbsize)*1.00 + convert (dec
(15,2),@.logsize))*1.00/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize)*1.00 -
convert (dec (15,2),@.reservedpages)*1.00) * 8192.00 / 1048576.00
SET @.str =str((@.unallocatedsize*1.00/@.totalsize*1.00)*100.00, 15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Thanks,
Pramod
ipramod@.gmail.com wrote:
> Hi Vt,
> I have tried the same with SQL Server 2000 also, but it is not working.
> Regards,
> Pramod
> vt wrote:
> > Sorry buddy.. still using 2000
> >
> >
> >
> > <ipramod@.gmail.com> wrote in message
> > news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...
> > > SQL Server 2005 RTM Version
> > >
> > > Thanks,
> > > Regards,
> > > Pramod
> > >
> > > vt wrote:
> > >> What version of sql server you using..'
> > >>
> > >> vt
> > >>
> > >>
> > >> <ipramod@.gmail.com> wrote in message
> > >> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> > >> > Hi Uri,
> > >> >
> > >> > Thanks for your feedback. It really worked.
> > >> > Now, I have another question.
> > >> >
> > >> > I have a variable @.dbsize to which I am assigning the value of database
> > >> > size and I am using the variable value in the code
> > >> >
> > >> > Below is my SQL query which returns the database free space in percent
> > >> > for all the databases.
> > >> >
> > >> > SET nocount on
> > >> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> > >> > master..sysdatabases
> > >> >
> > >> > OPEN AllDatabaseInfo
> > >> >
> > >> > IF object_id('tempdb..#test2') IS NOT NULL
> > >> > BEGIN
> > >> > DROP TABLE #test2
> > >> > END
> > >> >
> > >> > CREATE TABLE #test2 (
> > >> > [Database Name] [varchar] (1000),
> > >> > [Database Space Available] [varchar] (1000)
> > >> > )
> > >> >
> > >> > IF object_id('tempdb..#test3') IS NOT NULL
> > >> > BEGIN
> > >> > DROP TABLE #test3
> > >> > END
> > >> >
> > >> > CREATE TABLE #test3 (
> > >> > [dbsize] [varchar] (1000),
> > >> > [logsize] [varchar] (1000)
> > >> > )
> > >> >
> > >> > DELETE FROM #test2
> > >> > DECLARE @.DBName nvarchar(1000)
> > >> > DECLARE @.sql nvarchar(1000)
> > >> > DECLARE @.str sysname
> > >> > SET @.sql = ''
> > >> > SET @.DBName = ''
> > >> > DECLARE @.pages bigint
> > >> > ,@.dbsize bigint
> > >> > ,@.logsize bigint
> > >> > ,@.reservedpages bigint
> > >> > ,@.unallocatedsize bigint
> > >> > ,@.totalsize bigint
> > >> >
> > >> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
> > >> > WHILE @.@.FETCH_STATUS = 0
> > >> > BEGIN
> > >> > --
> > >> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => > >> > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> > >> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> > >> > 0 end))FROM ?.dbo.sysfiles'
> > >> >
> > >> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> > >> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> > >> > <> 0 then size else 0 end))
> > >> > FROM dbo.sysfiles
> > >> >
> > >> > SELECT @.reservedpages = sum(a.total_pages)
> > >> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
> > >> > = a.container_id
> > >> > left join sys.internal_tables it on p.object_id = it.object_id
> > >> >
> > >> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> > >> > (15,2),@.logsize))/128.00
> > >> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> > >> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
> > >> > --
> > >> >
> > >> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> > >> > 15,2)
> > >> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> > >> > @.str
> > >> > EXEC sp_executesql @.sql
> > >> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> > >> > END
> > >> >
> > >> > CLOSE AllDatabaseInfo
> > >> > DEALLOCATE AllDatabaseInfo
> > >> >
> > >> > SELECT * FROM #test2
> > >> > SET nocount off
> > >> >
> > >> >
> > >> >
> > >> > Now this code returns the free space value in percent only for one
> > >> > database because I am unable to substitute the database name when I
> > >> > calculate the @.dbsize.
> > >> >
> > >> > Can you help me?
> > >> >
> > >> > Thanks,
> > >> > Regards,
> > >> > Pramod
> > >> >
> > >> > Uri Dimant wrote:
> > >> >> Hi
> > >> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> > >> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> > >> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0
> > >> >> end))FROM
> > >> >> ?.dbo.sysfiles'
> > >> >>
> > >> >>
> > >> >>
> > >> >> <ipramod@.gmail.com> wrote in message
> > >> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> > >> >> > Hi,
> > >> >> >
> > >> >> > I have below SQL query which calculates the database size for all
> > >> >> > databases.
> > >> >> >
> > >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> > >> >> > 0
> > >> >> > end))
> > >> >> > from dbo.sysfiles
> > >> >> >
> > >> >> > But I am not able to substitute the database name which I am getting
> > >> >> > from the cursor at runtime.
> > >> >> > I want to place the database name in the following query instead of
> > >> >> > 'DBNAME'.
> > >> >> >
> > >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> > >> >> > 0
> > >> >> > end))
> > >> >> > from >>DBNAME<<<.dbo.sysfiles
> > >> >> >
> > >> >> > Can we replace the 'DBNAME' with the actual database name from the
> > >> >> > cursor and retrieve the values?
> > >> >> >
> > >> >> > Thanks,
> > >> >> > Regards,
> > >> >> > Pramod
> > >> >> >
> > >> >
> > >

Thursday, March 29, 2012

adjusting size of templog does not remain

Adjust Parameter Dropdown control Size/Length

Adjust Parameter Dropdown control Size/Length

Tuesday, March 20, 2012

Adding table to merge replication - What is result of Snapshot?

Monday, March 19, 2012

Adding sql datafile

Adding sql datafile

Adding sql datafile

Thursday, February 16, 2012

Adding database name at runtime

Adding database name at runtime

Adding database name at runtime

Sql Activity log backup

Blog Archive

About Me