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
ADVERTISEMENT
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?
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.
Total 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!
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 View RelatedEM 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 RelatedShrink 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 RelatedUnallocated Space In SQLSERVER
Aug 17, 2006HI,
I am short of space on one of our sqlservers and I want to claim unallocated space for one of my databases and give it back to OS.
I guess it is relatively simple task to do but unfortunately I am unable to find such a command. Can somebody help me and provide me that command.
Rgds
Wilson
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 RelatedDB Engine :: Find Total Memory Allocated To Server And CPU Utilization
Apr 30, 2015I am searching for query to find total memory allocated to sqlserver, Β and how much being used utilized as well cpu utilization percentage .Β
View 10 Replies View RelatedHow 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] ....
SQL Server Admin 2014 :: Analysis Services Not Taking Allocated Memory?
Mar 14, 2014We have run into an issue on a dedicated SSAS 2012 SP1 server where the allocated memory is not being utilized, causing some slowness in use, connections, and queries.
Total Memory on the server is 512, and after startup, the utilized memory gets up to a max of 60GB and stops there. Checking the Resource Monitor, msmdsrv.exe is only taking around 39GB overall. With the current properties, that should be at 330GB. Am I missing something in the settings or in configuration that should be changed?
Version: SQL Server 2012 SP1 Enterprise (11.0.3000)
OS: Windows Server 2012 Datacenter - Fully patched and up to date
Databases: 2 Tabular models
Server: 512GB RAM
Current memory configuration:
Hard Memory Limit - 0 (Default)
LowMemoryLimit - 65% (Default)
TotalMemoryLimit - 95% (Default is 80)
VertiPaqMemoryLimit - 60% (Default)
VertiPaqPaginingPolicy - 1 (Default)
MemoryHeapType - 2 (Default)
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 RelatedSQL Server 2008 :: Identify Size Of Data Cache Allocated From Buffer Pool
Oct 20, 2011How do i check the size of the datacache allocated from the buffer pool by sql server?
DMV or anything to show me the pool allocation sizes for the various pools in sql server i think i may be able to work from there.
SQL Server 2008 :: Does List Of Page IDs Allocated For A Table Change During Index Rebuilds
Jul 16, 2015We noticed a deadlock 3-4 weeks ago on a table (table1) and deadlock graph was captured.
When I am analyzing the deadlock graph, page number using DBCC PAGE, I am getting the object id for a different table (table2). But deadlock graph shows the name of the object as table1.
Is it possible that subsequent defragmentation of indexes would have caused the respective page id to got re-allocated to a different table? I checked the deadlock graph lately only after 3-4 weeks.
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.
Class Method Is Smoking Fast When Executed Outside Of SQLServer, Dog Slow As A CLR Function Is SQLServer - Anyone?
May 10, 2007We have a static class that makes an HTTPWebRequest to get XML data from one of our vendors. We use this as input to a stored proc in SQLServer2005. When I compile this class and call it from a console application in visual studio it executes in milliseconds, everytime. When I compile it, create the assembly and clr function and execute it in SQLServer, it takes around 14 seconds to execute the first time, then on subsequent requests it is again really fast, until I wait for 10 seconds and re-execute, once again it is slow the first time and then fast on subsequent requests. We do not see this behavior when executing outside SQLServer. Makes me think that some sort of authentication is perhaps taking place the first time the function is run in SQLServer? I have no idea how to debug this further. Anyone seen this before or have any ideas?
Here is the class:
Code Snippet
using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.IO;
namespace Predict.Services
{
public static class Foo
{
public static string GetIntradayQuote(string symbol)
{
string returnQuote = "";
HttpWebRequest request = (HttpWebRequest)(WebRequest.Create("http://data.predict.com/predictws/detailed_quote.html?syms=" + symbol + "&fields=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,30"));
request.Timeout = 1000;
HttpWebResponse response = (HttpWebResponse)(request.GetResponse());
StreamReader streamReader = new StreamReader(response.GetResponseStream());
returnQuote = streamReader.ReadToEnd();
streamReader.Close();
response.Close();
return returnQuote;
}
}
}
When I run call it from a console app it is fine.
I compile it into a dll and then create the assembly and function as follows:
Code Snippet
drop function fnTestGetIntradayQuoteXML_SJS
go
drop assembly TestGetIntradayQuoteXML_SJS
go
create ASSEMBLY TestGetIntradayQuoteXML_SJS from 'c:DataBackupsCLRLibrariesTestGetIntradayQuote_SJS.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
go
CREATE FUNCTION fnTestGetIntradayQuoteXML_SJS(@SymbolList nvarchar(max)) RETURNS nvarchar(max) AS EXTERNAL NAME TestGetIntradayQuoteXML_SJS.[Predict.Services.Foo].GetIntraDayQuote
go
declare @testing nvarchar(max)
set @testing = dbo.fnTestGetIntradayQuoteXML_SJS('goog')
print @testing
When I execute the function as above, again, really slow the first time, then fast on subsequent calls. Could there be something wrong with the code, or some headers that need to be set differently to operate from the CLR in SQLServer?
Regards,
Skipper.
Problem Unicode Data 0x2300 In SQLServer 2000 SQLServer 2005 Express
Sep 20, 2006Hi experts;
I have a problem with unicode character 0x2300
I created this table
create table testunicode (Bez nchar(128))
Insert Data
insert into testunicode (Bez)values('’')
with 2 Unicode characters
= 0x2300
’ = 0x2122
Selecting the data
select Bez from testunicode
I see
"?’"
’ = 0x2122 is ok but instead of 0x2300 there is 0x3f
When I modify the insert statement like that ( 8960 = 0x2300 )
insert into testunicode (Bez)values(NCHAR(8960)+'’')
and select again voila i see
"’"
Does anyone have an idea?
Thanks
Trying To 'load' A Copy Of A SQLServer 2000 Database To SQLServer 2005 Express
Apr 18, 2008
I am trying to 'load' a copy of a SQLServer 2000 database to SQLServer 2005 Express (on another host). The copy was provided by someone else - it came to me as a MDF file only, no LDF file.
I have tried to Attach the database and it fails with a failure to load the LDF. Is there any way to bypass this issue without the LDF or do I have to have that?
The provider of the database says I can create a new database and just point to the MDF as the data source but I can't seem to find a way to do that? I am using SQL Server Management Studio Express.
Thanks!!
MIcrosft SQLServer Best Practices Document On Securing SQLServer
Jul 29, 2005I'm chasing after a documetn that was available on one of the Microsoftwebsites that was titled somethign like "MS SQL Server Best Practices"and detailed a nyumber of best practices about securing the server.Included in this was revoking public access to the system tableobjects.Can someone post the URL where I can pick this up, or drop me a note oncontacting them for a copy of the document?
View 2 Replies View RelatedReplacing Sqlserver 2000 With Sqlserver 2005 Express
Jun 14, 2006I have an app that uses a sqlserver 2000 jdbc driver to connect to a sqlserver 2000.
Is it possible to do a direct replacement of sqlserver 2000 with sqlserver 2005 express just by reconfiguring the app to point to the express? The app would still be using the sqlserver 2000 jdbc driver to try and make the connection.
If that is a possibility, what can be some differences in the configuration? Previously with 2000 the config information I entered is:
server name: "machinename"( or ip). I've also tried "machiname/SQLEXPRESS"
DB name: name of db instance
port: 1433(default)
user and pass.
My attempts so far results in
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket."
and
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL."
Upgrade SQLServer Mobile (.sdf) Database To SQLServer 2005
Feb 9, 2006Hello,
I have an SQLServer Mobile database, and I would like to know if there is a way to upgrade it to SQLServer 2005 (.mdf) database. My database has no records in it, just the structure (tables etc). What I am actually asking is if I can create automatically a new SQLServer 2005 Database with the same structure as my existin SQLSErver Mobile database
Thanks in advance,
TassosTS
SQLSERVER 2005 X64 Linked Server To SQLSERVER 7.0
Jun 20, 2007Hello people.
I am in the process of planning a server upgrade to sql2005 x64.
I created 2 linked servers: one to a SQL2000 sp4 server and one to a SQL7.0 SP3.
I have the following error when I query the linked servers.
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "IVDM2K". The provider supports the interface, but returns a failure code when it is used.
I am aware of KB 906954.
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
I applied the instcat.sql on the SQL2000SP4 server and my linked server issues for that one are gone.
However, I ran the instcat.sql script on the SQL7.0 sp3 server and the linked server is still giving me an issue.
Can someone help me find a solution to this?
Post Update For SQLServer SP2--is There One For SQLServer Express?
Apr 18, 2007Regarding KB935356, is there a "post" service pack 2 update for SQLServer Express?
Thanks.