Space Usage
May 20, 1999I need to export data out of an sql database using the SQLOLE object
in visual basic.
I have no idea how to even begin how to do this.
ADVERTISEMENT
Log File Space Usage
Feb 24, 2000On SQL*Server7, is there a system table to find out the transaction log space used? DBCC SQLPERF (logspace) must be using a undocumented system table.
Thanks.
Monitor Space Usage
Jul 17, 2001Hi!
Please give me an idea how to monitor database size.
Thank you
TempDB Log Space Usage
Jul 21, 2015I received an alert from our alerting system that log space for tempdb is used over 60%.
My question is what options do I have to troubleshoot and fix it? If it would be a regular user database, I would check for log_reuse_wait_desc in sys.databases, run another log backup, and maybe some other things. But what I can do with tempdb?
Script To Analyze Table Space Usage
Feb 14, 2006Edit 2007-8-9:
Added code to show database file sizes. Not really closely related to tables sizes, but a lot of the people who need this want to know why their database it so large, so it may help to know which files, especially the logs, are so large, and if the files have empty space in them.
-- Script to analyze table space usage using the
-- output from the sp_spaceused stored procedure
-- Works with SQL 7.0, 2000, and 2005
set nocount on
print 'Show Size, Space Used, Unused Space, Type, and Name of all database files'
select
[FileSizeMB]=
convert(numeric(10,2),sum(round(a.size/128.,2))),
[UsedSpaceMB]=
convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,
[UnusedSpaceMB]=
convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
[Type] =
case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end,
[DBFileName]= isnull(a.name,'*** Total for all files ***')
from
sysfiles a
group by
groupid,
a.name
with rollup
having
a.groupid is null or
a.name is not null
order by
case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
a.groupid,
case when a.name is null then 99 else 0 end,
a.name
create table #TABLE_SPACE_WORK
(
TABLE_NAME sysnamenot null ,
TABLE_ROWS numeric(18,0)not null ,
RESERVED varchar(50) not null ,
DATA varchar(50) not null ,
INDEX_SIZE varchar(50) not null ,
UNUSED varchar(50) not null ,
)
create table #TABLE_SPACE_USED
(
Seqintnot null
identity(1,1)primary key clustered,
TABLE_NAME sysnamenot null ,
TABLE_ROWS numeric(18,0)not null ,
RESERVED varchar(50) not null ,
DATA varchar(50) not null ,
INDEX_SIZE varchar(50) not null ,
UNUSED varchar(50) not null ,
)
create table #TABLE_SPACE
(
Seqintnot null
identity(1,1)primary key clustered,
TABLE_NAME SYSNAME not null ,
TABLE_ROWS int not null ,
RESERVED int not null ,
DATA int not null ,
INDEX_SIZE int not null ,
UNUSED int not null ,
USED_MBnumeric(18,4)not null,
USED_GBnumeric(18,4)not null,
AVERAGE_BYTES_PER_ROWnumeric(18,5)null,
AVERAGE_DATA_BYTES_PER_ROWnumeric(18,5)null,
AVERAGE_INDEX_BYTES_PER_ROWnumeric(18,5)null,
AVERAGE_UNUSED_BYTES_PER_ROWnumeric(18,5)null,
)
declare @fetch_status int
declare @proc varchar(200)
select@proc= rtrim(db_name())+'.dbo.sp_spaceused'
declare Cur_Cursor cursor local
for
select
TABLE_NAME=
rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME)
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE= 'BASE TABLE'
order by
1
open Cur_Cursor
declare @TABLE_NAME varchar(200)
select @fetch_status = 0
while @fetch_status = 0
begin
fetch next from Cur_Cursor
into
@TABLE_NAME
select @fetch_status = @@fetch_status
if @fetch_status <> 0
begin
continue
end
truncate table #TABLE_SPACE_WORK
insert into #TABLE_SPACE_WORK
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
exec @proc @objname =
@TABLE_NAME ,@updateusage = 'true'
-- Needed to work with SQL 7
update #TABLE_SPACE_WORK
set
TABLE_NAME = @TABLE_NAME
insert into #TABLE_SPACE_USED
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
from
#TABLE_SPACE_WORK
end --While end
close Cur_Cursor
deallocate Cur_Cursor
insert into #TABLE_SPACE
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB,
USED_GB,
AVERAGE_BYTES_PER_ROW,
AVERAGE_DATA_BYTES_PER_ROW,
AVERAGE_INDEX_BYTES_PER_ROW,
AVERAGE_UNUSED_BYTES_PER_ROW
)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB=
round(convert(numeric(25,10),RESERVED)/
convert(numeric(25,10),1024),4),
USED_GB=
round(convert(numeric(25,10),RESERVED)/
convert(numeric(25,10),1024*1024),4),
AVERAGE_BYTES_PER_ROW=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),RESERVED))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_DATA_BYTES_PER_ROW=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),DATA))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_INDEX_BYTES_PER_ROW=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),INDEX_SIZE))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_UNUSED_BYTES_PER_ROW=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),UNUSED))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end
from
(
select
TABLE_NAME,
TABLE_ROWS,
RESERVED=
convert(int,rtrim(replace(RESERVED,'KB',''))),
DATA=
convert(int,rtrim(replace(DATA,'KB',''))),
INDEX_SIZE=
convert(int,rtrim(replace(INDEX_SIZE,'KB',''))),
UNUSED=
convert(int,rtrim(replace(UNUSED,'KB','')))
from
#TABLE_SPACE_USED aa
) a
order by
TABLE_NAME
print 'Show results in descending order by size in MB'
select * from #TABLE_SPACE order by USED_MB desc
go
drop table #TABLE_SPACE_WORK
drop table #TABLE_SPACE_USED
drop table #TABLE_SPACE
CODO ERGO SUM
DB Engine :: Transaction Log Space Usage On TempDB Ever Increasing
Jun 15, 2015today I've put in production a big database accessed by 200 concurrent users, this database has READ_COMMITTED_SNAPHOT set to ON.I know that RCSI set to ON is very aggressive on tempDB so I'm monitoring it.I've noticed that the Transaction log space usage (%) on TempDB is slowly but ever increasing, I mean in the last 24 hours I've started from a 99% space free, now we are 37% space free...is it normal? TempDB log is 35GB in size.
View 6 Replies View RelatedComputing The CPU Usage ,memory Usage For An Inserted Record
Nov 2, 2007
I have a client program that writes to sql server database 10 records per second . i want to compute the CPU usage and the memory usage for the whole program or CPU usage,memory usage for the insert statement in the program .
Can anybody help me with this?
CPU Usage(%), Logical IO Performed (%) Usage For Adhoc Queries Is 90%
Sep 7, 2007
Hello, When I am seeing SQL Server 2005 Management studio Server Dashboard> I am seeing my(USERS) databases and msdb database usage is very small % of in CPU Usage(%), Logical IO Performed (%) Usage pie chart.
90% of Total cpu usage is showing for Adhoc Queries. what excatly this means in Dashboard? if application uses more than it would have shown in Database level or not?
sicerely this dashboard is good, if any one is watching daily, please advice their experiences here.
Thanks in advance. Hail SQL Server!
SQL Server 2012 :: Query To Get CPU Usage / Memory Usage Details Of Server?
Jan 30, 2014providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...
I want to create a job which will run on a Node every 15 min and store data in a table for each instance...
DMV is not giving more stuff and xtended events not sure if i can store that data into a table?
How To Replace Empty Space Or White Space In A String In A Stored Procedure
Nov 14, 2007Hi,
I am trying to do this:
UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks
Mutilple Space Gets Converted To Single Space In Report Viewer Control
Feb 23, 2007I am generating a Report from Sql Data Source in Sql Server 2005 and viewing the Report in Report viewer control of Visual Studio 2005.
The data in the Data Source contains string with multiple spaces (for example €œ Test String €œ) but when they get rendered in Report viewer control, multiple spaces gets converted to single space €? Test String €œ.
I tried following solutions
1) Replacing spaces with €œ €?
2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried <Pre> instead of <pre>)
But in all the cases result is same. The Report Viewer control is showing €œ €? instead of space and €œ<Pre>€? tag instead of preserving spaces.
Please provide me a solution so that spaces can be preserved in Report Viewer.
Transact SQL :: How To Find Space Available Or Send Space Alerts In Percentage
Nov 26, 2015I am using the below script to get space alerts  and now i am interested in sending alerts  if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %Â
Declare @Drives Varchar(20)
DECLARE @Spaces Varchar(50)
DECLARE @availableSpace FLOAT
DECLARE @alertMessage Varchar(4000)
DECLARE @RecipientsList  VARCHAR(4000);
CREATE TABLE #tbldiskSpace
[Code] ....
Trans Log-&>space Allocated 27GB, Space Used 100MB
Mar 2, 2005Hi.. I was doing a good maintenance on my DB and my trans log LDF keep growing until 30GB but my DB data file MDF is only 2GB. I found the two following method to reduce my log size.
Method 1: I used veritas to backup log file with truncate
Method 2: I used the shrink database option in Enterprises manager to shrink it (file chosen=log , use default option)
After doing that, I found my LDF log file is still about the same size=27GB but when I see clearly, from the shrink database windows, the log spaced used reduced to only 100MB, the allocation log space is still 27GB. Why? How to make the LDF smaller to be the around the same size as the space used 100MB?
Double Space Replaced With Single Space By Dbms ??!
Jul 20, 2005This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale
View 2 Replies View RelatedProblem With Space Allocated For Transaction Log Space
Dec 5, 2001Hello,
Somebody know how to reduce the space allocated for the transaction log space for my SQL_DB ?
3700 MB allocated but only 100 MB used and 3600 MB are free !
Transaction log properties :
Automatically grow file are filled
file growth by percent = 5%
maximum file size - restrict filegrowth = 3700 MB (we can't reduce it !)
Thank you for your precious help !
Khaix from Brussel.
Suppress Multiple Space To Single Space..
Nov 14, 2006How do we suppress multiple spaces to a single space in T-SQL
E.G.
Field: FullName
e.g.
WOMENS HEALTH RIVER VALLEY
JOHN FAMILY MED GROUP
HERSH STWEART P.
PARK HEIGHTS MEDICAL CENTER
KOPP WHITEFIELD E
The o/p wanted is
HERSH STWEART P.
Thank you.
Space Error But I Have Enough Space
Nov 24, 2000I made some copy of table and I have this error but on my hard disk i have 4 gig of empty space.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object 'Backup_Date_11_24_00_Time_9_08_34_AM' in database 'LogActiviteIntramedia' because the 'PRIMARY' filegroup is full.
/Intranet_API/Forms/videTableLog.asp, line 16
My question is how can I increase the space of primary filegroup?
Thanks and have a good friday
BCP Usage ???
May 9, 2001hi there,
i need to transfer the data off a query output to a txt file in the same order as it prints. i have tried using the command
eg.
exec bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa -Ppassword
this is exactly what i used replacing the appropriate parameters, but it gave me a "syntax error near queryout"
PLEASE advice what is wrong or sugest the best method to achieve the same.
Any help would be appretiated.
Thanks,
c: )
Max SQL 7.0 Usage
Feb 15, 2000I need to provide some infomation on how much (trans/request)SQL7 can handle.
I checked the white papers and testimonials, but don't see any actual numbers.
We have a clustered SQL7 environment sitting on some Compaq 6400 using 4 cpu's. Our database size is only about 3.5 gig and we are using IIS 3. Does anyone know where to get this information?
Please advise
Thanks
Susan
CPU Usage Is 100 %
Jun 6, 2003Hi,
We have a SQL Server 2000 Enterprise Edition running as production
The CPU Usage is High when there were >350 user connections.
The buffer hit ratio is good. I checked all the monitor.
Can anyone can guide me why it is causing.
Thanks in advance,
Anu
Sql 100% Cpu Usage
Dec 1, 2004Hi guys, I have a sell server its a new server 4 processors and 4 gigs of ram. SQL server is pinning the CPU's 100% I can’t figure out why, I'm at mdac 2.7 with the hotfix. I don’t know what else to look at.
Any ideas?
BCP Usage
Apr 20, 1999Hi .... I'd like to know if I can use BCP to transfer the whole database to a device or file and then back to the server(I mean,using only one server) with a new char set/sort order configuration,or I have to do it table by table??? How do I use BCP to perform this task?What commands and parameters should I use???
Thanks for your attention,
Luciano
Cpu Usage 100% On 6.5 Sql
Sep 15, 2004we have CPU Usage upto almost 100% i increased the tempdb, and master to almost double the size
then we increased the memory to high amount.
does anyone have suggestion pls help since it running too slow, since we did an upgrade
SQL 100% Usage.
Apr 22, 2004I have a SQL 2000 Server, dual processer with hyperthreading. 2Gb of Ram. The machine is only being used as a SQL Server
Normally the server runs at about 15-20% usage. I have now noticed a problem where the SQL server suddenly jumps to 100%, this can happen after a week or a couple of days. It requires a server reboot to fix the problem. Stopping the SQL server and restarting will not work.
I don't know if this is part of the problem, but I have noticed that after a day or so the processor usage climbs by about 10%, if I stop the SQL service and restart, the processor usage drop by the 10%.
Also I have set the memory usage to be 1.5Gb, but it takes a day or two for the SQL server to consume this amount of memory. Don't know if this has anything to do with it.
Anyone know why this is happening ?
Kev
SQL And Ram Usage
Aug 30, 2005Hi All.
I am new to MSSQL, Iam using it to store largeamounts of data on a daily basis,that I import from a CSV file at the rate that I am going it should be about 1Gig a month of data. I noticed that as I add data to MSSQL my ram usage climbs by the size of the data. Is there someting Ihave done wrong inthe setup.
Thanks
Scott
Ram Usage
Apr 4, 2007I've got a copy of SQL2005 and IIS on the same machine. Sql Server seems to be eating a lot of RAM, eventually causing IIS to restart.
Anybody had any luck playing with max server memory ?
CPU Usage Is 100%
May 7, 2007hi all,
when I click web page (executing some stored procedure generally will take less than 2 minutes) the CPU usage is becoming 100% and taking a long time to run.I already posted a forum before (SQL server 2005 running slow ). I dont know these problems are related.If I restart the server then it will run as ususal.What should be problem.Server Windows 2003 ,SQL server 2005
Is it bcos of any memory lekage or any other reason..
Thanks
leo
100 % CPU Usage
Nov 23, 2005HiI am having a real issue with CPU usage by SQL Server, and it is notrelated to a poor query.I have a clients database that I am currently investigating some issueswith. After I perform a standard task using the application, and theresults have been returned to the application the cpu usage remains at100%.Even once the application has been completely closed down the cpu usageremains at 100%. Nothing else is happening.I am at a complete loss as to how to proceed to with investigation ofthis issue (i have been looking at this for over a week using SQLServer tools and Performance Monitor, and eliminating various otherpossibilities)I downloaded Process Explorer, and looking at the threads forsqlservr.exe there is one in particular that is consuming all of thecpu time:MSVCRT.DLLI am running SQL Server 2000 SP4 on the following machine:Windows 2000 SP4Pentium 4 3Ghz (Note this is seen as 2 processors so the reported cpuusage is 50%)1Gb MemoryI also have about 20Gig of free disk space.One other thing, the page faults reported on the Performance tab ofProcess Explorer exceeds 3 million. This is after running theapplication for around 10 minutes.Please can anybody suggest anything at all that might help? I'm sorrythere is not too much information in here but I have not been able tofind out anything useful!Many Thanks in advance.Paul
View 22 Replies View RelatedOdd SQL IN Usage?
Jul 6, 2006Hi guys,Got an odd SQL string that I need to produce that is most probably simple toconstruct but with it being hot in our office, I simply can't get my headaround it....!!Its based around an online emailing facility whereby multiple hotels can beemailed via a single application. Users within the application have accessrights to email only specific hotels.The tables are laid out like this (irrelevant columns left out)...CampaignID, CampaignName, CampaignHotelIDs1 Test Campaign 1,4,5,7,92 Test Campaign2 1,2UserID, UserName, UserHotelIDAccess1 Test User 1,6,72 Test User 2,7Now on the stats page I want to give users access to view ONLY sentcampaigns to which they have access to view, I was considering the IN SQLstatement to achieve something like this...'WHERE CampaignHotelIDs IN UserHotelIDAcess'....but that doesn't want to work, can anyone give me any ideas to get thisworking within just a single SQL query?Cheers, @sh
View 7 Replies View RelatedCPU Usage
Jan 24, 2008Hi,
I have 2 SQL Server 2005 instances in one server. We noticed that CPU usage is high in the server. Is there is any posibility to know how many percentage each SQL Server instances is taking?
LIke Total usage is 60%.
Instance 1 is 40%
Instance 2 is 20%
regards
Wilson Thomas
VDI Usage
Aug 29, 2007Hi all,
I want to write a backup application for SQL server.I have read the VDI specifications. I want to know whether I can use the VDI just to freeze and thaw without taking the snaphot and the backup. Or is there any other way to do the same ?
Thanks in Advance.
Too Much Memory Usage
Mar 26, 2008Good day to all,
I'm new here, so I don't know if this is the right forum to post my problem. I have a web application written using C# .net 2005 (W/ajax). The application has a module that uploads data from excel file to the sql server 2005 database. w/c is by the way, i'm using SQL 2005 Express Edition, the app can upload up to more than 10,000 records from an excel file. Everything is ok until it was deployed in a test environment, while having a run through with the system, the application encounter an error after which, we cannot log in to the system anymore. I restarted the server (web and sql server in 1 machine running winxp) then I can log-in again in the system. When I'm tracing where the problem came from, I noticed that the memory usage of sqlservr.exe increases everytime the app connects to the server. I already fix some code to close some objects that might have caused the high memory usage, then I run sp_who in the management studio and there are still connections used by the app AWAITING COMMAND. Then I manually kill (using kill spid) connection that are left opened by the application. But the mem usage of sqlservr did no decrease. Is there a way to release the memory usage of sqlservr.exe? In ASP.Net ? I have a hint that this has been causing the error. Thanks a lot.
Memory Usage.
Nov 25, 2001Hi,
it seems that every day SQL Server 2000 has some kind of memory leak,
the memory usage creeps above 150000 approximately 3 times per day. Is this normal? It starts at about 13000.
Is there any way that I can monitor what is causing the memory usage to be so high and maybe rectify it?
Thanks for the help.
Steve