checking size of a database
-
Hi,
I have created a copy of a database programmatically using asp.net. Now I need to do some checks before creating database. I need to check the size of a database that is being copied. is there a system stored procedure that returns the size of a database? After creating the database, I need to do the following things
1) shrink the database
2) detach the database
3) delete log file
Are there any stored procedures that will shrink the database and detach it?
please let me know.
Thanks,
Sridhar.
-
Size of db is reported using this system stored procedure
sp_helpdb
There are several ways to shrink a db, check books online for
DBCC SHRINKDATABASE
dbcc shrinkfile
A log file is an integral part of the database, you cannot just delete it unless the db has been properly detached
See sp_detach_db
-
The following statement will give you the currently allocated size of a database:
SELECT [maxsize] AS 'pages', (CAST([maxsize] AS bigint) * 8192) AS 'bytes'
FROM dbo.sysfiles WITH (NOLOCK)
WHERE ([status] & 64) = 0
The column [maxsize] returns the number of data pages, the second field is the number of data pages multiplied by the size of a data page. I am using 8192 hardcoded here, but you can verify this on your system using 'select [low] from master..spt_values where type = 'E' and number = 1'.
Shrinking the db is dependent on a number of factors. the amount of data, the number of files, the number of file groups and so on. in general, if you have a complex configuration, dbcc shrinkdb would be less coding, but also less efficient. if there is 1 db file and you want to drop the log file, use dbcc shrinkfile on the db file. this question can best be answered by testing.
as for dropping the log file, this can be done, but not by an operational database. the db can not operate without a log file, but i have cases where i perform a lot of work and need to reduce the size of the log file as small as possible for shipping a product. in this case, i shrink the db, detach the db using 'sp_detach_db' with the shipchecks flag set to false so that the statistics are updated. After the db is detached, i then delete the log file from disk, and re-attach the db using 'sp_attach_single_file_db'. this creates a log file, but it is minimum size without any transactions in it that can be shipped in the product. certainly this is limited in functionality, but it does address the need for having an empty tlog file without dealing with the virtual log file boundary issues.
-
Hi - I must be missing something on the usage of your query; I get the same results for each database I tried it on. I also was hoping for something that would give you quick size of DB (data vs. log) other than sp_helpdb.
thanks!
-
The original question was how big is the db. this is answered by looking at how much space is allocated for that db, the maxsize filed in the sysfiles table.
if you want the current size of a db then use this (format it as you prefer):
be aware that the results are dependent on updated table statistics. if you run this query once, get the results and then update statistics on the tables and re-run the query, you may get somewhat different results.
SELECT 'DATA = ' + CAST((SUM(CAST( AS bigint)) * 8192) AS varchar) + ' bytes'
FROM dbo.sysfiles WITH (NOLOCK)
WHERE ([status] & 64) = 0
UNION ALL
SELECT 'LOG = ' + CAST((SUM(CAST( AS bigint)) * 8192) AS varchar) + ' bytes'
FROM dbo.sysfiles WITH (NOLOCK)
WHERE ([status] & 64) <> 0
if you are getting the same from all db's then they all have the same settings.
watch that the query actually runs within the context of the desired db
-
I don't have something simpler than this. It shows exactly the data you would normaly found in EM clicking your database and then View->Taskpad. I use this SP to keep track of how my database has been growing in time. It collects data and inserts it in another table (at the end of the script). I run this script through a job nightly.
--------------------------
CREATE procedure DBusage
as
declare @idint-- The object id of @objname.
declare @typecharacter(2) -- The object type.
declare@pagesint-- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpagedec(15,0)
declare @pagesperMBdec(15,0)
declare @unallocatedspace float
declare @reserved float
declare @curdate datetime
/*Create temp tables before any DML to ensure dynamic
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
rowsint null,
reserveddec(15) null,
datadec(15) null,
indexpdec(15) null,
unuseddec(15) null
)
/*
** If @id is null, then we want summary data.
*/
/*Space used calculated in the following way
**@dbsize = Pages used
**@bytesperpage = d.low (where d = master.dbo.spt_values) is
**the # of bytes per page when d.type = 'E' and
**d.number = 1.
**Size = @dbsize * d.low / (1048576 (OR 1 MB))
*/
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)
select @logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 0)
select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
select @unallocatedspace=ltrim(str((@dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
)) / @pagesperMB,15,2))
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
/*
** data: sum(dpages) where indid < 2
**+ sum(used) where indid = 255 (text)
*/
select @pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2
select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255
update #spt_space
set data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- data
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
select @reserved = ltrim(str(reserved * d.low / 1048576,15,0) )
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
-- print @unallocatedspace
-- print @reserved
select @curdate=cast(floor(cast(GetDate() as float)) as datetime)
insert into [srv-name].database.dbo._dataused values (@curdate,@reserved+@unallocatedspace,@unallocatedspace,@reserved)
drop table #spt_space
GO
-
ahhh ... a taste of the old 'Sybase' code base ...
sp_spaceused is still the best tool for the JOB. system table 'stuff' is going to change in 2K5 (just as it did from v6.0 to v7.0 !!!) ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply