Hi all
Is there anyway to find out using TSQL for the used space VS what is available for TLOG file? Cause the GUI won't allow me [Database] > Report > Disk Usage. It said that Unable to display the report becuase the database has a compatibility level of 80 ?!?!
Thanks
I have a SQL Server 2012 DB in Full recovery mode that has never had a TLOG backup. the log is huge 200+GB.
I tried doing a transaction log backup but there is not enough space on the Disk.
How can I reclaim this log space in SQL Server 2012?
I am getting periodic failures of my Transaction log. SQL
Server 7.0 Service Pack 3 on NT 4.0. IT is not occurring
in a set pattern. I have attached the error. I am unable
to find anything on the Internet or n TechNet can someone
help me out.
Thanks;
Message is as follows:
Microsoft (R) SQLMaint Utility (Unicode),
Version [Microsoft SQL-DMO (ODBC SQLState: HYT00)]
Error 0: [Microsoft][ODBC SQL Server Driver]Timeout expired
Hi,
My Transaction log has 0% Log Space available. I have performed a dump transaction with no_log but to no effect. I have also extended the log size from
100MB to 200MB and this has not made any inmpression either after several dump database statements also.
The Data Device was 500MB and I extended it to 800MB and again performed a backup. Has anyone any idea how to recover from this situation ?
I can make queries on the data fine but the several backups(and Truncate log) I have made are still not creating any space for my Transaction log.
Please help !
Thanks in advance,
Alan
I am considering some disaster recovery scenarios.
Lets assume my MDF is gone - the disks are dead.
The LDF is on a different disk channel. Lets assume its fine.
Can I make a "final" TLog backup from the "good" LDF file?
Maybe copy some-earlier-MDF file into place, would that enable a TLog backup from the LDF file?
'Coz if I can then I could use that as a route to getting a zero-loss recovery - make a final TLog backup, and then restore the whole lot from last FULL + All TLog backups thereafter.
Kristen
recently i noticed that my trans Log size is not increasing(it is constant) , but the databse size is increasing at high speed.
why is it so? earlier the reverse used to happen i.e databse size increased only after a certain period. and log size increased at const. rate.
Hello,
I have an SQL 2005 maintenance plan that backups up the t-logs of my production db every 15 minutes. I want to begin log shipping over to a warm-standby secondary server. The network share, log shipping folder is not the same folder location as my maintenance plan folder. My question is, do I need to disable the maintenance plan for tlog backups in order for log shipping to be sucessful? Or will the dual backups to seperate backup folder locations cause a failure in the secondary server restore process?
I suspect the latter but wanted a second opinion.
Thanks.
With Migrating from SQL 2000 to SQL 2005 I realise that there are a few options available.
A SQL 2000 backup is able to be restored into 2005 and is workable however there are some issues with internals that have changed. I have identified some DDL which would need to be modified to work correctly.
If the Backup is restored and left in a recoverable state does 2005 allow for 2000 TLog backups to be restored as well ? I am assuming that as you can restore a full db backup that this is allowed.
I am asking the question as i have a very short and fixed deadline of moving from 2000 to 2005. A new datacentre is being moved to and the old DC is being turned off. all new hardware is being placed in the new DC so i need to move from the old to the new with very little downtime to the DB or app.
My approach is as follows
1. Full DB Backup (2000) copied across to 2005 server and loaded being left in a recoverable state.
2. Tlog backups copied across on scheduled basis and loaded to a point in time.
3. Test new 2005 DB with app/web frontend
4. Bring both in sync again and then perform 1 final Tlog backup, copy and load bringing app online again with very little downtime.
Has anybody tried this approach ? does anybody see any issues with my approach ? all comments welcome on this.
Thx
i have a very big database and number of people are working on it.. it's log file size is increaseing very day too much.. i am taking log back every 30mint...
i dont' know that wethare i need to truncate the log file after taking the log file backup or not.. i am taking differentail backup every day and full backup every week...
Please tell me do i need to trucate the log file to reduce the file size or i hv to leave as it....
Thanks..
I am in the process of formulating recommendations with respect to the purchase of additional storage for our current SQL 2012 SharePoint (2013) instance. My recommendation is to purchase separate storage (i.e, 15k disks) for the TempDB and Tlogs respectively (two sets of raid 10 disks). Currently, this server is hosting several instances, including SP, using two arrays (one for database and the other for Tlogs).
I am attempting to find information/recommendations on how to go about projecting the amount of storage for each of these while factoring in for growth.
Additional Details:
how to best formulate a reasonable estimate. Our largest content database belongs to IT and is currently ~80GB. That said, this is currently an outlier. The remaining content databases are less than 10GB (most are less than 2-3 GB). However, SharePoint will be used for digital document imaging in addition to, eventually, replacing file shares as our primary document storage medium once we roll it out.
Our current tempDB is ~400MB, but the instance was recently started a few days ago, as we had to failover to our backup server for hardware maintenance. I do not have any historical data on TempDB growth at this time. Also, I don't know how useful this would be given we have not fully deployed yet.
I have a vendor database that has a stored procedure that runs a long time.Eventually, the database runs out of log space.
Setting the database to FULL and doing frequent log backups does not work.
The log does not get truncated during this log backups.
The stored procedure in question has SET XACT_ABORT ON statement at the beginning.
I've already shrunk the tlog from 350 GB to 313.My DB Server (2008 R2 Sp2) cannot be restarted and the db cannot go offline or detach due to company policy.My DB after changing from full to simple mode still has 313GB tlog file and when I run DBCC OPENTRAN I get Transaction information for database 'DB'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (2882:26:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Which means this DB is participating in a High Availability process like replication, mirroring or log shipping.
So I run EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1.
This is useful when there are replicated transactions in the transaction log that are no longer valid and you want to truncate the log.
But I get an error:
Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.
There are currently 9 connections and all are sleeping.What else can i try in order to shrink the tlog file?
I have a 14GB database whose data content is legacy and is described as static. The log file is significantly large and continues to change size mostly increasing by 2-5GB a day (~60GB now) I have observed over the past two days; it shrank once unexpectly by a few GB. The instance is hosting other databases such as: EnterpriseVaultDirectory, EnterpriseVaultMonitoring, EnterpriseVaultStore, and NetPerfMon - might these seemingly unrelated data sources be involved?
I am trying to a trace to find traffic against the tables, no such luck.
Web applications are playing against it for queries but there should be no UPDATEs beign applied. I can only suspect that other unknown applications are performing operations but have yet to find unexplained connections.
Are there any other reasons why this type of log file activity would happen merely due to queries or stored procedure calls?
Lets also state, "mirroring, indexing, replication" are not at play. I know logging "Full" is not necessary as "Simple" should suffice but I am still hunting down why UPDATEs might be getting through. I realize I might adjust the migrated SQL 2000 security model to deny updates to find what breaks but would rather not take that iniative yet.
The installation is a fresh SQL 2005 Standard setup with SP2 applied; the databases were upgraded.
Hi,
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] ....
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?
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 RelatedHello,
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.
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.
If I create an adhoc db backup that takes, say 30 miuntes to complete, should I suspend the tran log backups that run every 10 minutes, until the full backup is complete?
Drew
I 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
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
Need to know how to shrink a 6.5 DB. When going inot Shrink dialog it won't let me change it to any amount less thatn what it is already set to.
DBCC won't let me do it bc I have to be in single mode.
Any other options? I know in 7/2K you can:
Backup Database dbname with truncate_only and then restore the bak file.
I have a database with almost 3 tables each contains atleast 1 million Rows, and My hard disk space is 14G.
I want to insert another table some queries i have selected from different tables.
I got this Error:
(254118 row(s) affected)
(935885 row(s) affected)
Server: Msg 1105, Level 17, State 2, Procedure sp_ProcessRowData, Line 68
Could not allocate space for object 'Processed_Data' in database 'Mydb' because the 'PRIMARY' filegroup is full.
Any Help............
Regards Mr. Mumin
Thanks All,
Hope this question is not too dumb for this forum.. but here goes.
I have a DB on SQL Server 2K which is the backend for our Great Plains System.
When I look at the properties of the Database from Enterprise Manager, It shows Size: 1370.57mb and space available: 0.00 The ZERO space available is what I'm concerned about. The DB is in FULL recovery mode with a nighly Full backup and transaction log backups throught the day. The DB is set to autogrow and the file growth is set at 20 percent.
My question is.. should I be worried about the ZERO space available and if so, how do I correct this??