T-SQL (SS2K8) :: Check How Much Space Is Allocated To Database?
Dec 11, 2014how can i check how much space is allocated to the database in SQL Server
View 2 Replies
ADVERTISEMENT
EM Doesn't Show Database Space Allocated Info , Etc
Mar 26, 2001Hi, all,
I found that the SQL2000 EM does not show database space allcoated information, as well as tables and indexes size while SQL 7.0 does.
Someitmes these information are fairly handy.
is there any other easy ways to find out the same info from SQL2000
through Em, or elsewhere ??
Thanks
Anthony
Can I Find The Space Allocated For A Database W/ Query Analyzer Using 7.0?
May 15, 2001My group is trying to ensure that there is a sufficient amount of cushion between the space allocated and the current size of a database. I know that I can check this using the Enterprise Manager. Is there a stored procedure or a systems table that holds this information. I know that sp_spaceused will give me the unallocated space, but i want the allocated space. Any suggestions?
View 2 Replies View RelatedTrans 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?
Problem 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.
Space Allocated In SQLServer
Jun 18, 2002We are using SQL 2k.Can we shrink the space allocated in SQL databases.Please help.
View 1 Replies View RelatedTotal Space Allocated In SQL 2000
Feb 2, 2004Could anybody tell me how to find out the total space allocated to a database excluding the log files in SQL server 2000. Thanks
View 1 Replies View RelatedAdjusting Data Files Space Allocated On The Fly
May 2, 2005We have a database that doesn't seem to be growing on its own correctly (or seems like it isn't). This database has been running really slow for the last week and we think this may be whu
Current size is listed at 963mb while allocated size is 959mb. Trans log size is set to auto-grow and is allocated 4mb
Can we change that number on the fly, and also change the the growth settings without affecting the system?
Being a live system we obviously do not want any downtime, but believe this will help with our slowdown.
Any other ideas would also be appreciated
Thanks!
Shrink File Tool From EM Took All The Empty Space Allocated To Data File
Jul 20, 2005I've production sql server 7 sp3 on windows NT. I had a 8GB data file ofwhich 5GB were used and 3GB were unused. I wanted to take back the unused3GB.So I did the following with EM GUI:1. I tried to "truncate fre space from end of the file". Didn't truncatethe file. I believe there was no empty space at the end of the file.2. Next I chose the option to "shrink file to 5GB". And to my horror thedata file instead of taking just 5GB took the empty spaces also and the sizeof the used data file went to 8GB.Any idea what's going on?TIA,SP
View 2 Replies View RelatedCheck Space Left
Sep 13, 2007Hi,
I understand that the Express Edition can create databases up to a size of 4GB. Is there a way to check the space left for a particular database via C++?
Thanks in advance.
Regards
Melvin
Check All Databases For File Space Used
Aug 29, 2006Hi There
As part of monitoring i want to hourly check the data file percentage used for each database, to monitor growth and know in advance when a data file will be full.
However i do not want to write a job for ever single database , this instance may have up to 100 databases that = 100 jobs.
So i want to write a job that checks the percentage space used for all databases.
My first dilema is that i cannot loop through databases dynamically, by that i mean if i use a cursor that loops through database names, and i dynamically build sql the say 'USE @DBNAME' and execute it the cursor context stays local you do not actually change database context.
So how do i loop though databases, i have found
sp_msforeachdb, however this is undocumented in BOL.
Secondly how do i check the percentage of space used for the data file or files for a database, i could use DBCC SHOWFILESTATS, however this is also not documented in BOL.
Obviously i would rather use documented methods.
So bottom line what tsql could i use to check the percentage of file space use for all databases?
Thanx
How To Check Amount Of Disk Space Left?
Jul 16, 2004Here is one of the MCDBA exam question.
Does anyone explain how to do the following?
I click new alert, and selected 'Sql server performance condition alert' Type
But I don't know which object and counter I should choose to get disk space left.
Thank you..
Q: You are the administrator of Microsoft Windows 2000 Advanced Server computer. The server is also
running SQL Server 2000 and contains a database named Sales. The server is configured as shown in
the exhibit.
The sales database is configured by using the default options and increases by 50MB per day. You
want an operator to be notified automatically before the database runs out of disk space. You also
want to minimize the amount of administrative overhead necessary for this database.
What should you do?
A:
Configure systems monitor to log an alert if the hard disk database contains less than 600MB of disk
space. Configure SQL server agent to send an e-mail message notifying the operator of the system
monitor alert.
Explanation: The solution with least administrative effort is to set up a system monitor alert to log when
disk space is below a certain threshold, here 600MB. Then configure the SQL Server agent to trap this event
and send an e-mail notifying the operator when this occurs.
Note: Using SQL Server Agent, you can define alerts and configure responses to alerts.
SQL Server Agent monitors the Windows application log and compares each SQL Server event logged with
the alerts that have been defined. If a match is found, an event alert fires. SQL Server Agent can also
monitor specific SQL Server performance object counters and fire a performance condition alert when the
value for the counter is less than, equal to, or greater than a defined threshold. The Create Alert Wizard can
be used to configure the SQL Server agent for alerts.
Transact SQL :: Check For Mount Points And Retrieve Available Space
Jun 8, 2015I have to check for existence of mount points on drives and retrieve the Space information for those mount points. Is this even possible with t-sql??
View 2 Replies View RelatedT-SQL (SS2K8) :: Check Multiple Columns With One Value
May 21, 2014I keep thinking this can be done in one query:UPDATE H
SET Phone1 = NULL
FROM Dialer.dbo.Agentless_Hold H
INNER JOIN Dialer.dbo.PhoneLookup D ON H.Phone1 = D.Phone;
UPDATE H
SET Phone2 = NULL
[Code] ....
I need to NULL any phone number in the Hold table where the number is in the Lookup table.
T-SQL (SS2K8) :: Blank Space In Integer Data Type Column View
Apr 4, 2014I am dealing with what I believe is Oracle that is the source of a SQL View.
I am seeing a data type of Integer in the View, but I am not able to see what makes up that View. When I query the View, I can see that an Integer data type column is storing a blank space. I use ISNUMERIC(ColumnName) = 0 and there are a lot of rows that show as a zero length blank space, or text, or something. I just know that it is not an Integer.
I have attempted to CAST and Convert this value, but it will not. I have changed the data type on the table that is being inserted in too, and it still fails with a Conversion error. I have tried REPLACE(), but still the same conversion error.
T-SQL (SS2K8) :: Check Constraint On Multiple Columns
Jul 25, 2014I have a case where if the Id field is a specific value, I don't want to allow null in another field, but if the Id value <> a specific value, null is ok.
In the example below, inserting the first record should succeed, the second should succeed, and the 3rd should fail. Right now the 2nd two fail. I gotta be missing something easy, but I can't figure it out.
USE tempdb
GO
IF OBJECT_ID('tempdb.dbo.CheckConstraintTest') IS NOT NULL
DROP TABLE tempdb.dbo.CheckConstraintTest;
CREATE TABLE CheckConstraintTest
[Code] .....
T-SQL (SS2K8) :: Check If Variable Is Empty Or Null?
Sep 9, 2014declare @user varchar(30) = ''
if(@user is not null or @user <> '')
BEGIN
print 'I am not empty'
END
ELSE
BEGIN
print 'I am empty'
ENd
The output should be 'i am empty' but when i execute this i am getting ' i am not empty'. even i did browse through but i don't find the difference in my logic.
T-SQL (SS2K8) :: Check Variable Values Dynamically
Nov 9, 2014I have created dynamic sql to declare variables based on columns from the table and i set values to those variable now here is the issue . i want to check the variable values how do i do that dynamically
drop table test
create table test
(
id varchar(10) not null,
col1 varchar(10) ,
col2 varchar(10)
[Code] .....
Now my next step is verify if the variable is blank or not how do i do that ?
How do i verify all of the columns one after the other .
I am after the statement like this dynamically
-- IF NOT (@col1 = '') THEN set @SQL = @SQL + '[col1] = ' + @col1 + ' '
--IF NOT (@col2 = '') THEN set @SQL = @SQL + '[col2] = ' + @col2 + ' '
I need to check if the columns are blank or not dynamically as i do not want to hard code the column names there.
T-SQL (SS2K8) :: Return A Count Per Unique Check In Invoice Table?
May 12, 2014We have a table that has customers invoices and payment records. In some cases a customer has 10 lines with 10 different invoice numbers but may have paid 2 or more invoices with one check. I need to know how many unique payments were made per customer.
Cust# Inv# Chk#
1 109 101
1 110 101
1 111 102
3 112 10003
2 113 799
2 114 800
1 115 103
3 116 10009
2 117 799
1 118 103
So I need the statement to update the customer table with the annual payments
Customer Table
Cust# Payments
1 3
2 2
3 2
I get close but just not getting it to sort itself out.
T-SQL (SS2K8) :: Check How Many Rows Delete Updated Per Day And Store It In Another Table?
May 8, 2015how to track how many rows updated or deleted per day in a single table and load the information in another table .
View 7 Replies View RelatedThe Log File For Database 'Home_alone' Is Full. Back Up The Transaction Log For The Database To Free Up Some Log Space.
Feb 4, 2008
Hi all
Iam working in Prodcution ENV,Please help how make space
The log file for database 'Home_alone' is full. Back up the transaction log for the database to free up some log space.
Maximum Of DBprocess Already Allocated
Mar 10, 2004Hi all,
Sql server 7.0
our client is getting the below error while accessing the web application
Maximum number of DBPROCESSes already allocated
when we check the server from our side its working fine.
please let us know is the solution for this.
TIA
Failover Cluster Not Using All Allocated RAM
Apr 23, 2007We are running on SQL 2000 on Windows 2003. We have active-passiveclustering set up. We have 16 GB of RAM on each box. This pastweekend, we failed over. I noticed that the Total Server Memory wentfrom about 15.5 GB to 8 GB. I have been trying to figure out why thishas occurred. Any help would be greatly appreciated.
View 1 Replies View RelatedSQL 2000 SP4 EE 32 Bit - SQL Is Not Using All Of The Memory Allocated To It.
May 17, 2007I recently changed the max. memory option in SQL from 24 GB to 30GB but the perfmon counters still only show 24 GB. Any ideas on why it is not recognizing the change? The server has Win 2003 EE and 32 GB of RAM.
View 5 Replies View RelatedError Message This Record Has Been Allocated Elsewhere
Dec 6, 2007 Hi i get an error whenever i try to insert some data into my database, the error basically says that the record has been inserted elsewhere, this might be from the way my code has been formed, can you see any mistakes in my code below; 1 --This stored procedure allocates serial numbers to a rep.
2 --Allocating by taking the Rep_ID as the search criteria input values as input parameters
3 --and start and end range of serial numbers.
4
5 CREATE PROCEDURE wc_Stock_Allocate_Update @Start_RangeID int, @End_RangeID int, @Rep_ID int, @IsAllocate bit, @ROWCOUNT int out , @TotalRequestedCards int out ,@Product_ID int , @OutputMsg varchar(200) out
6
7 AS
8 --BEGIN TRANSACTION
9
10 DECLARE @Start_Range_ID int
11 DECLARE @End_Range_ID int
12
13 SELECT @Start_Range_ID = Serial_ID FROM tblSerial WHERE Serial_No=@Start_RangeID AND Product_ID=@Product_ID
14 if IsNull(@Start_Range_ID,0)<1
15 begin
16 set @OutputMsg = 'Invalid Start Serial No'
17 return
18 end
19 SELECT @End_Range_ID = Serial_ID FROM tblSerial WHERE Serial_No=@End_RangeID AND Product_ID=@Product_ID
20 if IsNull(@Start_Range_ID,0)<1
21 begin
22 set @OutputMsg = 'Invalid End Serial No'
23 return
24 end
25
26 DECLARE @strSQL varchar(5000)
27 DECLARE @ThereIsCriteria bit
28 --DECLARE @TotalRequestedCards int
29
30 SET @ThereIsCriteria =0
31 --SET @TotalRequestedCards = (@End_RangeID - @Start_RangeID)+1
32 DECLARE @RangeDifference INT
33 DECLARE @TotalAlreadyAllocated int
34 SELECT @RangeDifference = COUNT(*) FROM tblSerial WHERE Serial_ID >=@Start_Range_ID AND Serial_ID <= @End_Range_ID
35 SELECT @TotalAlreadyAllocated = COUNT(*) FROM tblSerial WHERE Serial_No >=@Start_RangeID AND Serial_No <= @End_RangeID AND (isnull(Rep_ID,0)>0)
36 --SET @TotalRequestedCards = @RangeDifference - @TotalAlreadyAllocated
37 --if @IsAllocate=0
38 SET @TotalRequestedCards =@RangeDifference
39 IF @IsAllocate = 1
40 BEGIN
41 SET @strSQL = 'UPDATE tblSerial SET Rep_ID=' + LTRIM(RTRIM(CONVERT(CHAR(10),@Rep_ID))) +
42 ', Allocation_Date=GetDate() WHERE Serial_No BETWEEN ' + LTRIM(RTRIM(CONVERT(CHAR(10), @Start_RangeID ))) + ' AND ' + LTRIM(RTRIM(CONVERT(CHAR(10), @End_RangeID ))) + ' AND (tblSerial.Rep_ID=0 OR tblSerial.Rep_ID IS NULL) AND Product_ID=' + ltrim(rtrim(convert(varchar,@Product_ID)))
43
44 END
45 ELSE
46 BEGIN
47 SET @strSQL = 'UPDATE tblSerial SET Rep_ID=0
48 , Allocation_Date= ' + CHAR(39) + '1/1/1999' + CHAR(39) + ' WHERE Serial_No BETWEEN ' + LTRIM(RTRIM(CONVERT(CHAR(10), @Start_RangeID ))) + ' AND ' + LTRIM(RTRIM(CONVERT(CHAR(10), @End_RangeID ))) + ' AND (tblSerial.Rep_ID='+ ltrim(RTRIM(CONVERT(CHAR(10),@Rep_ID))) + ') AND Product_ID=' + ltrim(rtrim(convert(varchar,@Product_ID)))
49
50
51 END
52
53
54 exec (@strSQL)
55 SET @ROWCOUNT = @@ROWCOUNT
56
57 --Make sure this has saved, if not return 10 as this is unexpected error
58
59 IF @ROWCOUNT<1
60 BEGIN
61 RETURN @@error
62 END
63 ELSE
64
65 BEGIN
66
67 print @Start_RangeID
68 DECLARE @Tstamp timestamp
69 DECLARE @Stock_ID int
70
71 IF @IsAllocate = 1
72 BEGIN
73
74 declare @Stock_IDpass int
75 declare @StockSTatus_IDpass int
76 declare @ToRep_IDpass int
77 declare @Start_Range_IDpass int
78 declare @End_Range_IDpass int
79 declare @Product_IDpass int
80
81 set @Stock_IDpass =0
82 set @StockStatus_IDpass =2
83
84 set @ToRep_IDpass = @Rep_ID
85 set @Start_Range_IDpass = @Start_RangeID
86 set @End_Range_IDpass = @End_RangeID
87 set @Product_IDpass = @Product_ID
88
89
90 exec wc_Stock_Update 0,2,@Rep_ID, @Start_RangeID, @End_RangeID, @Product_ID,0
91
92 END
93 ELSE
94 BEGIN
95 DECLARE @Start_Serial_No int ,@End_Serial_No int
96 SELECT @Start_Serial_No =Serial_No FROM tblSerial WHERE Serial_ID=@Start_RangeID
97 SELECT @End_Serial_No =Serial_No FROM tblSerial WHERE Serial_ID=@End_RangeID
98
99 exec wc_Stock_Update 0,2,0, @Start_RangeID, @End_RangeID, @Product_ID,1
100
101 END
102
103 END
104
105 GO
SQL Server 2000 Not Using All Memory Allocated To It.
Aug 1, 2007One of our servers has 20GB of memory and SQL server has been allocated 18.5 GB out of it, however SQL Server only uses about 9GB or so.
OS: Windows 2003 Server
MS SQL Server 2000 SP4
awe enabled
min & max server memory (run values) are: 18432
/PAE switch in boot.ini
any help why SQL server is not using 18.5 GB allocated to it, would be greatly appreciated?
SQL 2012 :: How Indexes Allocated On Pages
Nov 6, 2014How Indexes are allocates on pages? And If a CREATE INDEX Statement Executed on a query Window, Query processor meets and executes these query. However it was meet, who decides to separate indexes onto pages? Storage Engine or Query Processor(Query Optimizer)? Does it work like UPDATE-Statements in Query Optimizer?
View 4 Replies View RelatedThe Database File May Be Corrupted. Run The Repair Utility To Check The Database File. [ Database Name = SDMMC Storage Cardwinp
Oct 9, 2007yes,I have an error, like 'The database file may be corrupted. Run the repair utility to check the database file. [ Database name = SDMMC Storage Cardwinpos_2005WINPOS2005.sdf ]' .I develope a program for Pocket Pcs and this program's database sometimes corrupt.what can i do?please help me
View 4 Replies View RelatedAvailable Space At My Database
Jan 9, 2002Hi, I work on SQL Server 6.5,
and I want to make a documentation about available space of my databases.
So, I must know how many databases space used everyday ?
If somebody know how to get this available space or how to make this script.
I hope you want to tell me about this.
Thanks you so much for your cooperation.
Database Space Used
Jun 18, 2003hiiii would appreaciate if somebody can help me to figure how can I monitor my database space used, available bu using Transact SQL through SQL Analyzer...
Thanks
Database Space Used
Oct 8, 1998When I go to the GUI in Enterprise Manager to check the space used by my database it says 11,790.05. It also says that my database is 13GB.
My database`s size should be 10GB and my log space is 3 GB.
Is this a case where my database is full and is using space in my log?
Is there another way to detemine how much space my data is actually occupying?
Thanks,
Bob
Database Space
Dec 28, 2000Hi !
When i try to check the space used in one of the databases in my server
I don't get the correct statistics on the bar graph. The statistics show that the database size & the space used are same.It is not the same for all the databases.
The database size is 17500MB.I did increase the database size to 18000MB
but the situation has not improved.
I tried running the sp_space used and i get the following output:
database_name database_size unallocated space
------------------------------ ------------------ ------------------
VSI 18000.00 MB -3553.26 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
22070542 KB 19325744 KB 2689192 KB 55606 KB
Why does the 'unallocated space show in -ve ?
Also, the database size as u can see is 18000MB but the reserved space shows 22070542MB.
Now i have a new problem, the Log also seems to have run out of space.
when i run the dbcc sqlperf(logspace) i see that the space used is 100%.
Please help.
Thanks in advance
Siv
Database Used Space
Jun 29, 1999Is anyone knows how to get the space used for both the data and the log
of a database using the system tables ? I was able to get the database size
but don't know how to compute for the space used.
Thanks in advance.
Vic