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.
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?
We are using SQL 2k.Can we shrink the space allocated in SQL databases.Please help.
View 1 Replies View RelatedCould 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 RelatedWe 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!
how can i check how much space is allocated to the database in SQL Server
View 2 Replies View RelatedHi, 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
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 RelatedI'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 RelatedHi,
I am trying to do this:
UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks
I 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.
I 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] ....
This 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 RelatedHi Folks,
It has come to my attention recently that my Transaction Log 200MB and a 1Gb Data Device has gotten much bigger than it used to be after
I deleted several thousand rows of data.
It used to be under 5% space used and now has settled at around 70 % space used. I have dumped the transaction log with no_log and done
backups immediately after on several ocassions and this has not solved the problem. I have also increased the log size from 150MB to 175MB
but this does not seem to have solved my problem.
Anyone got any ideas on how to decrease the % used log space in the Transaction Log ?
Is it bador unwise to have so much of the Transaction Log space used up ?
Thanks in advance,
Alan
How 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.
I am new to SQL and might be missing something very easy. I have a situation where the space allocated to the transaction log of a database is extremly large (5 Gig). I can not manually reduce it. This gives me a "Error 21335: [SQL-DMO]The new DBfile size must be larger than its current size." This is a problem because the increase in size has taken all available space on the server.
Mike
Hello,
I am having a strange problem, never encountered before. I have a database that has been allocated 3000MB of Data space and 1200 of Log space. When i go an check the Properties of Database it give a count of 662.23 Data space available and 47.99MB of log space available. That`s weired. I tried truncating the log the Log space available is still the same and neither there is a change in the data space. It should`nt eat that much of data space according to my analysis.
Any insight
Please help ASAP
vIvEk
Is there a way to know how much space is free in each transaction log file of the same database?
Example:
A database with 3, 1GB files for transaction log: A, B and C created in this sequence.
From what I have read, since SQL Server 2005 writes to a single transaction log only, I guess if the transaction log is using 2.5GB than A and B are full and B is only half full. Is this correct?
Thanks.
Hi friends ,
I have a database with around 2 GB space for the data and 5.8 GB for the
transaction log .
Now the problem is i do not have any more space on the system and data files requires more space than 2 G.B to execute some stored procedures .
Is it possible to decrease some space of the transaction log , say from 5.8 GB to 2 GB and allocate it to the data files .
My data and log files are on different drives . I did not find anything related to this topic in the BOL .
Can somebody help me with this problem ? Anthing related to this issue will be of great help to me since i have no expertise in this field .
Thanks and Regards
Christine S.
The transaction log takes up a lot of space on my database, and even after I try truncating the log, doing a transaction log backup, and then shrinking it, I am not allowed to reduce the size of the transaction log to less than 250MB. Is there some reason why this space is required?
View 1 Replies View RelatedHi,
I started working in one company as a DBA where the Ex-DBA left the company long time back. So all the databases have huge transaction logs (50-80GB) in size. But the problem is-
1. HDD space on the m/c its only 45GB. When I ran TLog backup cmd on that m/c, the hdd space was started reducing around 1GB/min and in few minutes I saw hdd space left is only 15GB so I stopped cmd (It had completed only 37% of the backup).
Second time again I tried to take TLog backup on hdd placed on n/w path but again I saw it was eating local disk space very fast.
So my question is, Is it like when you take Tlog backup of size say 80GB it will use 80GB local hdd as a temporary storage?
2. For this I found 1 workaround- I took Full backup then I restored it on some other m/c, then shrun the log files to few MB's then I again took Full backup of this shrunk db and restored it on production.
Is this only solution to my problem(Very less space on production's local HDD)? If anyone knows better soln than this then plz suggest me.
Thanks in Advance.
today 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 RelatedI am having a problem backing up my database and TLog files due to alack of local diskspace. The db file is about 30GB and the TLog isabout 20GB each on a different hard disk. Each disk doesn't haveenough available space to accomadate a backup. I also can't shrink thefiles because part of that procedure would require a backup.Question: Can I use a redirected drive for the backup media? Is therea way to trick SQL into allowing this? If the answer is no, doesanyone have a suggestion as to want I should do? I am in the processof requesting more disk space,but that could take a while.Thanks,
View 3 Replies View RelatedI 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
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.
I need to create a proc. which would send a warning via xp_sendmail when the database space gets within 10% of allocated space. I have written one which uses sp_spaceused however this proc requires DBCC udateusage to be run in order to get accurate results. This would work but i don't know how quickly the data returned by sp_spaceused becomes corrupt therefore how often DBCC should be run. I ran DBCC on our main production server and it took a considerable amount of time. Basically I am looking for another way to do this without using sp_spaceused. Any ideas
View 2 Replies View RelatedDoes anyone have any knowledge on how to find out the log space left in a database? I've tried to use the DBCC sqlperf (logspace) and it gave me the numbers but I only need one of the numbers. How can I retrieve it? Someone please let me know if you have any info. Thanks a million.
View 7 Replies View RelatedThe command sp_spaceused @updateusage = true
returns following result. How do I have to calculate the single values that it gives me an equation:
database size = data size plus reserved space minus etc.
Somehow it is not clear to me how it is split up.
Thank you
mipo
database_namedatabase_sizeunallocated space
------------------- -----------------------------------
opms 3498.88 MB807.41 MB
reserved data index_size unused
------------------ ------------------------------------ ------------------
2479576 KB 1050720 KB 1344792 KB 84064 KB
I have a 12 GB database growing at 25 MB daily. The server will soon run out of space, 3 GB left. I cannot buy a new server or new RAID subsystems at once, and want to prevent the server being filled up fast.
What are the best ways? Can you share with me some of your thougts or comment on the following:
1) Archive old data in many tables across servers -
Can SQL Server do it directly across server or we need to select into temp database and back it up to tape or using bcp out and in?
2) Put the >9Gb disk dump to another server. The daily disk dump takes up a lot of space.
3) Dump to the tape directly, saving 9 GB disk space of dump device.
4) ? ?
Hi All,
Which table in the master database has the information about free space available for a particular database? I am trying to find the information that is displayed under the general tab in EM when you look at the database properties.
thanks.
Hi!
I need to write a sql to find out the space used for the sql server database .
Please help.
Can i use sp_spaceused?
suresh
I have a fields with the space char in data.
How to find a space character in query?
'Smith David'
displaying the property of a database? What does it mean?
I am sure when this happens but now when I right click on a database and
select property, the space available= 0.00MB.
I still have physical space on the server.
When viewing the taskpad.
Space allocated:
data 5500MB used, 1700 MB free
log 15MB used, 300 MB free
Please help. Thank you