Adjusting Data Files Space Allocated On The Fly

May 2, 2005

We 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!

View 4 Replies


ADVERTISEMENT

Trans Log-&>space Allocated 27GB, Space Used 100MB

Mar 2, 2005

Hi.. 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?

View 1 Replies View Related

Problem With Space Allocated For Transaction Log Space

Dec 5, 2001

Hello,

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.

View 1 Replies View Related

Shrink File Tool From EM Took All The Empty Space Allocated To Data File

Jul 20, 2005

I'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 Related

Space Allocated In SQLServer

Jun 18, 2002

We are using SQL 2k.Can we shrink the space allocated in SQL databases.Please help.

View 1 Replies View Related

Total Space Allocated In SQL 2000

Feb 2, 2004

Could 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 Related

T-SQL (SS2K8) :: Check How Much Space Is Allocated To Database?

Dec 11, 2014

how can i check how much space is allocated to the database in SQL Server

View 2 Replies View Related

EM Doesn't Show Database Space Allocated Info , Etc

Mar 26, 2001

Hi, 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

View 1 Replies View Related

Can I Find The Space Allocated For A Database W/ Query Analyzer Using 7.0?

May 15, 2001

My 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 Related

SQL Server 2008 :: Identify Size Of Data Cache Allocated From Buffer Pool

Oct 20, 2011

How 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.

View 9 Replies View Related

What To Do When There Is Not Enough Space Left For Log Files?

Jun 1, 2008

After an INSERT INTO ... SELECT ... FROM ... query (and after waiting for ~40 minutes) I got the following errors:





Code Snippet

Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.SORT temporary run storage: 164288922910720' in
database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files,
dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused,
see the log_reuse_wait_desc column in sys.databases
I checked the space in the disk partition and it has only 10MBs left, so clearly there isn't enough space left.

I need a quick solution, and since I know nothing about logs and db backups in SQL Server, I thought to install SQL Server 2005 in another PC with a 30GBs primary partition for XP and applications and a 350GBs empty partition which would be enough for log files. So my question is how to setup SQL Server 2005 to use that big partition.

View 6 Replies View Related

Need To Obtain Space Used On Database Log Files

Mar 10, 2000

I need to be able to obtain the storage space used for database log files.
I can do this in version 6.5 but have been unable to accomplish this in version 7.0.
Does anyone have a suggestion?

View 1 Replies View Related

Full Back Does Not Release Used Space Of Log Files

Sep 14, 2000

SQL7: After doing a full backup Enterprise
Admin still shows "Transaction log space"
in use. Isn't it that way that full backup
releases all space formerly used by
log files?

Greetings from Mannheim, Germany
Ricardo

View 3 Replies View Related

Adjusting Collation

Apr 18, 2007

Can you point me in the correct direction in SQL2005 Server to adjust the SQL Server database collation case-insensitive, accent-sensitive, Kana-sensitive, and width-sensitive. We are trying to configure it to run with SharePoint Services.

Thanks in advance for any assistance that you might be able to provide.

-Austin

View 1 Replies View Related

SQL 2012 :: Alerting When Tempdb Files Have X% Free Space?

Aug 13, 2014

I have a tempdb split into 4 files (5 if you include the log).

Autogrowth is disabled on the mdf/ndf files so that they can be used round robin (1 file per logical CPU).

Is there a way to be alerted when there is x% of free space left?

I know hwo to check the free space via t-sql but want to be able to be alerted. I could run a sql job that reports the free space and send a database mail message if under x% but wondered if there was a built in (or better) method?

I also have SQL Sentry.

SQL 2012 Standard

View 9 Replies View Related

Database Files Free Space (SQL Server 2005)

Nov 4, 2006

Dear friends

I need to report the amount of free space in each datafile of a database.

I have tried sys.dm_db_file_space_usage , but the documentation says that it only works for the TempDB.

Please help.

Regards

Parviz

View 5 Replies View Related

DB Design :: Clear Space On The Drive - Shrinking Log Files

May 26, 2015

I have a database which has log file size 300 GB. As the drive is filling up i need to clear the space on the drive, for that i have to shrink the log file. 

Unfortunately i dont have option to take backup of the database.And i am not able to shrink the file now. Is there any way to shrink the log file with out taking backup of it ?

View 5 Replies View Related

Do I Save Disk Space Storing Images In Db Vs. Loose Files?

Jan 16, 2007

Hello --
I'm building an app that will allow users to create their own photo galleries.  At this point, I'm planning on storing all photos as byte arrays in SQL server image fields.
Besides the organizational benefit, is there a space benefit to doing this?  That is, if I have 1MB of .jpg's, will those same images take up less than 1MB of file space within the database?
One of the reasons I ask is that most hosting plans out there seem to offer more "normal" disk space than is allocated for the database, so I'm trying to make a best plan to accommodate what will probably end up being the biggest disk space consumer in my app (the photos, that is).
Any other recommendations re: this scenario (hosting, best practices) are appreciated.
TIA,
Eric

View 8 Replies View Related

Adjusting Parameter Dropdown Width

Oct 25, 2007

Hi there,
May be a kind of dumb question, but I have a multi-value parameter that I would like to give users the ability to select from in a SSRS 2005 Report and I was wondering if there was a way to adjust the width of the drop down so the user wouldn't have to scroll horizontally to see a full parameter item. It looks like it cuts off after about 30 characters.

Any help here would be appreciated.

Thanks!
Rich

View 1 Replies View Related

Adjusting Report Width Automatically

Dec 27, 2006

Hi All,

We are having a table report which has set of columns where the visibility is made false based on report parameters. It works fine and the table automatically shrinks. But, the page width remains as early and has lot of blank space in it. It also causes printer to print many blank pages.

Is there a way to control pagewidth dynamically? Thought of adding a custom assembly to control page width - but how to access the page that is getting displayed. "this" will refer to the the assembly class instead of report as such.

Any help is highly appreciated.

Regards, kart

View 4 Replies View Related

Database (model) Cannot Be Opened Due To Inaccessible Files Or Insufficient Memory Or Disk Space

Aug 4, 2015

How to fix this SQL error event logg 17204 and 17207 ?

View 8 Replies View Related

Adjusting Time Zone And Daylight Saving In SQL

Aug 6, 2007

Hi,
I have this simple SQL query which SELECTs the fields according to the criteria in WHERE clause.  SELECT callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber,
dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 , '01-01-1970 00:00:00') AS dateTimeConnect,
dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600, '01-01-1970 00:00:00') AS dateTimeDisconnect,
CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration
FROM Calls
WHERE
(callingPartyNumber = ISNULL(@callingPartyNumber, callingPartyNumber)) AND
(originalCalledPartyNumber = ISNULL(@originalCalledPartyNumber, originalCalledPartyNumber)) AND
(finalCalledPartyNumber = ISNULL(@finalCalledPartyNumber, finalCalledPartyNumber)) AND
(duration >= @theDuration) AND
((datetimeConnect - 14400) >= ISNULL(convert(bigint,
datediff(ss, '01-01-1970 00:00:00', @dateTimeConnect)), datetimeConnect)) AND
((dateTimeDisconnect - 14400) <= ISNULL(convert(bigint,
datediff(ss, '01-01-1970 00:00:00', @dateTimeDisconnect)), dateTimeDisconnect))
 If you notice, in the SELECT, I add 3600 (1 hour) to adjust the time according to the current DayLight saving setting. Similarly, while comparing in WHERE clause, I subtract 14400 seconds (4 hours) to adjust the comparison according to EST (-5 GMT, but doing -4 GMT since daylight saving) and daylight saving.
Also, dateTimeConnect and dataTimeDisconnect fields save time in UTC seconds.
Now this application may be used by clients in different timezones. The application may be hosted on their server which would mean a different time zone. What would be the best way to make this time zone and daylight adjustment more dynamic instead of hardcoding the values.
 

View 18 Replies View Related

Adjusting Column Width In Queried Dataset

Apr 17, 2007



I am trying to eliminate the extra space in columns that have been returned from a DB query...the tables have character lengths of 40, I would like to return just the necessary characters.



Thanks in advance.



Mark.

View 3 Replies View Related

Adjusting The Priority Of The Replication Process (SQL 2000)

Oct 5, 2006

Hi,

I need information on adjusting the priority of the replication thread(s) within SQL Server 2000. All I've been able to find is how-to's on adjusting the priority of the SQL Server process itself, as well as information on how to adjust the relative priority of changes made to the database by subscribers.

Here's the situation, from time to time our replication system goes down, it starts itself up after waiting 5 minutes but this causes a problem. When its starting up it seems to be taking up far too many CPU cycles. As such the server is unable to process SQL queries, which forces its subscribers to time out. This is a bad thing as the subscribers are control systems on an assembly line. What I want to do is lower the priority of replication so that the server can still process queries while the replication agent is restarting itself. Is this possible? If so how can it be done?

Thanks,

Geoff Dupuis

View 1 Replies View Related

SQL Server Admin 2014 :: Separate Data Files / Log Files / TempDB / Backups

Jan 9, 2015

I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.

View 2 Replies View Related

Reporting Services :: Print Data With Blank Space And Move To Continue Data To Next Page

May 15, 2015

I am using SQL Server report 2008/2012 (SSRS) and my report viewer contains body content with 3 Row groups.
While printing the report,  data print with blank space and move to continue data to next page. 

Departure flight : 70 rows
First Page            : 42 rows printed
Second Page      : 23  rows printed  [ Supposed to be print 28 ,  if the total count of records more than 23 and less than 42 then the page print only 23 records ]
Third Page           : 5 rows printed

Departure flight : 42  rows
First Page            : 42  rows printed [Report max. record allowed to print 42 rows so if total record is 42 then print perfectly ]

Departure flight : 26 rows
First Page            : 23 rows printed [Supposed to be print 26, if the total count of records more than 23 and less than 42 then the page print only 23 records ]
Second Page      : 3 rows printed

View 3 Replies View Related

Maximum Of DBprocess Already Allocated

Mar 10, 2004

Hi 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

View 2 Replies View Related

Failover Cluster Not Using All Allocated RAM

Apr 23, 2007

We 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 Related

SQL 2000 SP4 EE 32 Bit - SQL Is Not Using All Of The Memory Allocated To It.

May 17, 2007

I 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 Related

Error 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

 

View 1 Replies View Related

SQL Server 2000 Not Using All Memory Allocated To It.

Aug 1, 2007

One 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?

View 8 Replies View Related

SQL 2012 :: How Indexes Allocated On Pages

Nov 6, 2014

How 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 Related

Empty Log Files And Data Files

Sep 17, 2004

Hi!
I'm using replication with two database on SQL 2000,when begin, the log files size is 50mb and the data files size is 150mb. But now the log files size is 2Gb and the data files size is 4Gb. I would like to decrease the log files and the data files ??? How do i do this???
(I using Truncate and shrink doesn't change )
Thanks!!!

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved